Retiring a legacy SQL Server reporting platform

The situation

Axi is a global FX and CFD broker regulated in five jurisdictions. Its finance, FP&A and regulatory reporting ran on a SQL Server estate that was expensive to license and run, sat apart from the Databricks lakehouse the data team was building, and needed manual cross-checks to keep its numbers consistent.

Retiring it meant moving 25+ reports that finance relies on every day. The technical work was the easier half. The harder half was proving that every number on the new platform matched the old one before anyone was asked to give the old one up.

How the migration ran

1. Inventory. List every report with its owner, its consumers and the tables behind it, and agree with each owner what “matching” means.
2. Rebuild on the lakehouse standard. Recreate the logic as dbt models in medallion layers on Unity Catalog, with incremental merges as the default load pattern, instead of porting stored procedures one to one.
3. Reconcile automatically. Compare old and new outputs on every run, so a difference shows up as a failing test instead of a question from finance.
4. Test with the business. Run UAT with the seven teams that use the reports. A report is done when its owners sign it off.
5. Retire. Move every consumer to the new reports and shut the legacy platform down.

Key decisions

• Rebuild, don’t lift and shift

  Porting stored procedures as they were would have moved old problems onto a new platform. Rebuilding in dbt put every report on the same tested, version-controlled standard as the rest of the lakehouse.

• Make reconciliation a test, not a task

  Automated comparisons ran with every build, so the team could see at a glance which reports matched and which still needed work.

• Let the business decide when it’s done

  Seven teams ran UAT. Their sign-off, not a green pipeline, decided when a report had moved.

• Run both until the numbers agree

  The legacy platform stayed up until reconciliation and sign-off were complete, so nobody had to trust the new numbers on faith.

What it looks like in code

-- Fails when the new model and the legacy report disagree on any day.
{% test reconcile_daily_totals(model, legacy_relation, date_column, amount_column, tolerance=0.01) %}

with new_side as (
    select {{ date_column }} as report_date,
           count(*) as row_count,
           sum({{ amount_column }}) as total_amount
    from {{ model }}
    group by 1
),

legacy_side as (
    select {{ date_column }} as report_date,
           count(*) as row_count,
           sum({{ amount_column }}) as total_amount
    from {{ legacy_relation }}
    group by 1
)

select
    coalesce(n.report_date, l.report_date) as report_date,
    n.row_count    as new_rows,
    l.row_count    as legacy_rows,
    n.total_amount as new_total,
    l.total_amount as legacy_total
from new_side n
full outer join legacy_side l
    on n.report_date = l.report_date
where n.row_count is distinct from l.row_count
   or abs(coalesce(n.total_amount, 0) - coalesce(l.total_amount, 0)) > {{ tolerance }}

{% endtest %}

models:
  - name: fct_daily_deposits
    data_tests:
      - reconcile_daily_totals:
          legacy_relation: source('legacy_sqlserver', 'rpt_daily_deposits')
          date_column: deposit_date
          amount_column: amount_usd
          tolerance: 0.01   # agreed with the report owner

Illustrative sample written for this page, with made-up names. It shows the pattern, not client code.

If you’re planning the same move

• Define “matching” before writing code. Rounding, currency conversion timing and late data all cause small differences. Agree tolerances with report owners up front.
• Reconcile at more than one grain. Daily totals can match while individual rows don’t. Compare counts and sums by day, then by key for the reports that matter most.
• Track sign-off like a deliverable. A simple tracker of report, owner, reconciliation status and sign-off date keeps a long migration honest.
• Count the savings before you start. Licenses, servers and hours spent on manual checks make the business case, and they’re easy to forget once the old platform is gone.