Snowflake — Module 06

Semi-Structured Data: VARIANT, JSON, FLATTEN

Load and query JSON, arrays, nested fields, VARIANT, OBJECT, ARRAY, and LATERAL FLATTEN.

70 min September 2026

Loading Data with Stages and COPY INTO ELT and Medallion Architecture in Snowflake

// Part 01 — Plain-English foundation

Semi-Structured Data: VARIANT, JSON, FLATTEN From Scratch

Snowflake can store JSON, arrays, and nested objects in VARIANT columns. You can load messy API payloads first, then extract stable fields later with SQL.

Why this matters: Real data rarely arrives as perfect tables. APIs send nested JSON, event systems send flexible payloads, and partners change optional fields. If your warehouse cannot handle semi-structured data, every small schema surprise becomes a failed pipeline.

Mental model

VARIANT is a flexible box. You can put JSON inside the box, inspect keys, cast values into normal columns, and flatten arrays when one input record contains many child records.

// Part 02 — Core concepts

The Concepts You Must Own

✓VARIANT stores semi-structured values such as JSON objects, arrays, strings, numbers, and booleans.
✓Colon notation reads fields from objects, such as payload:customer:id.
✓Use casts to turn VARIANT values into typed SQL columns.
✓LATERAL FLATTEN expands arrays into rows.
✓Raw VARIANT is useful, but curated tables should expose typed columns for most users.

Concept	Meaning	Why it matters
VARIANT	Flexible column for JSON-like values.	Use for raw ingestion and fields that change often.
OBJECT	Key-value structure.	Represents JSON objects such as customer or address.
ARRAY	Ordered list of values.	Flatten when each item needs its own row.
FLATTEN	Table function that expands arrays/objects.	Critical for nested line items, events, and attributes.
Cast	Converts flexible values into SQL types.	Needed for reliable joins, metrics, and BI tools.

// Part 03 — How the work actually flows

Step-by-Step Workflow

✓Load raw JSON into a landing table with a VARIANT payload and load timestamp.
✓Profile which keys exist and which ones are optional.
✓Create a typed silver table or view with important fields cast to SQL types.
✓Flatten nested arrays such as line_items into child tables.
✓Add tests for required keys, valid types, and unexpected nulls.

Semi-Structured Data: VARIANT, JSON, FLATTEN example

CREATE OR REPLACE TABLE RAW.API_EVENTS (
  payload VARIANT,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

SELECT
  payload:event_id::STRING AS event_id,
  payload:customer:id::STRING AS customer_id,
  payload:total::NUMBER(12,2) AS total_usd,
  loaded_at
FROM RAW.API_EVENTS;

SELECT
  payload:order_id::STRING AS order_id,
  item.value:sku::STRING AS sku,
  item.value:quantity::NUMBER AS quantity
FROM RAW.API_EVENTS,
LATERAL FLATTEN(input => payload:line_items) item;

Do not read the example as magic syntax to memorize. Read it as a production habit: name the objects clearly, make assumptions visible, preserve enough metadata to debug later, and keep the business promise attached to the SQL.

// Part 04 — Mistakes and debugging

Common Mistakes That Break Snowflake Projects

Watch these carefully

✓Leaving every downstream user to parse raw JSON repeatedly.
✓Casting without checking missing keys, bad dates, or unexpected data types.
✓Flattening arrays without preserving the parent record key.
✓Using SELECT * on wide VARIANT-heavy tables for dashboards.
✓Assuming JSON field names are stable just because today’s sample has them.

How to debug this topic

Start by asking what promise failed: freshness, correctness, access, speed, or cost. Then inspect the Snowflake evidence: query history, warehouse metering, task history, copy history, grants, row counts, and sample records. Good Snowflake debugging is not guessing. It is reading the platform metadata until the failure has a shape.

// Part 05 — Production depth

Production Notes

✓Keep raw payloads for replay, but model typed curated tables for analytics.
✓Document JSON contracts with examples and owner names.
✓Monitor null rates after casts; schema drift often appears as sudden nulls.
✓Use TRY_TO_* functions when dirty input should be captured instead of failing the whole query.

Production standard: A Snowflake design is not complete when the query returns rows. It is complete when the team knows who owns it, how fresh it should be, how access is controlled, what it costs, how to detect failure, and how to recover safely.

// Part 06 — Interview and project readiness

Explain It Like a Professional

Explain that Snowflake supports semi-structured data through VARIANT, OBJECT, and ARRAY. Raw JSON can be loaded first, then accessed using path notation and converted into typed columns. Arrays are expanded with LATERAL FLATTEN. Good designs keep raw payloads but publish typed curated tables for users.

Mini project

Load order API JSON with customer, shipping address, promotions, and line_items. Build ORDERS_SILVER with typed order fields and ORDER_ITEMS_SILVER by flattening line_items. Add rejected-record handling for missing order_id or invalid totals.

Questions you should answer out loud

✓How would you explain Semi-Structured Data: VARIANT, JSON, FLATTEN to a non-technical manager?
✓Which Snowflake objects, roles, or SQL statements does this topic use?
✓What can fail in production and which metadata view would you inspect first?
✓What is the cost or security risk if this is implemented carelessly?
✓How would you test that the result is correct and rerunnable?

🎯 Key Takeaways

✓VARIANT lets Snowflake ingest flexible JSON safely.
✓Curated analytics should not depend on raw JSON parsing forever.
✓LATERAL FLATTEN turns nested arrays into rows.
✓Casting and validation are where raw data becomes trustworthy.
✓Schema drift must be monitored, not wished away.

Loading Data with Stages and COPY INTO

ELT and Medallion Architecture in Snowflake

Discussion

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub