Reshaping and transforming data inside a Snowflake query
Snowflake supports transforming data during load and via SQL using flattening, pivoting, and semi-structured data functions. Key skills include using FLATTEN to explode arrays/objects, PIVOT/UNPIVOT to reshape rows and columns, and querying JSON/VARIANT data with dot notation and bracket notation. These techniques are frequently combined with CTEs, streams, and tasks for ELT pipelines.
1 · Learn the must-know
- FLATTEN is a table function that explodes VARIANT, ARRAY, or OBJECT columns into multiple rows, and it must be used with LATERAL when referencing columns from the same FROM clause row.
- PIVOT converts row values into columns (requires an aggregate function), while UNPIVOT converts columns into rows; both require explicitly listing the values or columns involved.
- Semi-structured data is queried using colon notation (col:field) or dot/bracket notation after casting, and functions like GET,
GET_PATH, andTO_VARIANThelp navigate nested structures. - The COPY INTO <table> command supports transformations (e.g., column reordering, casting, expressions) during load, but only a limited set of functions are allowed and joins are not supported.
- Common transformation functions include
OBJECT_CONSTRUCT/OBJECT_KEYSfor building/inspecting objects,ARRAY_AGG/ARRAY_CONSTRUCTfor arrays, andPARSE_JSONto convert strings to VARIANT. - QUALIFY clause filters results of window functions without needing a subquery, simplifying deduplication and top-N transformation patterns.
2 · Check your understanding
A Snowflake Practitioner stores incoming orders in a VARIANT column named PAYLOAD, where each row contains a nested array field items that can be empty for cancelled orders. The practitioner runs LATERAL FLATTEN(input => o.payload:items) to expand each item into its own row. Cancelled orders with an empty items array no longer appear in the result set. Every order, including cancelled ones with no items, must appear in the output. Which change should the Practitioner make?
What you have tried across SnowPro Core's objectives, not a readiness score.
Snowflake AI Data Cloud Features and Architecture31% of the exam0 of 6 tried
Account Management and Data Governance20% of the exam0 of 3 tried
Data Loading, Unloading, and Connectivity18% of the exam0 of 3 tried
Performance Optimization, Querying, and Transformation21% of the exam0 of 4 tried
3 · Keep going
Ready for more? Take a weighted mock or try free practice questions.