SnowPro Advanced Data Engineer: Data Transformation

What candidates must know

Transformation questions test SQL fluency plus Snowflake-specific constructs: CTEs and recursive CTEs, joins, window functions, QUALIFY, PIVOT/UNPIVOT, MERGE, multi-table INSERT ALL/INSERT FIRST, and semi-structured handling with FLATTEN (comma-joined or LATERAL), OBJECT_CONSTRUCT, ARRAY_AGG, and higher-order functions such as TRANSFORM, FILTER and REDUCE. Beyond SQL, know UDFs (SQL, JavaScript, Python, Java), Snowflake Scripting stored procedures, Snowpark DataFrames, and declarative pipelines built with dynamic tables.

Traps

• QUALIFY is evaluated after window functions but before ORDER BY/LIMIT; it is the clean way to filter on a windowed value without a subquery.
• FLATTEN needs LATERAL (or an implicit comma join) and an alias; OUTER => TRUE preserves rows whose array is empty or missing.
• MERGE fails or behaves nondeterministically when the source has duplicate join keys; dedupe the source first.
• VARIANT comparisons and arithmetic usually require explicit casts (TRY_TO_NUMBER, ::VARCHAR); silent implicit casts produce surprising nulls.
• CAST errors on bad input while TRY_CAST returns NULL; questions love that distinction.

Rule of thumb

Push filters as early as possible and prefer set-based SQL over row-by-row JavaScript UDFs, which are slower and can obscure pruning.

Worked example

To keep only the latest event per user from a raw table: SELECT * FROM events QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) = 1. To explode a tags array: SELECT e.id, f.value::string AS tag FROM events e, LATERAL FLATTEN(input => e.tags) f.