Teradata to Databricks Migration: A Step-by-Step Guide

Quick Answer: Teradata to Databricks migration: A Teradata to Databricks migration succeeds when you treat it as four separate workstreams — inventory, extraction, conversion and validation — rather than as a port. The data moves first and moves easily: Teradata tables extract to Parquet chunks in parallel and land in a Delta landing zone within days. The hard parts are the two things nobody budgets for: splitting high-volume tables into parallel extractable chunks without hammering the source, and proving the migrated numbers still tie out. Migrate one domain at a time, in parallel with the existing system, and cut over only after control totals match.

What this guide covers

• How to inventory a Teradata estate you have to migrate
• Four ways to extract, and how to choose between them
• Splitting high-volume tables into parallel chunks
• Converting Teradata DDL and data types to Delta Lake
• Translating Teradata SQL to Spark SQL
• Loading, validating, and reconciling with control totals
• A phased cutover plan that does not require a big-bang weekend

---

Why Teradata estates still need migrating

Teradata has been in production for four decades, and it is not going anywhere quietly. What is happening is that the estates built on it in the 1990s and 2000s now need to do things the original platform was never designed for: serve a BI tool that expects SQL, feed a machine learning pipeline that expects files, expose data to a partner organisation, or support a reporting workload that has outgrown the appliance.

Teradata is a relational database management system built around a proprietary parallel architecture, where data is distributed across multiple nodes and rows are distributed by hash on a primary index. It remains a strong analytical database — the parallelism is real, and the optimiser is excellent on large scans.

The pressure usually comes from three directions at once:

• Licensing and hardware refresh. A dedicated appliance or a cluster licence has a predictable renewal cost attached to it.
• Access. New teams want to query the data with Spark, a notebook, or a BI tool over SQL rather than through an application-specific client.
• Modern consumption. Streaming, machine learning and sharing with external parties are all lakehouse-shaped requirements.

None of this makes Teradata bad. It makes it a system of record rather than the system of analysis, which is the distinction that decides what gets migrated. The tables that feed a regulatory filing may never need to move. The tables that feed a dashboard for 200 analysts probably do.

---

Step 1: Inventory what actually needs to move

Most migration proposals fail at this step, because the estimate is built from a list of table names rather than from usage.

Build the inventory across four dimensions, and pull usage from the system’s own query log rather than from anyone’s memory.

The four inventories

Inventory	What to capture	How to get it
Tables	Name, row count, size, primary index, partitioning, fallback, volatility	Database catalog views
Code	Every view, macro, stored procedure, BTEQ script, application query	Source control, plus the query log for what actually runs
Schedule	Jobs, dependencies, windows, downstream consumers	Scheduler definitions
Consumption	Who reads what, and which outputs are contractual	Query log plus a conversation with the owning team

The consumption inventory is the one that decides scope, and the query log is what makes it credible. A table that shows up in the catalog but not in six months of query history is a candidate for archival rather than migration. A table queried every fifteen minutes by the finance team is not a candidate for anything else.

Also capture, for every table:

• The primary index. In Teradata, rows are distributed across nodes by hash on the primary index. This single fact determines how you can parallelise an extract, and it is the difference between a migration that takes a week and one that takes a month.
• Fallback. Teradata’s fallback protects rows from node failure and can duplicate data on disk. It changes both the storage footprint and, occasionally, the row counts you get from different access paths.
• Volatility. A volatile table’s contents are not guaranteed stable until a checkpoint. Extracting from a volatile table concurrently with writes gives you a moving target — you must either quiesce it or reconcile afterwards.

---

Step 2: Choose the extraction method

Four realistic options, and the right answer is usually a mix.

Method	Good for	Limits
Teradata Parallel Transporter (TPT)	Very large tables, parallel multi-stream loads, bulk data movement	Operationally heavy; needs schema definitions; proprietary configuration
JDBC with partitioned parallel reads	Most migrations; flexible, scriptable, no extra moving parts	Requires a partition strategy to be genuinely parallel
BTEQ / FastExport style scripts	Small, scheduled, high-frequency extracts	Not built for very large volumes
CDC capture	Keeping source and target in sync during a long parallel run	More setup; needs change tracking on the source

For a first migration, JDBC with partitioned parallel reads is usually the best trade-off. It needs no additional licensed tooling, it is scriptable and repeatable, and it scales by adding workers. TPT earns its place when volumes are large enough that load throughput on the target side becomes the bottleneck.

Teradata’s own documentation covers the loading utilities and the JDBC path in detail — the Teradata documentation portal and Teradata support are the right starting points for the source-side specifics, and Databricks’ migration guides cover the destination side.

---

Step 3: Split high-volume tables into parallel chunks

This is the technique that makes migration timelines realistic, and it is the one most guides skip.

The principle: never read a large table in one pass. Instead, divide it into ranges that can be extracted simultaneously, each to its own output location, then land them together.

The chunking strategies, in order of preference

1. Partition-aligned chunks (best). If the table is partitioned, extract one partition per worker. Teradata partitions are commonly defined on a date or business range, which maps almost perfectly onto a date-partitioned Delta table on the other side. A table with 36 monthly partitions becomes 36 parallel extracts, and the landing layout is already correct.

from pyspark.sql import functions as F
from datetime import date

def chunk_by_partition(start: date, end: date, step_days: int = 7):
    """Yield (lo, hi) windows that map onto source partitions."""
    cur, hi = start, end
    while cur < hi:
        nxt = min(date.fromordinal(cur.toordinal() + step_days), end)
        yield (cur.isoformat(), nxt.isoformat())
        cur = nxt

2. Primary-index hash ranges. Rows are distributed by hash on the primary index, so a hash range maps to a contiguous set of rows and therefore to a bounded subset of the data. Extracting several disjoint hash ranges in parallel is the closest thing Teradata has to a native partition split. The caveat is that you need to know the index expression and its distribution, which is exactly why the inventory step matters.

3. Range chunks on a date or ID column. When neither partitioning nor a usable index is available, fall back to range chunks on a monotonically increasing column and read each range with a WHERE clause. This works but is less efficient, because it cannot use the distribution to prune — every worker still scans the index.

4. A staging copy. For the hardest tables, copy the source data to a purpose-built staging area first, then extract from the copy. It costs storage and time and buys back the source system’s performance during business hours.

Running the chunks

The pattern is the same regardless of strategy: each worker writes to its own path, and nothing merges until all chunks are present.

# One worker, one chunk, one output directory
def extract_chunk(jdbc_url: str, query: str, out_path: str, rows_per_partition: int = 2_000_000):
    df = (
        spark.read.format("jdbc")
        .option("url", jdbc_url)
        .option("query", query)          # includes the chunk predicate
        .option("fetchsize", 100000)
        .option("partitionColumn", "id") # parallel reads, if the source supports it
        .option("lowerBound", 0)
        .option("upperBound", 40_000_000)
        .option("numPartitions", 16)
        .load()
    )
    (
        df.write
        .mode("append")
        .option("maxRecordsPerFile", rows_per_partition)
        .parquet(out_path)
    )

The output layout matters. Write each chunk to a distinct path, then read the whole set as one dataset when landing:

s3://migration/teradata/orders/chunk=0000/  part-0000.parquet
s3://migration/teradata/orders/chunk=0001/  part-0000.parquet
s3://migration/teradata/orders/chunk=0002/  part-0000.parquet

Two operational rules that save real pain. Throttle the source. A full-speed parallel extract from Teradata can saturate its I/O and degrade every other workload on that appliance, including production reporting. Start with two workers and increase while watching source-side response time. And make extraction restartable — write to a per-chunk path, and on rerun skip chunks whose files already exist. A 40-chunk extraction that fails at chunk 31 should cost you one chunk, not forty.

---

Step 4: Convert the DDL and the types

Teradata’s type system is richer than Spark’s, and every gap needs a decision rather than a default.

Teradata type	Delta / Spark equivalent	Watch for
INTEGER, BIGINT, DECIMAL(p,s)	Direct equivalents	Precision must be preserved; DECIMAL defaults differ
CHAR(n) / VARCHAR(n)	STRING	Trailing-space semantics differ between the two systems
DATE, TIME, TIMESTAMP	Direct equivalents	Teradata DATE is a formatted value, not a date type, in older schemas
INTERVAL	INTERVAL variants, or derive to a numeric or string column	Multiple interval qualifiers do not map one-to-one
PERIOD (e.g. PERIOD(DATE))	Two columns: a start and an end	The most common source of conversion errors — decide on validity and missing end dates
MULTISET / SET (tables)	Normalised child table, or an array column	Row order is not guaranteed in either system
BYTE / VARBYTE	BINARY	Confirm the hex/bytes representation matches
CLOB / BLOB	STRING or BINARY	Large payloads should probably not live in the analytical table at all

PERIOD deserves a specific note, because it appears constantly in Teradata schemas that model validity windows. A PERIOD(DATE) column holds a start date and an end date together. In Delta, the honest representation is two columns plus a generated validity flag, so that “current” rows can be filtered efficiently rather than reconstructed at query time.

-- Teradata
SELECT employee_id, name FROM hr.employment
  WHERE validity > CURRENT_DATE;

-- Delta Lake equivalent
SELECT employee_id, name
FROM silver.employment
WHERE valid_from <= CURRENT_DATE()
  AND (valid_to IS NULL OR valid_to > CURRENT_DATE());

Where the mapping is lossy, record it. A migration log that lists every column whose semantics changed is worth more than the migration itself, because it is the document you will need when a number looks different and someone asks why.

---

Step 5: Translate the SQL

Teradata SQL and Spark SQL overlap heavily but diverge in a handful of places that cause silent differences rather than errors.

Teradata construct	Spark SQL equivalent	The difference that bites
QUALIFY	QUALIFY (supported) or a window function in a subquery	Teradata allows QUALIFY without a GROUP BY; Spark’s support depends on the context
NORMALIZE	COLLECT_SET / ARRAY_AGG	Teradata normalises into a row per value; Spark aggregates into an array
TOP n / TOP n PERCENT	ROW_NUMBER() window + filter, or LIMIT	TOP with ties returns a different count than LIMIT
SAMPLE	TABLESAMPLE or RAND()-based sampling	Sample sizes and reproducibility differ
DATE arithmetic (d - 1)	DATE_SUB(d, 1)	Teradata’s implicit date arithmetic is convenient and not portable
NULL handling in aggregates	Explicit COUNT(col) vs COUNT(*)	The same — but the default null ordering differs between the systems
GROUP BY with no aggregates	GROUP BY + collect_list	Teradata’s implicit grouping semantics differ
SELF JOIN with USING	Explicit ON clauses	Column resolution on ambiguous keys differs
WITH ... TOP	CTE + window function	Precedence of WITH and TOP is not identical

Two habits keep this honest. First, never trust an eyeball comparison — validate with control totals, which is the next section. Second, for anything that becomes a recurring report, write the Spark SQL to be explicit rather than idiomatic. A slightly verbose query that a reader can follow is worth more than a compact one whose null-handling behaviour is ambiguous.

---

Step 6: Land it, then validate with control totals

Landing

Read the Parquet chunks as one dataset, then write into the Delta landing zone with the target schema applied:

from pyspark.sql import functions as F

landed = (
    spark.read.parquet("s3://migration/teradata/orders/")
    .withColumn("valid_from", F.to_date("valid_from"))
    .withColumn("valid_to", F.to_date("valid_to"))
)

(
    landed.write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .option("mergeSchema", "true")
    .saveAsTable("bronze.teradata_orders")
)

Land in bronze, unchanged apart from type coercion. This is where the medallion pattern earns its keep — the Databricks medallion architecture guide covers why the raw landing layer needs to be complete and replayable rather than clean.

Control totals, not row counts

A row count tells you that the same number of rows arrived. It does not tell you they are the right rows, or that the values match. Four checks, in order of cost:

1. Row counts and distinct key counts. Cheap, and catches truncation and duplication — the two most common extraction failures.

SELECT
  count(*)                                        AS row_count,
  count(DISTINCT order_id)                         AS distinct_keys,
  count(*) - count(DISTINCT order_id)              AS duplicate_keys
FROM bronze.teradata_orders;

2. Aggregate control totals. Sum and average every numeric measure, plus counts of nulls and negative values. This is where type-conversion errors surface: a DECIMAL truncated on the way across shifts a sum, and a date parsed wrongly shifts a count.

SELECT
  sum(order_amount)  AS total_amount,
  avg(order_amount)  AS avg_amount,
  min(order_amount)  AS min_amount,
  max(order_amount)  AS max_amount,
  sum(CASE WHEN order_amount IS NULL THEN 1 ELSE 0 END) AS null_amounts
FROM bronze.teradata_orders;

3. Hash checksums on sampled keys. Take a deterministic sample — not a random one, which will differ between runs — and compare row-level checksums between source and target. This is the check that catches column mapping errors, which aggregates alone can miss.

4. A parallel run period. Run both systems over the same window and compare outputs automatically, daily, until they agree. For anything feeding regulatory reporting, this period is not optional, and the comparison should be a signed reconciliation rather than a spot check.

Set the tolerance in advance and write it down. “Within 0.01% on amounts, exact on key counts” is a decision. “Close enough” is not, and it is the point at which a migration quietly changes a number.

---

Step 7: Cut over one domain at a time

A big-bang cutover concentrates every risk into one weekend and gives you no way to isolate a problem afterwards. The alternative is boring and works.

Phase it by domain. Choose a first domain that is low-risk and visible — an internal reporting dataset with a known owner, not the regulatory ledger. Migrate it end to end, including validation and cutover, and use it to prove the pattern.

Keep the source read-only, not live. Once a domain has migrated, Teradata can stay available for validation and rollback for a defined period. “Deletable after the parallel run” is a decision with a date attached.

Retire the old path deliberately. The most common outcome of a partial migration is a permanently duplicated estate: two systems, two sets of schedules, two sets of reconciliation, and a bill for both. Set a decommission date when you cut over, not later.

Phase	Duration (indicative)	Exit criterion
Inventory and estimation	1–2 weeks	Every table classified: migrate, archive, or leave
Extraction tooling	1 week	One table extracted, chunked and landed in Delta
Bulk migration by domain	2–6 weeks	All in-scope tables landed and validated
Parallel run	2–4 weeks	Control totals match to agreed tolerance, daily
Cutover and decommission	1–2 weeks	Consumers moved, source decommissioned

The durations are indicative and depend entirely on the inventory numbers from step 1 — which is exactly why a migration estimated without that inventory is fiction.

---

What this means for a career in India

Teradata-to-lakehouse migration work shows up constantly in Indian banking and insurance, where Teradata estates are large, old and load-bearing. It is also unusually good work to be hired for, because the skill is not “I know two databases” — it is I can move data between systems of record and systems of analysis without breaking the numbers, which is a rarer and more valuable capability than any single platform skill.

The combination that gets hired is SQL fluency plus distributed-processing judgement plus the discipline to validate. The Databricks Training course covers the destination side — Delta Lake, Unity Catalog, jobs and pipelines, MLflow and a governed capstone — and the Data Engineering Training programme covers the platform-neutral foundations across SQL, Python, Spark, Airflow, Kafka and dbt.

Our Cloud Infrastructure Cost Benchmark 2026 covers the FinOps comparison behind the “should we move at all” question, and the AWS Data Engineering Salary Report 2026 covers what the destination roles pay.

---

How to read these numbers

No vendor pricing is quoted in this article, because Teradata commercial terms are not public and Databricks infrastructure costs vary by cloud and agreement. The structural comparison — a reserved-capacity licence versus metered consumption — is a property of the licensing models, not a price claim. The phase durations are indicative and depend on the inventory, and we would rather publish them as ranges than as false precision. Benchmark figures from our own reports are published with methodology and reproducible scripts.

---

Key terms

Term	What it means in practice
Primary index	The Teradata column or expression rows are hash-distributed by. Determines parallel extract strategy.
Fallback	Teradata’s node-failure protection. Can affect storage footprint and occasionally row counts.
Volatile table	A table whose contents are not stable until a checkpoint. Quiesce or reconcile before extracting.
TPT	Teradata Parallel Transporter. Bulk data movement for very large volumes.
Partition-aligned chunk	An extract unit that matches one source partition. The best parallel split when available.
Hash-range chunk	A slice of the primary-index hash space. Maps to a bounded subset of rows.
PERIOD	A Teradata validity-range type holding a start and an end. Usually becomes two columns.
Control total	An agreed aggregate used to prove migrated figures match the source.
Tolerance	The pre-agreed permitted difference. Must be written down before validation starts.
Parallel run period	A window where source and target both produce output for daily comparison.

---

Frequently asked questions

How long does a Teradata to Databricks migration take?

The honest answer is that it depends on the inventory, and most published timelines are wrong because they were built before anyone counted the tables, the code and the consumers. Data movement itself is usually fast — a multi-terabyte extract chunked in parallel lands in days. The schedule comes from the parts nobody budgets for: SQL translation for views and procedures, type conversion decisions, control-total reconciliation, and the parallel run period, which is measured in weeks by design.

What is the fastest way to extract a very large Teradata table?

Chunk it and parallelise, using the table’s own distribution. If it is partitioned, extract one partition per worker. If it has a usable primary index, split the hash space into disjoint ranges. If neither applies, range-chunk on a monotonically increasing column with a WHERE predicate, accepting that it prunes less efficiently. Always throttle against the source system’s response time, because a saturated Teradata instance degrades production workloads as well.

Can we migrate a Teradata table to Delta Lake without rewriting the SQL?

You can move the data without rewriting anything, and that is the right first move — it de-risks the project. But the SQL has to be rewritten eventually, because Teradata and Spark SQL differ in construct support and in semantics for nulls, ties and aggregates. Land the data unchanged, prove the numbers, then rewrite the logic against the landed copy where it is cheap to do so.

How do we handle Teradata PERIOD and INTERVAL columns?

A PERIOD column becomes two columns — a start and an end — with a validity rule expressed as a predicate, ideally generated so that current rows filter efficiently. INTERVAL types map to the closest Spark interval equivalent or are derived into a numeric duration or a formatted string, depending on how the values are consumed. In every case, write down the rule you chose, because it is the first thing a reviewer will question.

Is it worth migrating Teradata at all?

It depends on what the Teradata estate is doing for the business. If it is feeding regulatory reporting that has been stable for years, the cost of migration may never be recovered. If it is feeding analytics that needs to grow, integrate with machine learning, or be shared externally, the economics usually favour moving the consumption layer to the lakehouse while keeping the source as the system of record. Decide with numbers from your own inventory, not with a benchmark chart.

What validation do we need before cutover?

Four levels, in increasing cost: row and distinct-key counts, aggregate control totals on every numeric measure, deterministic sampled row-level checksums, and a parallel run period comparing outputs daily. Set the tolerance in advance and document it. For anything feeding a regulatory filing, the comparison should be a signed reconciliation record, not an email saying the numbers look right.

Can Teradata and Databricks run side by side during migration?

They should, and that is the normal shape of a migration. Keep Teradata as the system of record while Databricks serves analytics, migrate one domain at a time, and decommission the Teradata path deliberately once a domain has passed its parallel run period. The failure mode to avoid is a permanently duplicated estate with two schedulers and two bills — set the decommission date when you cut over, not later.

---

Where to take this next

Migration work is a data engineering job with a specific shape: inventory, chunked extraction, translation, reconciliation. Engineers who have done it once are in demand wherever legacy estates exist, which in India means banking, insurance and healthcare.

Start with the destination platform in the Databricks Training course, which covers Delta Lake, Unity Catalog, jobs and pipelines, MLflow and a governed capstone across 12 weeks and 72 hours. For the platform-neutral foundations — SQL, Python, Spark, Airflow, Kafka, Delta Lake, medallion modelling and CI/CD — the Data Engineering Training programme spans 16 weeks and 96 hours. The SAS to cloud migration guide covers the same inventory-translation-validation pattern applied to a different legacy estate.

Both programmes run in classroom, live online and hybrid modes from Thoraipakkam, Chennai, with placement support across 200+ hiring partners.

---

References

• Databricks — migration guides
• Databricks — Delta Lake documentation
• Databricks — Unity Catalog overview
• Databricks — data engineering overview
• Teradata documentation portal
• Teradata support
• Apache Spark — SQL join reference
• Apache Spark — SQL performance tuning
• SkilBrill — Cloud Infrastructure Cost Benchmark 2026

---

---

---

---

---

---