Operating · Chapter 22

Databricks migration guide: from a warehouse to the lakehouse

A Databricks migration from Synapse, Snowflake, Teradata or an on-premises warehouse is mostly planned around the part that goes well, converting SQL, and overruns on the parts nobody budgeted. This chapter is how we'd plan one: the three kinds of work, the order to move domains in, the tools that cover the mechanical part (Lakebridge and Lakehouse Federation), reconciliation that convinces a CFO, and switching the old system off.

The three kinds of work #

The three behave differently, carry different risk, and need different people.

Mechanical translation covers table definitions, straightforward SQL, and data movement. It's genuinely well automated, scales with tooling rather than headcount, and is the part vendor demonstrations show. On Databricks that tooling is Lakebridge, a Databricks Labs project: an Analyzer that grades every object's complexity, transpilers (Morpheus and BladeBridge among them) that convert dialect SQL, and a Reconciler that compares row counts, schema and values between source and target. Expect this part to go roughly as promised.

Semantic reconstruction covers stored procedures encoding business rules that exist nowhere else, scheduling dependencies implied by a legacy orchestrator's configuration, and definitions living inside a BI tool's semantic layer. None of it is written down, several of the people who wrote it have left, and the only reliable recovery is to read the code and ask questions. This is where migrations overrun.

Consumer migration covers every report, extract, application, and human process reading from the old system. It is not technically hard and it is politically slow, because each consumer has an owner with their own priorities and their own definition of ready.

Sequencing #

We'd migrate by data domain rather than by layer, and run the old and new systems in parallel for each domain until reconciliation passes.

Migrating by layer, meaning all bronze then all silver then all gold, sounds orderly and delivers nothing until the last step, so the project carries maximum risk for maximum duration. Migrating by domain delivers a working, verified domain early, which buys the credibility to keep going. The first domain also absorbs the platform setup work described in Unity Catalog and CI/CD, so later domains move faster and an estimate built from domain one errs in a useful direction.

The first domain should matter enough that finishing it means something, be self-contained enough not to drag half the estate behind it, and be owned by someone who wants the migration to succeed. Picking the largest and most contested domain first, on the grounds that it's the hard one, is an expensive instinct we'd argue against.

Parallel running is expensive and it is not optional. Reconciliation between old and new is the only evidence that the semantic reconstruction was correct, and it needs enough cycles to catch month-end, quarter-end, and seasonal edge cases. A domain that reconciles for a week has demonstrated less than teams think.

Before the plan: an inventory, not a guess #

We'd spend the first two weeks on assessment and not let anyone negotiate it away. A scope built by counting views and multiplying by a gut-feel hours figure is how a date gets sold that nobody can hit. Lakebridge's Analyzer reads exported SQL or ETL repository exports and returns a complexity report with the dependencies between objects, which is what you sequence waves from. Two habits make it pay: split monolithic DDL files into one object per file first, so results come back per object rather than per file, and keep the report's machine-readable output in a table so object counts and wave membership stay queryable instead of being copied into a spreadsheet by hand.

databricks labs lakebridge analyze      # prompts for source path, dialect, technology and report file

databricks labs lakebridge transpile \
  --source-dialect mssql \
  --input-source /path/to/split/output \
  --output-folder /path/to/transpiled/output

Objects the Analyzer grades HIGH or VERY HIGH are the ones that need senior eyes after transpilation, and that list, not the conversion rate, is the honest size of the semantic work below.

Reconciliation done properly #

Row counts match far more often than values do, and value equality on a sample is weaker evidence than it feels. We'd compare full aggregate profiles per column, including null rates, distinct counts, and distribution summaries, then compare exactly on a deterministic sample keyed by business identifier, and finally reconcile the handful of numbers the business actually reports.

The last of those is the one that matters politically. When the CFO's number matches, the migration is credible; when it does not, nothing else you demonstrate helps.

Reconciliation should be a scheduled job with stored history rather than a script somebody runs, because a trend of shrinking discrepancies persuades a steering committee better than one passing run, and because the same job becomes your regression test after cutover, turning migration evidence into the monitoring described in Observability.

Move consumers before data, with federation #

Lakehouse Federation lets Databricks query the old warehouse in place, which changes the shape of a migration more than its speed. Query federation creates a Unity Catalog connection and a foreign catalog and pushes queries down over JDBC, read-only; for Snowflake, catalog federation reads the Snowflake tables from object storage on Databricks compute, which Databricks positions for incremental migration and long hybrid periods (query federation). Azure Synapse is a supported query federation source too.

The move we'd make with it: repoint a dashboard at the Unity Catalog name while the table still lives in the old system, prove the numbers, then swap the storage underneath. The BI team sees one change instead of two. One trap on the Snowflake side: the OAuth refresh token behind the connection (OAUTH_REFRESH_TOKEN_VALIDITY) defaults to 90 days, and when it expires the connection needs re-authenticating (Snowflake federation). We'd put that date in the migration plan, because a federated catalog that stops answering mid-migration looks like a data problem for the first hour.

What we saw: a Synapse migration in six weeks #

Preetham Reddy, TechFabric's founder, wrote up a manufacturing company's move from Azure Synapse to Databricks that finished in six weeks, where that kind of move normally runs six months or more. His account of the difference is that conversion stopped being treated as the project. The estate was inventoried with Lakebridge Analyzer before any plan was written, with the tool version pinned so a grade could be explained months later. Consumers moved through federation first. Each object counted as migrated only when Lakebridge's Reconciler had produced parity evidence for it, and a wave of objects could not cut over until every object in it was certified and the approvals were collected; the tracking ran in Airlift, our migration tool. Where the deterministic transpiler left FIXME markers, an agent could propose one bounded repair, and people made every decision about evidence, approval, cutover and rollback.

The lesson we'd take from it for any migration, with or without that tooling: decide what "done" means for a single object before converting the first one, write it down per object class, and make cutover read that evidence rather than a checklist on a bridge call. Teams that define parity after conversion argue about it for the rest of the project.

Advanced: the parts experienced teams plan for and first-timers discover #

Change data capture during the parallel period. The source does not stop while you migrate it. Continuous capture into bronze, rather than repeated full extracts, is what makes parallel running affordable over months instead of weeks, and it turns cutover into stopping writes on the old side rather than a final heroic load. Real-Time and Change Data Capture covers the mechanics.

Shadow reads for consumers. The lowest-drama cutover we'd plan is to point a report at the new system while still serving the old one, compare the two outputs for a period, and only then flip which one the user sees. The comparison is automatic, the flip is boring, and the rollback is a configuration change.

Behavioural differences that are not bugs. Legacy warehouses differ from Spark SQL in ways that produce different results rather than errors, including implicit rounding, collation and case sensitivity in join keys, null ordering, empty string against null, and timestamps across daylight saving boundaries. Each is a small, plausible discrepancy that costs a day to find. A differences register built on the first domain and applied to the rest stops you finding each of them four times.

Query and workload archaeology. Before translating anything, we'd capture what actually runs against the legacy system, with frequency and consumer, from its query history. Scheduled jobs feeding reports nobody has opened in a year are common, and the cheapest migration work is the work you delete.

Cost and governance models do not translate. A workload that was economically invisible on a fixed-capacity appliance becomes a visible line item under consumption pricing, and nightly full rebuilds are the usual offender, so rewrite those as incremental before cutover rather than after the first surprising invoice. Legacy grants accumulated over a decade translate no better; copying them faithfully imports a mess you had an opportunity to fix, so we'd reconstruct entitlements from groups and intent instead.

The row worth dwelling on is federation, because it changes the shape of the project rather than its speed. Querying the legacy warehouse from the new engine lets the reconciliation job read both sides from one place, and lets consumers move to the new endpoint before their data has finished moving. The last row is there because migration tooling is routinely oversold, and no tool on any platform reconstructs undocumented business logic. A team told the whole thing is automatic won't have staffed for the part that isn't.

Decommissioning #

A migration is not finished when the new system works. It is finished when the old one is switched off, and estates that skip this step pay for both indefinitely while slowly repopulating the old system with new dependencies.

We'd set a decommissioning date per domain at the start, treat every extension as a decision needing a reason and a named owner, and make the final cutover a scheduled event with a rollback plan rather than a gradual fade. Before switch-off we'd take a final archival extract into cheap storage with schema and lineage recorded, because the compliance question about a legacy record arrives eighteen months later and the wrong answer is that the system was decommissioned.

The habits a migration forces are worth keeping. Reconciliation, lineage, a written record of what each domain means, and an owner per dataset are things an estate should have anyway, and a migration is the rare moment the organisation will pay for them.

Table of Contents