Azure Databricks migration playbook

azure5 min read

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 unused tables, unread pipeline outputs, and duplicate reports. Confirm each retirement with its owner and consumers before you remove it from the migration scope.
  • 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. Test conversion tools on representative code before you estimate automation savings. Include manual correction and result validation in the estimate.
  • 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. Lakebridge Reconcile compares schemas, row hashes, and column values through separate report types. Select the report for each validation requirement. Its all option combines data and schema reports; it does not run every report type. Confirm source support before you select a report.

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

Related topics and tags