One warehouse for 40+ lender schemas that all disagreed

The situation

The business aggregated portfolio data from dozens of lending partners and sold analytics back to them and to institutional buyers. Each partner delivered differently: nightly database replicas, SFTP flat files, a few APIs, one monthly spreadsheet that a person still opened. Every new partner meant a bespoke pipeline, and every partner's system upgrade broke it.

The constraint

Reporting could not pause. Buyers received figures on a fixed cycle, and several of those figures fed regulatory submissions, which meant a restatement was not a mildly embarrassing correction but a reportable event. Everything had to be built alongside the running system and cut over source by source.

What we built

A single landing contract

Regardless of transport, every source lands as immutable, partitioned raw files with a manifest: source, extraction window, row count, checksum. Reprocessing became possible without asking a partner to resend anything — which is the capability that makes everything downstream safe to change.

Schema contracts with graded failure

Each source declares an expected schema and column-level expectations. Additive changes warn and continue. Breaking changes quarantine the batch, alert the owning analyst and hold the affected downstream models rather than propagating nulls into a published report. The distinction between warn and fail is what stopped the monthly firefight.

Conformance before modelling

A conformance layer maps each lender's vocabulary to a common account, borrower and event model — including the awkward cases, like partners whose delinquency buckets are computed on different day counts. Mappings live in version-controlled configuration with tests, not in pipeline code.

Metrics defined once

Portfolio metrics are defined in the semantic layer and consumed by the BI tool, the customer-facing API and the scheduled extracts. Before this, the same metric had three implementations and two of them were subtly wrong depending on which quarter you asked about.

Cost as a design input

Iceberg with partition and sort layout chosen against the actual query mix, incremental models instead of full refreshes, and a monthly review of the ten most expensive queries with an owner attached.

What changed

Onboarding a new lender is now mostly configuration, so commercial deals stopped being gated on engineering capacity. The reporting cycle runs without the standing bridge call it used to require.

What we would do differently

We migrated the two largest sources first to prove the architecture under load. It worked, but it also meant the highest-risk cutover happened when the team was least practised at cutovers. Next time we would sequence a mid-sized source first, rehearse the runbook on it, then take the big ones.

Outcomes

New lender onboarding

weeks → about a week

Reports failing on upstream change

a few a month → effectively none

Query cost per monthly cycle

cut by roughly half

Client identity withheld under a mutual NDA. Figures are illustrative — rounded and directional, meant to show the shape of the change rather than an audited result. We will walk through the real numbers, and how they were measured, under NDA on a call.