RougeWarehouse | Migrating a Legacy Teradata or Oracle Warehouse to Amazon Redshift: SCT, DMS and the Parts That Actually Hurt

Most of the Redshift work we are asked to scope in 2026 is not greenfield. It is a fifteen-year-old Teradata, Oracle Exadata, Netezza or SQL Server warehouse with a few thousand tables, a few hundred thousand lines of stored procedure logic, and a hardware refresh or license renewal coming up. The Redshift part of that project is usually the easy part. This tutorial walks the whole path — assessment, schema conversion, historical load, change data capture, SQL rewrite, dual-run validation, cutover — and is honest about which steps consume the schedule.

Step 0: inventory before you convert anything

Before touching AWS SCT, build a fact base from the source system's own catalog. On Teradata that is DBC.TablesV, DBC.ColumnsV and the DBQL query log; on Oracle it is DBA_SEGMENTS, DBA_TAB_COLUMNS and AWR. You want four numbers:

Compressed and uncompressed size per schema. Redshift sizing follows uncompressed volume, not your current appliance's footprint.
Table count, and how many tables were actually queried in the last 90 days. On every migration we have run, 30–60% of objects are dead. Migrating them is pure cost.
Distinct queries and their frequency, extracted from the query log. This becomes both your rewrite backlog and your regression test suite.
Procedural code volume — stored procedures, BTEQ scripts, PL/SQL packages, macros. This is the line item that decides the project length.

Publish that inventory as a spreadsheet and get the business to sign off on a retire list. Cutting scope here is the highest-leverage hour in the entire migration.

Step 1: run the AWS SCT assessment report

The AWS Schema Conversion Tool connects to the source, targets Amazon Redshift, and produces an assessment report that buckets every object into converted automatically, converted with minor edits, or requiring manual rewrite. Run it early and read the action items, not the summary percentage.

Typical action items on a Teradata source:

QUALIFY clauses — Redshift has no QUALIFY, so each becomes a subquery over a window function.
Multiset vs set tables, and reliance on Teradata's implicit duplicate-row rejection.
Primary and secondary indexes, join indexes, partitioned primary indexes — none of which exist in Redshift and all of which need to be re-expressed as sort keys, distribution keys or materialized views.
BTEQ and MultiLoad scripts, which become COPY from S3 or an orchestrated ELT step.

On Oracle:

CONNECT BY hierarchical queries → recursive CTEs.
PL/SQL packages with state, autonomous transactions and cursors → Redshift stored procedures in PL/pgSQL, or, far better, ELT models in dbt.
MERGE with complex WHEN branches → Redshift's native MERGE, which handles the common upsert case cleanly.
Sequences, ROWID, and triggers, which have no Redshift equivalent and force a design conversation.

SCT also emits data type mappings. Review NUMBER without precision (becomes an oversized numeric), CLOB/LOB columns (Redshift maxes at VARCHAR(65535); anything larger belongs in S3 with a pointer column), and TIMESTAMP WITH TIME ZONE handling.

Step 2: convert the schema, then redesign it

Do not ship SCT's output as-is. A converted schema is a syntactically valid schema, not a good MPP schema. Two passes:

Pass A — mechanical. Apply SCT's DDL to a scratch Redshift Serverless workgroup. Fix compile errors, unresolved types and identifier-length collisions.

Pass B — physical design. For each of the top 50 tables by size or query frequency, decide distribution and sort explicitly:

CREATE TABLE sales.fct_order_line (
  order_line_id   BIGINT      NOT NULL,
  order_id        BIGINT      NOT NULL,
  customer_id     BIGINT      NOT NULL,
  order_ts        TIMESTAMP   NOT NULL,
  product_id      INTEGER     NOT NULL,
  quantity        INTEGER,
  net_amount      NUMERIC(18,2),
  PRIMARY KEY (order_line_id)
)
DISTSTYLE KEY
DISTKEY (order_id)
SORTKEY (order_ts);

Rules of thumb that survive contact with real workloads:

Small dimensions (under a few million rows) → DISTSTYLE ALL.
Large fact tables → DISTKEY on the column used in the highest-cardinality join, usually the transaction grain key, not the date.
Sort key on the column used in nearly every predicate — almost always the event timestamp.
Declare primary and foreign keys even though Redshift does not enforce them; the optimizer uses them, and so do BI tools.
Leave everything outside the top 50 on AUTO and let Redshift's automatic table optimization pick. Revisit after the workload is real.

A Teradata primary index is not a Redshift distribution key by default. Copying it over is the single most common cause of a slow post-migration warehouse.

Step 3: move the history

For the historical load, prefer bulk extract to S3 over row-by-row replication. Extract to gzip-compressed, roughly 100 MB–1 GB Parquet or delimited files, one prefix per table, and load with COPY:

COPY sales.fct_order_line
FROM 's3://acme-migration/teradata/fct_order_line/'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftLoadRole'
FORMAT AS PARQUET;

Splitting extracts into multiple files matters: a single large file is loaded by one slice, while many files load in parallel across all of them. AWS SCT data extraction agents can automate the extract, chunk and upload for Teradata, Oracle, Netezza, Greenplum and Vertica sources, and for a genuinely large warehouse the extracts can be shipped on Snowball devices instead of over the wire.

For the largest fact tables, load one partition (say, one month) first, measure, then parallelize. It is much cheaper to discover a bad type mapping on 40 GB than on 40 TB. Watch SYS_LOAD_ERROR_DETAIL after every load.

Step 4: keep the old system in sync with DMS CDC

You will not cut over the same weekend you finish the history load. Bridge the gap with AWS DMS in CDC mode: a full-load-plus-CDC task, or CDC-only starting from the SCN/LSN you captured at extract time.

Practical notes:

Enable supplemental logging (Oracle) or the appropriate change capture mechanism on the source before the extract, and record the exact position.
Point DMS at S3 as an intermediate target and load with COPY, or target Redshift directly and accept that DMS uses S3 under the hood anyway.
CDC into Redshift lands as inserts, updates and deletes. Batch-apply settings matter far more than you expect; single-row applies will throttle a busy source.
Monitor CDCLatencySource and CDCLatencyTarget in CloudWatch. Rising target latency means Redshift is the bottleneck — usually merge logic, not raw throughput.

If the source is Aurora or RDS rather than a legacy appliance, skip DMS entirely and use a zero-ETL integration.

Step 5: rewrite the SQL, in priority order

Use the query log inventory from Step 0. Convert the top 200 queries by frequency and the top 50 by cost, then stop and let the long tail come from users. Common rewrites:

-- Teradata
SELECT customer_id, order_ts, net_amount
FROM fct_order_line
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1;

-- Redshift
SELECT customer_id, order_ts, net_amount
FROM (
  SELECT customer_id, order_ts, net_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
  FROM sales.fct_order_line
) t
WHERE rn = 1;

Migrate procedural ETL to set-based ELT wherever you can. A 900-line BTEQ script that loops over accounts is usually forty lines of MERGE in Redshift, and it will run in a fraction of the time because it stops fighting the MPP execution model.

Step 6: dual-run and validate

Run both warehouses in parallel for at least two full reporting cycles, including a month-end close. Validation has three levels:

Row counts and checksums per table per partition. Cheap, automatable, catches load gaps.
Aggregate reconciliation. Sum every measure column by day and diff. Numeric precision differences show up here — Teradata and Oracle round differently from Redshift on some NUMERIC divisions.
Report-level diffs. Execute the top reports against both systems and compare result sets cell by cell. This is what the business will actually accept as proof.

Automate all three in the pipeline and publish a daily pass/fail dashboard. A migration with a green validation board gets signed off; one with a slide deck does not.

Step 7: cut over

A low-drama cutover looks like this: freeze DDL on the source, let CDC drain to near-zero latency, run the final validation pass, repoint BI connections (a DNS alias or a BI-tool connection variable makes this a one-line change), leave the source read-only for 30 days as a rollback path, then decommission.

Right-size afterwards, not before. Run on Redshift Serverless or a generously sized RA3 cluster through cutover, collect two weeks of real workload data, and only then tune base RPUs, concurrency scaling and WLM priorities.

What actually consumes the schedule

Across the legacy-to-Redshift migrations we have delivered, the time split is roughly: 10% schema conversion, 15% data movement, 45% procedural code and SQL rewrite, 30% validation and stakeholder sign-off. Teams that budget for the first two and improvise the last two are the ones whose six-month migration turns into eighteen.

Planning a move off Teradata, Oracle, Netezza or SQL Server? RougeWarehouse's senior consultants have built and migrated MPP warehouses processing terabytes a day. We run fixed-scope assessments — inventory, SCT report, target architecture and a costed migration plan — and can lead the delivery or work as a subcontractor alongside your existing team. Get in touch to scope it.