Workload

Redshift SQL queries to BigQuery

Translate Redshift SQL—analytic/window patterns, date/time logic, and legacy DIST/SORT-era assumptions—into BigQuery Standard SQL with validation gates that prevent semantic drift and cost surprises.

Book assessment Explore workloads

Quick answer

Translate Redshift SQL—analytic/window patterns, date/time logic, and legacy DIST/SORT-era assumptions—into BigQuery Standard SQL with validation gates that prevent semantic drift and cost surprises.

Back to pair page

Context

Why this breaks

Redshift SQL estates often encode more than syntax: performance assumptions shaped by DISTKEY/SORTKEY, vacuum/analyze habits, and UPSERT patterns implemented via staging + delete/insert. BigQuery will compile many translated queries—but outputs and costs can drift when implicit casts, NULL semantics, and time handling aren’t made explicit. 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 - Performance regressions because DIST/SORT-era query shapes don’t prune well in BigQuery SQL migration must preserve both meaning and a BigQuery-native execution posture.

Approach

How conversion works

  • Inventory & prioritize the SQL corpus (BI extracts, views, dbt/ELT models, app-embedded SQL). Rank by business impact, frequency, and complexity. - Normalize Redshift dialect: quoting/identifiers, date literals, function idioms, and CTE normalization. - Rewrite with rule-anchored mappings: function equivalents, window/analytic rewrites, explicit cast strategy, and deterministic ordering for top-N/windowed filters. - Pattern libraries for high-risk constructs: NULL-safe comparisons, time semantics, UPSERT/delete-insert patterns, and string/regex behaviors. - Validate with gates: compile/run checks, catalog/type alignment, and golden-query parity with edge-window sampling diffs. - Performance-safe refactors: pruning-aware filters, join strategy adjustments, and pre-aggregation/materialization guidance for expensive BI queries.

Coverage

Supported constructs

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

SourceTargetNotes
Window functions + top-N filtersWindow functions + QUALIFY/LIMIT patternsDeterministic ORDER BY and tie-breakers enforced.
DELETE+INSERT UPSERT queriesMERGE with bounded apply windowsIdempotency and match-key semantics made explicit.
Date/time arithmeticDATE_ADD/DATE_DIFF/TIMESTAMP_* functionsDATE vs TIMESTAMP intent normalized explicitly.
NULL/type coercion idiomsExplicit casts + null-safe comparisonsPrevents join drift and filter selectivity changes.
String/regex functionsBigQuery string/regex equivalentsEdge-case behavior validated via golden cohorts.
DISTKEY/SORTKEY-driven query shapesPruning-aware rewrites + partitioning/clustering guidanceReplace physical tuning assumptions with BigQuery-native layout and pruning.

Compare

How workload changes

TopicRedshiftBigQueryNotes
Performance modelDISTKEY/SORTKEY + VACUUM/ANALYZE habitsPartition pruning + clustering + slot usageConverted queries often need pruning-aware rewrites to keep cost predictable.
UpsertsDELETE+INSERT patterns commonMERGE with bounded apply windowsAvoid full scans and protect idempotency under retries.
Type behaviorImplicit casts often toleratedExplicit casts recommended for stable outputsValidation focuses on CASE/COALESCE and join keys.

Examples

Examples

Representative Redshift → BigQuery 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_bigquery.sql
-- BigQuery: same pattern (explicit grouping)
SELECT
  product_id,
  SUM(revenue) AS rev
FROM `proj.ds.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_bigquery.sql
-- BigQuery: DATE_ADD
SELECT DATE_ADD(order_dt, INTERVAL 7 DAY) AS plus_7
FROM `proj.ds.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_bigquery.sql
-- BigQuery: MERGE with bounded apply window
DECLARE min_d DATE DEFAULT (SELECT MIN(DATE(updated_at)) FROM `proj.ds.stg_orders`);
DECLARE max_d DATE DEFAULT (SELECT MAX(DATE(updated_at)) FROM `proj.ds.stg_orders`);

MERGE `proj.mart.fact_orders` t
USING `proj.ds.stg_orders` s
ON t.id = s.id
AND 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.

Book assessment

Avoid

Common pitfalls

  • Implicit casts: Redshift queries that “just work” can drift in BigQuery unless types are made explicit.
  • NULL semantics in joins: join keys and filters behave differently when NULLs are involved unless null-safe equality is used deliberately.
  • Window ordering ambiguity: ROW_NUMBER/RANK without stable tie-breakers causes nondeterministic drift.
  • DIST/SORT assumptions carried over: physical tuning treated as schema; BigQuery needs partitioning/clustering and pruning-aware SQL.
  • UPSERT via DELETE+INSERT: naive rewrites break idempotency under retries and partial failures.
  • Unbounded scans: missing partition filters cause bytes-scanned blowups and cost spikes.

Proof

Validation approach

  • Compilation gates: converted queries compile under BigQuery Standard SQL.
  • Catalog/type checks: referenced tables/columns exist; implicit casts are surfaced and made explicit.
  • Golden-query parity: critical dashboards and queries match on outputs or agreed tolerances.
  • KPI aggregates: compare aggregates by key dimensions (date, region, product, cohorts).
  • Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and timezone transitions.
  • Performance baseline: capture runtime/bytes scanned/slot time for top queries; set regression thresholds.

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, dbt models, and app-embedded 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 date/time 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, regex/collation expectations).

  4. 04

    Validate with golden queries and edge cohorts

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

  5. 05

    Optimize top queries for BigQuery

    Add pruning-aware filters, align partitioning/clustering to access paths, and recommend materializations for expensive BI workloads.

FAQ

Frequently asked questions

Is Redshift SQL directly compatible with BigQuery? +

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 performance regressions from missing pruning. 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 query shapes often need pruning-aware rewrites and BigQuery layout alignment (partitioning/clustering). We baseline and tune the top queries.

Migration Acceleration

Ship BigQuery 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.

Book assessment
Book assessment