Replatforming a twelve-year-old warehouse without a reporting freeze

Context

A building society runs its regulatory returns, its product pricing models and its branch performance reporting from a single warehouse, built in 2013 and extended every year since by whoever needed the next table.

By the time we were asked, the platform underneath it was two major versions out of support and the nightly load was finishing after the business day had started. Replacing it was already agreed. Replacing it had also already been attempted twice.

The problem

The warehouse held about 1,400 tables. Tracing readers established that 61% of them had no consumer at all, a proportion nobody had believed before it was measured.

Transformation logic lived in stored procedures, the largest running to just over 3,000 lines and amended by eleven people across a decade. There was no test suite and no specification, so the code was the only surviving statement of what the numbers meant.

The binding constraint was regulatory. Returns go out on a fixed calendar, and a figure in a submitted return has to be reproducible from source records on request. A migration that produced different numbers without being able to explain why would have been a reportable failure rather than a project delay.

Why earlier approaches failed

The 2019 attempt moved the stored procedures to a new engine largely unchanged. Its plan required a four week window in which nothing in the old warehouse could change while the two were reconciled. The business granted eleven days, then withdrew them when a pricing change had to go out, and the reconciliation was never finished.

The 2022 attempt rebuilt from source against a new model, and was the better piece of engineering. It also assumed a freeze, deferred to cutover rather than asked for up front, so the assumption went untested until the work was two years old. It ran for those two years and moved no consumer onto the new platform, because that freeze was never going to be granted either.

The pipeline we built

The third attempt asked for no freeze at all. Both platforms ran together for the whole engagement, and the unit of cutover was one reporting consumer rather than the platform.

1. 01Consumer census

   An inventory of everything that reads the warehouse rather than everything the warehouse holds: 210 reports, extracts, models and downstream feeds, each with a named owner. Ordering the work by consumer rather than by table is what made a per consumer cutover possible at all.

2. 02Source contracts

   A contract per source feed covering column types, nullability, allowed value sets, the meaning of each coded field and the freshness the consumers of that feed need. Contracts are enforced on landing, and a breach quarantines the batch rather than loading it and flagging it afterwards.

3. 03Dimensional model

   Conformed dimensions for party, product, account, branch and calendar, with fact grain agreed with the consumer instead of inferred from the report it currently feeds. Two facts turned out to exist at three different grains in the old warehouse, and reconciling that was the single longest piece of discovery.

4. 04Transformation rebuild

   Stored procedures reimplemented as version controlled, tested transformations. Behaviour was established from observed output rather than from reading the code, because the code had stopped being a reliable description of itself: the 3,000 line procedure was pinned down by 900 differential tests before a line of it was rewritten.

5. 05Parallel running

   Both platforms loaded from the same sources every night for the full 34 weeks. A reconciliation job compares every measure for every consumer nightly and files a variance report, so the question of whether the new platform agrees with the old one is answered daily rather than at a cutover weekend.

6. 06Variance adjudication

   Every variance is triaged into three buckets: the old platform is wrong, the new platform is wrong, or the two definitions genuinely differ. The old platform was wrong more often than anyone expected. Each resolution is written back as a test on the contract or the model, so the same variance cannot reappear silently.

7. 07Cutover per consumer

   A consumer moves when it has reconciled at zero unexplained variance for 20 consecutive business days, at least one of which is a month end. The move itself is a configuration change and reverts inside an hour, which is what let owners agree to go first.

8. 08Decommission

   A table is dropped from the old platform only when no consumer reads it and its lineage is reproducible on the new one. Decommissioning is the last step for each consumer rather than a phase at the end, so the estate shrinks continuously instead of all at once.

What shipped

• A warehouse on the society's Azure tenancy with a conformed dimensional model and enforced contracts on all 31 source feeds.
• A nightly reconciliation harness comparing every measure per consumer across both platforms, owned and run by the society's data team.
• A variance ledger recording every difference found during parallel running and how it was adjudicated.
• A cutover and rollback runbook for a single consumer, exercised twice by the client team before handover.

Outcomes

Each figure below carries the method behind it and the baseline it is measured against, which is the form a result has to take before it means anything.

Consumers migrated with no reporting freeze

Counted against the 210 consumer census taken in week two. The 23 that did not move were retired instead, each having no owner willing to claim it once asked.

187 of 210, 0 days frozen

Nightly load completion

Median completion time of the full nightly load over the final eight weeks of parallel running, against the twelve week median on the old platform immediately before the engagement.

07:42 → 03:05

Unexplained variances at cutover

Every consumer moved only after 20 consecutive business days at zero unexplained variance on the nightly reconciliation. Of the variances adjudicated during parallel running, 41 were errors in the old platform rather than in the new one.

0 at every cutover, 41 old platform errors found

Time to reproduce a submitted return figure

Median time to trace a figure in a submitted regulatory return back to its source records, timed on twelve returns before the engagement and twelve after, using the lineage the contracts and the model carry.

2 to 3 days → 25 min

What next

The reconciliation harness stays in place for the two consumers still reading from both platforms, and the same contract stage is being applied to the pricing feeds, which sat outside this scope.