Case Study · Lead Analyst Project
Fintech Analytics Engineering Platform
A portfolio workflow that transforms a historical Freddie Mac and FRED data snapshot into tested, documented, BI-ready mortgage delinquency marts using dbt Cloud, BigQuery, and Power BI.
8Recorded dbt Models
24Recorded Passing Tests
3BI-Ready Marts
Jan–Jun2025 Dashboard Window
The Analytical Need
Mortgage delinquency reporting requires consistent definitions, reliable loan-month data, and traceable transformations before a dashboard can be trusted.
This project simulates how an analytics team could organize that work: clean raw financial and macroeconomic inputs, centralize delinquency logic, test key assumptions, document lineage, and expose curated marts to Power BI.
Data Flow
01
Historical Source Snapshot
Uses Freddie Mac acquisition and monthly-performance inputs plus the FRED 30-year mortgage-rate series. The inspection notebook references Q1 2025 files, while the dashboard covers January through June 2025.
02
Staging Models
Renames generic fields, applies explicit types, and creates clean one-to-one source representations without adding business aggregations.
03
Reusable Delinquency Logic
Intermediate models align the data to a loan-month grain and centralize monthly delinquency flags so downstream calculations do not duplicate logic.
04
Marts, Tests, and Power BI
Three fact marts support monthly, credit-band, and servicer analysis. YAML documentation, tests, lineage, and a dbt exposure connect the curated output to the included Power BI dashboard.
Evidence in the Repository
Quality
Recorded dbt results
The included dbt Catalog screenshot records eight successful models and 24 passing tests at project completion.
Lineage
Documented transformations
Source, staging, intermediate, and mart layers are represented in the project DAG with model and selected column documentation.
Reporting
Mortgage delinquency dashboard
Power BI presents monthly delinquency rates, lending channels, state filtering, and mortgage-rate context from a curated mart.
Governance
Explicit BI exposure
A dbt exposure documents the dashboard owner, business context, and dependency on the mart used by the included report.
Metric Interpretation
The mart is aggregated at one record per loan per reporting month. Dashboard volume cards represent loan-month observations, not distinct loans.
Delinquency rate is calculated as delinquent loan-month observations divided by total loan-month observations within the selected filters.
The credit-band and servicer marts support additional analysis, while the included dashboard is connected to the monthly delinquency mart.
Limitations
Portfolio boundary: this is a completed historical snapshot, not a live production pipeline. The raw Freddie Mac files and populated BigQuery source tables are not included, freshness status in the recorded dbt evidence is unknown, and the project is not scheduled for ongoing ingestion. Reproduction requires obtaining the source data, loading matching schemas, and supplying a BigQuery dbt profile.