# Databricks migration guide

A general method for migrations from legacy data platforms to Azure Databricks. Use the
[SQL Server guide](sql-server-to-databricks.md) or the
[Azure Synapse guide](azure-synapse-to-databricks.md) 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:

- Unity Catalog, medallion, workspace strategy, and networking:
  [Databricks platform lessons](../lessons-learned/platform-lessons.md).
- Grants the right way (`databricks_grant` singular vs `databricks_grants`
  plural; grant to groups): [Unity Catalog grants](../best-practices/unity-catalog-grants.md).
- Identity via Entra Automatic Identity Management or SCIM:
  [Identity with Entra ID and SCIM](../best-practices/identity-entra-scim.md).
- Terraform owns the platform, DABs own the workloads:
  [Terraform and DABs ownership boundaries](../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 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](https://learn.microsoft.com/azure/databricks/admin/system-tables/).
- PR-driven Terraform, CODEOWNERS, pinned providers, retained plans
  ([Databricks platform lessons](../lessons-learned/platform-lessons.md)).
- 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

- Databricks — Migrate to Databricks: https://www.databricks.com/solutions/migration
- Databricks — Lakebridge: https://www.databricks.com/solutions/migration/lakebridge