26 Oct 2026

Teradata to Databricks Migration: From BTEQ to Lakehouse

A Teradata-to-Databricks migration moves a Teradata warehouse, including its tables, BTEQ scripts, stored procedures, macros, TPT load jobs, security, and reports, onto Delta Lake tables in the Databricks Lakehouse. It is a re-architecture, not a data copy: the risk sits in the scripted logic and in behavior differences such as SET-table duplicate handling, case-insensitive comparisons, and NULL and type semantics, not in moving the data itself. Databricks' free Lakebridge toolkit converts Teradata SQL with its BladeBridge transpiler and reconciles results, but engineers still need to refactor procedural code and prove figure-for-figure parity before cutover.

Teradata has run enterprise warehouses at banks, retailers, telecoms, and insurers for decades. Appliance refresh cycles, capacity-based pricing, and the push toward AI and real-time analytics are making Teradata to Databricks migration one of the most common modernization programs, and one of the easiest to underestimate. This page covers why teams move, what has to move, how each Teradata component migrates, and a phased method that controls risk. For the services side, see our overview of Databricks migration services.

Quick Summary

A Teradata migration is won in the scripts, not the tables: BTEQ, procedures, macros, and volatile-table chains must be refactored for Spark, and Teradata tuning does not transfer. Lakebridge automates assessment, SQL conversion, and reconciliation, but procedural logic and exact-match validation still need engineers. A discovery-led sequence of inventory, design, data migration, code conversion, and validation keeps both risk and cost under control.

Key Takeaways

It is a re-architecture, not a copy. The lakehouse model re-expresses storage, scripted logic, load utilities, orchestration, and security.
The risk lives in the scripts. BTEQ, stored procedures, macros, and volatile-table chains must become set-based Spark SQL or PySpark.
Teradata tuning does not transfer. Primary indexes, PPI, join indexes, and COLLECT STATISTICS give way to liquid clustering, data skipping, and Photon.
Lakebridge covers Teradata. Its BladeBridge transpiler converts Teradata SQL to Databricks SQL, and Reconcile supports Teradata, but procedural logic still needs refactoring.
Sequence matters. Inventory, design, data migration, code conversion, and validation, in that order.
Parity must be proven. Reconcile against Teradata figure for figure, through at least one full business cycle, before cutover.

Why Organizations Move from Teradata to Databricks

The case for leaving Teradata rarely rests on one event. Pressures accumulate until the cost of staying exceeds the cost of moving:

Cost and capacity planning. Teradata platforms are sized for peak demand, so growth means buying nodes or capacity ahead of need. Databricks compute is billed by usage, so auto-terminating clusters stop accruing charges, and data sits in low-cost cloud object storage.
Elastic scale. A Teradata system has a fixed number of AMPs shared by every workload, while Databricks scales out for month-end loads and back down when idle.
AI and machine learning on the same data. Data scientists and BI analysts work on the same governed tables, with notebooks, MLflow, and model serving built in, without extracts out of the warehouse.
Open formats. Delta Lake stores data as open Parquet files with a transaction log, readable by other engines rather than locked in a proprietary format.
Streaming and semi-structured data. JSON, logs, IoT feeds, and change data capture are first-class rather than add-on products.
Unified governance. Unity Catalog provides permissions, row filters, column masks, lineage, and audit in one place.

Savings aren't automatic; they depend on governing usage from day one. If you are still choosing a target, our comparisons of Databricks vs Snowflake, Snowflake migration services, and Microsoft Fabric migration services cover the alternatives.

What Has to Move

Tables are the visible tip of the iceberg. A complete Teradata inventory has five layers, and skipping any one of them is where projects go over budget:

Layer	What It Includes	Commonly Missed
Data and storage	Databases, SET and MULTISET tables, data types, partitioning, and historical volumes	How much history genuinely needs to move
Scripted and procedural logic	BTEQ scripts, stored procedures, macros, views, and volatile or global temporary tables that encode business rules	Business rules buried in session-level scripts
Loads and orchestration	TPT and legacy load utilities, ETL tool mappings, scheduler chains (Control-M, Autosys, cron), and file feeds	Schedules and restart behavior
Security and governance	Users, roles, access rights, row-level security, and view-based masking	View-based security that must be rebuilt in Unity Catalog
Consumers	Reports, dashboards, SAS jobs, extracts, APIs, and downstream applications	Consumers nobody documented

The surrounding estate often includes ETL tools such as Informatica, DataStage, or Ab Initio (covered in our ETL migration to Databricks article) and reporting layers that follow our reports conversion approach. For the broader picture, see data warehouse to Databricks and legacy systems to Databricks.

How Each Teradata Component Migrates

Every part of the Teradata stack fails in its own way when migrated. This table maps the common challenge for each component to a concrete solution:

Teradata Component	Common Challenge	How to Solve It
BTEQ scripts	Thousands of scripts mixing SQL with dot-commands, .IF ERRORCODE branching, .EXPORT and .IMPORT, and session settings with no direct Spark equivalent	Parse scripts into a dependency graph, convert in waves by complexity, move control flow and error handling into Lakeflow Jobs, and keep the SQL set-based
Stored procedures and macros	SPL cursors, dynamic SQL, and parameterized macros embed business logic that runs row by row	Refactor into PySpark functions, Spark SQL, or Databricks SQL stored procedures and scripting, replacing cursor loops with DataFrame operations; see our PL/SQL conversion article for the same principle
Data types and semantics	SET tables drop duplicate rows, NOT CASESPECIFIC columns compare case-insensitively, and DATE arithmetic, PERIOD, and BYTEINT behave differently	Agree a type-mapping standard up front, add explicit de-duplication and collation handling where needed, and test edge cases before bulk loads
Primary index, PPI, and join indexes	Physical design, COLLECT STATISTICS, and AMP-level distribution drive Teradata performance and carry no meaning on Spark	Drop the physical design, use liquid clustering and data skipping on Delta tables, enable Photon, and benchmark top queries before cutover
Teradata-specific SQL	SEL, TOP n, SAMPLE, ZEROIFNULL, NULLIFZERO, OREPLACE, FORMAT clauses, and implicit casts are proprietary	Translate with an automated converter, rewrite to ANSI equivalents (COALESCE, NULLIF, REPLACE, DATE_FORMAT), and validate query by query; QUALIFY is supported natively in Databricks SQL
Volatile and global temporary tables	Multi-step scripts stage results in session-scoped tables created and dropped on every run	Replace with DataFrames, temporary views, or CTEs, and persist only intermediate results that need restartability
TPT, FastLoad, and MultiLoad	Bulk loads, error tables, and checkpoint and restart behavior are built into proprietary utilities	Land files in cloud storage and ingest with Auto Loader or COPY INTO, using Lakeflow expectations to quarantine bad rows instead of error tables
Security and access layers	Access rights, roles, and view-based row-level security do not carry over	Redesign access in Unity Catalog with groups, row filters, and column masks, and test per role before go-live; see our data security compliance checklist
Workload management (TASM)	Priorities and throttles that protect critical workloads disappear	Isolate workloads on separate SQL warehouses and job clusters, with cluster policies, autoscaling limits, and budget alerts

Load jobs and orchestration also connect to broader ETL automation for legacy data migration, and the shift from staged transformation to in-platform transformation is covered in our comparison of traditional vs modern ETL.

What Lakebridge Automates for Teradata

Lakebridge is Databricks' free, open-source migration toolkit from Databricks Labs, built on technology from BladeBridge, which Databricks acquired in 2025. Its documentation lists Teradata as a supported source for the BladeBridge transpiler, which converts Teradata SQL to Databricks SQL, with the LLM-powered Switch transpiler available as an experimental option. Reconcile also supports Teradata, and Databricks has published a Teradata-specific migration guide and profiler of its own.

Lakebridge Piece	What It Does for Teradata	What Still Needs Engineers
Assessment	Profiles the Teradata estate and inventories objects to migrate	Deciding what to retire, mining DBQL usage, and sequencing waves
Transpiler	Converts Teradata SQL to Databricks SQL (BladeBridge); experimental LLM-based conversion (Switch)	BTEQ control flow, procedural logic, and behavior that syntax alone cannot capture
Reconcile	Compares Teradata and Databricks data to prove the move	Choosing what to compare, resolving every difference, and signing off

Third-party estimates of how much of a Teradata migration this automates vary widely, and Databricks itself states only that Lakebridge can automate up to 80% of migration tasks, a vendor figure. The reliable approach is to run it on a pilot workload and measure. For how we combine such tooling with validation, see our Databricks migration services overview.

A Phased Migration Method

The reliable path is sequential and evidence-led, and each phase produces an artifact the next depends on:

Phase	What Happens	Output
Discovery and assessment	Inventory databases, tables, volumes, BTEQ scripts, procedures, macros, load jobs, scheduler chains, and consumers; mine DBQL query logs for usage, complexity, and dependencies	A scoped inventory sized from evidence
Rationalization	Retire unused tables, scripts, and reports; merge duplicates; decide how much history must move	A smaller scope, and the biggest lever on cost
Target lakehouse design	Define the medallion architecture, Unity Catalog structure, naming standards, cluster policies, and migration waves	A target architecture and wave plan
Schema and historical data migration	Convert Teradata DDL and types to Delta tables; bulk-unload history with TPT export, NOS writes to cloud storage, or parallel JDBC extracts landed as Parquet	History loaded into Delta
Incremental sync and code conversion	Keep Databricks current with incremental extracts or CDC; convert BTEQ, procedures, and ETL into PySpark, Spark SQL, and Lakeflow pipelines	Converted, tested code
Validation and parallel run	Reconcile row counts, aggregates, and checksums table by table; compare key report outputs; run both platforms in parallel until numbers match and users sign off	Signed-off reconciliation
Cutover and decommission	Repoint BI tools, SAS jobs, and feeds; train users; monitor performance and cost; retire Teradata workloads and capacity	Realized savings

The phase order mirrors the planning discipline in our key checklist for BI modernization, and our data lake work covers landing zones for the unloaded history. If your migration also touches a wider cloud move, see cloud migration to Databricks and data platform migration to Databricks.

Refactoring BTEQ: A Worked Example

BTEQ wraps SQL in session-level control flow, volatile staging tables, and error-code checks. A literal translation carries that scaffolding into Databricks, where it adds cost and fragility. The goal is to re-express the intent as set operations, as in this customer dimension load.

Teradata BTEQ

CREATE VOLATILE TABLE vt_cust AS (
  SEL cust_id, cust_name, region_cd, upd_ts FROM stg.customer
  QUALIFY ROW_NUMBER() OVER (PARTITION BY cust_id
                             ORDER BY upd_ts DESC) = 1
) WITH DATA PRIMARY INDEX (cust_id) ON COMMIT PRESERVE ROWS;
.IF ERRORCODE <> 0 THEN .QUIT 8;

MERGE INTO edw.dim_customer t USING vt_cust s
ON t.cust_id = s.cust_id
WHEN MATCHED THEN UPDATE SET cust_name = s.cust_name,
                             region_cd = s.region_cd
WHEN NOT MATCHED THEN INSERT VALUES (s.cust_id, s.cust_name, s.region_cd);
.IF ERRORCODE <> 0 THEN .QUIT 8;

Databricks PySpark

w = Window.partitionBy("cust_id").orderBy(F.col("upd_ts").desc())

latest = (spark.table("silver.customer")
          .withColumn("rn", F.row_number().over(w))
          .where("rn = 1").drop("rn"))

(DeltaTable.forName(spark, "gold.dim_customer").alias("t")
 .merge(latest.alias("s"), "t.cust_id = s.cust_id")
 .whenMatchedUpdate(set={"cust_name": "s.cust_name",
                         "region_cd": "s.region_cd"})
 .whenNotMatchedInsert(values={"cust_id": "s.cust_id",
                               "cust_name": "s.cust_name",
                               "region_cd": "s.region_cd"})
 .execute())

The scaffolding disappears: a DataFrame replaces the volatile table, Lakeflow Jobs handles failures and retries instead of QUIT codes, and one atomic Delta MERGE does the load. Because Databricks SQL supports QUALIFY, the same latest-row filter can also stay in SQL if your team prefers it.

Where Silent Drift Hides

Three differences cause most of the numbers that "never quite reconcile":

SET tables and case-insensitive comparisons need testing, not assumptions. Teradata SET tables reject duplicate rows, silently dropping them on INSERT-SELECT loads, and NOT CASESPECIFIC columns treat 'ABC' and 'abc' as equal. Delta tables do neither by default, so a join or filter can quietly shift totals without raising an error. Test every mapping rule against real data before bulk loads begin, the same discipline behind automated ETL testing with Python.
The medallion architecture replaces staging and semantic layers. Teradata warehouses typically use staging, core (often 3NF or an industry data model), and semantic or mart layers built from views. In Databricks, bronze holds raw data, silver holds cleansed and conformed data, and gold holds business-ready aggregates. Mapping existing layers onto this pattern keeps lineage clear and reprocessing safe.
Performance comes from data layout and governed compute. Without primary indexes, PPI, and collected statistics, performance depends on file layout and compute choice. Liquid clustering, data skipping, and Photon replace Teradata physical design, while cluster policies, auto-termination, and serverless SQL warehouses keep spend predictable.

Best Practices for a Low-Risk Migration

Rationalize before you migrate. Use DBQL logs to find tables, scripts, and reports nobody uses.
Design Unity Catalog and access groups first, before loading the first table.
Start with a pilot that is visible to the business but low in risk.
Refactor for Spark rather than translating BTEQ and SPL line by line.
Automate reconciliation and run it on every load during the parallel run.
Run both platforms in parallel through at least one full business cycle, including month-end.
Set cluster policies and budget alerts from day one to control cost.
Version all notebooks and pipelines in Git with CI/CD deployment.

Frequently Asked Questions

How long does a Teradata to Databricks migration take?

It depends on data volume, the number of BTEQ scripts and procedures, and how many consumers rely on the warehouse. A pilot can run in weeks, while an enterprise warehouse is usually delivered in waves over several months.

Can BTEQ scripts run directly in Databricks?

Not as-is. The SQL inside often ports with light syntax changes, but dot-commands, error handling, volatile tables, and export and import steps need conversion to PySpark, Spark SQL, and Lakeflow Jobs.

What tool does Databricks provide for Teradata migration?

Lakebridge, a free, open-source toolkit whose BladeBridge transpiler converts Teradata SQL to Databricks SQL and whose Reconcile component supports Teradata.

How do we move very large Teradata data volumes?

Bulk history is typically unloaded in parallel with TPT export, NOS writes, or JDBC extracts to cloud storage as Parquet, then loaded into Delta, with incremental loads keeping Databricks current until cutover.

Is Databricks cheaper than Teradata?

It can be, because compute is billed by usage and data sits in low-cost object storage, but savings are not automatic. They depend on governing usage with cluster policies, auto-termination, and right-sized warehouses.

Scope Your Teradata Migration with DataTerrain

The data is the easy part of leaving Teradata. The scripts, and the totals they produce, are what you are really migrating. DataTerrain has modernized enterprise data and reporting platforms for 400+ customers over 17+ years, with automated assessment, BTEQ and ETL conversion, and figure-for-figure validation- the same approach we use in our ETL Solutions.

Talk to our migration team