Estimating a data warehouse migration: four cost phases and the complexity of each object

A bank’s data warehouse migration is usually budgeted as a single number. The board approves it, the project starts, and within a few months the number no longer matches what is being spent. The gap rarely comes from one mistake. It comes from estimating the whole project with one method, when the work actually falls into phases that grow for different reasons.

This article sets out how to build the estimate so that a CIO or chief data officer can read it, challenge it and defend it. It uses a structure of four cost phases, objects grouped by complexity, and an explicit reserve for code that automation does not convert. What the one-week migration scan measures is covered in a separate article; this one is about turning those measurements into effort.

Four phases, four different cost drivers

Every migration splits into four phases, and each is costed differently. That distinction is the point. An estimate that applies one rate per table to all four will be wrong in at least three of them.

Architecture and governance. Target architecture, workspace and platform design, the ingestion design, the data migration strategy, the conversion strategy (which code goes through automated conversion and which is rewritten by hand), and the testing and validation plan. For a bank this phase also carries the access model: how roles in the identity directory map to permissions in Unity Catalog, and who signs off. Its size depends on the number of decisions to be made and the number of people who must agree, not on the number of objects. A small warehouse in a heavily regulated bank can need as much architecture work as a large one.

Conversion and syntax testing. Converting SQL, stored procedures and ETL jobs, migrating historical and incremental data, and unit testing each converted component. This is the phase that scales with the inventory, and the one where complexity per object matters most.

UAT and SIT. System integration testing checks that converted jobs run together in the right order and feed the right downstream systems. User acceptance testing checks that the reports and figures the business relies on are the same as before. In a bank, the second part is decided by the people who own regulatory, management and risk reports, and their availability often sets the pace more than the technical team does.

Production deployment and parallel runs. Cut-over in stages, repointing source systems and BI tools, monitoring, and running the old and new platforms side by side until results are accepted. This phase is driven by calendar time. A parallel run that must span a month-end or quarter-end close costs the same whether the wave holds fifty objects or five hundred.

Separating the four phases also shows the board where the uncertainty sits. Conversion effort can be measured before the project starts. UAT and parallel running depend on decisions the bank itself has to make.

Classifying objects by complexity

Counting objects is not enough, because a view that selects from one table and a stored procedure with cursors, dynamic SQL and nested transactions are not the same unit of work. Lakebridge Analyzer reads the exported code and classifies each job or script into four bands: low, medium, complex and very complex. Its output also feeds a conversion calculator for estimating effort.

In practice the bands reflect a handful of factors:

Size and structure of the code. Length, number of statements, depth of nesting.
Dialect-specific features. Cursors, dynamic SQL, proprietary functions and native features of the source warehouse such as materialised views, scheduled tasks or streams, which have no direct one-to-one equivalent.
Dependencies. How many tables a job reads and writes, and how many other jobs and reports depend on its output.
Where the logic lives. SQL scripts that run on their own convert differently from logic embedded in, or generated by, an ETL tool that the bank wants to keep using.

The estimate then becomes a table rather than a single figure: the number of objects in each band, multiplied by the effort per object for that band, per wave. Data migration is classified in the same way, using profiling results such as table size, change rate and whether the source supports change data capture.

Two cautions apply. First, the complexity bands describe conversion, not business importance. A low-complexity view that feeds a regulatory report still needs careful acceptance. Second, the effort per object should come from the team doing the work and be checked against a sample: converting a few objects from each band at the start is the most reliable way to calibrate the table.

A reserve for code that does not convert

Databricks states that Lakebridge can automate up to 80% of migration tasks. Read the other way, part of the work remains manual, and for a bank’s warehouse, built up over fifteen or twenty years, that part tends to sit in the oldest and most critical code. Which tool covers which phase, and what each leaves to people, is set out in Databricks migration accelerators.

Even with a converter, the plan should set aside a share of development time for functions and patterns that are not converted, along with the unit testing, data validation and integration testing that follow. The early sprints also convert less than later ones, because converter rules for the bank’s own coding patterns are added as the project goes, and the automation rate rises once they are in place.

Three practices make the reserve defensible:

Show it as its own line, not hidden inside per-object rates. The board can then see what happens to it.
Size it from the scan. The share of complex and very complex objects, and the number of dialect-specific features found, indicate how large the reserve needs to be.
Draw it down by wave. If the first wave uses less than planned, later waves can be re-estimated with real data; if it uses more, the gap appears early rather than at the end.

What a bank should ask of any estimate

Whoever prepares the estimate, internal team or partner, the following questions separate a sound estimate from an optimistic one:

Are the four phases costed separately, with the basis for each stated?
How many objects fall into each complexity band, and was the classification produced from the actual code?
What reserve is set for code that does not convert automatically, and how will it be tracked?
How long is the parallel run, which closing periods does it cover, and who on the bank’s side signs off?
Which waves are in scope, and can the bank stop after any of them?

An estimate that answers these questions can be compared, challenged and managed. One that cannot tends to grow during the project.

How Sancire builds the estimate

Our Migration Scan takes one week and produces the inputs above: an inventory classified by complexity, a plan in waves, a risk register and a fixed-price quote per wave, with the four phases shown separately. The migration itself runs in waves under the Migration package, each wave accepted and invoiced before the next begins.

Sources: Introducing Lakebridge, Databricks blog, 12 June 2025. Lakebridge Analyzer documentation and Lakebridge transpiler documentation, read on 16 October 2026. Lakebridge is changing quickly; check the current documentation for supported sources and converters before planning.