Semi-Structured Data: JSON and VARIANT

Analogy categoryAnalogies

Snowflake stores semi-structured data such as JSON, Avro, ORC, Parquet, and XML in a single column of type VARIANT, which can hold any of these formats internally as an optimized, self-describing binary representation without requiring you to define a rigid schema upfront. Unlike storing raw JSON as plain text, a VARIANT column lets Snowflake automatically infer and index nested paths, so queries against deeply nested fields can still benefit from columnar pruning rather than scanning and re-parsing text on every query.

🏏

Cricket analogy: Like a modern scorecard app that stores every ball's full context (field positions, shot type, speed) as structured data rather than a plain-text commentary line, so you can later query 'all yorkers bowled in the death overs' instantly instead of re-reading every sentence.

Navigating VARIANT with Dot and Bracket Notation

You access fields inside a VARIANT column using colon-and-dot notation for object keys, like payload:customer.name, and bracket notation with an index for arrays, like payload:items[0].sku. Because every value extracted this way is itself typed as VARIANT, you generally need an explicit cast such as ::STRING or ::NUMBER to get a proper native type for comparisons, joins, or arithmetic, otherwise you're comparing variant wrapper values rather than plain scalars.

🏏

Cricket analogy: Like drilling into a match scorecard's nested structure — innings:batsmen[3].runs — to get one player's score, but you still need to cast it as a number before you can sum it with other scores in a total.

The FLATTEN table function is how you turn a nested array inside a VARIANT into multiple relational rows, which is essential whenever you need to join or aggregate over repeated sub-elements like line items in an order or tags on a support ticket. LATERAL FLATTEN(input => payload:items) produces one output row per array element, exposing VALUE (the element itself), INDEX (its position), and KEY (for object flattening), which you then join back to the outer row to reconstruct a normal tabular result.

🏏

Cricket analogy: Like unpacking a match JSON's array of overs into one row per over so you can average runs conceded per over, instead of treating the whole innings as one indivisible blob.

sql

-- Load raw JSON into a VARIANT column
CREATE OR REPLACE TABLE raw_orders (payload VARIANT);

COPY INTO raw_orders
  FROM @sales.raw_stage/orders_json/
  FILE_FORMAT = (TYPE = 'JSON');

-- Query nested fields and flatten a line-item array
SELECT
    payload:order_id::STRING          AS order_id,
    payload:customer.name::STRING     AS customer_name,
    item.value:sku::STRING            AS sku,
    item.value:quantity::NUMBER       AS quantity
FROM raw_orders,
     LATERAL FLATTEN(input => payload:items) AS item;

Schema Evolution and Performance Considerations

Because VARIANT is self-describing, upstream producers can add new fields to their JSON payloads without breaking existing queries, which is invaluable for event streams from third-party APIs whose schema you don't control. The tradeoff is that Snowflake's automatic clustering and micro-partition pruning work less precisely on deeply nested, highly variable JSON than on flat native columns, so for hot paths queried frequently it's common practice to extract the most-used fields into dedicated native columns alongside the raw VARIANT, giving you both flexibility and pruning performance.

🏏

Cricket analogy: Like a scoring app that keeps accepting new optional match fields (like a new DRS metric) without breaking old scorecards, but a commentator who needs instant access to strike rate every ball still keeps a dedicated quick-reference stat sheet rather than digging through the full raw feed each time.

A single VARIANT value is capped at 16 MB of compressed storage. If a source document could exceed that (a huge nested export, for example), split it into multiple smaller documents before loading, since Snowflake will reject a single value over the limit.

VARIANT stores semi-structured JSON, Avro, ORC, Parquet, or XML in a self-describing, indexable binary format.
Colon notation (payload:field) navigates objects; bracket notation (payload:items[0]) navigates arrays.
Extracted VARIANT values need explicit casts like ::STRING or ::NUMBER for comparisons and arithmetic.
LATERAL FLATTEN turns a nested array into one relational row per element, exposing VALUE, INDEX, and KEY.
VARIANT's flexible schema lets upstream producers add new fields without breaking existing queries.
Extracting hot fields into native columns alongside VARIANT improves pruning and query performance.
A single VARIANT value has a hard 16 MB compressed storage limit.