Workload

Databricks SQL queries to Snowflake

Translate Spark SQL idioms—MERGE/upserts, window logic, array/map patterns, and time handling—into Snowflake SQL with validation gates that prevent semantic drift and KPI surprises.

Book assessment Explore workloads

Quick answer

Translate Spark SQL idioms—MERGE/upserts, window logic, array/map patterns, and time handling—into Snowflake SQL with validation gates that prevent semantic drift and KPI surprises.

Back to pair page

Context

Why this breaks

Databricks SQL estates are usually a mix of Spark SQL, Delta-specific patterns, and BI-generated queries. Many will translate syntactically—but drift happens when implicit semantics differ: NULL behavior, type coercion in CASE/COALESCE, timestamp/timezone assumptions, and array/map handling. Common symptoms after cutover:

  • KPI drift from window logic and top-N selections with incomplete ordering - MERGE behavior changes because match keys, casts, or update predicates were implicit
  • Arrays/maps and semi-structured fields don’t translate 1:1 - Date/time logic shifts due to timezone assumptions and TIMESTAMP vs DATE intent - Performance regressions because Spark-era query shapes don’t align to Snowflake pruning/clustering SQL migration must preserve both meaning and a Snowflake-native execution posture.

Approach

How conversion works

  • Inventory & prioritize the SQL corpus (dbt models, notebooks, BI extracts, jobs, views). Rank by business impact and risk patterns (MERGE, windows, arrays/maps, time). - Normalize Spark SQL: identifier/quoting rules, functions, and CTE shapes to reduce dialect-specific noise. - Rewrite with rule-anchored mappings: Spark SQL → Snowflake SQL function equivalents, explicit cast strategy, and deterministic ordering for windowed filters. - Apply pattern libraries for high-risk constructs: MERGE/upserts, late-arrival dedupe, arrays/maps, timezone normalization, and null-safe comparisons. - Validate with gates: compilation, catalog/type alignment, and golden-query parity (plus edge cohorts for ties/null-heavy segments). - Performance-safe refactors: tune query shapes for Snowflake pruning (clustering alignment, bounded MERGE scopes, and staging boundaries).

Coverage

Supported constructs

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

SourceTargetNotes
Spark SQL window filters (ROW_NUMBER/RANK)Snowflake window functions + QUALIFYDeterministic ordering and tie-breakers enforced.
Delta MERGE and upsert patternsSnowflake MERGE with staged applyMatch keys, casts, and update predicates made explicit and testable.
explode/arrays/maps/structsFLATTEN/VARIANT + lateral joins (or normalized staging)Empty-array semantics preserved intentionally.
DATE/TIMESTAMP arithmeticSnowflake date/time functionsTimezone intent (NTZ/LTZ/TZ) normalized explicitly.
NULL-safe comparisonsExplicit null-safe equality patternsPrevents join drift and duplicate inserts.
String/regex functionsSnowflake string/regex equivalentsEdge-case behavior validated via golden cohorts.

Compare

How workload changes

TopicDatabricks / Spark SQLSnowflakeNotes
Execution assumptionsShuffle-heavy plans and partition overwrite patterns commonPruning + clustering alignment and bounded apply scopesSnowflake rewards pruning discipline and explicit apply windows.
Semi-structured handlingStruct/array/map types and explode patternsVARIANT + FLATTEN or normalized stagingChoose between VARIANT-centric or fully typed modeling.
Time semanticsTimezone assumptions often implicitExplicit NTZ/LTZ/TZ and conversion rulesTime intent must be declared to prevent boundary-day drift.

Examples

Examples

Representative Databricks → Snowflake rewrites for windowed dedupe, explode/flatten, and MERGE patterns. Adjust keys, paths, and casts to your model.

01_windowed_dedupe_databricks.sql
-- Databricks: latest row per key
SELECT *
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY business_key
  ORDER BY event_ts DESC
) = 1;
01_windowed_dedupe_snowflake.sql
-- Snowflake: enforce deterministic tie-breakers
SELECT *
FROM EVENTS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY BUSINESS_KEY
  ORDER BY EVENT_TS DESC, SRC_OFFSET DESC NULLS LAST, INGESTED_AT DESC
) = 1;
02_explode_databricks.sql
-- Databricks: explode array
SELECT o.id, i.item_id
FROM orders o
LATERAL VIEW explode(o.items) e AS i;
02_flatten_snowflake.sql
-- Snowflake: FLATTEN (items stored as VARIANT array)
SELECT o.id, f.value:item_id::STRING AS item_id
FROM ORDERS o, LATERAL FLATTEN(input => o.items) f;
03_merge_snowflake.sql
-- Snowflake MERGE (staged apply)
MERGE INTO MART.FACT_ORDERS t
USING STG_DEDUP s
ON t.ID = s.ID
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);

info

Make the dedupe rule explicit

Most KPI drift comes from missing dedupe rules or unstable ordering. Treat match keys, tie-breakers, and late-arrival policy as requirements—not implementation details.

info

Avoid full-target MERGE scans

Design staging boundaries and pruning-aware apply strategies so credit burn stays predictable as volume grows.

Workload Assessment

Translate Databricks 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

  • Type coercion drift: Spark’s implicit casts inside CASE/COALESCE differ; Snowflake may require explicit casts to preserve intent.
  • NULL semantics in joins: equality and null-safe comparisons can change match behavior if not explicit.
  • Window ordering ambiguity: ROW_NUMBER/RANK filters without stable tie-breakers cause nondeterministic drift.
  • Array/map handling: Spark explode, map, and nested structs need explicit Snowflake VARIANT/FLATTEN rewrites when semi-structured.
  • MERGE scope blowups: unbounded MERGE patterns cause large scans and credit spikes.
  • Timezone assumptions: Databricks sessions often assume a timezone; Snowflake timestamp types (NTZ/LTZ/TZ) require explicit intent.
  • Over-trusting “it runs”: compilation success is not parity; validate with golden outputs and edge cohorts.

Proof

Validation approach

  • Compilation gates: converted queries compile and execute in Snowflake reliably under representative parameters.
  • Catalog/type checks: referenced objects exist; implicit casts are surfaced and made explicit where needed.
  • Golden-query parity: business-critical queries/dashboards 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, scanned micro-partitions, and credit burn 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 dbt models, notebooks, job SQL, view definitions, and BI SQL. Rank by business impact, frequency, and risk patterns (MERGE, windows, arrays/maps, time).

  2. 02

    Define semantic contracts for risky patterns

    Make tie-breakers, NULL handling, casting strategy, timezone intent, and array/map handling explicit—especially for top-N, dedupe, and MERGE logic.

  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

    Align clustering to access paths, bound MERGEs and applies, and recommend materializations where BI patterns repeatedly scan large facts.

FAQ

Frequently asked questions

Is Databricks SQL the same as Snowflake SQL? +

No. Databricks uses Spark SQL with Delta-centric patterns; Snowflake has different function sets, typing behavior, and semi-structured handling. We convert syntax and make semantic intent explicit where drift is common.

What are the biggest drift risks when converting Spark SQL? +

Implicit casts, NULL semantics in joins/CASE branches, timezone assumptions, and window/top-N ordering. These areas get explicit contracts and golden-query validation.

Do you handle MERGE and upsert query patterns? +

Yes. We migrate MERGE logic with explicit match keys, deterministic dedupe, and staged apply patterns, then validate idempotency and KPI parity under reruns and late data.

Will performance be the same after translation? +

Not automatically. Spark-era query shapes often need pruning/clustering-aware adjustments in Snowflake. We baseline and tune the top queries as part of this workload.

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.

Book assessment
Book assessment