PowderInsights | Amazon Redshift to Snowflake Migration: Unlearning DISTKEY, SORTKEY, and WLM

Redshift is the migration source we are asked about most often and the one with the least written about it, because on the surface it looks easy: both engines speak columnar, set-based SQL, both are cloud-native, and Redshift's dialect is PostgreSQL-flavoured, so most DDL and queries move with light edits. The hard part is not the SQL. It is unlearning the physical design decisions Redshift forced on you — DISTKEY, SORTKEY, VACUUM, WLM queues, fixed cluster sizing — and not reproducing them in a system that does not need them.

This playbook walks the phases we use on real Redshift-to-Snowflake engagements, with the gotchas that cost teams weeks.

Phase 0 — Inventory and baseline

Before touching Snowflake, pull the facts out of the Redshift cluster. You will need them for scoping, for warehouse sizing, and for the "did it work?" conversation at the end.

-- Object inventory
SELECT schemaname, COUNT(*) AS tables, SUM(size) AS mb
FROM svv_table_info GROUP BY 1 ORDER BY 3 DESC;

-- Workload shape: what actually runs, and how often
SELECT TRIM(u.usename) AS usr, COUNT(*) AS queries,
       AVG(DATEDIFF(ms, q.starttime, q.endtime)) AS avg_ms,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DATEDIFF(ms, q.starttime, q.endtime)) AS p95_ms
FROM stl_query q JOIN pg_user u ON u.usesysid = q.userid
WHERE q.starttime > DATEADD(day, -30, GETDATE())
GROUP BY 1 ORDER BY 2 DESC;

-- Queue waits and spill: the two numbers that justify the project
SELECT service_class, COUNT(*), AVG(total_queue_time)/1000000.0 AS avg_queue_s
FROM stl_wlm_query WHERE service_class > 4
  AND queue_start_time > DATEADD(day, -30, GETDATE())
GROUP BY 1;

Capture also: external schemas (Redshift Spectrum), materialized views, stored procedures (PL/pgSQL), UDFs (SQL and Python), UNLOAD/COPY jobs, and every downstream consumer — BI tools, reverse-ETL, notebooks, scheduled UNLOADs to S3, and the application that nobody documented.

Record a baseline: p95 latency per workload, nightly ELT wall-clock, and monthly cluster cost including concurrency scaling and managed storage. You will be held to these.

Phase 1 — Schema conversion

SnowConvert supports Redshift and handles the bulk DDL, PL/pgSQL procedures, and SQL rewrites; for a few hundred tables it is worth running even if you intend to hand-tune afterwards. What it cannot decide for you is what to drop.

Throw away, do not translate:

Redshift construct	What to do in Snowflake
`DISTKEY` / `DISTSTYLE`	Delete. Snowflake has no distribution; there is nothing to co-locate.
`SORTKEY` (compound/interleaved)	Delete. Consider a clustering key only on tables > ~1 TB with a proven pruning problem.
`ENCODE` / compression settings	Delete. Compression is automatic and not user-tunable.
`VACUUM`, `ANALYZE`, deep copies	Delete the jobs entirely.
WLM queues, query groups, QMR rules	Replace with separate virtual warehouses per workload.
Concurrency Scaling clusters	Replace with multi-cluster warehouses (`MIN_CLUSTER_COUNT`/`MAX_CLUSTER_COUNT`).
`SORTKEY`-based "time series" table rotation	Delete; use one table and let pruning work.

Type mapping gotchas:

VARCHAR(n) in Redshift is bytes; in Snowflake it is characters. Multi-byte data that fit in Redshift will fit in Snowflake, but naive LENGTH()-based validation will disagree. Widening everything to VARCHAR costs nothing in Snowflake — storage is based on actual data.
SUPER → VARIANT. Mostly mechanical, but PARTIQL navigation syntax differs: Redshift s.a.b[0] works in Snowflake as s:a.b[0] on a VARIANT.
TIMESTAMP (no zone) → TIMESTAMP_NTZ; TIMESTAMPTZ → TIMESTAMP_TZ. Decide account-wide TIMEZONE and TIMESTAMP_TYPE_MAPPING parameters before loading, not after.
GEOMETRY/GEOGRAPHY map across, but SRID handling and function names differ — test spatial workloads explicitly.
Redshift IDENTITY columns are not gap-free; Snowflake sequences are not either. If a downstream system assumes contiguity, it was already broken.

SQL dialect gotchas that SnowConvert flags but reviewers often wave through:

GETDATE() → CURRENT_TIMESTAMP(); DATEADD/DATEDIFF argument order is the same, but Redshift's DATEDIFF truncates differently for month boundaries.
NVL2, DECODE, LISTAGG all exist in Snowflake; APPROXIMATE COUNT(DISTINCT ...) becomes APPROX_COUNT_DISTINCT.
Redshift silently truncates on INSERT into a too-small VARCHAR when TRUNCATECOLUMNS is set on COPY. Snowflake errors. This surfaces real data quality problems during migration — treat them as findings, not blockers.
PL/pgSQL stored procedures convert to Snowflake Scripting. Cursor-heavy, row-by-row procedures should be rewritten set-based instead of translated; that is where the performance win lives.

Phase 2 — Data movement

Three patterns, in order of preference:

1. UNLOAD to Parquet, COPY into Snowflake. The default for history. Parquet preserves types and is far faster to ingest than CSV.

-- On Redshift
UNLOAD ('SELECT * FROM analytics.fact_orders WHERE order_date < ''2026-01-01''')
TO 's3://acme-migration/fact_orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-unload'
FORMAT AS PARQUET
MAXFILESIZE 256 MB
PARTITION BY (order_date);

-- On Snowflake
CREATE OR REPLACE STAGE migration_stage
  URL = 's3://acme-migration/'
  STORAGE_INTEGRATION = s3_migration_int
  FILE_FORMAT = (TYPE = PARQUET);

COPY INTO analytics.fact_orders
FROM @migration_stage/fact_orders/
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = ABORT_STATEMENT;

Aim for 100–250 MB compressed files and load with an L or XL warehouse; ingestion scales with warehouse size, so a bigger warehouse for a shorter time is usually cheaper than a small one running all night.

2. Iceberg hand-off. If the lake is already Iceberg or you are standing one up, have Redshift (or the upstream Spark job) write Iceberg and have Snowflake read it — zero copy, and it doubles as the rollback path.

3. Re-ingest from source. For anything Redshift itself loaded from an operational database, point the CDC tool (Fivetran, Debezium, Openflow, AWS DMS) at the source and skip Redshift altogether. Fewer hops, and it is the pipeline you will run in production anyway.

Do not migrate everything. A Redshift cluster that has been live for five years is typically 30–50% unused tables. Cross-check svv_table_info against stl_scan for the last 90 days and park the cold tail in S3.

Phase 3 — Rebuild the workload, not just the data

Replace WLM with warehouses, one per workload class:

CREATE WAREHOUSE wh_elt       WITH WAREHOUSE_SIZE = 'LARGE'  AUTO_SUSPEND = 60 INITIALLY_SUSPENDED = TRUE;
CREATE WAREHOUSE wh_bi        WITH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 60
  MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4 SCALING_POLICY = 'STANDARD';
CREATE WAREHOUSE wh_adhoc     WITH WAREHOUSE_SIZE = 'SMALL'  AUTO_SUSPEND = 60
  STATEMENT_TIMEOUT_IN_SECONDS = 1800;

That single change removes most of the queueing pain that motivated the migration — but it also removes the hard spend ceiling a fixed cluster gave you. Put resource monitors and budgets in place on day one, before the first BI user connects, or the finance conversation will be the thing that defines the project.

Other substitutions:

Redshift Spectrum external tables → Snowflake external tables or Iceberg tables over the same S3 paths.
Redshift materialized views with auto-refresh → dynamic tables with a TARGET_LAG. This is usually a simplification, not a port.
Scheduled UNLOADs feeding downstream systems → secure data sharing or a listing, which removes the file-drop entirely for consumers who are also on Snowflake.
GRANT scripts → a proper functional/access role model. Redshift grant sprawl is not worth replicating; rebuild RBAC deliberately.

Phase 4 — Parallel run and reconciliation

Run both systems on the same inputs for at least two full business cycles, including one month-end close.

-- Row + checksum comparison, per table, loaded into a results table
SELECT 'fact_orders' AS table_name,
       COUNT(*) AS row_count,
       SUM(HASH(order_id, customer_id, order_date, amount, status)) AS fingerprint
FROM analytics.fact_orders;

Reconcile three layers, in this order: raw row counts and fingerprints, aggregate business metrics (revenue by month, active customers by region), and finally the BI dashboards themselves, screenshot against screenshot. Most "Snowflake gives different numbers" tickets resolve to one of: a timestamp/time-zone default, Redshift's VARCHAR byte truncation having silently dropped characters years ago, or NULL ordering differences in ORDER BY (NULLS FIRST is the Redshift default for ascending; Snowflake defaults to NULLS LAST).

Keep the Redshift cluster running — paused, if possible — until reconciliation has passed on a full close. Pausing is cheap; being wrong in public is not.

Phase 5 — Cutover and decommission

Freeze schema changes on Redshift.
Final incremental load of anything that moved during the parallel run.
Repoint BI, reverse-ETL, and application connections (keep the old DSNs alive but read-only for a week).
Final reconciliation run; sign-off from the business owner, not just the data team.
Pause the Redshift cluster, take a final manual snapshot with a long retention, and schedule the delete for 60–90 days out.
Decommission the VACUUM/ANALYZE jobs, WLM tuning scripts, and the sizing spreadsheet. That is the real win.

Timeline expectations

A single-cluster Redshift estate of a few hundred tables with moderate PL/pgSQL, migrated by a focused team: 10–16 weeks to cutover, with phases 1 and 4 taking the bulk of it. Stored-procedure-heavy estates and anything with a hand-built CDC framework run longer. The schema and data move faster than anyone expects; the consumers and the reconciliation do not.

Common ways this goes wrong

Porting DISTKEY/SORTKEY as clustering keys on every table. Clustering costs money to maintain and helps only a handful of very large tables. Start with none.
One giant warehouse for everything. You reproduce WLM contention and lose per-workload cost attribution.
Lift-and-shift of row-by-row procedures. They will run, slowly, and you will conclude Snowflake is not faster.
No budget guardrails before user access. Elastic compute plus unchanged user behaviour equals a surprise invoice in month one.
Skipping the parallel run because the data "matched in dev". Month-end is where the differences live.

PowderInsights runs Redshift-to-Snowflake migrations end to end — assessment, SnowConvert-assisted conversion, warehouse and RBAC design, parallel-run reconciliation, and cutover — and we also supply senior Snowflake architects and contractors to teams running the migration themselves. Get in touch with your table counts, procedure counts, and cutover date, and we will tell you what it realistically takes.