Data Transformation Study Guide — DEA-C02

Data Transformation

This domain (25% of DEA-C02, the single largest weighted domain alongside Data Movement) tests your ability to reshape data after it lands in Snowflake. Expect scenario questions that hand you a requirement — idempotent upserts, SCD Type 2 history, JSON explosion, reusable business logic, a near-real-time aggregate layer — and ask you to pick the correct construct (MERGE vs. stream+task vs. dynamic table; UDF vs. UDTF vs. stored procedure; view vs. materialized view) and to write or debug the SQL/Python. The exam is heavy on exact semantics: what FLATTEN(OUTER => TRUE) returns, what a materialized view is not allowed to contain, what happens to a transaction when a DDL statement runs inside it, and which dbt materialization produces which DDL. Memorize behaviors and limits, not marketing.

Data transformation patterns: ELT in Snowflake SQL, window functions, MERGE

ELT means land raw data first (COPY INTO → raw/landing table, often with a VARIANT column), then transform with SQL using Snowflake compute. Standard layering: raw → staging (typed, deduped) → intermediate → marts. Use transient tables for intermediate layers (no Fail-safe, cheaper storage) and CTAS (CREATE OR REPLACE TABLE ... AS SELECT) for full rebuilds.

MERGE is the core upsert. Syntax essentials:

MERGE INTO tgt t
USING (SELECT * FROM src QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC)=1) s
  ON t.id = s.id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.ts > t.ts THEN UPDATE SET t.val = s.val, t.ts = s.ts
WHEN NOT MATCHED THEN INSERT (id,val,ts) VALUES (s.id,s.val,s.ts);

• Multiple WHEN MATCHED clauses are allowed and are evaluated in order; the first matching clause wins.
• If more than one source row joins the same target row, the MERGE is nondeterministic. The session parameter ERROR_ON_NONDETERMINISTIC_MERGE defaults to TRUE, so the statement errors. Setting it to FALSE makes Snowflake silently pick one row. The correct fix on the exam is almost always deduplicate the source with QUALIFY ROW_NUMBER() ... = 1, not flipping the parameter.
• The sibling parameter for UPDATE ... FROM is ERROR_ON_NONDETERMINISTIC_UPDATE (default FALSE).

Window functions are the second pillar. function() OVER (PARTITION BY ... ORDER BY ... frame).

Incremental ELT uses streams (CDC) + tasks (scheduling). Stream metadata columns: METADATA$ACTION (INSERT/DELETE), METADATA$ISUPDATE (TRUE for the delete/insert pair produced by an update), METADATA$ROW_ID. A standard stream shows net row-level deltas; an append-only stream shows inserts only (cheaper, no delete/update rows); an insert-only stream is the external-table equivalent. A stream is consumed only inside a DML transaction that reads it; reading in SELECT does not advance the offset.

Bold caption: choosing the right incremental transformation pattern.

Common traps

• Choosing ERROR_ON_NONDETERMINISTIC_MERGE = FALSE as the "fix" for duplicate source keys — it hides data loss; dedupe instead.
• Assuming WHERE can filter ROW_NUMBER() output. It cannot; you need QUALIFY or a subquery.
• Forgetting that consuming a stream requires DML; a task that only SELECTs from a stream never advances the offset and reprocesses forever.
• Believing MERGE is atomic across multiple statements — a single MERGE is atomic, but a MERGE + a follow-up INSERT needs an explicit transaction.
• Confusing RANK (gaps) with DENSE_RANK (no gaps) and ROW_NUMBER (always unique).

Worked scenario. A landing table receives late-arriving CDC with multiple versions of the same order_id in one batch, including hard deletes flagged op='D'. Requirement: target must reflect the latest state, idempotently. Correct approach: stream or batch → dedupe to one row per order_id by highest commit_ts with QUALIFY → single MERGE with WHEN MATCHED AND op='D' THEN DELETE, then WHEN MATCHED THEN UPDATE, then WHEN NOT MATCHED AND op<>'D' THEN INSERT. One statement = one transaction = idempotent and atomic.