Workload

Redshift SQL queries to Snowflake

Translate Redshift SQL—analytic/window patterns, date/time logic, and Redshift-era query shapes—into Snowflake SQL with validation gates that prevent semantic drift and credit spikes.

Book assessment Explore workloads

Quick answer

Translate Redshift SQL—analytic/window patterns, date/time logic, and Redshift-era query shapes—into Snowflake SQL with validation gates that prevent semantic drift and credit spikes.

Back to pair page

Context

Why this breaks

Redshift SQL estates often embed more than syntax: performance assumptions shaped by DISTKEY/SORTKEY, VACUUM/ANALYZE habits, and UPSERT patterns implemented via staging + delete/insert. Snowflake will execute translated queries—but outputs and costs can drift when implicit casts, NULL semantics, and time handling aren’t made explicit, and when query shapes don’t align to Snowflake pruning and warehouse posture. Common symptoms after cutover:

  • KPI drift from window logic and top-N selections with incomplete ordering - Type coercion changes in CASE/COALESCE and join keys, producing silent mismatches - Date/time edge cases shift (DATE vs TIMESTAMP intent, timezone boundaries) - “UPSERT queries” translate syntactically but change behavior under retries - Credit spikes because Redshift-era shapes don’t prune well in Snowflake SQL migration must preserve both meaning and a Snowflake-native execution posture.

Approach

How conversion works

  • Inventory & prioritize the SQL corpus (BI extracts, views, dbt/ELT models, app SQL). Rank by business impact, frequency, and risk patterns (window/top-N, casts, time, upserts). - Normalize Redshift dialect: quoting/identifiers, date literals, and common function idioms. - Rewrite with rule-anchored mappings: function equivalents, explicit cast strategy, and deterministic ordering for windowed filters and top-N logic. - Replace high-risk patterns: delete/insert UPSERTs → staged MERGE patterns; make match semantics explicit. - Validate with gates: compile/run checks, catalog/type alignment, golden-query parity with edge-window cohorts. - Cost-safe refactors: pruning-friendly predicates, bounded applies, and warehouse isolation guidance for heavy BI refresh.

Coverage

Supported constructs

Representative Redshift SQL constructs we commonly convert to Snowflake SQL (exact coverage depends on your estate).

SourceTargetNotes
Window functions + top-N filtersSnowflake window functions + QUALIFYDeterministic ORDER BY and tie-breakers enforced.
DELETE+INSERT UPSERT queriesSnowflake MERGE with bounded apply windowsIdempotency and match-key semantics made explicit.
Date/time arithmeticSnowflake date/time equivalentsDATE vs TIMESTAMP intent normalized explicitly (NTZ/LTZ/TZ as needed).
NULL/type coercion idiomsExplicit casts + null-safe comparisonsPrevents join drift and filter selectivity changes.
String/regex functionsSnowflake string/regex equivalentsEdge-case behavior validated via golden cohorts.
DISTKEY/SORTKEY-driven query shapesPruning-aware rewrites + optional clustering guidanceReplace physical tuning assumptions with Snowflake-native posture.

Compare

How workload changes

TopicRedshiftSnowflakeNotes
Performance modelDIST/SORT keys + VACUUM/ANALYZE habitsWarehouse credits + pruning effectivenessConverted queries often need pruning-aware rewrites to keep credits predictable.
UpsertsDELETE+INSERT patterns commonMERGE with bounded apply windowsAvoid full scans and protect idempotency under retries.
Type behaviorImplicit casts often toleratedExplicit casts required for stable outputsValidation focuses on CASE/COALESCE and join keys.

Examples

Examples

Representative Redshift → Snowflake rewrites for top-N, date arithmetic, and UPSERT patterns. Adjust identifiers and types to your schema.

01_topn_redshift.sql
-- Redshift: TOP-N with ORDER BY
SELECT
  product_id,
  SUM(revenue) AS rev
FROM sales
WHERE sale_dt BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1
ORDER BY rev DESC
LIMIT 100;
01_topn_snowflake.sql
-- Snowflake: same pattern
SELECT
  product_id,
  SUM(revenue) AS rev
FROM SALES
WHERE sale_dt BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
GROUP BY product_id
ORDER BY rev DESC
LIMIT 100;
02_dateadd_redshift.sql
-- Redshift: dateadd
SELECT dateadd(day, 7, order_dt) AS plus_7
FROM t;
02_dateadd_snowflake.sql
-- Snowflake: DATEADD
SELECT DATEADD(day, 7, order_dt) AS plus_7
FROM T;
03_upsert_redshift.sql
-- Redshift: UPSERT via staging + delete/insert
DELETE FROM fact_orders
USING stg_orders
WHERE fact_orders.id = stg_orders.id;

INSERT INTO fact_orders (id, status, amount, updated_at)
SELECT id, status, amount, updated_at
FROM stg_orders;
03_merge_snowflake.sql
-- Snowflake: MERGE with bounded apply window
SET min_d = (SELECT MIN(TO_DATE(updated_at)) FROM STG_ORDERS);
SET max_d = (SELECT MAX(TO_DATE(updated_at)) FROM STG_ORDERS);

MERGE INTO MART.FACT_ORDERS t
USING STG_ORDERS s
ON t.id = s.id
AND TO_DATE(t.updated_at) BETWEEN $min_d AND $max_d
WHEN MATCHED THEN UPDATE SET
  t.status = s.status,
  t.amount = s.amount,
  t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, status, amount, updated_at)
  VALUES (s.id, s.status, s.amount, s.updated_at);

Workload Assessment

Translate Redshift SQL with parity gates

We inventory your SQL estate, convert a representative slice, and deliver parity evidence on golden queries—plus a risk register for constructs that carry business meaning and cost risk.

Book assessment

Avoid

Common pitfalls

  • Implicit casts: Redshift queries that “just work” can drift in Snowflake unless types are explicit.
  • NULL semantics in joins: equality behavior differs; joins can drop or duplicate rows when NULLs exist.
  • Window ordering ambiguity: ROW_NUMBER/RANK without stable tie-breakers causes nondeterministic drift.
  • DIST/SORT assumptions carried over: physical tuning treated as schema; Snowflake needs pruning-friendly filters and (optional) clustering where justified.
  • UPSERT via DELETE+INSERT: naive rewrites break idempotency under retries and partial failures.
  • Unbounded scans: MERGE/apply touches too much history and spikes credits without scope boundaries.

Proof

Validation approach

  • Compilation gates: converted queries compile and execute in Snowflake reliably under representative parameters.
  • Catalog/type checks: referenced objects exist; implicit casts surfaced and made explicit.
  • Golden-query parity: critical dashboards and queries match outputs or agreed tolerances.
  • KPI aggregates: compare aggregates by key dimensions and windows.
  • Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and timezone transitions.
  • Cost baseline: capture runtime and credit/scan behavior for top queries; set regression thresholds to prevent credit spikes.

Execution

Migration steps

A sequence that keeps correctness measurable and prevents semantic drift.

  1. 01

    Collect and prioritize the query estate

    Export BI SQL, view definitions, ETL SQL, and app queries. Rank by business impact, frequency, and risk patterns (UPSERTs, windows, time, implicit casts).

  2. 02

    Define semantic contracts for risky patterns

    Make tie-breakers, NULL handling, casting strategy, and timestamp intent explicit—especially for top-N/window logic and UPSERT behavior.

  3. 03

    Convert with rule-anchored mappings

    Apply deterministic rewrites for common constructs and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, timezone assumptions).

  4. 04

    Validate with golden queries and edge cohorts

    Compile and run in Snowflake, compare KPI aggregates, and run targeted diffs on edge cohorts (ties, null-heavy segments, boundary dates).

  5. 05

    Tune top queries for Snowflake

    Ensure pruning-friendly predicates, optionally apply clustering where justified, and scope MERGE/applies to affected windows to keep credit burn predictable.

FAQ

Frequently asked questions

Is Redshift SQL directly compatible with Snowflake? +

Many queries translate cleanly, but semantic parity depends on explicit casting, NULL behavior, date/time intent, and deterministic ordering in window/top-N logic. We validate these with golden queries.

What’s the biggest risk area when migrating Redshift queries? +

Implicit casts and NULL semantics (especially in joins and CASE branches), plus credit spikes from unbounded scans. These get explicit contracts and validation gates.

How do you handle Redshift UPSERT patterns? +

We convert delete/insert UPSERTs into MERGE with bounded apply windows and deterministic staging, then validate idempotency so retries don’t double-apply or delete wrong rows.

Will performance be the same after translation? +

Not automatically. DIST/SORT-era shapes often need pruning-aware rewrites and Snowflake-native posture (warehouse isolation, bounded applies). We baseline and tune the top queries.

Migration Acceleration

Ship Snowflake queries with proof-backed sign-off

Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria—without credit spikes.

Book assessment
Book assessment