Starting a warehouse migration by translating stored procedures will stall it

The kick-off meeting usually has a spreadsheet in it. Somebody has queried the system catalogue, exported every stored procedure in the legacy database, and sorted the result by name. There are a few hundred rows. Each one becomes a ticket, the tickets go into a board, and the board becomes the migration plan. It is a satisfying artefact. You can count it, you can burn it down, and you can put a percentage on a slide every fortnight.

The plan is coherent and it is also the reason the project will be in trouble by month seven. Progress on that board measures procedures rewritten, which is not the same thing as work the business can use. You can be sixty percent through the list and have delivered nothing anybody can switch to, because the forty percent left contains the joins that make the finished sixty percent mean anything.

By then the old system is still running, the new one is running alongside it, two teams are keeping both alive, and the honest answer to "when can we turn the old one off" is that nobody knows. The estate has stopped being a migration and become a second production system. The way out is not a faster translator. It is a different starting point.

A procedure estate is a graph, not a list

The catalogue export flattens something that is not flat. Procedures call other procedures. They write to staging tables that a later procedure reads without any declared dependency. They build SQL as a string and execute it, so the table names never appear in a dependency search. They rely on a temp table that a caller populated three steps earlier. The scheduler holds the real execution order, and often the scheduler is a list of jobs with sleep intervals between them that somebody tuned by hand in 2016.

Alphabetical order is orthogonal to that graph. Working down it, you build leaves before roots and roots before the things that feed them, and each finished procedure sits inert until its neighbours arrive. Worse, you carry the whole estate forward by default, including the parts that exist only because a report that no longer runs once needed them. Nobody is asked to justify a procedure. It is on the list, so it gets a ticket, so it gets built.

You can be sixty percent through the list and have delivered nothing anybody can switch to.

The pattern, stated plainly

Start where the data is read

The only durable definition of done for a migration is that nobody is asking for the old system any more. That is a statement about consumption, so consumption is where the plan should begin. Before writing a single model, establish what is actually read: which dashboards are opened, which extracts are scheduled to somebody's inbox, which downstream systems pull from which tables, which regulatory return is assembled from what.

This is evidence you can gather rather than opinion you have to canvass. Query history exists on every serious database and on every warehouse you might move to. BI platforms keep usage and subscription logs. Service accounts and their connection strings tell you who is pulling on a schedule. Put those together and the picture is usually uncomfortable: a small number of reports carry nearly all the genuine reading, a long tail is opened rarely and mostly by the person who built it, and a middle band is technically alive because a subscription is still emailing a PDF into a mailbox nobody reads.

With the real consumption list in hand, trace backwards. Column-level lineage tooling will get you a good part of the way from a report field to the procedures that populate it, and dbt's lineage graph will keep the new side honest once you start building. Expect the automated trace to break in three places: dynamic SQL, logic that lives in an ETL tool rather than in the database, and the spreadsheet somebody maintains between two steps. Those gaps are found by reading code and asking people, and they are usually where the interesting business rules are hiding.

Rebuild the logic, not the syntax

Line-by-line translation is attractive because it is checkable, and it is a trap because it preserves accidents alongside intent. Legacy procedures are full of behaviour that is a property of the old engine rather than of the business: implicit type conversion that silently rounds, an ordering dependence that happens to hold because of how the old optimiser chose to scan, date arithmetic that assumes a particular first day of the week, isolation-level behaviour that makes a read consistent by luck. Carry that across faithfully and you have bought a second decade of bugs that now behave differently, because Postgres and Snowflake do not make the same choices as SQL Server or Oracle in the corners.

So read each step and sort what it does into three piles before you write anything.

• A rule the business would defend Returns are excluded from revenue after the credit note posts. This is real. It should be expressed clearly in the new model and covered by a test, because it is currently written down nowhere except in this procedure.
• A workaround for a platform limitation A cursor that exists because the old engine handled a set-based version badly, or a nightly rebuild that exists because there was no cheap way to do incremental updates. Delete it and solve the problem the way the new platform solves it.
• A bug the organisation has reconciled around A join that quietly drops rows with a null key, and a manual adjustment further down that adds a similar number back. Fixing it changes a published number, which is a decision for the business, not for the engineer. Raise it, document it, get an answer in writing.

That third pile is where migrations get politically dangerous, and it is worth being direct about it early. If the new system produces a more correct figure than the old one, somebody has to sign for the change. Discovering that at reconciliation, two weeks before a cutover, is how a technically finished migration gets postponed for a quarter.

Letting the rest expire

The tail does not need a decision meeting each. It needs a mechanism. Freeze the legacy estate so nothing new is built on it, instrument what is still being read, publish a date after which the old system is read-only, and then a later date after which it is gone. Circulate both dates to named owners rather than to a distribution list. Then wait. The things that matter surface, because somebody asks for them, and each request is a genuine signal that you should migrate one more path. The rest goes quiet, and quiet is the answer.

Somebody senior does have to own the sentence "we are not migrating this", dated and written down. Without that, every procedure gets carried forward on the grounds that it might be needed, which is how the estate got to a few hundred in the first place. A migration is one of the few moments when an organisation is allowed to delete things, and the permission expires when the project closes.

When translation actually is the right answer

There are cases where all of the above is wrong. If the driver is a licence that lapses on a fixed date, the honest move may be a lift and shift onto managed infrastructure with the procedures intact, and a proper rebuild later on your own timetable. If a regulator requires that a historical figure be reproducible exactly as it was published, faithful reproduction is the requirement and improvement is a defect. If the estate is genuinely small, forty procedures rather than four hundred, the tracing overhead is not worth it and you should just do the work. And if the procedures are not reporting logic at all but the operational core of an application, writing back into tables that a live system depends on, then this is an application migration wearing a data project's clothes, and it should be planned as one.

Everywhere else, the sequencing question is the whole project. A migration that starts from the catalogue ends when the list is empty, which is a date nobody can forecast. A migration that starts from what people read ends when the last person stops asking for the old system, and that date arrives noticeably sooner than the list does.