A public MBS disclosure decision product with exact source reconciliation, 38 released metric contracts, evidence-backed investigations, and a complete digest-verified data release.
Freddie Mac MBS Disclosure Intelligence
Can an MBS disclosure release be trusted, what changed, and what evidence should be investigated next?
The product starts with exact source disposition and schema evidence, then connects certified metrics to correction-aware cohort and concentration views and immutable investigation context.
Delivered a public trust-to-investigation workflow over 693,640,933 physical disclosure records, 264,922,553 loan-period facts, 9,240,038 security-period facts, and all 167 approved source and derived release artifacts.
Data Engineer
Built a governed Freddie Mac MBS pipeline that reconciles 693.6 million records into 264.9 million loan-period and 9.2 million security-period facts with zero rejected, duplicate, or quarantined rows.An MBS disclosure operations analyst, market-data analyst, capital-markets reviewer, or hiring manager.
Context and stakes
The decision context behind the system.
The release turns a large Freddie Mac disclosure corpus into an auditable trust-to-investigation workflow. It covers 693.6 million physical records, releases every supported contract, and preserves complete row-level source and derived data in a verified 45.7 GB release.
This product answers the first disclosure question: can the release be trusted? It reconciles every source record, exposes only supported measures, connects movements to evidence, and publishes the complete approved data release.
Approach
A bounded approach shaped by the published constraints.
The product starts with exact source disposition and schema evidence, then connects certified metrics to correction-aware cohort and concentration views and immutable investigation context.
Design constraints
- Eleven methodology, two field-extension, and three external contracts remain unreleased.
- The product makes no borrower, investment, valuation, trading, hedging, or causal claims.
- The cited assistant remains disabled in the public runtime.
- Power BI remains parked and is not required for the delivered workflow.
Decision journey
The system moves through an inspectable sequence.
Each stage below comes from the published project manifest.
- Stage
Validate 125 source archives
Official archives pass package, member, schema, period, duplicate, and reconciliation controls before downstream use.
- Stage
Reconcile every physical record
Correction-aware SQLite security facts and compressed loan-period partitions preserve native grain and provenance.
- Stage
Build correction-aware security facts
A versioned metric engine emits components and formulas only for 38 approved source-backed contracts.
- Stage
Calculate and independently verify
The browser product, investigation workflow, and semantic API consume one governed product contract.
- Stage
Connect the verified release
GitHub Pages serves the decision workflow while GitHub Releases distributes all approved row-level source and derived artifacts outside Git history.
- Stage
Publish the static product
Publish the static product and every approved source and derived artifact with exact integrity verification
Interactive architecture
The published architecture, made interactive.
Exact contracts validate packages, members, schema eras, periods, and duplicates. SQLite stores correction-aware security facts while compressed partitions preserve 264.9 million loan-period facts. A versioned engine emits 38 supported contracts. The browser and semantic API share one governed payload, and investigations preserve immutable evidence context. GitHub Pages serves the interface while GitHub Releases distributes all 167 approved source and derived artifacts under a verified manifest.
Drag to pan / use controls to zoom / select any stageRead the architecture as text
- Official archives pass package,Stage 1 / Python
Official archives pass package, member, schema, period, duplicate, and reconciliation controls before downstream use.
- Correction-aware SQLite security factsStage 2 / SQLite
Correction-aware SQLite security facts and compressed loan-period partitions preserve native grain and provenance.
- A versioned metric engineStage 3 / gzip CSV
A versioned metric engine emits components and formulas only for 38 approved source-backed contracts.
- The browser product, investigationStage 4 / JavaScript
The browser product, investigation workflow, and semantic API consume one governed product contract.
- GitHub Pages serves theStage 5 / GitHub Actions
GitHub Pages serves the decision workflow while GitHub Releases distributes all approved row-level source and derived artifacts outside Git history.
Pivotal decisions
Technology choices recorded with their trade-offs.
These choices are derived from the project's published decision record.
Use SQLite plus compressed period partitions within the published boundary
Preserves exact detail and correction lineage across hundreds of millions of records with bounded memory.
- Constraints
- SQLite and compressed partitions preserve exact detail with bounded memory and no recurring warehouse cost, but processing is batch-oriented.
- The static product removes request-time compute and mutation exposure, while bulk review uses downloadable release assets instead of arbitrary public queries.
- GitHub Releases supports the measured data volume, but flattened asset names depend on the manifest for path restoration.
- Alternatives
- Provision a managed warehouse before measuring need.
- Criteria
- Fit for the stated scope / Inspectable evidence / Explicit operating boundary
- Selected
- SQLite plus compressed period partitions
- Why
- Preserves exact detail and correction lineage across hundreds of millions of records with bounded memory.
- Trade-off
- Processing is batch-oriented and offers no shared arbitrary-query concurrency.
- What supported it
- 693.6M Physical records reconciled: Complete M4 source population
- Revisit when
- Retain compressed partitions and bounded-memory streaming while measured refresh and verification targets pass.
Use Static GitHub Pages product within the published boundary
The public workflow needs no request-time database, credential, mutation surface, or recurring compute.
- Constraints
- SQLite and compressed partitions preserve exact detail with bounded memory and no recurring warehouse cost, but processing is batch-oriented.
- The static product removes request-time compute and mutation exposure, while bulk review uses downloadable release assets instead of arbitrary public queries.
- GitHub Releases supports the measured data volume, but flattened asset names depend on the manifest for path restoration.
- Alternatives
- Expose the semantic API as a permanent public service.
- Criteria
- Fit for the stated scope / Inspectable evidence / Explicit operating boundary
- Selected
- Static GitHub Pages product
- Why
- The public workflow needs no request-time database, credential, mutation surface, or recurring compute.
- Trade-off
- Public users receive governed views and data downloads rather than arbitrary queries.
- What supported it
- 38 of 38 Supported contracts released: Versioned M5 metric catalog
- Revisit when
- Add a hosted query plane only when a measured concurrency or latency requirement justifies recurring infrastructure.
Use GitHub Releases for complete data publication within the published boundary
The measured asset set fits provider limits and keeps immutable data beside source, documentation, and workflow evidence.
- Constraints
- SQLite and compressed partitions preserve exact detail with bounded memory and no recurring warehouse cost, but processing is batch-oriented.
- The static product removes request-time compute and mutation exposure, while bulk review uses downloadable release assets instead of arbitrary public queries.
- GitHub Releases supports the measured data volume, but flattened asset names depend on the manifest for path restoration.
- Alternatives
- Commit data to Git, publish only summaries, or add another dataset provider.
- Criteria
- Fit for the stated scope / Inspectable evidence / Explicit operating boundary
- Selected
- GitHub Releases for complete data publication
- Why
- The measured asset set fits provider limits and keeps immutable data beside source, documentation, and workflow evidence.
- Trade-off
- Flattened asset names require the manifest to restore logical paths.
- What supported it
- 167 of 167 Public data artifacts verified: Complete 45,675,556,283-byte data-v1 release
- Revisit when
- Reopen Power BI only when a Windows runtime can produce engine evidence against the same contracts.
Results and evaluation
Measured results within the disclosed evaluation boundary.
- Method
- Explicit source disposition and join reconciliation
- Meaning
- Complete M4 source population
- Limitation
- Eleven methodology, two field-extension, and three external contracts remain unreleased.
Limitations and next steps
Useful evidence, bounded claims.
What this does not prove
- Eleven methodology, two field-extension, and three external contracts remain unreleased.
- The product makes no borrower, investment, valuation, trading, hedging, or causal claims.
- The cited assistant remains disabled in the public runtime.
- Power BI remains parked and is not required for the delivered workflow.
- The static product exposes no arbitrary SQL, row API, or shared investigation mutation.
What I would do next
- Retain compressed partitions and bounded-memory streaming while measured refresh and verification targets pass.
- Add a hosted query plane only when a measured concurrency or latency requirement justifies recurring infrastructure.
- Reopen Power BI only when a Windows runtime can produce engine evidence against the same contracts.
- Add metrics only with explicit source, methodology, denominator, timing, correction, and golden-case evidence.
This public disclosure analytics project supports descriptive release operations and MBS investigation. It does not make borrower, investment, valuation, trading, hedging, causal, or production-availability claims. Source materials remain attributable to Freddie Mac and subject to applicable provider terms.
Explore the rest of the work