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

• 1A 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.
• 2Use the LATERAL FLATTEN table function to explode arrays or objects into multiple rows, producing key/value/index/path/this columns for further transformation.
• 3Traverse nested structures with colon notation (col:field.subfield) combined with explicit casts (e.g., ::string, ::number) since VARIANT field access returns VARIANT by default.
• 4Snowflake 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.
• 5Functions like OBJECT_CONSTRUCT, ARRAY_CONSTRUCT, ARRAY_AGG, PARSE_JSON, and TO_VARIANT are used to build or convert semi-structured values, while GET/GET_PATH safely retrieve nested elements without raising errors on missing paths.
• 6INFER_SCHEMA combined 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.

3 · Keep going

These are independent practice questions, written against this certification's published exam guide. They are not the certification vendor's own questions, and not the real exam.