Data Warehouse to Databricks Migration: A Practical Step-by-Step Guide

What a data warehouse to Databricks migration actually involves

It is tempting to describe the project as “moving the data”. The data is usually the most predictable part. A mature enterprise warehouse also holds twenty years of business logic, written into stored procedures, BTEQ or PL/SQL scripts, ETL mappings, views on views, scheduler chains and report definitions, and nobody has a complete map of it. A migration is the job of finding that logic, deciding what still matters, moving it, and proving that the new platform gives the same answers as the old one.

That is why the best predictor of effort is not data volume. It is the amount of procedural code, the number of downstream consumers, and how strictly the business needs numbers to match. A 200 TB warehouse with clean, set-based SQL can be easier to move than a 5 TB one held together by a thousand scripts.

Why teams migrate to Databricks now, and when they should not

Three things changed in 2025 and 2026 that make a warehouse migration easier to justify and to plan.

The migration runbook at a glance

A good data migration plan has steps that overlap. Code conversion for wave 2 starts while wave 1 is reconciling, and the parallel run starts long before conversion is finished. The chart shows the shape of a mid-sized program. It illustrates how the steps overlap and is not a benchmark for your timeline.

Illustrative shape, not a benchmark. The real timeline depends on code volume, the share that converts automatically, and how long the business needs both systems side by side.

1. Assess

   Inventory every object, job and consumer, score complexity, and decide what to retire, move as is or redesign.

   Lakebridge ProfilerAnalyzer

2. Design the target

   Catalogs and schemas, medallion layers, clustering, compute, security mapping and cost guardrails.

   Unity Catalog

3. Convert code

   Transpile DDL, SQL and procedures, then review, test and refactor what the tools could not handle.

   Lakebridge Converter

4. Move the data

   Bulk-load history, then keep the target current with incremental loads or change data capture until cutover.

5. Reconcile

   Prove the target matches, layer by layer, from schema to the business KPIs.

   Lakebridge Reconcile

6. Parallel run and cutover

   Run both platforms side by side through real business cycles, switch consumers wave by wave, keep a rollback path, then decommission.

Step 1: Assess the warehouse before you touch any code

Assessment answers three questions: what exists, what is actually used, and how hard each piece will be to move. Skipping it is the most expensive mistake in data migration, because every unused object you carry forward has to be converted, tested and reconciled for nothing.

What Lakebridge automates in assessment

Lakebridge has two assessment tools. The Profiler (experimental) connects to the source system and collects workload metadata from Synapse, Teradata, Snowflake, SQL Server, Oracle, BigQuery and Redshift. The Analyzer reads exported source code, inventories it and produces a complexity report. It covers 31 technologies, including Teradata, Oracle, SQL Server, Netezza, Greenplum, Vertica, DB2, Hive, Impala, SSIS, DataStage, Talend, SAS and Alteryx.

# install the Lakebridge extension (needs the Databricks CLI)
databricks labs install lakebridge

# 1 · analyze exported source code into a complexity report
databricks labs lakebridge analyze \
  --source-directory ./edw_export/sql \
  --report-file ./reports/teradata_analysis.xlsx \
  --source-tech Teradata

# 2 · transpile a folder of Teradata SQL to Databricks SQL
databricks labs lakebridge install-transpile
databricks labs lakebridge transpile \
  --source-dialect teradata \
  --input-source ./edw_export/sql \
  --output-folder ./converted

# 3 · compare source and target once data has landed
databricks labs lakebridge configure-reconcile
databricks labs lakebridge reconcile

Step 2: Design the target platform, not a copy of the old one

A lakehouse does not need the indexes, distribution keys and partition schemes a legacy warehouse used to get its performance. Copying them one to one is one of the most common ways to end up with a slower, more expensive platform. Map each legacy concept to the Databricks feature built for the same job.

Two design decisions are worth settling early because they are expensive to change later: the catalog layout and naming convention, and the cost guardrails (budgets, tags, auto-stop and per-workload compute policies). The second one matters more than teams expect. A migration replaces a fixed-capacity appliance with elastic, usage-billed compute, and during the parallel run you pay for both. For the architecture side of this decision, see our data engineering services.

Step 3: Convert SQL, procedures and ETL

Code conversion is where Lakebridge does the most work. It has three converters, each suited to different sources:

Here is a typical conversion: a Teradata table and a “latest order per customer” query held in a volatile table, and what they become on Databricks.

-- primary index drives data distribution
CREATE MULTISET TABLE sales.fact_orders,
  NO FALLBACK, NO JOURNAL (
  order_id     BIGINT NOT NULL,
  cust_id      INTEGER,
  order_ts     TIMESTAMP(0),
  discount_amt DECIMAL(18,2)
) PRIMARY INDEX (cust_id);

CREATE VOLATILE TABLE last_order AS (
  SEL cust_id, order_id, order_ts,
      ZEROIFNULL(discount_amt) AS discount_amt
  FROM sales.fact_orders
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY cust_id ORDER BY order_ts DESC) = 1
) WITH DATA ON COMMIT PRESERVE ROWS;

-- liquid clustering replaces the primary index
CREATE TABLE main.sales.fact_orders (
  order_id     BIGINT NOT NULL,
  cust_id      INT,
  order_ts     TIMESTAMP,
  discount_amt DECIMAL(18,2)
) CLUSTER BY (cust_id);

-- volatile table -> temporary view
-- (or a temp table on DBR 18.1+)
CREATE OR REPLACE TEMPORARY VIEW last_order AS
SELECT cust_id, order_id, order_ts,
       COALESCE(discount_amt, 0) AS discount_amt
FROM main.sales.fact_orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY cust_id ORDER BY order_ts DESC) = 1;

Most set-based SQL converts like this, with small, predictable changes: SEL becomes SELECT, ZEROIFNULL becomes COALESCE, table options disappear, and three-level names arrive. QUALIFY is supported natively, so it stays. The effort is concentrated elsewhere.

Treat converted code like any other code: version it, review it, and test it against recorded inputs and outputs from the source system. A conversion that compiles has passed the first of several tests, not the last.

Step 4: Move historical and incremental data

Data movement has two jobs: load the history once, and keep the target current until each wave cuts over. The right tool depends on volume, change rate and how long the two systems will run side by side. These are the main options:

Step 5: Reconcile source and target, layer by layer

Data reconciliation is how you prove the migration worked. Each level below catches a class of defect the level before it cannot see, so a wave is not reconciled until every level passes.

1. Objects and schemaEvery table and view exists; column names, types, precision and nullability match the agreed mapping.
   schema
2. Row countsPer table, and per partition or date range so a mismatch points somewhere useful.
   COUNT(*)
3. Column aggregatesSUM, MIN, MAX and AVG on numbers; MIN and MAX on strings and dates; null and distinct counts on every column.
   SUM · MIN · MAX
4. Row-level comparisonHash each row’s business columns and compare by key to find exactly which records differ, are missing or are extra.
   row hash
5. Business reconciliationThe reports, KPIs and regulatory outputs people rely on produce the same numbers. This is the level that earns sign-off.
   KPIs

-- run the same profile on source and target for one slice, then compare
SELECT
  COUNT(*)                   AS row_count,
  COUNT(DISTINCT order_id)   AS distinct_keys,
  COUNT(*) - COUNT(cust_id)  AS null_cust_id,
  SUM(discount_amt)          AS sum_discount,
  MIN(order_ts)              AS min_ts,
  MAX(order_ts)              AS max_ts
FROM main.sales.fact_orders
WHERE order_ts >= DATE '2026-01-01'
  AND order_ts <  DATE '2026-02-01';

-- rows whose business columns differ, or exist on one side only
SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       CASE WHEN s.order_id IS NULL THEN 'missing in source'
            WHEN t.order_id IS NULL THEN 'missing in target'
            ELSE 'values differ' END   AS issue
FROM recon.src_orders_hashed s
FULL OUTER JOIN recon.tgt_orders_hashed t
  ON s.order_id = t.order_id
WHERE s.row_hash IS DISTINCT FROM t.row_hash;

Hashes only match if both sides normalize values the same way first: trimmed strings, one timestamp precision, one decimal scale, one representation of null. Agree the normalization rules before the first comparison, or every row will differ. Lakebridge Reconcile automates much of this. Its schema, row, data and all report types compare structure, row hashes and column values across Oracle, Snowflake, SQL Server, Redshift, Teradata (with a user-installed hash function) and BigQuery. It supports aggregate checks, absolute or percentage thresholds and sampling.

Step 6: Parallel run, cutover and rollback

During the parallel run both platforms receive the same inputs and produce the same outputs, and someone compares them every day. It is the only test that covers the real business calendar: the month-end close, the quarterly regulatory file, the Monday morning load after a weekend outage. Databricks’ own migration guides recommend running pipelines in parallel for a set period, and starting validation with control and lookup tables.

1. 01 · Freeze
   Change freeze on the wave’s scope

   No new logic lands in the legacy system for the objects in this wave, or it lands in both places under one change ticket.

2. 02 · Sync
   Final incremental load

   The target is brought fully current and reconciled at every level for the final slice.

3. 03 · Switch
   Consumers repointed

   BI connections, extracts, APIs and downstream feeds move to Databricks, one consumer group at a time, each with a named owner who confirms it works.

4. 04 · Watch
   Hypercare

   Heightened monitoring of freshness, failures, cost and query performance, with the migration team on call.

5. 05 · Hold
   Legacy kept read-only

   The old warehouse stays available, unchanged, as the rollback target until the wave passes its exit criteria.

6. 06 · Retire
   Decommission

   Archive what regulation requires, revoke access, stop the jobs and switch off. The licence and support savings only start here.

We use the same discipline outside warehouses. On a state immunization registry, up to 15 years of history, 2 million patient records, moved in an 8-month migration that ran in parallel with the new build. Data quality was fixed at source, records moved in validated batches, and the final batch was synchronized at go-live. It was a registry migration, not a warehouse one, but the method of validated batches, parallel running and a controlled final sync carries over directly.

Notes by source system: Teradata, Oracle, SQL Server and more

Search volume for each “X to Databricks” pair is small, but the questions behind them are specific. One runbook with a note for each source works better than seven separate plans. Pick a source system:

How long does a Databricks migration take, and what does it cost?

We have not found a credible, independent benchmark for how long an enterprise warehouse migration takes, and we are wary of anyone who quotes one without seeing the estate. Databricks’ own professional services team warns that migrations “create a misleading impression of a clearly defined project”. The best public evidence is customer stories, which are vendor-reported and selected because they went well:

Source: Databricks blog, October 2026. All figures are vendor-reported and not independently verified.

What actually drives cost and duration is predictable, even if the total is not:

9 common data warehouse migration pitfalls

We see each of these in most programs. They are listed with their cause, because the fix follows from it.

Data warehouse migration checklist

Use this to check readiness at each gate. Tick items as you confirm them (progress is saved in this browser only), or print it for a steering meeting.

How Bonami helps with Databricks migration

Our data engineering team runs migrations end to end or alongside your engineers. A typical engagement starts with a fixed-scope assessment: we profile the estate, score code complexity, build the retire list and wave plan, and give you an estimate you can defend.

Frequently asked questions about Databricks migration