Semi-structured data uses keys, markup, and hierarchical nesting instead of a fixed relational schema. Its schema can be absent initially or change over time. Snowflake stores it in VARIANT, which can contain OBJECT, ARRAY, and nested VARIANT values. section 4.1.4 section 4.6
A practical Snowflake pipeline handles semi-structured data in stages:
- Create a raw staging table with a VARIANT column, source filename, and load timestamp. section 4.1.4
- Load records from an external stage with COPY, preserving file metadata and controlling load errors. section 4.1.4
- Query nested values with the colon operator, then cast returned VARIANT values to the required types. section 4.6
- Use FLATTEN with LATERAL to turn nested arrays or objects into rows for relational downstream use. section 4.6
Hotel orders to bakery analytics
A hotel supplies order information as JSON. Snowflake loads each document into ORDERS_PARK_INN_RAW_STG, retaining the source filename and load timestamp, then flattens nested delivery dates and daily orders into a relational table so the bakery can count each baked-good type. section 4.1.4
