Mortgage Lending in New York, 2022 to 2025
PostgreSQL: an analytics project on 1,755,419 real mortgage applications from US Home Mortgage Disclosure Act (HMDA) data, asking who gets access to mortgage credit in New York, on what terms, and whether the answer differs by applicant group once income, product, neighbourhood and lender are held constant. The whole database runs in your browser, with nothing to install.
Tools & Skills
- PostgreSQL star schema design (partitioned fact table, dimensions, bridge tables)
- Joins, Subqueries, Window functions, CTEs & shift-share decomposition
- Data profiling, quarantine rules & executable quality assertions
- Query performance tuning & index analysis
- DuckDB browser playground with a PostgreSQL parity test
Approach
Raw regulatory files from the CFPB and the Philadelphia Fed were landed as untouched text, profiled, then modelled into fifteen tables: a year-partitioned fact table, eight dimensions, four bridges, a quarantine table and a code reference. Because the regulator strips every loan identifier, surrogate keys are derived from an MD5 hash of each source row, so the model rebuilds identically on any machine.
Nothing is deleted. Impossible values are quarantined with the rule they broke, and eight assertions gate every build. The data dictionary is generated from the PostgreSQL catalogue itself, so the documentation cannot drift from the database. The work is written up in eleven phases, each one showing at least one thing that went wrong.
Key Findings
- The lender matters most. Two lenders in the same census tract typically differ by 30.6 percentage points in how often they say no, a larger gap than any other factor measured.
- Group gaps survive income. In 2025, Black applicants were denied 39.6% of the time against 22.4% for White applicants. Among applicants earning over $200k, the rates were 30.2% against 15.7%.
- Group gaps survive neighbourhood. In the 62 tracts with enough decided applications from both groups, the Black denial rate is higher in 60.
- The 2023 tightening was behaviour, not mix. A shift-share analysis showed 76% of the rise in denials came from products denying more, the opposite of my own prediction.
- Denial is not the only failure. Applications closed for incompleteness range from 0.2% to 28.5% between large lenders, a factor of 142.
Data Quality Finding
Profiling found 80,167 rows carrying a code the regulator documents nowhere, and showed that counting HMDA race codes naively overstates multi-race applicants sixfold (106,971 against a true 18,051). Every published row is accounted for: 1,754,846 modelled plus 573 quarantined equals the 1,755,419 published. As HMDA holds no credit score, the findings are presented as disparities that persist after controls, not as proof of cause.
Outcome
A reproducible, fully documented database where every claim is executable. Twenty-two published queries open in the browser playground already loaded and run against the real 1.75 million rows, so anyone can check a figure in seconds or write their own SQL queries.