Setting the file. One moment.
Chapter 54 · Migrating To Amazon Redshift
Subchapter 54.1
references/teradata/architecture-mapping.mdMarkdown6 KBView on GitHub
AI-facing knowledge. Pairs with
conversion-rules.md(the mechanics) — this doc is the so the AI makes sound physical-design choices, not just syntactic ones.
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).
| 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. |
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.
WHERE.
Map TD PPI columns here directly.VACUUM REINDEX). Default to compound.-- 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 rangeIf store is small, also consider DISTSTYLE ALL on store so the sales↔store join is local.
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.
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.)
BANKING_DW / tpcds tables from discovery output.