CutoverGuard

Every record checked. Every bad cutover blocked. CutoverGuard gates financial data migrations on Databricks, catching duplicates, missing rows, wrong amounts and wrong owners totals can't see.

Comment

Inspiration

Every financial company is migrating data: mainframes to the cloud, old CRMs to new ones, document databases to relational ones. Before go-live, teams sign off on a checklist, usually row counts and totals. If those match, the migration ships.

Wrong totals are easy to catch. The dangerous errors are the ones that don't change a total at all: a transaction that lands on the wrong customer, a missing row offset by an extra one, a penny moved from one account to another. Engineers we talked to at Knight Hacks described migrations, especially non-relational to relational ones, as some of the most painful work they do. Ownership has to be rebuilt through ID mappings, and that's exactly where these errors hide.

The stakes are public: TSB's 2018 platform migration cost about £330M plus a £48.65M fine, and RBS's 2012 batch-software upgrade hit 6.5M+ customers. We don't claim we'd have prevented those; they show what a bad cutover costs.

What it does

CutoverGuard is a cutover gate for financial data migrations on Databricks. Run any migration through it: it compares the legacy and migrated data record by record, by key, passes the good migrations and blocks the bad ones.

It catches:

  • wrong totals and row counts
  • duplicates, missing and extra records
  • changed amounts, dates and currencies
  • malformed rows (quarantined, never silently dropped)
  • records attached to the wrong owner, even when every total matches

The output is every finding by key, the signed effect on each account (who is over-allocated and who is under), and one decision: BLOCKED, INCOMPLETE or READY for human review. A failing decision stops promotion: nothing reaches production.

The proof: why totals can't see wrong owners

With transaction amounts \( a_i \), the firm total and an owner's balance are

$$T = \sum_i a_i \qquad B_o = \sum_{i:\,\text{owner}(i)=o} a_i$$

Reassigning owners only permutes which owner each amount belongs to. Every \( a_i \) still appears exactly once, so \( T \), the row count, every daily total and the multiset of owner IDs are all unchanged. Only the \( B_o \) change. No aggregate-only check can detect it, at any data size. CutoverGuard flags transaction \( i \) whenever \( \text{owner}{\text{legacy}}(i) \neq \text{owner}{\text{migrated}}(i) \), and the signed effects always sum to zero.

The result: 7.5 million orders

We built a real migration pipeline on Databricks over 7,500,000 TPC-H orders, with a realistic bug: the customer ID mapping has 25 customer pairs swapped.

Check Legacy vs migrated Result
Row count 7,500,000 = 7,500,000 ✅ PASS
Total amount identical ✅ PASS
Quarantined rows 0 ✅ PASS
Customer mappings 50 wrong 🔴 BLOCKED
Order ownership 811 wrong owners 🔴 BLOCKED

That's 0.011% of orders, invisible to every total. An independent SQL count confirms exactly 811. The promotion gate fails, and nothing is promoted. With the mapping fixed, the same gate passes and promotes all 7,500,000 rows.

Databricks dashboard: totals pass, ownership blocked

We also ran 700 seeded trials with six injected defect types plus clean controls:

  • Totals plus a checksum caught 0% of ownership swaps, compensating penny changes, missing-plus-equal-extra rows and same-day timestamp shifts. CutoverGuard caught 100%.
  • Both caught 100% of duplicates and single amount changes.
  • Neither raised a false alarm on clean data.

How we built it

  • Databricks Lakeflow pipeline (SQL), 15 tables:
    • Bronze: raw legacy extract and ID mappings
    • Silver: typed decimals, UTC timestamps, one row per key; malformed rows go to quarantine via expectations (ON VIOLATION DROP ROW)
    • Gold: keyed findings, customer effects, aggregate checks and the cutover decision
  • Promotion gate: a separate pipeline with EXPECT (status = 'ready') ON VIOLATION FAIL UPDATE. Splitting evidence from promotion keeps the explanation even when the gate blocks.
  • AI/BI dashboard on the gold tables for review and sign-off.
  • Databricks Genie for plain-English questions over the verified gold tables. We benchmarked it on 5 questions with independent SQL answers: 4/5 at first (it missed a unit conversion), 5/5 after one curated example.
  • Reference engine in Python 3.12 with exact integer money, quarantine, run fingerprints and JSON/CSV evidence export, plus a small local review app.
  • Verification: every Databricks result is checked against the independent local engine. The public repo has 323 automated tests.
  • Real public data: 1,467 historical UCI Online Retail invoice lines with controlled defects. Two ownership swaps were found while totals matched.

Why not just ask an LLM? Reading both sides of 7.5M orders once is roughly 300M tokens: hundreds of calls and hundreds of dollars, with no guarantee of exactness. Keyed SQL does it exactly for well under a dollar at list prices. SQL computes, AI explains.

Challenges we ran into

  • Making Databricks results match our exact reference engine (decimal formatting, leading-zero IDs, timestamp types) before calling anything "verified"
  • Designing a gate that blocks promotion without destroying the evidence that explains why
  • Keeping every claim honest: synthetic and benchmark data, no invented accuracy or savings numbers

Accomplishments that we're proud of

  • A totals-matching, wrong-owner migration stopped automatically at 7.5M-row scale
  • A measured 700-trial comparison instead of an anecdote
  • Genie's failed first benchmark kept in the record, not hidden

What we learned

Matching totals don't mean correct data. The question that matters at cutover is "did every record keep its owner and its amount?", and keyed SQL answers it exactly and cheaply. AI is most useful explaining verified results, not producing them.

What's next

  • A document → relational connector that rebuilds ownership from nested records
  • Running the gate after every scheduled batch (Informatica, Airflow or Lakeflow jobs) during a parallel run
  • A reviewer workflow: assign breaks, record sign-off, export the evidence pack
  • A pilot on one real migration

Scope: a working proof of concept on synthetic and benchmark data with known, injected defects. READY means ready for human review, not automatic go-live approval.

Built With

  • ai-bi-dashboards
  • css
  • databricks
  • databricks-cli
  • databricks-genie
  • databricks-sql
  • delta-lake
  • html
  • javascript
  • lakeflow
  • pytest
  • python
  • sql
  • tpc-h
  • unity-catalog
Share this project:

Updates

Submission history