Semi-Structured Data in Snowflake

VARIANT, OBJECT, ARRAY, JSON loading, path navigation, FLATTEN, and why semi-structured support is a first-class warehouse feature.

Everything so far in this series has been rows and columns — TPCH orders, a customer key pointing at a nation key, tidy typed columns you SELECT and JOIN. That’s the world relational databases were built for. But most of the data that actually shows up at a warehouse’s door in 2026 doesn’t arrive that shape. It arrives as JSON from an API, as Parquet from a data lake, as event payloads where every producer nests things a little differently and adds a field next Tuesday without asking. The traditional answer was an ETL pipeline that flattened all of that into columns before it ever touched the database. Snowflake’s answer is different, and it’s one of the reasons people fell for the product: load the messy thing as-is, and still query it with SQL.

That capability rests on one data type, VARIANT, and one function, FLATTEN. Learn those two well and you’ve learned most of Snowflake’s semi-structured story. This chapter builds them up from nothing.

The three types: VARIANT, OBJECT, ARRAY

Snowflake has three semi-structured column types, and they nest inside each other exactly the way JSON does.

  • VARIANT is the universal container. A VARIANT value can hold anything — a number, a string, a boolean, a SQL NULL, an array, an object, or an arbitrarily deep nesting of those. When in doubt, this is the type you want. Loading JSON almost always lands in a VARIANT.
  • OBJECT is specifically a set of key–value pairs, where keys are strings and values are themselves VARIANT. It’s Snowflake’s map/dictionary type.
  • ARRAY is an ordered, zero-indexed list of VARIANT values. The elements don’t have to share a type — [1, "two", {"three": 3}] is a perfectly legal ARRAY.

In practice you’ll declare columns as VARIANT most of the time and let the actual JSON structure — object here, array there — live inside. OBJECT and ARRAY are useful when you construct semi-structured values or when you want to signal intent, but a VARIANT column can hold an object or an array just fine.

The thing worth understanding early — because it’s the reason this feature performs at all — is how Snowflake stores a VARIANT under the hood. It does not dump a blob of text into a cell. When you load JSON into a VARIANT column, Snowflake inspects the structure and, wherever it sees the same paths repeating across many rows with consistent types, it transparently pulls those out into their own hidden columnar sub-columns, compressed and statistics-tracked just like a native typed column. A ragged, unpredictable field falls back to being stored as-is. The upshot: querying payload:customer.id across a billion rows can be nearly as fast as querying a real customer_id column, because physically it often is one. You get schema-on-read ergonomics with much of schema-on-write performance. That’s the trick, and it’s automatic — you don’t configure it.

Getting a value in: PARSE_JSON

The smallest possible experiment needs no loading at all. PARSE_JSON takes a string and returns a VARIANT:

SELECT PARSE_JSON('{"book": "Dune", "price": 18.99, "tags": ["sci-fi", "classic"]}') AS v;

That gives you a single VARIANT value with an object inside. Note the contrast with TO_VARIANT, which wraps a scalar without parsing — PARSE_JSON('42') gives you the number 42 as a variant, while TO_VARIANT('42') gives you the string "42". When your input is a JSON document, you want PARSE_JSON.

Its defensive sibling is TRY_PARSE_JSON, which returns NULL instead of raising an error when handed malformed JSON. This matters more than it sounds. Real ingestion streams contain the occasional truncated or garbage record, and a single bad row shouldn’t blow up a query over ten million good ones:

SELECT TRY_PARSE_JSON('{"book": "Dune", broken') AS v;   -- returns NULL, not an error

Reach for TRY_PARSE_JSON any time the string isn’t guaranteed clean — which, with data from the outside world, is almost always.

Getting a file in: COPY INTO a VARIANT column

Inline strings are for learning. Real data arrives as files in a stage (which we covered when we loaded TPCH-shaped data earlier in the series). The pattern for JSON is: a table with one VARIANT column, a JSON file format, and a COPY INTO.

CREATE OR REPLACE FILE FORMAT ff_json TYPE = JSON;

CREATE OR REPLACE TABLE raw_orders (payload VARIANT);

COPY INTO raw_orders
FROM @my_stage/orders/
FILE_FORMAT = (FORMAT_NAME = ff_json);

Each JSON object in the staged files becomes one row, its whole structure sitting in payload. If your files are the newline-delimited “one JSON object per line” shape, the default works. If instead each file is a single top-level array of objects and you want each element to become a row, set STRIP_OUTER_ARRAY = TRUE on the file format so Snowflake unwraps the array during load rather than landing the entire array as one giant row:

CREATE OR REPLACE FILE FORMAT ff_json_array
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;

The same COPY INTO ... (VARIANT column) approach works for the binary semi-structured formats too — Parquet, Avro, and ORC — just by changing TYPE in the file format. You can always land the whole record in a single VARIANT and navigate it later. For these self-describing formats, though, Snowflake offers something better.

Letting Snowflake write the schema: INFER_SCHEMA

Parquet and friends carry their own schema in the file footer. Instead of guessing column types by hand, you can ask Snowflake to read that footer and tell you the columns:

SELECT *
FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@my_stage/books/',
    FILE_FORMAT => 'ff_parquet'
  )
);

INFER_SCHEMA returns a row per detected column — its name, its Snowflake type, nullability, and the ordering. On its own that’s just a report. The payoff is feeding it straight into a CREATE TABLE with USING TEMPLATE, which turns the inferred schema into a real typed table without you typing a single column definition:

CREATE OR REPLACE FILE FORMAT ff_parquet TYPE = PARQUET;

CREATE OR REPLACE TABLE books
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION => '@my_stage/books/',
        FILE_FORMAT => 'ff_parquet'
      )
    )
  );

COPY INTO books
FROM @my_stage/books/
FILE_FORMAT = (FORMAT_NAME = ff_parquet)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

That MATCH_BY_COLUMN_NAME on the COPY is what lines each Parquet field up with the correctly-named column the template just created, rather than relying on positional order. This inferred-schema path is the modern way to ingest Parquet into typed columns; the plain COPY INTO (VARIANT) route is what you use when you’d rather keep the raw structure and navigate it. You’ll pick between them constantly, and the rest of this chapter is really about being fluent in the second, more Snowflake-flavored option.

Once data is in a VARIANT, you pull values out with path expressions. The primary accessor is the colon, :, which steps into an object by key. From there, dots and bracket notation drill deeper, and integer brackets index into arrays.

Let’s give ourselves a concrete value to navigate. Here’s an order with a nested customer object and an array of line items:

CREATE OR REPLACE TABLE orders_raw (payload VARIANT);

INSERT INTO orders_raw
SELECT PARSE_JSON($$
{
  "order_id": 4001,
  "customer": { "id": 77, "name": "Arrakis Books", "vip": true },
  "items": [
    { "sku": "DUNE-01",  "qty": 2, "price": 18.99 },
    { "sku": "FDN-42",   "qty": 1, "price": 24.50 },
    { "sku": "NEURO-07", "qty": 3, "price": 15.00 }
  ],
  "notes": null
}
$$);

(The $$ ... $$ is Snowflake’s dollar-quoting — handy for pasting JSON without escaping every inner quote.)

Now navigate it:

SELECT
  payload:order_id            AS order_id,
  payload:customer.name       AS customer_name,
  payload:customer['id']      AS customer_id,
  payload:items[0].sku        AS first_sku,
  payload:items[2]:price      AS third_price
FROM orders_raw;

A few things to read carefully here. After the leading colon that enters payload, you can descend with either a dot (customer.name) or another colon (items[2]:price) or bracket-quoting (customer['id']) — they’re interchangeable ways to name a key, and you’ll mix them by taste. Bracket notation with a quoted string earns its keep when a key contains a space, a dot, or other characters a bare identifier can’t hold: payload:['order date']. Integer brackets index arrays, zero-based, so items[0] is the first line and items[2] is the third.

Keys are case-sensitive here — a real gotcha

This is the moment to internalize a Snowflake quirk that trips up newcomers coming from its ordinary SQL, where unquoted identifiers fold to uppercase. Inside a VARIANT, keys are matched exactly as written in the JSON — case included. The JSON key "order_id" is reached by payload:order_id. If the source had spelled it "orderId", then payload:orderId is what works and payload:order_id returns NULL — silently, with no error. When a path unexpectedly yields nothing, mismatched case is the first thing to check. This is the opposite of the case-folding rule for column and table names, and holding both in your head at once is part of becoming fluent.

Everything comes out as VARIANT — cast it

Here’s the subtlety that separates working code from surprising code. A path expression returns a VARIANT, not a native type. If you SELECT payload:customer.name, the result displays with surrounding quotes — "Arrakis Books" — because it’s still a JSON string value, not a SQL VARCHAR. Comparisons, joins, and functions on it may not behave the way you expect while it’s wrapped.

The fix is the cast operator ::, which unwraps the variant into a real typed value:

SELECT
  payload:order_id::number          AS order_id,
  payload:customer.name::string     AS customer_name,
  payload:customer.vip::boolean     AS is_vip
FROM orders_raw;

Now customer_name is a genuine VARCHAR with no quotes, order_id is a NUMBER, is_vip is a real BOOLEAN. Make casting a reflex. The rule of thumb: navigate with : and ., then cast with :: the moment you’re done navigating and want a usable column. v:price::number is the idiomatic shape — path in, type out.

Two flavors of NULL

VARIANT carries a distinction SQL normally doesn’t, and it will bite you if you ignore it. There’s SQL NULL — the absence of a value, what you get when a path doesn’t exist — and there’s JSON null, an actual value the document contains. In our order, payload:notes is a JSON null: the key is present and its value is the null literal. That is not the same as payload:missing, a key that isn’t there.

SELECT
  payload:notes            AS notes_variant,       -- a VARIANT holding JSON null
  payload:notes IS NULL    AS sql_null_check,      -- FALSE — the value exists
  payload:missing IS NULL  AS missing_check        -- TRUE  — no such path
FROM orders_raw;

If you need to treat JSON null as absent, the cleanest test is IS_NULL_VALUE(payload:notes), which returns TRUE specifically for JSON null. And casting a JSON null out — payload:notes::string — gives you a SQL NULL, which is usually what downstream logic wants anyway. Keep the distinction in mind mostly when you’re filtering on presence.

FLATTEN: the technique that makes it all worth it

Navigation gets you scalar values and whole nested chunks. But the defining problem of semi-structured data is the array: an order has many line items, and you want one row per line item so you can sum, group, and join like normal. Turning a nested array into rows is called unnesting or exploding, and in Snowflake the tool is FLATTEN.

FLATTEN is a table function. You call it with LATERAL (or via TABLE(...)) so that, for each row of your base table, it produces a set of rows — one per element of the array you point it at. Here’s the whole move on our order:

SELECT
  o.payload:order_id::number        AS order_id,
  o.payload:customer.name::string   AS customer_name,
  item.value:sku::string            AS sku,
  item.value:qty::number            AS qty,
  item.value:price::number(10,2)    AS price,
  (item.value:qty::number * item.value:price::number(10,2)) AS line_total
FROM orders_raw o,
  LATERAL FLATTEN(input => o.payload:items) AS item;

Our one order row becomes three rows — one per line item — and each carries the order-level fields (order_id, customer_name) alongside the per-item fields it exploded out. That’s the pattern you’ll write over and over: a base table, comma-joined to a LATERAL FLATTEN over one array field, then everything cast into typed columns.

The alias — item here — exposes a fixed set of columns FLATTEN emits for each element, and knowing them unlocks the function:

  • value — the element itself, as a VARIANT. This is the one you use most; item.value:sku reaches into each line-item object.
  • index — the element’s zero-based position in the array. Perfect for “which line number was this.”
  • key — the key name, when you flatten an object rather than an array (null for arrays).
  • this — the entire collection currently being flattened.
  • seq — a unique sequence number per input row, useful for grouping exploded rows back to their parent.

Once the array is rows, ordinary SQL takes over. Want the total value of each order across its lines?

SELECT
  o.payload:order_id::number AS order_id,
  SUM(item.value:qty::number * item.value:price::number(10,2)) AS order_total
FROM orders_raw o,
  LATERAL FLATTEN(input => o.payload:items) AS item
GROUP BY 1;

Nothing exotic — a GROUP BY over exploded rows. That’s the joy of it: FLATTEN is the one unfamiliar step, and the moment it’s done you’re back in the plain SQL you already know from the TPCH chapters.

Two options worth knowing. OUTER => TRUE makes FLATTEN behave like a left join — if the array is empty or missing, you still get the parent row once (with nulls) instead of losing it entirely, which matters when you’re counting orders and some have no items yet. RECURSIVE => TRUE walks the entire nested structure top to bottom, exploding objects within objects, which is handy for auditing an unfamiliar payload’s every leaf.

Building semi-structured values

The traffic runs both ways. Sometimes you have flat relational rows and need to assemble JSON — to hand a nested document to an API, or to collapse a child table into one column. Snowflake gives you constructor functions that mirror the accessors.

OBJECT_CONSTRUCT builds an object from alternating key/value arguments:

SELECT OBJECT_CONSTRUCT('name', 'Dune', 'price', 18.99, 'in_stock', TRUE) AS book;
-- { "in_stock": true, "name": "Dune", "price": 18.99 }

ARRAY_CONSTRUCT builds an array from its arguments, and ARRAY_AGG is the aggregate version — it rolls the values of a group into a single array, the natural inverse of FLATTEN. Together they let you take normalized rows and nest them back up. Here’s TPCH line items collapsed into one JSON array of objects per order:

USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

SELECT
  l_orderkey,
  ARRAY_AGG(
    OBJECT_CONSTRUCT(
      'part',  l_partkey,
      'qty',   l_quantity,
      'price', l_extendedprice
    )
  ) AS lines
FROM lineitem
WHERE l_orderkey < 100
GROUP BY l_orderkey
ORDER BY l_orderkey;

Each order now has a lines column holding an array of line-item objects — you’ve turned a relational child table into nested JSON, the exact structure FLATTEN would explode back apart. OBJECT_CONSTRUCT(*) is a shorthand that packs an entire row into an object keyed by column name (you saw it earlier inside the USING TEMPLATE trick).

Finally, OBJECT_KEYS returns an array of an object’s top-level keys — indispensable when you’re profiling a payload whose shape you don’t fully know:

SELECT OBJECT_KEYS(payload) AS top_level_keys
FROM orders_raw;
-- ["customer", "items", "notes", "order_id"]

The pattern that survives production: raw VARIANT, typed views on top

Now the part that turns a party trick into an architecture. The temptation, once you can query JSON directly, is to leave everything in VARIANT forever and sprinkle :path::type casts across every query and dashboard. Don’t. That scatters your schema knowledge into a hundred BI tools and breaks silently the day an upstream key gets renamed.

The durable pattern is a hybrid: land the payload raw, expose it typed. Concretely — a raw ingestion table with a single VARIANT column that you never delete from, because it’s your audit trail and your insurance against the transform you haven’t thought of yet. Then a view on top that does all the navigating and casting in one place, presenting clean typed columns to everyone downstream:

CREATE OR REPLACE VIEW orders_typed AS
SELECT
  payload:order_id::number             AS order_id,
  payload:customer.id::number          AS customer_id,
  payload:customer.name::string        AS customer_name,
  payload:customer.vip::boolean        AS is_vip,
  payload                              AS raw_payload
FROM orders_raw;

BI users query orders_typed and see ordinary columns; they never learn a colon path. If an upstream key changes, you fix the view once and every dashboard keeps working. If auditors ask what actually arrived, raw_payload still holds the original bytes. When a view’s casting becomes a query-time cost you can measure — flattening a huge array on every read, say — you graduate the hottest fields from the view into a materialized, physically-typed staging table. The rule of thumb: navigate on read while the shape is still settling; extract to typed columns once the shape is stable and the volume is large.

Performance and the 16 MB wall

Two practical realities to carry out of this chapter.

First, the good news about performance, and it comes back to that columnar-storage trick from the opening. Because Snowflake shreds well-structured VARIANT paths into hidden sub-columns with their own min/max statistics, a filter like WHERE payload:status::string = 'shipped' can benefit from partition pruning — skipping micro-partitions that can’t contain a match — much as a native column would. The caveat is that pruning helps most when a path appears consistently across rows with stable types. Ragged, sparse, or wildly-varying fields don’t shred as cleanly and fall back to slower access. This is precisely why the “extract hot fields to typed columns” advice pays off at scale: a materialized NUMBER column always prunes; a chaotic VARIANT path sometimes doesn’t. For the fields BI hits constantly on billions of rows, the extraction is worth it.

Second, the hard limit: a single VARIANT (or OBJECT or ARRAY) value cannot exceed roughly 16 MB compressed. That’s per value, per row — plenty for ordinary records, but a real ceiling for pathological documents: one giant array with hundreds of thousands of elements, or an event that embeds a whole file. If you hit it, the load or the PARSE_JSON fails outright. The remedy is architectural rather than a setting to flip: unnest earlier so each row holds a smaller slice (flatten during ingestion with STRIP_OUTER_ARRAY), or split the monster document upstream before it reaches Snowflake. Bumping into 16 MB is a design smell — it usually means a row is standing in for what should be many rows.

Final thoughts

Semi-structured support is the feature that let Snowflake sit at the seam between the messy world that produces data and the tidy world that analyzes it. VARIANT lets the mess in without a fight; : and :: let you read it as SQL; FLATTEN turns its arrays into the rows every downstream tool expects; and the constructor functions let you hand nested structure back out when something upstream wants it. The whole surface reduces to a small, learnable vocabulary — and the columnar storage underneath means using it rarely costs what schema-on-read usually costs elsewhere.

If you take one habit away, make it the hybrid pattern: raw VARIANT first, typed columns or views second. Keep the original payload for the audit trail and the transform you can’t yet foresee, but never make a dashboard author learn a colon path. Load the mess as-is, then present it clean. That discipline is what keeps a JSON firehose from turning your warehouse back into the pile of ad-hoc casts you adopted Snowflake to escape.

Examples in this chapter are docs-checked against the series’ stated versions, but they were not executed in this repository unless a companion project explicitly says so.

Next: Table Types and Views

Comments