Snowflake – JSON and Semi-Structured Data

Snowflake's handling of JSON is one of the genuinely good reasons to choose it. You load a document without designing a schema, query it with ordinary SQL, and decide later — with the data in front of you — which parts deserve real columns. This lesson covers the syntax, the two mistakes everyone makes, and the decision that actually matters.

The three types

In practice you declare VARIANT and let it hold whatever arrives. The limit is 16 MB compressed per value, which is generous for events and restrictive for documents.

Loading JSON

The pattern that works for almost every source is one VARIANT column plus whatever metadata you want alongside it:

CREATE OR REPLACE TABLE learn_snowflake.staging.raw_orders (
  payload    VARIANT,
  source_file STRING,
  loaded_at  TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

COPY INTO learn_snowflake.staging.raw_orders (payload, source_file)
FROM (
  SELECT $1, METADATA$FILENAME
  FROM @learn_snowflake.staging.s3_stage
)
FILE_FORMAT = (TYPE = JSON, STRIP_OUTER_ARRAY = TRUE);

STRIP_OUTER_ARRAY = TRUE is for files that are one big [ {...}, {...} ] — it loads one row per element rather than one enormous row. Newline-delimited JSON needs it off, since each line is already a separate document.

METADATA$FILENAME costs nothing and is worth taking every time. When a downstream model produces something strange, being able to trace a row back to the file it came from turns a day of guessing into a query.

Reading it back

Given a document like this:

{
  "order_id": "A-1001",
  "placed_at": "2026-08-22T14:05:00",
  "customer": {"id": 42, "email": "sam@example.com", "segment": "BUILDING"},
  "items": [
    {"sku": "SKU-1", "qty": 2, "price": 19.99},
    {"sku": "SKU-7", "qty": 1, "price": 45.00}
  ],
  "coupon": null
}

the access syntax has three forms, and they are interchangeable:

SELECT payload:order_id::STRING            AS order_id,      -- colon then dot
       payload:customer.email::STRING      AS email,
       payload['customer']['segment']::STRING AS segment,    -- bracket form
       GET_PATH(payload, 'customer.id')::INT  AS customer_id, -- function form
       payload:items[0].sku::STRING        AS first_sku,
       ARRAY_SIZE(payload:items)           AS item_count
FROM   learn_snowflake.staging.raw_orders;

Use the bracket form when a key contains a space or a hyphen, and when a key is case-sensitive — which brings us to the first of the two mistakes.

Mistake one: forgetting the cast

Every path expression returns a VARIANT, not a string or a number. A VARIANT holding text renders with its JSON quotes, and those quotes are part of the value:

SELECT payload:order_id           AS uncast,   -- "A-1001"  <- quotes included
       payload:order_id::STRING   AS cast;     -- A-1001

-- So this join matches nothing at all, and reports no error:
SELECT *
FROM   learn_snowflake.staging.raw_orders r
JOIN   learn_snowflake.staging.orders     o
       ON r.payload:order_id = o.order_ref;   -- VARIANT vs STRING

-- This is the one you meant:
       ON r.payload:order_id::STRING = o.order_ref;

The rule is simple and worth applying without thinking about it: cast at every extraction, every time. A view over the raw table that does all the casting once is the usual way to stop this leaking into every query downstream.

Mistake two: case sensitivity

Unquoted SQL identifiers are folded to upper case. JSON keys are not — they are data, and they are exactly as written in the document. So a document with "orderId" is not reachable as payload:orderid:

SELECT payload:orderId::STRING   AS works,        -- matches "orderId"
       payload:ORDERID::STRING   AS returns_null, -- no such key
       payload['orderId']::STRING AS also_works;

When a field is unexpectedly NULL, check the spelling in the source document before anything else. OBJECT_KEYS tells you exactly what is there:

SELECT DISTINCT key
FROM   learn_snowflake.staging.raw_orders,
       LATERAL FLATTEN(input => OBJECT_KEYS(payload)) f(seq, key_, path, index, key, this);

FLATTEN: turning arrays into rows

The order document above has two items in an array. To get one row per item — which is what any analysis of items needs — use FLATTEN:

SELECT r.payload:order_id::STRING AS order_id,
       i.value:sku::STRING        AS sku,
       i.value:qty::INT           AS qty,
       i.value:price::NUMBER(10,2) AS price,
       i.index                    AS line_no
FROM   learn_snowflake.staging.raw_orders r,
       LATERAL FLATTEN(input => r.payload:items) i;

FLATTEN returns a table with six columns; the two you use constantly are value (the element) and index (its position). It behaves like an inner join, so an order with an empty items array disappears from the result entirely. Pass OUTER => TRUE to keep it with nulls:

FROM   learn_snowflake.staging.raw_orders r,
       LATERAL FLATTEN(input => r.payload:items, OUTER => TRUE) i;

For nested arrays — items each containing a list of options — either chain two FLATTENs or pass RECURSIVE => TRUE to walk the whole document. Chaining is clearer and is what you want most of the time.

Building JSON, not just reading it

The same functions work in reverse, which is how you produce a JSON payload for an API or a downstream consumer without string concatenation:

SELECT OBJECT_CONSTRUCT(
         'order_id', o.order_id,
         'total',    o.total,
         'lines',    ARRAY_AGG(OBJECT_CONSTRUCT('sku', l.sku, 'qty', l.qty))
       ) AS document
FROM   learn_snowflake.staging.orders     o
JOIN   learn_snowflake.staging.order_line l ON l.order_id = o.order_id
GROUP  BY o.order_id, o.total;

OBJECT_CONSTRUCT drops keys whose value is NULL, which is usually what you want and occasionally is not — OBJECT_CONSTRUCT_KEEP_NULL keeps them. To render the result as text rather than a VARIANT, wrap it in TO_JSON.

Handling documents whose shape moves

The reason to land raw JSON in the first place is usually that the upstream shape is not stable. Two habits keep that from becoming a surprise.

Watch the key set. A new key appearing, or an old one vanishing, is the earliest signal that a producer changed. It costs one query to notice:

SELECT f.key, COUNT(*) AS documents, MIN(r.loaded_at) AS first_seen
FROM   learn_snowflake.staging.raw_orders r,
       LATERAL FLATTEN(input => r.payload) f
GROUP  BY f.key
ORDER  BY first_seen DESC;

Use TRY_CAST in the projection. When a field that has always been a number arrives as "12" or "n/a", a plain ::INT fails the whole statement and the typed table stops refreshing. TRY_TO_NUMBER lands a NULL instead, and a count of nulls is something you can alert on.

SELECT payload:order_id::STRING                    AS order_id,
       TRY_TO_NUMBER(payload:total::STRING, 12, 2) AS total,
       TYPEOF(payload:total)                       AS total_type_in_source
FROM   learn_snowflake.staging.raw_orders
WHERE  TYPEOF(payload:total) NOT IN ('DECIMAL', 'INTEGER', 'DOUBLE');

TYPEOF reports what a VARIANT actually holds, and that last query is the one to run when a field has started behaving oddly — it finds the rows where the source type is not what you assumed.

The decision that matters: leave it or shred it

Queries against VARIANT are not free. Snowflake stores semi-structured data columnar-ised where it can — repeated paths with consistent types get extracted internally and prune almost as well as real columns — but paths that vary, or hold mixed types, fall back to reading the whole document.

So the choice is between two shapes, and most teams end up wanting both:

The standard pattern keeps the raw payload as the record of truth and projects a typed table from it, so nothing is lost and the common queries are fast:

CREATE OR REPLACE TABLE learn_snowflake.staging.orders_typed AS
SELECT payload:order_id::STRING              AS order_id,
       payload:placed_at::TIMESTAMP_NTZ      AS placed_at,
       payload:customer.id::INT              AS customer_id,
       payload:customer.segment::STRING      AS segment,
       ARRAY_SIZE(payload:items)             AS item_count,
       payload                               AS raw   -- keep the original
FROM   learn_snowflake.staging.raw_orders;

Keeping raw alongside the typed columns costs storage and buys you the ability to answer a question nobody anticipated without re-loading anything. On a transient staging table that is a good trade almost every time.

Next: querying data — the SQL worth knowing beyond SELECT … WHERE.