Moving data between semi-structured and structured shapes
Snowflake stores semi-structured data (JSON, Avro, ORC, Parquet, XML) natively in VARIANT, ARRAY, and OBJECT columns, letting you query nested data with dot/bracket notation without predefining a rigid schema. The FLATTEN table function and casting operators are the primary tools for transforming this data into relational rows and columns for downstream use.
1 · Learn the must-know
- A VARIANT column has a maximum size of 16 MB compressed per value, and exceeding it causes load or query errors, so oversized JSON/arrays must be split or restructured.
- Use the LATERAL FLATTEN table function to explode arrays or objects into multiple rows, producing key/value/index/path/this columns for further transformation.
- Traverse nested structures with colon notation (col:field.subfield) combined with explicit casts (e.g., ::string, ::number) since VARIANT field access returns VARIANT by default.
- Snowflake automatically stores semi-structured columns internally as a hybrid columnar structure with per-path statistics, enabling pruning and efficient querying even without explicit relational schema.
- Functions like
OBJECT_CONSTRUCT,ARRAY_CONSTRUCT,ARRAY_AGG,PARSE_JSON, andTO_VARIANTare used to build or convert semi-structured values, while GET/GET_PATHsafely retrieve nested elements without raising errors on missing paths. INFER_SCHEMAcombined with CREATE TABLE ... USING TEMPLATE can auto-generate a relational schema from staged semi-structured files (e.g., Parquet, JSON, CSV), simplifying schema-on-read to schema-on-write transitions.
2 · Check your understanding
A Data Engineer maintains a table ORDERS with a VARIANT column ORDER_DATA that stores documents such as {"order_id": 1001, "customer": "C77", "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]}. The engineer runs: SELECT o.order_data:order_id::INT AS order_id, f.value:sku::STRING AS sku, f.value:qty::INT AS qty FROM orders o, LATERAL FLATTEN(input => o.order_data) f; The result contains one row for every top-level key in the document instead of one row per item, and SKU and QTY are always NULL. What change to the FLATTEN call will return one row per item with correct SKU and QTY values?
What you have tried across SnowPro Advanced Data Engineer's objectives, not a readiness score.
Data Movement28% of the exam*0 of 7 tried
Performance Optimization19% of the exam*0 of 3 tried
Storage and Data Protection14% of the exam*0 of 3 tried
Data Governance14% of the exam*0 of 2 tried
Data Transformation25% of the exam*0 of 7 tried
* Our estimate. Snowflake publishes no section weights.
3 · Keep going
Ready for more? Take a weighted mock or try free practice questions.