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.
- Input
- Redshift SQL / query migration logic
- Output
- Snowflake equivalent (validated)
- 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.
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.
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.
Supported constructs
Representative Redshift SQL constructs we commonly convert to Snowflake SQL (exact coverage depends on your estate).
| Source | Target | Notes |
|---|---|---|
| Window functions + top-N filters | Snowflake window functions + QUALIFY | Deterministic ORDER BY and tie-breakers enforced. |
| DELETE+INSERT UPSERT queries | Snowflake MERGE with bounded apply windows | Idempotency and match-key semantics made explicit. |
| Date/time arithmetic | Snowflake date/time equivalents | DATE vs TIMESTAMP intent normalized explicitly (NTZ/LTZ/TZ as needed). |
| NULL/type coercion idioms | Explicit casts + null-safe comparisons | Prevents join drift and filter selectivity changes. |
| String/regex functions | Snowflake string/regex equivalents | Edge-case behavior validated via golden cohorts. |
| DISTKEY/SORTKEY-driven query shapes | Pruning-aware rewrites + optional clustering guidance | Replace physical tuning assumptions with Snowflake-native posture. |
How workload changes
| Topic | Redshift | Snowflake |
|---|---|---|
| Performance model | DIST/SORT keys + VACUUM/ANALYZE habits | Warehouse credits + pruning effectiveness |
| Upserts | DELETE+INSERT patterns common | MERGE with bounded apply windows |
| Type behavior | Implicit casts often tolerated | Explicit casts required for stable outputs |
Examples
Representative Redshift → Snowflake rewrites for top-N, date arithmetic, and UPSERT patterns. Adjust identifiers and types to your schema.
-- 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;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.
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.
Migration steps
- 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).
- 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.
- 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).
- 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).
- 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.
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.
Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria—without credit spikes.