HMDA County Panel

Large-scale public mortgage-loan pipeline using DuckDB, Parquet, schema harmonization, and QA checks.

Reproducible data-construction pipeline for public HMDA mortgage-lending records from 2007 through 2024. The project converts annual loan-level files into Parquet, builds a DuckDB database, normalizes geography across the 2018 schema break, and exports analysis-ready county-year and lender-county-year panels.

The project is built as a research foundation rather than a final causal study. It makes the data source, assumptions, exclusions, schema breaks, and generated outputs legible enough for later work on lender presence, geographic expansion, and fintech or nonbank mortgage lending.

Data-construction and research-readiness artifact; not a final causal analysis

GitHub repo Research-methods memo County-year CSV Lender-county sample

312.1M Loan-level HMDA rows processed across annual files.

58,006 County-year rows in the main public aggregate.

8.9M Lender-county-year rows generated locally as a Parquet panel.

Pipeline

Downloads public HMDA loan-level source files for 2007-2024 from historic CFPB files and the HMDA Data Browser API.
Converts annual raw files into Parquet to avoid full-file in-memory pandas workflows on tens of gigabytes of raw data.
Builds a DuckDB database with canonical loan-level views, geography-normalized views, metadata tables, and QA tables.
Normalizes pre-2018 and post-2018 geography, including state FIPS, county FIPS, and prefix mismatch checks.
Builds county-year and lender-county-year aggregates for later descriptive, panel, and geographic-expansion research.

Quality Checks

County-year aggregate record totals reconcile to the geography-normalized loan-level rows with usable county FIPS.
Missing-geography rows are excluded from the main county-level tables and preserved in QA tables.
County-year and lender-county-year outputs pass non-null key and duplicate-grain checks.
Application-style denominators separate non-purchase application records from purchased-loan records.

Limitations

The current pipeline does not classify lenders as fintech, nonbank, or bank. Pre-2018 lender IDs use respondent IDs and post-2018 IDs use LEIs, so lender continuity across the schema break requires external identity enrichment. That is the right next step before calculating fintech lender shares or running fintech-expansion research designs.