Migration

Databricks migration guide

guidecuratedofficial evidenceazure5 min readreview needed

A general method for migrations from legacy data platforms to Azure Databricks. Use the SQL Server guide or the Azure Synapse guide for source-specific information.

A migration must improve the platform, not only move the existing design. Keep necessary queries, remove unused assets, and give the operations team a governed platform.

The six phases#

1. Assess and build the business case#

  • Inventory the source: schemas, tables, row counts, stored procedures, ETL jobs, reports, downstream consumers, and refresh SLAs. Many organizations do not have this inventory. Make it the first migration output.
  • Rank workloads by value and effort. Find the dead tables, the pipelines no one reads, the reports that duplicate each other. A third of most estates can be retired rather than migrated.
  • Estimate effort from code complexity, not table count. Procedural logic (cursors, T-SQL/PL-SQL, SAS macros) drives the bill, not data volume.
  • Output: current-state map, target-state sketch, migration strategy, a phased plan with cost and a TCO comparison.

2. Design the target#

Use the related platform guidance:

Map source objects to the target: source schemas to bronze (schema-per-source), data products to silver/gold (schema-per-data-product). Decide the landing format (Delta), partitioning/liquid clustering, and the orchestration tool (Lakeflow Jobs via DABs) before anyone writes a pipeline.

3. Migrate#

Work in this order. Each layer unblocks the next.

  • Schema — translate DDL and data types to Delta. Catch the type mismatches early (precision, dates, identity columns, collation).
  • Code — convert stored procedures, views, and ETL to Spark SQL / PySpark and Lakeflow pipelines. This work usually takes the most time. Automate the mechanical 60-80% and reserve human effort for the procedural logic that does not translate.
  • Data — backfill history once, then keep it current with CDC until cutover (see Strategy choices). One-shot bulk load for cold history, incremental for the tail.
  • Orchestration — rebuild schedules and dependencies as Lakeflow Jobs. Run them as service principals, one per environment (see platform lessons).

4. Validate and reconcile#

No cutover without parity evidence. See Validation patterns below.

5. Cut over and optimize#

  • Pick the cutover style (big-bang vs phased) deliberately, not by default.
  • Repoint BI tools and downstream consumers; keep the old system readable for a defined fallback window.
  • Optimize after correctness is proven: right-size warehouses and clusters, OPTIMIZE/liquid clustering, photon, prune the medallion. Premature tuning hides correctness bugs.

6. Operate and hand off#

  • Turn on system tables and build cost and usage dashboards. Give stakeholders clear cost information. See the system tables documentation.
  • PR-driven Terraform, CODEOWNERS, pinned providers, retained plans (Databricks platform lessons).
  • Document the operating procedures, train the operations team, and define the support model. Include the handoff in the migration plan.

Strategy choices#

Name the choice on each axis explicitly in the migration plan.

  • Rehost vs replatform vs refactor. Rehost = move as-is (rare, only for code that already fits). Replatform = adapt to Delta/Spark with minimal redesign (the common case). Refactor = redesign the data model and logic (highest value, highest cost; reserve for the workloads that justify it).
  • Big-bang vs phased / strangler-fig. Big-bang suits small, well-understood estates with a hard deadline. Phased (strangle the old platform one data product at a time) suits large estates and lowers risk; the cost is running both platforms longer.
  • Dual-run / parallel. Run old and new side by side and compare outputs on every cycle until trust is established. The strongest validation signal there is. Budget for the duplicate compute.
  • CDC for ongoing sync. Keep the target current with change data capture while you build and validate, so cutover is a switch, not a freeze. Decide the CDC mechanism per source (log-based, timestamp, or a tool).

Validation and reconciliation patterns#

Layer these from cheap to expensive; do not skip the last one.

  • Row counts per table, source vs target, after every load. Cheapest signal, catches gross load failures.
  • Checksums / hashes on columns or whole rows (e.g. hash aggregates) to catch value drift counts miss — truncation, type coercion, encoding, null handling.
  • Aggregate parity on numeric columns: sums, mins, maxes, distinct counts.
  • Business-metric parity. Reconcile the numbers the business actually reports — revenue, headcount, the figures on the dashboard — old vs new. This is the only check executives believe. Bake it into the dual-run.
  • Reconciliation report as a deliverable: per-table pass/fail with the discrepancies listed, signed off before cutover. Databricks Lakebridge includes a reconcile step that reports schema, row, and column differences (verify scope against current docs).

Risk management and rollback#

  • Keep the source authoritative until parity is signed off. No deletions on the source during the dual-run.
  • Define the rollback before cutover: what triggers it, who calls it, how long the old system stays warm (a defined fallback window, e.g. one to two reporting cycles). A cutover without a written rollback is a gamble.
  • Use phases to limit the effect of a failure. A phased migration means a failure affects one data product, not the whole estate.
  • Track risks explicitly — top sources are procedural-logic translation, hidden downstream consumers, and SLA regressions on first runs. Surface them in status, do not bury them.

Source-specific migration considerations#

  • Teradata — heavy stored-procedure/BTEQ logic and proprietary SQL; data movement and code conversion dominate. Mature accelerator territory.
  • Oracle — PL/SQL packages, sequences, and Exadata tuning assumptions do not port; expect real refactoring of procedural code.
  • Snowflake — closest paradigm (cloud SQL warehouse); mostly SQL dialect and semi-structured handling differences, lighter on code translation, watch governance and cost model remapping.
  • Hadoop / Hive — migrating off HDFS/Hive metastore to Delta + Unity Catalog; table format conversion and decommissioning a cluster fleet are the work.
  • Amazon Redshift — SQL dialect, UNLOAD/COPY patterns, and a cross-cloud data egress (AWS to Azure) to plan and pay for.
  • Cognos / TM1 — these are BI/planning tools, not warehouses. The job is re-pointing reports and rebuilding semantic/planning models, not moving rows.

Sources#

Referenced by