Parallel-run Migration for Warehouse Replacement

The problem

A legacy warehouse is being replaced.

The new platform is built, the models are rewritten, the pipelines run.

The remaining question is the only one that matters: does it produce the same numbers?

The default answer is to test, migrate the reports, and cut over on a weekend.

This fails in a specific and predictable way.

On Monday, a figure in a report that nobody thought to check is different from last week’s.

Nobody can say whether the old number was wrong, the new number is wrong, or both are right and measure different things.

The business loses confidence immediately, and the pressure to roll back arrives before there is any diagnosis.

Often there is no clean rollback, because the old pipelines were switched off to avoid double cost.

The underlying issue is that a warehouse migration is not a code migration.

Two systems can be functionally equivalent and still disagree, because the legacy system contains a decade of undocumented behaviour.

Rounding may be applied in one stored procedure. A timezone assumption may exist in a nightly job. A filter added in 2019 may quietly exclude a transaction type. A late-arriving-data rule may exist only as a scheduling quirk.

None of it is written down.

All of it is embedded in numbers people have been reporting for years.

When to use this

• You are replacing a warehouse or transformation layer that feeds reporting the business relies on.
• Historical figures must remain consistent, or discrepancies must be explainable.
• The legacy system’s logic is partly undocumented, which is nearly always.
• A rollback path is required for a defined period after cutover.
• Regulatory or financial reporting depends on the output.

When not to use this

Nothing downstream depends on the old numbers yet

A new platform with no established reporting has nothing to reconcile against.

Build it properly and move on.

The explicit goal is to change the definitions

Where the migration is an opportunity to fix metrics that were wrong, parallel running will report differences by design.

Reconciling to the old numbers in that situation means reconciling to known errors.

Separate the two projects.

Migrate to parity first, then change definitions afterwards as a visible and approved change.

Doing both simultaneously makes every discrepancy ambiguous.

The legacy system cannot be kept running

Parallel running requires both systems to operate against the same inputs.

Where the old platform’s licence has expired or its source connections are gone, this pattern is unavailable.

The migration therefore carries more risk, which should be stated plainly to whoever is approving it.

The cost of running both is genuinely prohibitive

This is a real constraint for very large estates, but calculate it rather than assuming it.

Parallel running costs more during the overlap and usually far less in aggregate, because the alternative includes the cost of a failed cutover.

Architecture

Both pipelines run against the same sources, on the same schedule, producing their own outputs.

A reconciliation layer compares them systematically.

That comparison includes row counts at defined grains, aggregate measures at the grain the business reports on, and row-level comparison on a sampled or full key set depending on volume.

Results are recorded per run, so parity can be demonstrated as sustained over time rather than asserted from a single successful comparison.

Every discrepancy needs a classification

Every difference is investigated and resolved to one of three conclusions.

First, the new system has a defect, in which case it is fixed.

Second, the legacy system has a defect that the new system does not reproduce.

In that case the difference is expected and must be documented and communicated before cutover, because someone’s report is about to change.

Third, the two systems are computing genuinely different things.

That means a definition was mistranslated and requires an explicit decision about which interpretation is correct.

Cut over gradually, not all at once

Cutover happens per report or per data domain rather than as one event.

It happens only after parity has held for a defined number of consecutive runs.

That period should be long enough to cover a month-end, because month-end is where undocumented behaviour tends to concentrate.

The legacy system remains available for a stated period afterwards.

The decision to decommission it is separate from the decision to cut reporting over.

Components

Tradeoffs

Implementation notes

Reconcile at the grain the business reports on

Do not reconcile only at row level.

A row-level diff that passes is necessary but not sufficient.

Two systems can agree on individual rows and still disagree on a total because of a join that fans out or a filter applied at a different stage.

Reconcile the actual reported figures.

Cover at least one month-end

Cover at least one month-end, and one quarter-end where quarterly logic exists.

Periodic processes are where undocumented behaviour tends to live — accruals, adjustments, allocation runs and manual corrections applied by finance.

A two-week parallel run in the middle of a month can show clean parity and prove almost nothing.

Maintain a discrepancy register

Give every discrepancy a named owner and a documented resolution.

Treat the register as the primary project artifact.

At the end of the migration it is the evidence that parity was achieved deliberately rather than by coincidence.

It is also what you show to whoever signs off the migration.

Communicate expected differences before cutover

Never wait until after cutover to explain why a number has changed.

Where the legacy system was wrong and the replacement is right, someone’s number is still going to change.

They need to hear that from the project rather than discover it in a meeting.

A number that changes without warning is a defect regardless of which value is correct.

Keep reconciliation after the migration

Treat automated reconciliation as a permanent asset rather than a disposable migration tool.

The framework that compares two systems is the framework that will validate the next migration.

Much of it also generalises directly into ongoing data quality monitoring.

Define the legacy decommission trigger

Decide and document the decommission trigger for the legacy platform, with a date and an owner.

Parallel running that nobody ever ends becomes permanent double cost.

The decision to switch the old system off will not make itself — there is always a reason to keep it for one more month.

References

• Audit helper in dbt: bringing data auditing to a higher level — dbt’s guide to comparison macros for validating migrated datasets.
  dbt
• Evaluating dbt_audit_helper and DataComPy — a process-oriented comparison of two open-source options for validating migrated tables.
  Medium
• Google Cloud Data Validation Tool — an open-source CLI for comparing heterogeneous source and target tables across platforms.
  GitHub
• data-diff — dataset comparison tooling for migration and replication validation across SQL databases.
  GitHub
• Tools for data pipeline reconciliation and cross-source validation — a survey of available reconciliation approaches and where ordinary dbt tests stop being sufficient.
  DEV Community