Migrating from Teradata and SQL Server to Databricks: A Practical Playbook

Teradata and SQL Server served enterprises well for decades, but both were built for a world of predictable batch windows and structured-only data. Databricks, running on Azure Data Lake Storage (ADLS) with Unity Catalog governing everything on top, is built for a different world: streaming and batch side by side, machine learning next to BI, and compute that scales down to zero when nobody's using it. If you're staring down a Teradata or SQL Server estate that's grown expensive, rigid, or both, here's how the migration actually plays out on the ground.

Start with the inventory, not the tooling

The instinct is to jump straight to picking a migration tool. Resist it. The first real work is a full inventory of what you're moving:

• ▸Teradata: BTEQ scripts, Fast Load/Fast Export jobs, stored procedures (often heavier logic than people remember), and any Teradata-specific SQL dialect features (QUALIFY, statistics collection, PPI partitioning).
• ▸SQL Server: SSIS packages, linked servers, CLR procedures, and any reliance on T-SQL-specific constructs (MERGE, CROSS APPLY, temp tables used as pseudo-cursors).

Score each object on complexity and business criticality. This is what determines your migration wave plan — not "which tables are biggest," but "which pipelines have the most tangled dependency graphs and who screams if they break."

Architecture: land in a medallion pattern

Regardless of source, the target shape is consistent:

1. ▸Bronze — raw ingested data, schema-on-read, append-only, mirrors source structure as closely as possible for auditability.
2. ▸Silver — cleansed, conformed, deduplicated, with business keys and slowly changing dimension logic applied.
3. ▸Gold — curated, aggregated, consumption-ready tables mapped to specific BI or ML use cases.

Unity Catalog sits across all three layers and is what makes this defensible from a governance standpoint — a single place for access grants, lineage, and audit logs instead of the patchwork of SQL Server logins and Teradata roles you're leaving behind.

Moving the data itself

For SQL Server, the well-worn path is BCP-based extraction to flat files or Parquet, landed in ADLS, then picked up by Auto Loader into Bronze. For larger, continuously changing tables, Azure Data Factory (ADF) with change data capture is usually more sustainable than repeated full extracts — set it up once and let incremental loads carry the weight.

For Teradata, Fast Export to flat files staged in blob storage is still the most reliable bulk-extraction pattern for the largest tables. Teradata Parallel Transporter can work too, but in practice the operational overhead of managing PT jobs during a migration rarely pays for itself versus a straightforward Fast Export → ADLS → Auto Loader chain.

Whichever source, validate with row counts and checksums at every hop, not just at the end. A discrepancy caught at the Bronze layer costs you an hour; the same discrepancy discovered after three transformation layers costs you a week.

The part everyone underestimates: SQL translation

Teradata and T-SQL dialect quirks don't map cleanly onto Spark SQL, and this is where migrations quietly blow their timelines. A few patterns worth knowing in advance:

• ▸Teradata's
  Code

  QUALIFY

  clause has no direct equivalent — rewrite using window functions in a subquery with an outer filter.
• ▸SQL Server's
  Code

  MERGE

  statement often needs to become a
  Code

  MERGE INTO

  in Delta Lake, which is close but not identical in locking and conflict semantics — test concurrent write behavior explicitly.
• ▸Identity columns and sequences behave differently; Delta Lake's
  Code

  IDENTITY

  columns work but aren't a drop-in replacement for SQL Server's
  Code

  IDENTITY(1,1)

  under high-concurrency inserts.
• ▸Stored procedure logic with heavy cursor use is the single biggest source of rewrite effort. Budget real time to convert cursor-based row-by-row logic into set-based Spark transformations — this is usually where the biggest performance wins live too.

Orchestration and CI/CD

Whatever your current orchestrator, plan the target state early. Databricks Workflows handles native job orchestration well for Databricks-only pipelines; if you already run Airflow elsewhere in your stack, keeping a single orchestration plane (with Databricks jobs triggered as tasks) is usually less disruptive than running two schedulers side by side. Pair this with Terraform for workspace and cluster policy management, and a proper CI/CD pipeline (Databricks Asset Bundles work well here) so promotions across dev/stage/prod are consistent and auditable rather than manual notebook copies.

Cutover strategy: incremental, not big-bang

Don't flip an entire subject area at once. Run Databricks pipelines in parallel with the legacy system for at least one full business cycle (month-end close, if that's relevant to your domain), reconciling outputs continuously. Move consumption layer by layer — start with a low-risk reporting workload, prove it out, then move higher-stakes pipelines once the pattern is trusted by both the engineering team and the business stakeholders who depend on the numbers.

What actually determines success

The technical migration is the easy half. The harder half is organizational: getting analysts comfortable with a new query surface, getting DBAs comfortable with cluster-based cost models instead of fixed licensing, and giving the business confidence that Gold-layer numbers match what they've trusted for years. Budget real time for parallel validation and stakeholder sign-off — it's not a checkbox at the end, it's the thing that makes the whole migration land well.