Redshift to Snowflake Migration: A Low‑Risk Cutover Plan

You need to move workloads off Redshift without breaking dashboards, doubling warehouse spend forever, or spending months rewriting SQL. Here’s the exact plan we use: inventory every dependency, bulk-transfer and change-capture data, map security, adapt SQL/dbt where it matters, parallel-run with reconciliation, then cut BI over and decommission on a schedule. The steps below include queries, dbt and YAML examples, clear exit criteria, and controls to keep a temporary dual-warehouse from becoming permanent. If you want a second set of hands, Vertex Data Consulting has built and migrated production platforms at scale, but you can run this plan in‑house with a competent team.

1) Inventory workloads and dependencies (the gate to scope and risk)

Build a complete picture before you migrate. Catalog tables, jobs, SLAs, BI dependencies, users, and security. Pull the top tables and their physical assumptions from Redshift; these drive data transfer order and SQL fixes.

-- Heavily scanned tables and current physical design
SELECT schemaname, tablename, size AS mb, diststyle, sortkey1, sortkey_num
FROM svv_table_info
WHERE size > 0
ORDER BY size DESC
LIMIT 100;

-- Last 7 days of query workload (spot noisy consumers)
SELECT userid, query, starttime, endtime
FROM stl_query
WHERE starttime >= dateadd(day, -7, getdate());

Inventory orchestration (Airflow DAGs, Lambda, cron), ingestion (Fivetran/ELT, custom), transformation (dbt/stored procs), and BI connections. Record every external dependency (S3 data lake, Spectrum, JDBC apps). For a structured way to do this, see Data Warehouse Migration Planning That Won’t Stall.

Asset	What to capture	Why it matters
Tables	Row counts, data size, freshness SLA	Prioritize transfer and reconciliation
Physical design	DIST/SORT keys on each schema	Don’t copy assumptions blindly to Snowflake
Workflows	Schedule, upstream/downstream	Parallel-run and cutover sequencing
BI	DSNs, extracts, owners	Connection swaps and UAT owners
Security	Groups, grants, row filters	Role mapping to Snowflake RBAC

Tip: tag everything in the Redshift database with owners (schemas, tables, views). If you still have a single busy redshift cluster serving mixed workloads, split ingestion vs analytics windows now; it eases parallel-run.

2) Data transfer and change capture without rescanning the lake

Do a bulk load, then keep up with changes. For full loads, UNLOAD from Redshift to S3, then COPY into Snowflake through an external stage. Use compressed, columnar, 100–250MB files for efficient micro-partitioning.

-- Redshift: full extract to S3
UNLOAD ('SELECT * FROM public.orders')
TO 's3://my-bucket/rs/orders_'
IAM_ROLE 'arn:aws:iam::123:role/RedshiftUnload'
PARALLEL TRUE GZIP ALLOWOVERWRITE;

-- Snowflake: external stage + COPY
CREATE STAGE s3_stage URL='s3://my-bucket/rs/'
  CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...')
  FILE_FORMAT=(TYPE=CSV FIELD_DELIMITER='|' COMPRESSION=GZIP);

COPY INTO analytics.public.orders
FROM @s3_stage/orders_
ON_ERROR='ABORT_STATEMENT';

For ongoing changes, pick a CDC path you can support in production: database-native logs (DMS to S3), Kafka/Debezium, or Fivetran. Land deltas into Snowflake, then deduplicate and MERGE using Streams & Tasks. This avoids rescanning the entire data lake for every micro-batch.

-- Snowflake: near‑real‑time upsert without full rescans
CREATE OR REPLACE STREAM stg_orders_stream ON TABLE stg.orders_deltas;
CREATE OR REPLACE TASK upsert_orders
  WAREHOUSE = XSMALL
  SCHEDULE = '5 MINUTE'
AS
MERGE INTO analytics.public.orders t
USING (
  SELECT * FROM stg.orders_deltas WHERE METADATA$ACTION IN ('INSERT','UPDATE')
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

If your catalog lives in AWS Glue, mirror the table list to seed COPY patterns. Keep transfer idempotent with filename manifests and load history tables. For high-volume tables, partition by ingestion date to isolate retries.

3) SQL/dbt compatibility and security mapping (copy behavior, not syntax)

Translate behavior table‑by‑table. Start with DDL and critical SQL patterns. Using AWS Schema Conversion Tool (AWS SCT) for quick DDL drafts is fine, but hand‑review anything beyond straight SELECTs. Also review Snowflake Inc.’s SnowConvert if you prefer vendor tooling; still validate outputs. In dbt, keep logic in models/macros, not the warehouse.

# dbt profiles.yml: Snowflake prod target
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: <acct>
      user: <svc_user>
      password: <secret>
      role: TRANSFORMER
      warehouse: TRANSFORMING
      database: ANALYTICS
      schema: PUBLIC
      threads: 8
      client_session_keep_alive: false

-- SQL translation examples
-- Redshift IDENTITY -> Snowflake IDENTITY is supported, but sequences behave differently across CTAS
-- Remove Redshift DISTKEY/SORTKEY in DDL; prefer cluster keys only if access patterns demand it
-- NVL(x,y) -> COALESCE(x,y) to standardize across engines
-- Redshift regex: REGEXP_REPLACE -> Snowflake REGEXP_REPLACE (same signature)
-- Use QUALIFY in Snowflake to filter window functions (rewrite WHERE + subquery)

{# dbt macro shim to centralize differences #}
{% macro safe_divide(n, d) %}
  (CASE WHEN {{ d }} = 0 THEN NULL ELSE {{ n }} / {{ d }} END)
{% endmacro %}

Security: map Redshift groups/grants to Snowflake roles at database and schema layers. Create least‑privilege roles for loaders, transformers, and BI readers; grant usage on warehouses separately. Enforce network policies/IP allowlists and set SSO before user UAT. If you have row‑level filters, re‑implement with Snowflake row access policies or secure views. For dbt refactors specific to production realities, see Refactoring Legacy SQL in a dbt Project—Without Breaking Reports and dbt Repo Performance.

4) Performance assumptions, parallel run, BI cutover, reconciliation, and decommissioning

Don’t copy Redshift distribution/sort rules. Snowflake uses micro‑partitions; cluster keys are optional and data‑driven. File sizing during COPY, statistics‑friendly predicates, and the right warehouse size matter more than old DISTKEY lore. For semi‑structured data, land JSON in VARIANT early and model later.

Area	Redshift	Snowflake	Action
Physical design	Dist/sort keys	Micro‑partitions	Start with none; add cluster keys if queries prove it
Scaling	Resize/Concurrency Scaling	Per‑query virtual warehouses	Separate load vs BI warehouses; auto‑suspend
Security	Group grants	RBAC roles	Map group‑>role, validate least privilege

Parallel‑run exit criteria (write these down):

• Row counts match for top 50 tables; sampled aggregates match (sum, min, max) on key measures.
• dbt prod run completes under target SLA on an X‑Small warehouse (or sized baseline).
• BI UAT sign‑off for each subject area.
• Error budget: 0 failed loads and no ≥5% metric drift for 7 days.

-- Reconciliation pattern (run on both systems)
SELECT 'orders' AS t, COUNT(*) AS cnt, MIN(created_at) AS min_dt, MAX(created_at) AS max_dt
FROM analytics.public.orders
WHERE created_at >= dateadd(day,-30,current_date);

BI cutover: clone models/views, then swap connection strings and secrets in one change window. Freeze Redshift writers, run a final delta load, re‑point BI, and monitor. Decommissioning: disable ingestion to Redshift, archive snapshots, drop users, and set a date to tear down the redshift cluster. Cost control: enable Snowflake resource monitors, auto‑suspend warehouses, and tag objects for chargeback; see Snowflake Cost Optimization: Where the Credits Go. The rule: 14‑day dual‑run max, with daily spend checks in both systems.

FAQ: Why and how to migrate without surprises

Why move from Amazon Redshift to Snowflake?

Simpler scaling, isolated compute for ELT vs BI, lower ops overhead, and features like zero‑copy clones and time travel. If you rely on frequent clones for UAT or need elastic BI concurrency, that’s your business case.

How should we prepare before migrating?

Finish the inventory above, decide table transfer order, freeze risky changes, and write acceptance tests. Stand up roles/warehouses early. If you migrate from Redshift to Snowflake with dbt, get CI in place; see dbt CI/CD That Catches Problems Before They Reach Prod.

Now how do we deduplicate near‑real‑time CDC without rescans?

Land append‑only files, key them by primary key + change timestamp, then MERGE using a stream to isolate news. Use a window to keep only the latest version per key before MERGE.

MERGE INTO dim.customer t
USING (
  SELECT * FROM (
    SELECT c.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM stg.customer_cdc c
  ) WHERE rn = 1
) s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

What tools help with SQL and DDL conversion?

For a redshift migration using AWS Schema Conversion Tool, generate a first pass of DDL using AWS SCT (official docs: link), then review types and constraints. If you prefer vendor tooling for amazon redshift to snowflake translation, see Snowpipe/Streams for loading (link, link). Validate every data type mapping against your own queries.

Can a partner accelerate the migration from Redshift to Snowflake?

Yes—an experienced team will harden CDC, refactor dbt, and design RBAC/cost controls quickly. Vertex can own the risky middle (data migration, SQL edge cases, BI cutover) while your team keeps delivering. See dbt Cloud Migration and our dbt Incremental Models guide.

---

Ready to move? Start with a one‑week assessment and a dated cutover plan. If you want help, start a project with Vertex or browse our warehousing articles.