Warehouse migration: what actually takes the time

Anyone planning a migration estimates data volume first. That is understandable and misleading. A terabyte over a 10-gigabit link is a matter of hours. The four weeks a migration typically takes go somewhere else.

What actually costs time

SQL dialects: the differences are unglamorous and numerous

An automatic translator gets most objects through. What remains is rarely exotic, just numerous: window functions with vendor-specific frame clauses, date arithmetic, implicit casting, NULL ordering, behavior on division by zero.

-- NULL ordering: not the same everywhere
SELECT customer FROM revenue ORDER BY amount DESC;
-- Standard: NULLS FIRST on DESC. Some systems: NULLS LAST.
-- A "top 10" report therefore returns different rows.

-- Division by zero
SELECT total / count FROM metrics;
-- Error, NULL or infinity — depending on the system.

-- Explicit is cheaper than surprised:
SELECT customer FROM revenue ORDER BY amount DESC NULLS LAST;
SELECT total / nullif(count, 0) FROM metrics;

None of these differences produces an error. They produce different numbers — which is the worse case, because nobody notices until somebody does.

Permissions are archaeology

A grown warehouse typically has more roles than departments, because roles were created for individual cases over years. A migration exposes that: a one-to-one transfer copies the sprawl, a redesign needs decisions from people who have no time.

The pragmatic route is an inventory taken from the query history rather than the permission model: which role actually touched which tables in the last six months? Roles with no access are candidates for removal, and that list is easier to discuss than a permissions concept.

Time zones: the quietest source of error

Warehouses treat timestamps differently: with time zone, without, implicitly in UTC, implicitly in the session zone. A migration therefore shifts day boundaries. Revenue booked at 23:40 local time lands on the previous day depending on interpretation.

Day boundaries are where migrations fail after being declared successful.

The check is simple and rarely done: for every fact table, compare daily totals for the last thirty days across both systems. A shift of exactly one day at exactly the boundary hours is the signature.

Reconciliation is the sign-off

Technically the migration is done when queries run. Organizationally it is done when someone signs. In between sits reconciliation, and that needs parallel operation: both systems running, the same reports run against both, deviations explained.

1. 1Pick the twenty most important reports — not all of them, the ones decisions rest on.
2. 2Run each report on both systems with identical parameters and compare results row by row.
3. 3Explain every deviation. “Rounding” is not an explanation, “NULL ordering in row 7” is.
4. 4Only then switch the BI connections over and shut the old system down.

Step three is the one you want to skip. It is also the one that makes the migration defensible: an unexplained deviation is not a tolerance, it is an unknown defect.

What that means for planning

Four weeks are realistic if week one belongs to the inventory — metadata, query history, actual permission usage — and week four to reconciliation. The technical work sits in the middle and is the predictable part. Starting with the installation only pushes the uncertainty to the end.