+1 (415) 997-4269
PowderInsights

Semi-Structured Data in Snowflake: VARIANT, FLATTEN, Schema Evolution, and Structured Types

Almost every Snowflake account has at least one table with a VARIANT column that nobody wants to touch: API payloads, event streams, SAP IDocs, clickstream JSON, webhook bodies. It loaded easily, which is why it is still there, and it is quietly the most expensive thing in the warehouse — every query re-parses it, pruning barely works, and the analysts have given up and asked for "a flat table please".

This tutorial is the middle path most teams actually need: land the raw payload, flatten deliberately, and promote the fields that matter into typed columns — including Snowflake's structured types (OBJECT(...), ARRAY(...), MAP(...)), which are what make semi-structured data portable to Iceberg readers.

1. Land it raw, and keep it raw

Resist the urge to parse at load time. The raw landing table is your replay buffer when the upstream schema shifts.

CREATE OR REPLACE FILE FORMAT ff_json
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE
  COMPRESSION = AUTO;

CREATE OR REPLACE TABLE raw.events_json (
  payload        VARIANT,
  src_file       STRING,
  src_row        NUMBER,
  loaded_at      TIMESTAMP_NTZ DEFAULT SYSDATE()
);

COPY INTO raw.events_json (payload, src_file, src_row)
FROM (
  SELECT $1, METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
  FROM @lake_stage/events/
)
FILE_FORMAT = (FORMAT_NAME = ff_json)
ON_ERROR = CONTINUE;

Two habits that pay for themselves: carry METADATA$FILENAME / METADATA$FILE_ROW_NUMBER so any bad record can be traced back to a byte range in a file, and never ON_ERROR = ABORT_STATEMENT on a feed you do not control.

For Parquet and Avro you rarely want VARIANT at all — use schema detection instead (section 5).

2. Reading VARIANT without guessing

Path notation and casts are the whole language:

SELECT
  payload:event_id::STRING              AS event_id,
  payload:user.id::NUMBER               AS user_id,
  payload:user."firstName"::STRING      AS first_name,   -- quoted = case sensitive
  payload:ts::TIMESTAMP_NTZ             AS event_ts,
  payload:items[0].sku::STRING          AS first_sku
FROM raw.events_json;

Things that bite people:

  • payload:a.b is case sensitive for the JSON key. payload:firstName and payload:firstname are different paths; the second silently returns NULL. Quote the key exactly as it appears.
  • :: is a cast, : is a path lookup, [0] is an array index. Missing paths return SQL NULL; a JSON null returns a VARIANT null — IS_NULL_VALUE(payload:x) distinguishes them.
  • TRY_CAST / TRY_TO_NUMBER on dirty feeds, always. One "quantity": "N/A" should not fail a pipeline run.
  • Comparing a VARIANT to a string without a cast works, but it defeats pruning and constant folding. Cast first.

Use TYPEOF() when you are profiling a feed you did not design:

SELECT key, TYPEOF(value) AS t, COUNT(*)
FROM raw.events_json, LATERAL FLATTEN(input => payload)
GROUP BY 1,2 ORDER BY 1,3 DESC;

That single query tells you which keys exist, how often, and whether a field is sometimes a string and sometimes a number — which it will be.

3. FLATTEN: arrays into rows

FLATTEN is a table function; you join it laterally to the row that owns the array.

SELECT
  e.payload:order_id::STRING   AS order_id,
  li.index                     AS line_no,
  li.value:sku::STRING         AS sku,
  li.value:qty::NUMBER         AS qty,
  li.value:price::NUMBER(12,2) AS price
FROM raw.orders_json e,
     LATERAL FLATTEN(input => e.payload:items) li;

Four arguments matter:

  • OUTER => TRUE keeps the parent row when the array is empty or missing (a LEFT JOIN in spirit). Without it, orders with no line items disappear — a classic silent revenue gap in a reconciliation.
  • RECURSIVE => TRUE walks the whole tree; combined with PATH, it is how you build a generic "explode everything" profiler.
  • PATH => 'items' is an alternative to input => payload:items.
  • MODE => 'ARRAY' | 'OBJECT' | 'BOTH' controls what gets expanded when the input is mixed.

Nested arrays chain, and the order of the flattens is the order of the nesting:

SELECT o.value:id::STRING AS order_id,
       s.value:tracking::STRING AS tracking
FROM raw.orders_json e,
     LATERAL FLATTEN(input => e.payload:orders)          o,
     LATERAL FLATTEN(input => o.value:shipments, OUTER => TRUE) s;

Each extra FLATTEN multiplies row counts. Aggregate as early as you can rather than flattening five levels and then DISTINCT-ing the mess — that pattern is behind a surprising share of runaway warehouse bills.

4. Promote to typed columns (the part teams skip)

The refined layer should not contain VARIANT for fields that analysts query. Materialise them:

CREATE OR REPLACE DYNAMIC TABLE mart.order_lines
  TARGET_LAG = '15 minutes'
  WAREHOUSE  = wh_transform
AS
SELECT
  e.payload:order_id::STRING            AS order_id,
  e.payload:customer.id::NUMBER         AS customer_id,
  e.payload:ts::TIMESTAMP_NTZ           AS ordered_at,
  li.index                              AS line_no,
  li.value:sku::STRING                  AS sku,
  TRY_TO_NUMBER(li.value:qty)           AS qty,
  TRY_TO_NUMBER(li.value:price, 12, 2)  AS unit_price,
  e.payload                             AS raw_payload   -- keep the escape hatch
FROM raw.orders_json e,
     LATERAL FLATTEN(input => e.payload:items, OUTER => TRUE) li;

Why it matters beyond tidiness: min/max metadata on a typed TIMESTAMP or NUMBER column drives partition pruning. A predicate on payload:ts::TIMESTAMP can only prune if Snowflake extracted that sub-column during load — which it does for well-behaved, consistently typed JSON, and does not do for paths that appear in only some rows, for elements inside arrays, or for values whose type varies. Typed columns make pruning deterministic instead of hopeful.

Keeping raw_payload alongside costs little (it compresses well) and means a new field request is a column addition, not a backfill from cloud storage.

5. Schema detection and schema evolution

You do not have to hand-write DDL for Parquet, Avro, ORC, CSV, or JSON files:

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

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

Then let the table track upstream additions:

ALTER TABLE raw.events SET ENABLE_SCHEMA_EVOLUTION = TRUE;

COPY INTO raw.events
FROM @lake_stage/events/
FILE_FORMAT = (FORMAT_NAME = ff_parquet)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

With schema evolution on, a COPY that meets a new source column adds it to the table, and a column missing from the file is relaxed to NULLABLE instead of failing the load. Constraints worth knowing: it applies to COPY (and Snowpipe) loads using column matching, it adds columns but never drops or retypes them, and there is a cap on how many columns one load can add. Audit what it did:

SELECT column_name, data_type, created
FROM information_schema.columns
WHERE table_name = 'EVENTS' AND created > DATEADD(day, -7, CURRENT_TIMESTAMP());

Turn it on for raw landing tables. Leave it off for curated marts, where a surprise column is a change request, not an event.

6. Structured types: OBJECT, ARRAY, MAP

VARIANT is schemaless and Snowflake-specific. Structured types give you nesting with a declared schema — which is what Iceberg, Parquet, and external engines need:

CREATE OR REPLACE TABLE mart.orders_structured (
  order_id  STRING,
  customer  OBJECT(id NUMBER, name STRING, tier STRING),
  items     ARRAY(OBJECT(sku STRING, qty NUMBER, price NUMBER(12,2))),
  tags      MAP(STRING, STRING)
);

INSERT INTO mart.orders_structured
SELECT
  payload:order_id::STRING,
  CAST(payload:customer AS OBJECT(id NUMBER, name STRING, tier STRING)),
  CAST(payload:items    AS ARRAY(OBJECT(sku STRING, qty NUMBER, price NUMBER(12,2)))),
  CAST(payload:tags     AS MAP(STRING, STRING))
FROM raw.orders_json;

You still use items[0].sku and FLATTEN, but the types are enforced at write time and the column maps cleanly onto a Parquet/Iceberg schema. This is the required move if those tables will live as Iceberg tables read by Spark or Trino — untyped VARIANT has no faithful Iceberg equivalent. It is stricter: a payload whose qty arrives as "3" will fail the cast rather than quietly storing a string, so keep the TRY_CAST + quarantine pattern in front of it.

7. A quarantine pattern that keeps pipelines green

CREATE OR REPLACE TABLE raw.events_rejects (
  payload VARIANT, reason STRING, src_file STRING, detected_at TIMESTAMP_NTZ
);

INSERT INTO raw.events_rejects
SELECT payload,
       'missing or invalid event_ts',
       src_file,
       SYSDATE()
FROM raw.events_json
WHERE TRY_TO_TIMESTAMP_NTZ(payload:ts::STRING) IS NULL;

Wire a Snowflake alert or a data metric function to the row count of the rejects table and you get schema drift detection for free: the day an upstream team renames ts to event_time, the rejects table spikes at 03:00 instead of the CFO finding it in a dashboard three weeks later.

Choosing, in one paragraph

Land raw as VARIANT with file metadata and no parsing. Profile with FLATTEN + TYPEOF before you model. Build the curated layer as typed columns (dynamic tables are the least-effort way to keep them fresh), keeping the raw payload as an escape hatch. Enable schema evolution on landing tables only. Use structured OBJECT/ARRAY/MAP types wherever the data must leave Snowflake as Iceberg or Parquet. And quarantine rather than fail.

PowderInsights designs and rebuilds Snowflake ingestion and modelling layers — including the "one giant VARIANT table" refactors that nobody on the team wants to own. Get in touch with a sample payload and your current load pattern and we will sketch the target model.