Migration with proof, not hope

The first phase is inventory, and it goes deeper than counting tables. We catalogue every object, but more importantly we profile actual usage: which tables are queried, by whom, how often, and which have not been touched in a year. In a typical estate, thirty to forty per cent of objects are dead — migrating them is pure cost, and identifying them early is one of the largest savings in the project.

Then we find the logic. Stored procedures, views, materialised views, scheduled jobs, external ETL, and — invariably — some transformation that happens in a spreadsheet after the report is exported. Each carries semantics that must be reproduced, and each may be subtly wrong today in ways that need a decision rather than a translation.

We also profile the data itself: row counts, cardinality, null rates, date ranges, distribution of key columns. This becomes the baseline for validation and frequently surfaces quality problems the organisation did not know it had.

Platforms differ in ways that look trivial and are not. Date and timestamp handling — time zones, truncation behaviour, what happens at daylight boundaries in source data collected internationally. Numeric precision and rounding, which changes financial totals in the third decimal and therefore in the annual figure. Null handling in aggregates and joins. String collation and case sensitivity, which changes join results. Empty string versus null, which Oracle famously conflates and PostgreSQL does not.

Each of these produces a small, plausible, wrong number. We handle them by cataloguing the differences relevant to your source and target pair up front, writing explicit translation rules, and then testing against real data rather than assuming.

This is also where we find that the old platform was wrong. On a recent Redshift-to-Snowflake project, a revenue figure differed by 0.3% and investigation showed the legacy query had been double-counting a category of returns since 2019. The migration did not create that problem; it exposed it. Handling that discovery well — surfacing it, letting the business decide, documenting the change — is part of the job.

Both systems run in parallel for six to fourteen weeks. Data flows into both. Queries run against both. And an automated reconciliation compares outputs continuously — row counts by partition, control totals on financial measures, and full row-level comparison on critical tables.

Reconciliation results are published to a dashboard the business can see, so trust is built by evidence rather than assertion. A difference is investigated and either fixed or explicitly accepted with a documented reason. Cutover happens only when reconciliation has been clean for a defined period, typically three consecutive full reporting cycles.

We also shadow queries where possible: production queries are replayed against the target and results compared automatically. This catches the long tail of report-specific differences that table-level reconciliation misses, and it is what allows us to say with confidence that switching the BI tool over will not change any number on any dashboard.

During dual running, both systems must stay current. For warehouses this usually means running ingestion into both targets from the same source pipelines, which is straightforward if your pipelines are well-built and painful if they are not — a common reason migration projects also become pipeline modernisation projects.

For transactional database migrations we use change data capture: an initial bulk load followed by continuous replication of changes, so the target is never more than seconds behind. Cutover then becomes a matter of stopping writes to the source for a brief window, letting replication drain, verifying, and pointing the application at the target. On our last fourteen migrations that window has been under four minutes, which for most businesses is indistinguishable from zero.

A straight lift-and-shift almost always performs worse and costs more than it should, because query patterns that suited the old engine are wrong for the new one. Distribution keys that made sense on Redshift are irrelevant on Snowflake, where clustering keys and micro-partition pruning govern performance. Oracle hints do not translate to PostgreSQL. Spark jobs written for one file layout perform badly on another.

So tuning is part of the project, not a follow-up. We benchmark the heaviest queries before and after, restructure the physical layout for the target engine, right-size compute with aggressive auto-suspend, and materialise the aggregations that get computed repeatedly. The cost reduction we quote comes from this work as much as from the pricing model.

Every migration we run has a tested rollback. For warehouse migrations that means the legacy platform stays running and current for a defined period after cutover — usually thirty days — so reverting is a matter of repointing BI tools. For database migrations, replication is reversed so changes made on the new system flow back, which keeps rollback viable for the first days rather than only the first hours.

We rehearse cutover at least twice against production-scale data before the real one. The rehearsal produces a timed runbook, and it invariably finds two or three things that would have gone wrong — which is exactly why it is worth doing.