A live collateral-surveillance workbench that connects portfolio delinquency movement to the complete masked Freddie Mac CRT loan-period disclosure record.
Freddie Mac CRT Disclosure Analytics
What problem does Freddie Mac CRT Disclosure Analytics solve?
Delivered bounded public investigation across 20,439,666 masked loan-period rows, exact portfolio rate-and-mix attribution, private object storage, authenticated data retrieval, and a zero-incremental-cost production release.
Delivered bounded public investigation across 20,439,666 masked loan-period rows, exact portfolio rate-and-mix attribution, private object storage, authenticated data retrieval, and a zero-incremental-cost production release.
Analytics Engineer
Built a Freddie Mac CRT surveillance product across 20.4 million loan-period records, reconciling 292 deal-month groups exactly and decomposing portfolio D60+ change into signed deal rate and exposure-mix contributions.A capital-markets credit analyst, collateral-surveillance reviewer, data engineering reviewer, or hiring manager assessing analytical controls and public-cloud delivery.
Context and stakes
The decision context behind the system.
The live workbench connects portfolio-level D60+ movement to full masked Freddie Mac CRT disclosure records. It covers 20.4 million loan-period rows, distinguishes deal deterioration from portfolio contribution, and keeps complete Parquet objects behind authenticated server-to-server access. The release costs $0 incrementally and carries a hard free-plan request ceiling, but remains a descriptive portfolio demonstration rather than an operational investment or borrower-decision system.
This product answers a capital-markets review question: which CRT exposures deteriorated, how much did each deal contribute, and what disclosed records support the investigation? It preserves the complete approved archive, makes rate-versus-mix attribution explicit, and serves masked records through a private-storage boundary with measured evidence and honest decision limits.
Approach
A bounded approach shaped by the published constraints.
Delivered bounded public investigation across 20,439,666 masked loan-period rows, exact portfolio rate-and-mix attribution, private object storage, authenticated data retrieval, and a zero-incremental-cost production release.
Design constraints
- Stable masking removes the original loan identifier and ZIP3, but other public disclosure fields may remain linkable to the source release.
- The product is descriptive collateral surveillance, not a forecast, causal estimate, tranche-pricing model, investment recommendation, or borrower-decision system.
- Hosted browser evidence covers Chromium only; Firefox, Safari, and assistive-technology sessions remain unverified.
- No independent user study, field performance measurement, or concurrent-load test has been completed.
Decision journey
The system moves through an inspectable sequence.
Each stage below comes from the published project manifest.
- Stage
Archive inventory, schema-width validation,
The private build plane streams the approved Clarity archive into 292 source-faithful Parquet partitions and calculates versioned surveillance measures in DuckDB.
- Stage
Source-faithful Parquet foundation and
A release builder preserves all accepted rows, adds three lineage columns, and replaces the original loan identifier and ZIP3 with stable keyed tokens before publication.
- Stage
Stable keyed replacement of
The 292 immutable masked Parquet assets live in a private Cloudflare R2 bucket with no public bucket endpoint.
- Stage
Complete masked release validation
A Cloudflare Worker accepts only authenticated GET requests for one exact allowlisted object under the fixed release prefix.
- Stage
Immutable upload to private
A Vercel Python function validates deal, period, status, limit, and offset, fetches one partition with a server-side token, and returns no more than 50 rows.
- Stage
Bounded Vercel query function,
The browser renders the risk-committee review docket and record register without receiving storage URLs, service credentials, raw identifiers, or restricted build artifacts.
Interactive architecture
The published architecture, made interactive.
The private build plane validates historical 89, 90, and 93-field layouts, writes 292 source-faithful Parquet partitions, and calculates metric version m8.1.0 in DuckDB. The public release builder retains every accepted row, adds lineage, and replaces loan identifier and ZIP3 with stable keyed tokens. Immutable masked assets move to private R2. An authenticated GET-only Worker exposes one exact object to a Vercel Python function, which validates bounded filters and returns at most 50 records. The static browser surface combines a filed-sheet risk review with masked record pagination and 96-field detail. Source data, storage, serving, and browser lifecycles remain separate.
Drag to pan · use controls to zoom · select any stageRead the architecture as text
- The private build planeStage 1 · Python
The private build plane streams the approved Clarity archive into 292 source-faithful Parquet partitions and calculates versioned surveillance measures in DuckDB.
- A release builder preservesStage 2 · DuckDB
A release builder preserves all accepted rows, adds three lineage columns, and replaces the original loan identifier and ZIP3 with stable keyed tokens before publication.
- The 292 immutable maskedStage 3 · Parquet
The 292 immutable masked Parquet assets live in a private Cloudflare R2 bucket with no public bucket endpoint.
- A Cloudflare Worker acceptsStage 4 · JavaScript
A Cloudflare Worker accepts only authenticated GET requests for one exact allowlisted object under the fixed release prefix.
- A Vercel Python functionStage 5 · Vercel Functions
A Vercel Python function validates deal, period, status, limit, and offset, fetches one partition with a server-side token, and returns no more than 50 rows.
- The browser renders theStage 6 · Cloudflare Workers
The browser renders the risk-committee review docket and record register without receiving storage URLs, service credentials, raw identifiers, or restricted build artifacts.
Pivotal decisions
Technology choices recorded with their trade-offs.
These choices are derived from the project’s published decision record.
Use DuckDB and partitioned Parquet within the published boundary
The measured 20.4 million-row workload meets refresh and query budgets locally while preserving exact source grain and one-file pruning.
- Constraints
- Private R2 separates data retention from Git publication and offers more free-tier headroom than Vercel Blob, but adds one Worker and one shared service token.
- Deal-month Parquet retrieval keeps the serving path simple and bounded, but broad cross-partition queries remain a local analytical workflow.
- The public API caps page size at 50 rows and offset at 150,000, favoring investigation over bulk download.
- Alternatives
- Provision a cloud warehouse before measuring need
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- DuckDB and partitioned Parquet
- Why
- The measured 20.4 million-row workload meets refresh and query budgets locally while preserving exact source grain and one-file pruning.
- Trade-off
- The design is single-user and batch-oriented, but avoids paid infrastructure and extra governance until scale evidence requires it.
- What supported it
- 20.4M Masked loan-period rows: Every accepted standard monthly disclosure row is present across 292 immutable release assets.
- Revisit when
- Measure production latency, cache hit rate, and concurrent browsing before changing the deal-month object design.
Use Stable keyed masking before release within the published boundary
Cross-period investigation needs a persistent pseudonymous loan key without publishing the original loan identifier or ZIP3.
- Constraints
- Private R2 separates data retention from Git publication and offers more free-tier headroom than Vercel Blob, but adds one Worker and one shared service token.
- Deal-month Parquet retrieval keeps the serving path simple and bounded, but broad cross-partition queries remain a local analytical workflow.
- The public API caps page size at 50 rows and offset at 150,000, favoring investigation over bulk download.
- Alternatives
- Publish original identifiers or aggregate away every record
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Stable keyed masking before release
- Why
- Cross-period investigation needs a persistent pseudonymous loan key without publishing the original loan identifier or ZIP3.
- Trade-off
- Stable tokens preserve analytical linkage but do not remove the linkage risk of all other disclosed field combinations.
- What supported it
- 0 variance Shared metric reconciliation: Current UPB, D30+, and D60+ measures matched across all 292 groups shared with the earlier release.
- Revisit when
- Introduce R2 SQL or a hosted analytical database only when cross-partition query demand or measured concurrency exceeds the current bounded function.
Use Private R2 with an authenticated Worker within the published boundary
Object storage separates data lifecycle from Git, retains free-tier headroom, and keeps bucket locations and credentials out of the browser.
- Constraints
- Private R2 separates data retention from Git publication and offers more free-tier headroom than Vercel Blob, but adds one Worker and one shared service token.
- Deal-month Parquet retrieval keeps the serving path simple and bounded, but broad cross-partition queries remain a local analytical workflow.
- The public API caps page size at 50 rows and offset at 150,000, favoring investigation over bulk download.
- Alternatives
- GitHub release assets, public R2, or Vercel Blob
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Private R2 with an authenticated Worker
- Why
- Object storage separates data lifecycle from Git, retains free-tier headroom, and keeps bucket locations and credentials out of the browser.
- Trade-off
- The narrow gateway adds one service and one rotating secret, while avoiding public bulk objects and a nearly exhausted storage allowance.
- What supported it
- 0.588 ms Materialized query p95: Twenty common latest-period watchlist queries over the complete local metric database.
- Revisit when
- Automate immutable monthly release promotion and token rotation after a real new-month archive has exercised the documented runbook.
Use Bounded Vercel query function within the published boundary
Server-side validation and paging support record investigation without shipping service credentials or exposing unrestricted object access.
- Constraints
- Private R2 separates data retention from Git publication and offers more free-tier headroom than Vercel Blob, but adds one Worker and one shared service token.
- Deal-month Parquet retrieval keeps the serving path simple and bounded, but broad cross-partition queries remain a local analytical workflow.
- The public API caps page size at 50 rows and offset at 150,000, favoring investigation over bulk download.
- Alternatives
- Direct browser access or a persistent analytical API
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Bounded Vercel query function
- Why
- Server-side validation and paging support record investigation without shipping service credentials or exposing unrestricted object access.
- Trade-off
- Queries are limited to one deal-month partition and 50 rows per response, which favors review over bulk exploration.
- What supported it
- $0 Incremental deployment cost: Private R2, Workers Free, and the existing Vercel project host the permanent portfolio release.
- Revisit when
- Add Firefox, Safari, assistive-technology, and field-performance evidence before making broader experience claims.
Results and evaluation
Measured results within the disclosed evaluation boundary.
- Method
- Manifest reconciliation and complete scan of the public Parquet collection.
- Meaning
- The public investigation surface is based on the full approved record rather than an aggregate sample.
- Limitation
- Stable masking removes the original loan identifier and ZIP3, but other public disclosure fields may remain linkable to the source release.
Limitations and next steps
Useful evidence, bounded claims.
What this does not prove
- Stable masking removes the original loan identifier and ZIP3, but other public disclosure fields may remain linkable to the source release.
- The product is descriptive collateral surveillance, not a forecast, causal estimate, tranche-pricing model, investment recommendation, or borrower-decision system.
- Hosted browser evidence covers Chromium only; Firefox, Safari, and assistive-technology sessions remain unverified.
- No independent user study, field performance measurement, or concurrent-load test has been completed.
- The public API is intentionally bounded and does not support bulk or arbitrary cross-partition queries.
What I would do next
- Measure production latency, cache hit rate, and concurrent browsing before changing the deal-month object design.
- Introduce R2 SQL or a hosted analytical database only when cross-partition query demand or measured concurrency exceeds the current bounded function.
- Automate immutable monthly release promotion and token rotation after a real new-month archive has exercised the documented runbook.
- Add Firefox, Safari, assistive-technology, and field-performance evidence before making broader experience claims.
This portfolio product uses Freddie Mac public disclosure data with selected fields masked before serving. It supports descriptive collateral-surveillance investigation only and must not be used for borrower decisions, re-identification, outside-data matching, forecasts, causal claims, or investment recommendations.
Explore the rest of the work