Semi-Structured Data: VARIANT, JSON, and FLATTEN

Snowflake reads JSON as a first-class type. A VARIANT column holds the document, path notation reads into it, and — this is the part that matters — it is stored columnar, so querying one field does not parse the whole document.

Landing JSON

create or replace table events (
    event_id   number,
    received_at timestamp_ntz,
    payload    variant
);

insert into events
select
    1,
    '2026-01-04 09:14:02'::timestamp_ntz,
    parse_json($$
    {
      "type": "order_placed",
      "customer": { "id": 1, "name": "Ada Lovelace", "country": "GB" },
      "items": [
        { "sku": "BK-1041", "title": "SICP",            "qty": 1, "price": 25.50 },
        { "sku": "BK-2277", "title": "The Mythical Man-Month", "qty": 2, "price": 12.00 }
      ],
      "channel": "web"
    }
    $$);

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.688s

$$ ... $$ is Snowflake’s dollar-quoting — it avoids escaping every double quote in the JSON. parse_json validates as it converts; try_parse_json returns null instead of failing, which is what you want when loading a file that may contain junk.

Reading fields

select
    event_id,
    payload:type                     as event_type,
    payload:customer.name            as customer_name,
    payload:customer.id              as customer_id,
    payload:channel::string          as channel
from events;

+----------+----------------+------------------+-------------+---------+
| EVENT_ID | EVENT_TYPE     | CUSTOMER_NAME    | CUSTOMER_ID | CHANNEL |
|----------+----------------+------------------+-------------+---------|
|        1 | "order_placed" | "Ada Lovelace"   |           1 | web     |
+----------+----------------+------------------+-------------+---------+
1 Row(s) produced. Time Elapsed: 0.402s

Look at the quotes. payload:type returned "order_placed" with them, because the result is still a VARIANT holding a JSON string. payload:channel::string returned web without them.

That difference breaks joins silently:

select count(*)
from events e join customers c on e.payload:customer.id = c.customer_id;

+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.402s

select count(*)
from events e join customers c on e.payload:customer.id::number = c.customer_id;

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+
1 Row(s) produced. Time Elapsed: 0.402s

Always cast on the way out. Zero rows from a JSON join is nearly always a missing ::.

Bracket notation works too, and is required for keys that are not valid identifiers:

select payload['customer']['country']::string as country,
       payload:items[0].sku::string           as first_sku
from events;

+---------+-----------+
| COUNTRY | FIRST_SKU |
|---------+-----------|
| GB      | BK-1041   |
+---------+-----------+
1 Row(s) produced. Time Elapsed: 0.402s

Paths that do not exist return NULL rather than erroring — convenient, and a trap when a field is renamed upstream. The query keeps working and quietly returns nulls forever.

Exploding arrays

select
    e.event_id,
    e.payload:customer.name::string as customer,
    i.value:sku::string             as sku,
    i.value:title::string           as title,
    i.value:qty::number             as qty,
    i.value:price::number(10,2)     as price,
    i.index                         as line_no
from events e,
     lateral flatten(input => e.payload:items) i;

+----------+--------------+---------+--------------------------+-----+-------+---------+
| EVENT_ID | CUSTOMER     | SKU     | TITLE                    | QTY | PRICE | LINE_NO |
|----------+--------------+---------+--------------------------+-----+-------+---------|
|        1 | Ada Lovelace | BK-1041 | SICP                     |   1 | 25.50 |       0 |
|        1 | Ada Lovelace | BK-2277 | The Mythical Man-Month   |   2 | 12.00 |       1 |
+----------+--------------+---------+--------------------------+-----+-------+---------+
2 Row(s) produced. Time Elapsed: 0.911s

One event became two rows. FLATTEN returns a table with these columns:

Now the aggregate people actually want:

select
    e.payload:customer.country::string as country,
    sum(i.value:qty::number * i.value:price::number(10,2)) as revenue
from events e,
     lateral flatten(input => e.payload:items) i
group by 1;

+---------+---------+
| COUNTRY | REVENUE |
|---------+---------|
| GB      |   49.50 |
+---------+---------+
1 Row(s) produced. Time Elapsed: 0.688s

An empty or missing array drops the parent row entirely, because FLATTEN behaves like an inner join. Keep the row with outer => true:

select e.event_id, i.value:sku::string as sku
from events e,
     lateral flatten(input => e.payload:promotions, outer => true) i;

+----------+------+
| EVENT_ID | SKU  |
|----------+------|
|        1 | NULL |
+----------+------+
1 Row(s) produced. Time Elapsed: 0.402s

The outer flag is the fix for “my row count dropped after I added a flatten”.

Nested arrays

recursive => true walks the whole structure:

select path, typeof(value) as type, value
from events e, lateral flatten(input => e.payload, recursive => true)
where typeof(value) != 'OBJECT' and path not like '%[%'
limit 5;

+-------------------+---------+----------------+
| PATH              | TYPE    | VALUE          |
|-------------------+---------+----------------|
| type              | VARCHAR | "order_placed" |
| customer.id       | INTEGER | 1              |
| customer.name     | VARCHAR | "Ada Lovelace" |
| customer.country  | VARCHAR | "GB"           |
| channel           | VARCHAR | "web"          |
+-------------------+---------+----------------+
5 Row(s) produced. Time Elapsed: 0.911s

This is the fastest way to discover the shape of a document you have never seen — much better than opening a sample file in an editor.

Building JSON

select object_construct(
    'customer_id', c.customer_id,
    'name',        c.full_name,
    'orders',      array_agg(object_construct('id', o.order_id, 'amount', o.amount))
) as customer_doc
from customers c
join orders o on o.customer_id = c.customer_id
where c.customer_id = 1
group by c.customer_id, c.full_name;

+------------------------------------------------------------+
| CUSTOMER_DOC                                               |
|------------------------------------------------------------|
| {                                                          |
|   "customer_id": 1,                                        |
|   "name": "Ada Lovelace",                                  |
|   "orders": [                                              |
|     { "amount": 25.50, "id": 1001 },                       |
|     { "amount": 40.00, "id": 1003 }                        |
|   ]                                                        |
| }                                                          |
+------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.688s

object_construct drops keys whose value is null; object_construct_keep_null keeps them. That difference matters when the consumer distinguishes “absent” from “null”.

Loading JSON files

create or replace file format json_standard
    type = json
    strip_outer_array = true;

copy into events (payload)
from (select $1 from @bookshop_stage/events_2026_01.json.gz)
file_format = (format_name = json_standard);

+-------------------------------------+--------+-------------+-------------+-------------+
| file                                | status | rows_parsed | rows_loaded | errors_seen |
|-------------------------------------+--------+-------------+-------------+-------------|
| bookshop_stage/events_2026_01.json.gz | LOADED |      41209 |       41209 |           0 |
+-------------------------------------+--------+-------------+-------------+-------------+
1 Row(s) produced. Time Elapsed: 8.204s

strip_outer_array = true turns a file containing one big [...] into one row per element. Without it, the whole array lands as a single row — and hits the 16 MB VARIANT limit on any real file.

When to promote fields to columns

Raw VARIANT is the right landing format: nothing is lost, and a new field needs no migration. It is the wrong query interface. Build the extracted layer as a view:

create or replace view order_events as
select
    event_id,
    received_at,
    payload:type::string                as event_type,
    payload:customer.id::number         as customer_id,
    payload:customer.country::string    as country_code,
    payload:channel::string             as channel,
    payload                             as raw_payload
from events
where payload:type::string = 'order_placed';

+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| View ORDER_EVENTS successfully created.     |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

Consumers get typed columns and autocomplete, raw_payload stays available for the field nobody has needed yet, and when a path changes upstream it is fixed in one view rather than in forty dashboards.

For a table that is queried constantly, materialise it — Snowflake’s automatic sub-columnar extraction only covers paths it sees often, and only for values under 32 KB with consistent types. A field that is sometimes a string and sometimes an object is not extracted, and each query re-parses it.

Practice

-- e.payload:customer.id = c.customer_id
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+

-- e.payload:customer.id::number = c.customer_id
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

select
    e.event_id,
    sum(i.value:qty::number * i.value:price::number(10,2)) as order_total
from events e, lateral flatten(input => e.payload:items) i
group by 1;

+----------+-------------+
| EVENT_ID | ORDER_TOTAL |
|----------+-------------|
|        1 |       49.50 |
+----------+-------------+
1 Row(s) produced. Time Elapsed: 0.688s

-- without outer
0 Row(s) produced.
-- with outer => true
+----------+------+
| EVENT_ID | SKU  |
|----------+------|
|        1 | NULL |
+----------+------+

select distinct path, typeof(value) as type
from events, lateral flatten(input => payload, recursive => true)
order by path;

+---------------------+---------+
| PATH                | TYPE    |
|---------------------+---------|
| channel             | VARCHAR |
| customer            | OBJECT  |
| customer.country    | VARCHAR |
| customer.id         | INTEGER |
| customer.name       | VARCHAR |
| items               | ARRAY   |
| items[0]            | OBJECT  |
| items[0].price      | DECIMAL |
| items[0].qty        | INTEGER |
| items[0].sku        | VARCHAR |
+---------------------+---------+

Next: micro-partitions and clustering — why one query scans 40 GB and another scans 40 MB.