Migrate

Migration playbook

Migrate5 min readreview needed

A source-agnostic methodology for moving legacy data platforms onto Azure Databricks. Source-specific detail lives in the sibling docs (sql-server-to-databricks.md, azure-synapse-to-databricks.md); this is the reusable spine they hang off.

The goal of any migration is not "the same thing, on Databricks." It is a smaller, cheaper, better-governed platform that the client can run themselves. Lift-and-shift the queries that still earn their keep; retire the rest.

The six phases#

1. Assess and build the business case#

  • Inventory the source: schemas, tables, row counts, stored procedures, ETL jobs, reports, downstream consumers, refresh SLAs. Most clients do not have this list. Producing it is the first deliverable.
  • 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#

Lean on the platform docs in this repo rather than re-deriving them:

  • Unity Catalog, medallion, and the env + medallion catalog strategy: lessons-learned/platform-lessons.md.
  • Workspace and environment strategy (workspace per environment): lessons-learned/platform-lessons.md.
  • Networking (size subnets generously, you cannot resize them; Private Link): lessons-learned/platform-lessons.md.
  • Grants the right way (databricks_grant singular vs databricks_grants plural; grant to groups): best-practices/unity-catalog-grants.md.
  • Identity via Entra Automatic Identity Management or SCIM: best-practices/identity-entra-scim.md.
  • Terraform owns the platform, DABs own the workloads: best-practices/terraform-vs-dabs.md.

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 is the long pole. 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/usage dashboards (best-practices/auditing-and-system-tables.md). The client must see spend.
  • PR-driven Terraform, CODEOWNERS, pinned providers, retained plans (lessons-learned/platform-lessons.md).
  • Document runbooks, train the client's team, and define the support model. Hand-off is a deliverable, not an afterthought.

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.
  • Phase to shrink blast radius. Strangler-fig 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.

Other sources (what's distinctive)#

  • 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 don't 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#