Architecture mapping — Teradata → Redshift

AI-facing knowledge. Pairs with conversion-rules.md (the mechanics) — this doc is the why/when so the AI makes sound physical-design choices, not just syntactic ones.

Purpose

Teradata and Redshift distribute and organize data very differently. A syntactically correct DDL conversion can still perform badly if distribution/sort/encoding are chosen poorly. The conversion rules in conversion-rules.md apply the mechanical mapping (e.g. PRIMARY INDEX (x) → DISTKEY(x)); use this doc to reason about and improve those choices using discovery data (table sizes, cardinality, join/filter patterns).

Core construct mapping

Teradata	Redshift	Notes
`PRIMARY INDEX (col)` (single)	`DISTKEY(col)`	TD PI drives data distribution; DISTKEY is the closest analog. Distribution only — uniqueness is not enforced in Redshift.
`PRIMARY INDEX (a, b, …)` (multi)	Mechanical default = `DISTSTYLE EVEN` (per `conversion-rules.md`); this optimization pass may upgrade to `DISTKEY(col)` on one high-cardinality join col (or `DISTSTYLE AUTO`)	Redshift DISTKEY is single-column. Don't blindly pick the first PI column.
`UNIQUE PRIMARY INDEX (col)`	`DISTKEY(col)` + enforce uniqueness upstream	Redshift does not enforce PK/unique constraints; declare them (informational) for the optimizer, but dedup in the load/ETL.
`PARTITION BY RANGE_N(col …)` (PPI)	`SORTKEY(col)`	TD partitions for pruning; Redshift uses sort keys + zone maps for the same effect. Date/timestamp PPIs → date `SORTKEY`.
Secondary Index (SI), Hash Index	(none)	No direct equivalent. Rely on SORTKEY + zone maps; for selective lookups consider a `SORTKEY` on the filter column.
Join Index	Materialized View	Pre-computed/aggregated join → `CREATE MATERIALIZED VIEW`.
`SET TABLE`	`CREATE TABLE` + dedup logic	Redshift allows duplicate rows; if SET (no-dupes) semantics matter, dedup on load (`ROW_NUMBER()`/staging).
`MULTISET TABLE`	`CREATE TABLE`	Direct — duplicates allowed in both.
`COMPRESS` / `COMPRESS (vals)`	`ENCODE az64` / `ENCODE bytedict`	Column compression. Let `ENCODE AUTO` decide unless you have a reason; bytedict suits low-cardinality.
`NO PRIMARY INDEX` (NoPI)	`DISTSTYLE EVEN`	Even, round-robin distribution.
`COLLECT STATISTICS`	`ANALYZE`	Run after load; Redshift also auto-analyzes.

Choosing DISTSTYLE / DISTKEY

Decide per table using discovery data (inventory.json sizes + join/filter info):

DISTSTYLE ALL — small dimension tables (roughly < ~2–3M rows / a few hundred MB). Replicates the table to every node → eliminates redistribution on joins. Best default for dimensions.
DISTKEY(col) — large fact tables. Choose the column most often joined to other large tables (typically the FK to the biggest dimension), with high cardinality and even distribution (avoid skew). Co-locating both sides of a frequent join on the same DISTKEY is the biggest performance win.
DISTSTYLE EVEN — large tables with no dominant join column, or staging tables.
DISTSTYLE AUTO — reasonable default when unsure; Redshift manages it. Prefer explicit KEY/ALL once you know the workload (from performance.md query analysis).

Avoid skew: don't pick a DISTKEY with heavy value concentration (e.g. a status flag, or a nullable FK with many NULLs) — it overloads one slice.

Choosing SORTKEY

Put the most common range/filter column first — usually the date/timestamp used in WHERE. Map TD PPI columns here directly.
Compound SORTKEY (default) — great when queries filter on a leading prefix of the keys.
Interleaved SORTKEY — only when queries filter on different subsets of columns unpredictably; higher maintenance cost (VACUUM REINDEX). Default to compound.
Keep it short (1–3 columns).

Worked example

-- Teradata
CREATE MULTISET TABLE sales (
    sale_id     INTEGER,
    store_id    INTEGER,
    sale_dt     DATE,
    amount      DECIMAL(15,2) COMPRESS
)
PRIMARY INDEX (store_id)
PARTITION BY RANGE_N(sale_dt BETWEEN DATE '2020-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH);

-- Redshift (script does the mechanical mapping; reasoning below)
CREATE TABLE sales (
    sale_id   INTEGER,
    store_id  INTEGER,
    sale_dt   DATE,
    amount    DECIMAL(15,2) ENCODE az64
)
DISTKEY(store_id)     -- store_id is the join FK to the store dimension, high-cardinality → good
SORTKEY(sale_dt);     -- from the monthly PPI; most queries filter by date range

If store is small, also consider DISTSTYLE ALL on store so the sales↔store join is local.

How this pairs with conversion

Applying conversion-rules.md gives the mechanical result (single-col PI → DISTKEY, PPI → SORTKEY, multi-col PI → DISTSTYLE EVEN, COMPRESS → ENCODE). Treat that as the starting point, then revisit DISTKEY/DISTSTYLE/SORTKEY per the heuristics above using discovery sizes and the representative-query analysis from performance.md.

Case sensitivity (collation)

Teradata defaults to NOT CASESPECIFIC in Teradata-session mode, so most string comparisons/joins are case-insensitive; Redshift is case-sensitive by default. To preserve semantics without wrapping columns in UPPER() (which defeats sort-key use and sargability), set COLLATE CASE_INSENSITIVE at the column or database level on tables this migration creates. Reserve UPPER() for ad-hoc queries against pre-existing case-sensitive columns. This affects most TD string columns — treat it as a default, not an edge case. (See the clause rule in conversion-rules.md.)

TODO / extend

Add table-size thresholds for ALL vs KEY tuned to the target node type (RA3 vs serverless).
Worked examples for a couple of real BANKING_DW / tpcds tables from discovery output.

/migrating-to-amazon-redshift

referencesteradataarchitecture-mapping.md