/migrating-to-amazon-redshift

Teradata → Redshift Conversion Rules (72 validated rules)

AI-facing knowledge: the validated Teradata→Redshift conversion rules the AI applies directly when converting DDL / SQL / stored procedures / macros / BTEQ. These rules ARE the source of truth — the skill ships no converter; the AI applies them at conversion time and reasons from them to fix Redshift errors and handle the manual-rewrite long tail below.

Validated across 518 test cases (94.6% pass rate on the source corpus).

Critical rules by impact

SQL function replacements

Statement transformations

Clause handling

Reserved words requiring double-quoting as aliases

returns, interval, language, position, result, trim, user, type,
default, comment, action, condition, connection, current, input,
output, session, system, value, year, month, day, hour, minute,
second, zone

DDL conversion

Clauses to strip (Teradata-specific, no RS equivalent)

Table type conversion

Index and key conversion

Compression and encoding

Recommended encodings: CHAR/VARCHAR <250 → LZO; 250+ → ZSTD; DATE/TIMESTAMP/INT/ NUMERIC → AZ64; DOUBLE/FLOAT → ZSTD; first sort-key column → RAW. Use ENCODE AUTO / DISTSTYLE AUTO / SORTKEY AUTO when unsure — Redshift will optimize.

Constructs requiring manual rewrite (no deterministic mapping)

These have no direct Redshift equivalent — flag them for review and apply the suggested structural rewrite rather than a mechanical substitution. Confidence should drop when they appear, and each should land in the manual_review.json backlog (see reporting.md) with its location.

Workflow: apply the deterministic rules above to the well-understood bulk, then flag each manual-rewrite construct with its suggested rewrite. This is the same split the old converter used (mechanical rules vs. flagged review items) — now applied directly by the AI.

Complexity assessment

Score a file by summing a weight per matched construct, then map the total to a rating. Weights: low = 1, medium = 3, high = 8.

Pattern → complexity catalog (what to scan for; extends the rule tables above):

Worked examples (golden input → output)

Concrete targets a correct conversion should match.

DDL (MULTISET + FALLBACK + PI + PPI + COMPRESS):

-- Teradata
CREATE MULTISET TABLE sales (
    id INTEGER, store_id INTEGER, sale_dt DATE, amt DECIMAL(15,2) COMPRESS
) FALLBACK
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
CREATE TABLE sales (
    id INTEGER, store_id INTEGER, sale_dt DATE, amt DECIMAL(15,2) ENCODE az64
)
DISTKEY(store_id)
SORTKEY(sale_dt);

SQL (SEL + TOP + SUBSTR + ZEROIFNULL + reserved-word alias):

-- Teradata
SEL TOP 50 SUBSTR(name,1,3), ZEROIFNULL(amt) AS returns FROM t;
-- Redshift
SELECT SUBSTRING(name,1,3), COALESCE(amt, 0) AS "returns" FROM t
LIMIT 50;

Stored procedure (fragment):

-- Teradata:  REPLACE PROCEDURE p(OUT c INTEGER) ... SET c = ACTIVITY_COUNT;
-- Redshift:  CREATE OR REPLACE PROCEDURE p(INOUT c INTEGER) LANGUAGE plpgsql AS $$ ...
--              GET DIAGNOSTICS c = ROW_COUNT; ... $$;

BTEQ → RSQL (error handling):

-- Teradata:  .IF ERRORCODE <> 0 THEN .GOTO ERR
-- RSQL:       \if :ERROR <> 0
--               \echo 'error'
--               \exit 1
--             \endif

Conversion confidence (deterministic rubric)

Score confidence per object from the complexity of the rules applied (not a free-form guess), so it is reproducible across models and drives the HITL gate in orchestration.md:

Weights are the Low/Medium/High tiers in the complexity catalog above. Same input → same confidence regardless of model.

manual_review.json (contract)

Produced by conversion, consumed by reporting.md. One entry per flagged object:

{
  "items": [
    {
      "object": "BANKING_DW.proc_x",
      "object_kind": "procedure|table|view|macro|bteq|sql",
      "construct": "multi-result macro",
      "location": "file/line or statement index",
      "snippet": "…offending source…",
      "suggested_rewrite": "…from the manual-rewrite table above…",
      "confidence": 0.7,
      "status": "open|accepted|rewritten"
    }
  ],
  "summary": { "objects": 0, "needs_review": 0, "min_confidence": 1.0 }
}

The summary shape matches what the orchestration.md HITL gate reports.

Golden corpus (acceptance scoring key)

Input→output pairs the conversion must reproduce, each tagged with the rule it exercises. Use as the yardstick for the model-matrix consistency test (docs/test-plan.md, Scenario A): a converted object is "correct" when it matches the intent below and runs on Redshift.

Critical-10 (one per critical rule)

Manual-rewrite constructs

QUALIFY — native in Redshift (keep as-is); reshape to a CTE only when mixed with GROUP BY:

-- TD plain QUALIFY → RS keeps it (native). Redshift restriction: when QUALIFY directly
-- follows FROM, the FROM relation must have an alias — add one if the TD source lacks it.
SELECT id, k, d FROM t src QUALIFY ROW_NUMBER() OVER (PARTITION BY k ORDER BY d DESC) = 1;

-- TD QUALIFY mixed with GROUP BY (dedupe-then-aggregate) → RS must reshape to a CTE:
-- TD:
SELECT k, SUM(amt) FROM t GROUP BY k
  QUALIFY ROW_NUMBER() OVER (PARTITION BY k ORDER BY ts DESC) = 1;
-- RS:
WITH deduped AS (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY k ORDER BY ts DESC) AS rn FROM t
)
SELECT k, SUM(amt) FROM deduped WHERE rn = 1 GROUP BY k;

PERIOD type → two columns:

-- TD
CREATE TABLE emp (id INT, employment PERIOD(DATE));
-- RS
CREATE TABLE emp (id INT, employment_start DATE, employment_end DATE);

RESET WHEN → windowed grouping (running total restarts when flag=1):

-- TD
SELECT id, amt,
       SUM(amt) OVER (ORDER BY id RESET WHEN flag = 1 ROWS UNBOUNDED PRECEDING) AS running
FROM t;
-- RS: build a group id that increments on each reset, then sum within the group
WITH g AS (
  SELECT id, amt,
         SUM(CASE WHEN flag = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp
  FROM t
)
SELECT id, amt,
       SUM(amt) OVER (PARTITION BY grp ORDER BY id ROWS UNBOUNDED PRECEDING) AS running
FROM g;

TD_UNPIVOT → UNION ALL:

-- TD
SELECT * FROM TD_UNPIVOT(
  ON sales USING VALUE_COLUMNS('v') UNPIVOT_COLUMN('quarter') COLUMN_LIST('q1','q2','q3','q4')
) AS x;
-- RS
SELECT id, 'q1' AS quarter, q1 AS v FROM sales
UNION ALL SELECT id, 'q2', q2 FROM sales
UNION ALL SELECT id, 'q3', q3 FROM sales
UNION ALL SELECT id, 'q4', q4 FROM sales;

EXPAND ON, TD_NORMALIZE_OVERLAP, NONSEQUENCED, ALTER TABLE MODIFY, cursors, triggers, join indexes → apply the manual-rewrite table above and record each in manual_review.json.

Full DDL (MULTISET + multi-col PI + PPI + COMPRESS + FALLBACK)

-- TD
CREATE MULTISET TABLE sales (
    a INTEGER, b INTEGER, dt DATE, amt DECIMAL(15,2) COMPRESS
) FALLBACK
PRIMARY INDEX (a, b)
PARTITION BY RANGE_N(dt BETWEEN DATE '2020-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH);
-- RS  (multi-col PI → DISTSTYLE EVEN; PPI → SORTKEY; COMPRESS → ENCODE; FALLBACK dropped)
CREATE TABLE sales (
    a INTEGER, b INTEGER, dt DATE, amt DECIMAL(15,2) ENCODE az64
)
DISTSTYLE EVEN
SORTKEY(dt);

Macro (type-2, returns rows)

A macro ending in a SELECT becomes a procedure returning a refcursor (not RAISE INFO) — see stored-procedure-migration.md → Multi-statement macro pattern (type 2).

BTEQ → RSQL block

-- TD (BTEQ)
.IF ERRORCODE <> 0 THEN .GOTO ERR
.IMPORT VARTEXT '|' FILE=data.txt
BT;
INSERT INTO staging SELECT * FROM src;
ET;
-- RS (RSQL)
\if :ERROR <> 0
  \echo 'error'
  \exit 1
\endif
-- COPY staging FROM 's3://bucket/data.txt' IAM_ROLE 'arn:aws:iam::acct:role/role' DELIMITER '|';
BEGIN;
INSERT INTO staging SELECT * FROM src;
COMMIT;