Snowflake
Semi-Structured Data: VARIANT & LATERAL FLATTEN
Query and shred nested JSON, Avro, Parquet, or XML data using native Snowflake types.
Snowflake natively supports semi-structured data via the VARIANT data type, which can store JSON, Avro, ORC, Parquet, or XML data in a compact, self-describing binary format called OPTIMIZED ROW COLUMNAR internally, while still allowing efficient columnar pruning on the paths accessed. This means you can load raw JSON directly into a single VARIANT column without predefining a rigid schema.
VARIANT is like keeping a mixed box of assorted parts and only unpacking and labeling the specific parts you need right now (colon-path access); LATERAL FLATTEN is like tipping out a bag of loose items onto the table so each becomes its own countable row.
Key Concepts
1
Accessing nested fields uses colon notation (payload:customer.name) or bracket notation for arrays (payload:items[0]), with an explicit cast (::STRING, ::NUMBER) typically needed since VARIANT fields are untyped until cast. Snowflake also automatically collects statistics on the paths within VARIANT columns that are actually queried, allowing pruning benefits similar to structured columns over time.
colon notationpayload:customer.namepayload:items[0]::STRING::NUMBER
2
When a VARIANT column contains an array or nested object that needs to become multiple relational rows, LATERAL FLATTEN is the tool: it's a table function that "explodes" an array or object into one row per element, similar to UNNEST or CROSS APPLY in other SQL dialects. Combined with a LATERAL join, this lets a query flatten deeply nested structures inline without a separate ETL step.
LATERAL FLATTENUNNESTCROSS APPLYLATERAL
3
This flexible-schema approach is especially powerful for ingesting data from APIs, event streams, or logs where the exact structure may evolve over time — you load first and reshape at query time, rather than needing rigid upfront schema design.