Workload

Redshift ETL pipelines to Snowflake

Re-home incremental ETL—staging, dedupe, UPSERT patterns, and orchestration—from Redshift into Snowflake with an explicit run contract and validation gates that prevent KPI drift and credit spikes.

Book assessment Explore workloads

Quick answer

Re-home incremental ETL—staging, dedupe, UPSERT patterns, and orchestration—from Redshift into Snowflake with an explicit run contract and validation gates that prevent KPI drift and credit spikes.

Back to pair page

Context

Why this breaks

Redshift ETL systems often hide correctness in operational conventions: watermark tables, VACUUM/ANALYZE-driven assumptions, and UPSERT patterns implemented via staging + delete/insert. Snowflake can implement equivalent outcomes—but the run contract must be explicit: windows, keys, deterministic ordering, dedupe rules, and restart semantics. Common symptoms after cutover:

  • Duplicates or missing updates because watermarks and tie-breakers were implicit - DELETE+INSERT UPSERT logic drifts under retries and partial failures - Late-arrival behavior changes because reprocessing windows weren’t defined - Credit spikes because MERGE/apply touches too much history (full-target scans) - Orchestration differences change retries/dependencies, turning failures into silent data issues

Approach

How conversion works

  • Inventory & classify ETL jobs, schedules, dependencies, and operational controls (watermarks, control tables, retries). - Extract the run contract: business keys, incremental boundaries, dedupe tie-breakers, late-arrival policy, and failure/restart semantics. - Re-home transformations to Snowflake staging (landing → typed staging → dedupe → apply) with bounded MERGE scopes. - Implement restartability: applied-window/batch tracking, idempotency markers, deterministic ordering, and safe retries. - Re-home orchestration (Airflow/dbt/your runner) with explicit DAG dependencies, retries, alerts, and warehouse isolation for batch vs BI. - Gate cutover with evidence: golden outputs + incremental integrity simulations (reruns, backfills, late injections) and rollback-ready criteria.

Coverage

Supported constructs

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

SourceTargetNotes
Staging + DELETE/INSERT UPSERT patternsSnowflake MERGE with bounded apply windowsAvoid full scans and ensure idempotency under retries.
Watermark/control tablesExplicit high-water marks + applied-window trackingRestartable and auditable incremental behavior.
ROW_NUMBER-based dedupe patternsDeterministic dedupe with explicit tie-breakersPrevents nondeterministic drift under retries.
SCD Type-1 / Type-2 logicMERGE + current-flag/end-date patternsBackfills and late updates validated as first-class scenarios.
Copy/load ingestion chainsLanding tables + batch manifestsIngestion made explicit; replayability supported.
Scheduler-driven chainsAirflow/dbt orchestration with warehouse isolationRetries, concurrency, and alerts modeled and monitored.

Compare

How workload changes

TopicRedshiftSnowflakeNotes
Incremental correctnessOften encoded in staging + delete/insert conventions and job timingExplicit high-water marks + deterministic staging + integrity gatesCorrectness becomes auditable and repeatable under retries/backfills.
UpsertsDELETE+INSERT common for UPSERT semanticsMERGE with bounded apply windowsAvoid full-target scans and protect idempotency.
Cost predictabilityCluster utilization and WLM behaviorWarehouse credits + pruning effectivenessBounded applies + isolation keep credit burn stable.
OrchestrationSchedulers + script chainsAirflow/dbt with explicit DAG contractsRetries, alerts, and dependencies become measurable.

Examples

Examples

Canonical Snowflake incremental apply pattern for Redshift-style pipelines: stage → dedupe deterministically → bounded MERGE + applied-batch tracking. Adjust keys, offsets, and casts to your model.

01_applied_batches.sql
-- Applied-batch tracking (restartability)
CREATE TABLE IF NOT EXISTS CONTROL.APPLIED_BATCHES (
  job_name STRING NOT NULL,
  batch_id STRING NOT NULL,
  applied_at TIMESTAMP_NTZ NOT NULL,
  PRIMARY KEY (job_name, batch_id)
);
02_stage_dedupe_apply.sql
-- Stage → dedupe → apply (bounded scope)
CREATE OR REPLACE TEMP TABLE STG_DEDUP AS
SELECT *
FROM STG_TYPED
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY BUSINESS_KEY
  ORDER BY EVENT_TS DESC, SRC_SEQ DESC NULLS LAST, INGESTED_AT DESC
) = 1;

SET min_d = (SELECT MIN(TO_DATE(event_ts)) FROM STG_DEDUP);
SET max_d = (SELECT MAX(TO_DATE(event_ts)) FROM STG_DEDUP);

MERGE INTO MART.FACT_ORDERS t
USING STG_DEDUP 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.EVENT_TS
WHEN NOT MATCHED THEN
  INSERT (ID, STATUS, AMOUNT, UPDATED_AT)
  VALUES (s.ID, s.STATUS, s.AMOUNT, s.EVENT_TS);
03_mark_applied.sql
-- Mark batch applied (idempotency marker)
INSERT INTO CONTROL.APPLIED_BATCHES (job_name, batch_id, applied_at)
VALUES (:job_name, :batch_id, CURRENT_TIMESTAMP());

Workload Assessment

Migrate Redshift ETL with restartability intact

We inventory your Redshift ETL estate, formalize the run contract (windows, restartability, SCD), migrate a representative pipeline end-to-end, and deliver parity evidence with cutover gates.

Book assessment

Avoid

Common pitfalls

  • DELETE+INSERT drift: partial failures and retries double-apply or delete wrong rows without idempotency guards.
  • Implicit watermarks: using job runtime or CURRENT_TIMESTAMP instead of persisted high-water marks.
  • Non-deterministic dedupe: ROW_NUMBER without stable tie-breakers causes drift under retries.
  • Full-target MERGE: missing apply boundaries causes large scans and credit spikes.
  • Schema evolution surprises: upstream types widen/change; typed targets break without a drift policy.
  • No warehouse isolation: BI refresh shares warehouse with ETL; tail latency and spend spikes follow.

Proof

Validation approach

  • Execution checks: pipelines run reliably under representative volumes and schedules.
  • Structural parity: window-level row counts and column profiles (null/min/max/distinct) for key tables.
  • KPI parity: aggregates by key dimensions for critical marts and dashboards.
  • Incremental integrity (mandatory): - _Idempotency:_ rerun same window/batch → no net change - _Restart simulation:_ fail mid-run → resume → correct final state - _Backfill:_ historical windows replay without drift - _Late-arrival:_ inject late corrections → only expected rows change - _Dedupe stability:_ duplicates eliminated consistently under retries
  • Cost/performance gates: bounded MERGE scope verified; credit/runtime thresholds set for top jobs.
  • Operational readiness: retry/alerting tests, canary gates, and rollback criteria defined before cutover.

Execution

Migration steps

A sequence that keeps correctness measurable and cutover controlled.

  1. 01

    Inventory ETL jobs, schedules, and dependencies

    Extract job chains, upstream/downstream dependencies, SLAs, retry policies, and control-table conventions. Identify business-critical marts and consumers.

  2. 02

    Formalize the run contract

    Define load windows/high-water marks, business keys, deterministic ordering/tie-breakers, dedupe rules, late-arrival policy, and restart semantics. Make idempotency explicit.

  3. 03

    Rebuild staging and apply on Snowflake

    Implement landing → typed staging → dedupe → bounded MERGE apply. Define schema evolution policy (widen/quarantine/reject) and explicit DQ checks.

  4. 04

    Re-home orchestration and warehouse posture

    Implement DAGs with retries/alerts and isolate batch warehouses from BI. Add applied-batch tracking and failure handling.

  5. 05

    Run parity and integrity gates

    Golden outputs + KPI aggregates, idempotency reruns, restart simulations, late-data injections, and backfill windows. Cut over only when thresholds pass and rollback criteria are defined.

FAQ

Frequently asked questions

Is Redshift ETL migration just rewriting scripts? +

No. The critical work is preserving the run contract: windows/watermarks, restartability, dedupe/tie-break rules, and UPSERT semantics under retries. Script translation is only one part.

How do you handle Redshift DELETE+INSERT UPSERT patterns? +

We migrate them into Snowflake MERGE with bounded apply windows and deterministic staging, then validate idempotency so retries don’t double-apply or delete wrong rows.

How do you avoid Snowflake credit spikes for ETL? +

We bound MERGE scope to affected windows, design pruning-aware staging, and isolate batch warehouses from BI. Validation includes credit/runtime baselines and regression thresholds for top jobs.

Can you keep pipelines incremental in Snowflake? +

Yes. We implement explicit high-water marks and late-arrival windows, use bounded applies, and validate incremental integrity with reruns/backfills/late injections.

Migration Acceleration

Cut over pipelines with proof-backed gates

Get an actionable migration plan with restart simulations, incremental integrity tests, reconciliation evidence, and credit baselines—so ETL cutover is controlled and dispute-proof.

Book assessment
Book assessment