Explain Semi-Structured Data: VARIANT & LATERAL FLATTEN in Databases

56 / 66

Snowflake

Semi-Structured Data: VARIANT & LATERAL FLATTEN

Query and shred nested JSON, Avro, Parquet, or XML data using native Snowflake types.

A VARIANT column holds JSON, Avro, Parquet, ORC or XML without a predefined schema. Load the document first, work out its shape later.

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

    CREATE TABLE events (payload VARIANT);
    COPY INTO events FROM @stage FILE_FORMAT = (TYPE = JSON);

It is not a text blob. Snowflake parses the document on load and stores it in a columnar form internally, keeping statistics on the paths it contains.

So a filter on a nested field can still prune micro-partitions, and reading one field does not read the whole document. That is the difference between this and storing JSON in a string column, where every query parses everything.

Reading fields: colon for paths, brackets for arrays, and always cast.

    SELECT payload:customer.name::STRING     AS name,
           payload:items[0].sku::STRING      AS first_sku,
           payload:total::NUMBER             AS total
    FROM events;

The cast matters. Without it the value stays VARIANT, so comparisons and joins behave oddly — a VARIANT string carries its quotes, so payload:status = 'PAID' can quietly be false.

payload:status = 'PAID'

LATERAL FLATTEN turns an array into rows. One order containing three items is one row, and you need three:

LATERAL FLATTEN

    {"order_id": 1, "items": [{"sku":"A"}, {"sku":"B"}, {"sku":"C"}]}

    SELECT e.payload:order_id::NUMBER AS order_id,
           i.value:sku::STRING        AS sku
    FROM events e,
         LATERAL FLATTEN(input => e.payload:items) i;

    order_id  sku
    1         A
    1         B
    1         C

FLATTEN gives you value (the element), index (its position), key (for objects) and path. Nesting two flattens handles arrays inside arrays.

FLATTENvalueindexkeypath

The trade this buys, and what it costs. You can ingest an API's output without agreeing a schema first, and a producer adding a field breaks nothing.

The cost is that nothing is validated. A field that silently changes from a number to a string, or vanishes, produces nulls rather than errors — and those nulls appear in a report weeks later.

So the usual pattern is two layers. Land the raw VARIANT, then build a typed table from it:

    raw.events (payload VARIANT)   <- loads never fail
         |  scheduled task
    analytics.orders (typed columns) <- casts, and fails loudly when the shape changes

The raw table keeps everything, including fields nobody has modelled yet, and Time Travel makes it replayable. The typed table is what analysts query.

Performance note. Filtering directly on a deeply nested path over a very large table is slower than a real column. If a path is queried constantly, promote it to its own column — Snowflake will prune on it properly.

What the interviewer is probing.1. "What does VARIANT give you?" Probing: the value. Stalls: "JSON storage." Moves up: schemaless storage that is still columnar — Snowflake extracts repeated paths into columns internally, so queries prune.

2. "What does FLATTEN do?" Probing: the operation. Stalls: "Parses JSON." Moves up: turns array or object elements into rows, which is how you join nested data to relational tables.

3. "When should you flatten into real columns?" Probing: the judgement. Stalls: "Never." Moves up: when a path is queried constantly — extracting it to a typed column gives better pruning and clearer contracts.

4. "What is the size limit worth knowing?" Probing: the detail. Stalls: "None." Moves up: 16MB compressed per VARIANT value, which large nested documents can hit.