Migrating from Teradata to Amazon Redshift: What the Diagrams Don't Tell You

Every Teradata-to-Redshift migration deck looks the same: extract, land in S3, load with COPY, done. The diagram isn't wrong, it's just missing the ten decisions that actually determine whether the migration succeeds. Here's the version with those decisions included.

Understand what you're actually replacing

Teradata's architecture leans heavily on its own optimizer, PPI (Partitioned Primary Index) for physical data organization, and AMPs distributing work evenly by design. Redshift's distribution and sort key model is conceptually similar but requires you to make those decisions explicitly rather than letting the platform infer them. This is the single biggest mindset shift for a Teradata team: you're going from "the engine figures out data placement" to "you design data placement, and the engine executes against your design."

Get this wrong and you'll ship a migration that's functionally correct but painfully slow — a common failure mode where teams migrate the schema faithfully and then wonder why query times tripled.

Distribution and sort keys: do this analysis before you write a single COPY command

For every fact table, identify:

• ▸Distribution style:
  Code

  KEY

  distribution on the most common join column for large tables that join frequently;
  Code

  ALL

  distribution for small, frequently-joined dimension tables;
  Code

  EVEN

  distribution as the fallback when no clear join pattern dominates.
• ▸Sort keys: compound sort keys for tables filtered predictably on a leading column (commonly a date), interleaved sort keys only when multiple, roughly-equal-weight filter columns exist — and even then, sparingly, because interleaved sort keys carry real maintenance overhead (
  Code

  VACUUM REINDEX

  isn't cheap).

Mapping Teradata's PPI columns to Redshift sort keys is usually your fastest starting point, since PPI was already telling you how the business queries the data.

Data movement: S3 as the staging layer

The standard, well-tested pattern:

1. ▸Extract from Teradata using Fast Export (or BTEQ for smaller tables) to delimited or Parquet files.
2. ▸Land files in S3, partitioned in a way that mirrors your intended load pattern.
3. ▸Load into Redshift using the
   Code

   COPY

   command, which is dramatically faster than row-by-row INSERT and is the only sane way to move bulk historical data.
4. ▸For ongoing incremental loads, AWS DMS (Database Migration Service) with CDC can keep Redshift synchronized during the parallel-run period, though for larger, high-change-volume tables a custom extraction and merge pattern is often more controllable than DMS's black-box replication.

Validate every load with row counts and, for financial or clinical-grade data, checksum comparisons on key aggregate columns — not just row counts, since row counts alone won't catch value-level corruption from encoding or type mismatches.

SQL and stored procedure conversion

Teradata SQL and Redshift's Postgres-derived dialect diverge in specific, predictable ways:

• ▸
  Code

  QUALIFY

  has no direct equivalent — convert to a window function in a CTE with an outer
  Code

  WHERE

  .
• ▸Teradata's implicit casting is far more permissive than Redshift's; audit for casts that were silently working in Teradata and will error out or (worse) silently truncate in Redshift.
• ▸BTEQ macros and stored procedures with heavy procedural logic need to become Redshift stored procedures (
  Code

  CREATE PROCEDURE

  with PL/pgSQL-style syntax) or, better, be pushed into your orchestration layer as a sequence of set-based SQL statements — Redshift performs much better with set-based logic than procedural row-by-row processing.
• ▸Teradata's date/time functions (
  Code

  ADD_MONTHS

  ,
  Code

  SUBSTR

  on dates) need explicit conversion to Redshift's function set.

Workload management (WMM) matters more than people expect

Redshift's concurrency scaling and WLM queues are how you protect interactive dashboard users from a heavy nightly ETL job hammering the cluster. Set this up before go-live, not after the first complaint. If you're on Redshift Serverless rather than provisioned clusters, the equivalent lever is base RPU sizing and usage limits — get these wrong and either performance suffers or costs run away quietly in the background.

RA3 nodes and Redshift Spectrum change the calculus

If cost was part of what pushed you off Teradata, know that RA3 node types decouple storage from compute, and Redshift Spectrum lets you query data directly in S3 without loading it at all. For colder historical data — the kind Teradata often forced you to keep in expensive hot storage — Spectrum against S3-resident Parquet is frequently both cheaper and fast enough for the access pattern.

Parallel run and cutover

Run Redshift and Teradata side by side against the same source feeds for at least one full reporting cycle. Reconcile at the aggregate level first (are the totals right), then at the row level for any discrepancies. Move read-only reporting workloads first, then ELT/ETL ownership, then finally decommission Teradata access last — once every downstream consumer has been confirmed off it, not before.

The real lesson

Teams that treat this as "same schema, different engine" get a working-but-slow Redshift cluster. Teams that treat it as a genuine redesign opportunity — rethinking distribution, consolidating overlapping tables, retiring unused reports — come out the other side with something meaningfully better than what they had, not just a re-platformed copy of it.