Semi-Structured Data: VARIANT, JSON, FLATTEN

Load and query JSON in Snowflake with VARIANT, OBJECT, ARRAY, path notation, casting, LATERAL FLATTEN, schema drift handling, and production modeling patterns.

Est. 70 min Updated Suggest a fix

Quick answer

Snowflake stores JSON in VARIANT columns. You read nested fields with path notation (payload:customer.city), cast them to real types with ::, and turn arrays into rows with LATERAL FLATTEN.

  • Cast extracted values: payload:total::NUMBER(10,2).
  • FLATTEN gives one row per array element.
  • Curated tables should hold typed columns, not raw VARIANT.
Query nested JSON and flatten its items
SELECT e.payload:order_id::NUMBER        AS order_id,
       e.payload:customer.city::STRING   AS city,
       i.value:sku::STRING               AS sku,
       i.value:qty::NUMBER               AS qty
FROM raw.order_events AS e,
     LATERAL FLATTEN(input => e.payload:items) AS i;
// Part 01 — The plain-English idea

Snowflake Can Store Messy JSON Before You Fully Model It

Real business data does not always arrive as clean rows and columns. APIs send nested JSON. Product applications emit flexible event payloads. Payment processors add optional fields. Marketing tools return arrays of campaign attributes. A partner may add a new field without telling your team first. Semi-structured data is the name for this kind of flexible, nested, not-perfectly-tabular data.

Snowflake handles semi-structured data with data types such as VARIANT, OBJECT, and ARRAY. The most important one is VARIANT. A VARIANT column can hold JSON-like values: objects, arrays, strings, numbers, booleans, and nulls. This lets you land raw payloads safely first, then extract stable fields later into clean relational tables.

Plain-English definition: VARIANT is Snowflake's flexible container for JSON-like data. It is excellent for raw ingestion and exploration, but production analytics should usually expose typed columns in Silver or Gold tables.

Everyday analogy
Imagine receiving customer forms where some people fill out apartment number, some include company name, some include multiple phone numbers, and some leave optional fields blank. VARIANT lets you keep the whole form without throwing away fields just because they do not fit your final table yet.
// Part 02 — Data types

VARIANT, OBJECT, and ARRAY

Snowflake uses normal relational types such as STRING, NUMBER, BOOLEAN, DATE, and TIMESTAMP. Semi-structured data adds flexible types. You do not need to memorize every internal detail first. Start by learning what kind of JSON shape each type represents.

TypeWhat it storesJSON exampleHow you usually use it
VARIANTAny semi-structured value.{"order_id":"O-100","total":42.50}Raw API payloads, event bodies, flexible source records.
OBJECTKey-value object.{"city":"Austin","state":"TX"}Nested records such as address, customer, metadata.
ARRAYOrdered list of values.[{"sku":"A1"},{"sku":"B2"}]Line items, tags, events, product attributes.
Create a raw JSON landing table
CREATE OR REPLACE TABLE RAW.ORDER_EVENTS (
  payload VARIANT,
  source_file STRING,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO RAW.ORDER_EVENTS (payload, source_file)
SELECT
  PARSE_JSON('{
    "event_id": "evt_1001",
    "event_type": "order_created",
    "order": {
      "order_id": "O-1001",
      "customer_id": "C-42",
      "total_usd": 149.99
    },
    "line_items": [
      {"sku": "SKU-1", "quantity": 2, "price_usd": 49.99},
      {"sku": "SKU-2", "quantity": 1, "price_usd": 50.01}
    ]
  }'),
  'manual_insert.json';
Raw does not mean trusted
Landing JSON in a VARIANT column preserves the source payload, but it does not prove the payload is valid for analytics. You still need typing, validation, deduplication, and modeling.
// Part 03 — Path notation

Reading Fields From JSON

Snowflake lets you read nested fields using path notation. The colon moves from a VARIANT value into a key. Dot-style or bracket-style navigation moves deeper. After extracting a value, cast it into a normal SQL type so joins, filters, dashboards, and tests behave predictably.

Extract fields with path notation
SELECT
  payload:event_id::STRING AS event_id,
  payload:event_type::STRING AS event_type,
  payload:order.order_id::STRING AS order_id,
  payload:order.customer_id::STRING AS customer_id,
  payload:order.total_usd::NUMBER(12,2) AS total_usd,
  loaded_at
FROM RAW.ORDER_EVENTS;
ExpressionMeaningResult type before castProduction note
payload:event_idRead top-level event_id.VARIANTCast to STRING for stable use.
payload:order.total_usdRead nested order total.VARIANTCast to NUMBER for sums and comparisons.
payload:line_items[0]:skuRead first item sku.VARIANTGood for inspection, not for modeling all items.
payload:missing_keyRead absent key.NULL-like valueMissing fields should be profiled and tested.

Important habit: do not leave important business columns as uncast VARIANT in curated models. A value that looks like a number inside JSON should become NUMBER. A timestamp should become TIMESTAMP_NTZ, TIMESTAMP_LTZ, or TIMESTAMP_TZ based on the business requirement.

// Part 04 — Casting safely

Use TRY_TO_* Functions When Source Data Can Be Dirty

Source JSON often contains surprises: empty strings where a number should be, timestamps in a new format, nulls inside required fields, or a nested object where a string used to be. Normal casts are useful when you want the query to fail loudly. TRY_TO_* functions are useful when you want bad values to become NULL so you can capture them in quality checks.

Safe casting pattern
CREATE OR REPLACE TABLE SILVER.ORDER_EVENTS AS
SELECT
  payload:event_id::STRING AS event_id,
  payload:event_type::STRING AS event_type,
  payload:order.order_id::STRING AS order_id,
  payload:order.customer_id::STRING AS customer_id,
  TRY_TO_NUMBER(payload:order.total_usd::STRING, 12, 2) AS total_usd,
  TRY_TO_TIMESTAMP_NTZ(payload:occurred_at::STRING) AS occurred_at,
  payload AS raw_payload,
  source_file,
  loaded_at
FROM RAW.ORDER_EVENTS;
FunctionUse it forWhy it helps
TRY_TO_NUMBERDirty numeric strings.Bad values become NULL instead of failing the whole transformation.
TRY_TO_TIMESTAMP_NTZTimestamp text without timezone semantics.Keeps invalid timestamps visible through null checks.
TRY_TO_DATEDate strings.Useful for partition/date dimensions from messy sources.
IS_NULL_VALUEJSON null detection.Distinguishes JSON null from some SQL null situations during profiling.
Do not hide bad data forever
TRY_TO_* is not permission to ignore bad input. It lets the pipeline continue while you count, alert, and quarantine invalid records. If bad values become NULL silently, dashboards can lie.
// Part 05 — FLATTEN

LATERAL FLATTEN Turns Arrays Into Rows

JSON arrays are common. An order has line items. A user has multiple devices. A product has many attributes. A single event may include multiple experiments. Analytics usually needs those children as rows, not trapped inside one nested array. Snowflake's FLATTEN table function expands arrays and objects.

Flatten order line items
SELECT
  payload:order.order_id::STRING AS order_id,
  item.index AS line_item_index,
  item.value:sku::STRING AS sku,
  item.value:quantity::NUMBER AS quantity,
  item.value:price_usd::NUMBER(12,2) AS price_usd,
  item.value:quantity::NUMBER * item.value:price_usd::NUMBER(12,2) AS line_total_usd
FROM RAW.ORDER_EVENTS,
LATERAL FLATTEN(input => payload:line_items) item;
FLATTEN outputMeaningWhy useful
VALUEThe array element or object value.Where most nested fields are extracted from.
INDEXArray element position.Keeps original order when it matters.
KEYObject key when flattening objects.Useful for dynamic attribute maps.
PATHLocation in the nested document.Useful for debugging complex JSON.
THISCurrent element being flattened.Helpful for recursive or deeper inspection.
Preserve the parent key
When flattening child records, always carry the parent identifier such as order_id, event_id, customer_id, or session_id. Without it, the child rows lose their meaning.
// Part 06 — Modeling pattern

Raw JSON Should Become Typed Silver Tables

A common production pattern is to keep raw JSON for replay and debugging, then publish typed Silver tables for regular use. Analysts should not need to remember JSON paths for every dashboard. BI tools should not repeatedly parse nested payloads if a curated table can expose reliable columns.

Raw to Silver parent and child tables
CREATE OR REPLACE TABLE SILVER.ORDERS AS
SELECT
  payload:order.order_id::STRING AS order_id,
  payload:order.customer_id::STRING AS customer_id,
  payload:event_id::STRING AS source_event_id,
  TRY_TO_NUMBER(payload:order.total_usd::STRING, 12, 2) AS total_usd,
  TRY_TO_TIMESTAMP_NTZ(payload:occurred_at::STRING) AS order_ts,
  source_file,
  loaded_at
FROM RAW.ORDER_EVENTS
WHERE payload:event_type::STRING = 'order_created'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY payload:order.order_id::STRING
  ORDER BY loaded_at DESC
) = 1;

CREATE OR REPLACE TABLE SILVER.ORDER_ITEMS AS
SELECT
  payload:order.order_id::STRING AS order_id,
  item.index AS line_number,
  item.value:sku::STRING AS sku,
  item.value:quantity::NUMBER AS quantity,
  item.value:price_usd::NUMBER(12,2) AS price_usd,
  loaded_at
FROM RAW.ORDER_EVENTS,
LATERAL FLATTEN(input => payload:line_items) item
WHERE payload:event_type::STRING = 'order_created';
LayerWhat it storesWho should use it
RAW.ORDER_EVENTSFull source JSON payload plus metadata.Engineers debugging loads and replaying data.
SILVER.ORDERSOne row per order with typed columns.Analytics engineers, data quality checks, downstream marts.
SILVER.ORDER_ITEMSOne row per order line item.Revenue, product, inventory, and fulfillment models.
GOLD.ORDER_REVENUEBusiness-ready metrics.Analysts, BI dashboards, finance stakeholders.
// Part 07 — Schema drift

Schema Drift Is Normal, So Detect It Early

Schema drift means the incoming payload shape changes over time. A new key appears. A key disappears. A field changes from number to string. An array becomes an object. In a source system, that may be a small release. In your warehouse, it can break dashboards or quietly turn metrics into NULL.

Profile keys and null rates
-- Which top-level keys exist?
SELECT DISTINCT key
FROM RAW.ORDER_EVENTS,
LATERAL FLATTEN(input => OBJECT_KEYS(payload)) keys;

-- Are important extracted fields going null?
SELECT
  COUNT(*) AS rows_checked,
  COUNT_IF(payload:order.order_id IS NULL) AS missing_order_id,
  COUNT_IF(TRY_TO_NUMBER(payload:order.total_usd::STRING, 12, 2) IS NULL) AS invalid_total,
  COUNT_IF(payload:line_items IS NULL) AS missing_line_items
FROM RAW.ORDER_EVENTS
WHERE loaded_at >= DATEADD(day, -1, CURRENT_TIMESTAMP());
Drift typeExampleImpactResponse
New optional fieldcoupon_code appears.Usually safe; may unlock new analysis.Add to Silver when business wants it.
Missing required fieldorder_id disappears.Cannot identify records reliably.Quarantine or fail quality check.
Type changetotal_usd becomes "149.99 USD".Casts fail or become NULL.Alert source owner and update parsing only if contract changes.
Shape changeline_items array becomes object.FLATTEN logic breaks.Version payload handling or request source fix.
Contracts beat surprises
For important feeds, write a source contract: required fields, optional fields, types, example payloads, owner, expected freshness, and change process. VARIANT makes ingestion flexible, but it does not replace communication.
// Part 08 — Performance

Semi-Structured Queries Can Be Expensive If You Use Them Carelessly

Snowflake is good at semi-structured data, but that does not mean every dashboard should scan and parse giant JSON payloads all day. Repeatedly extracting the same fields from raw payloads can waste compute. Wide SELECT * queries over raw event tables can scan far more data than the user needs.

  • ✓Extract frequently used fields into typed columns in Silver tables.
  • ✓Avoid SELECT * from raw JSON-heavy tables in BI dashboards.
  • ✓Filter on typed date/timestamp columns when possible.
  • ✓Keep raw payloads for replay, but do not make every consumer parse them.
  • ✓Use Query Profile to inspect bytes scanned and expensive expressions.
Better dashboard input
-- Weaker dashboard pattern:
SELECT
  payload:order.customer_id::STRING,
  payload:order.total_usd::NUMBER(12,2)
FROM RAW.ORDER_EVENTS
WHERE payload:event_type::STRING = 'order_created';

-- Better dashboard pattern:
SELECT customer_id, total_usd
FROM SILVER.ORDERS
WHERE order_ts >= '2026-09-01'
  AND order_ts <  '2026-10-01';
// Part 09 — Loading JSON files

Loading JSON Files Into VARIANT

In production, JSON usually arrives in files or through a connector, not by hand-written PARSE_JSON inserts. Snowflake commonly loads JSON from an internal or external stage using a file format and COPY INTO. The safest raw-load pattern captures the payload plus metadata about where the record came from.

Create JSON file format, stage, and raw table
CREATE OR REPLACE FILE FORMAT JSON_EVENTS_FORMAT
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE
  IGNORE_UTF8_ERRORS = FALSE;

CREATE OR REPLACE STAGE ORDER_EVENTS_STAGE
  FILE_FORMAT = JSON_EVENTS_FORMAT;

CREATE OR REPLACE TABLE RAW.ORDER_EVENTS (
  payload VARIANT,
  source_file STRING,
  source_row_number NUMBER,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

COPY INTO RAW.ORDER_EVENTS (payload, source_file, source_row_number)
FROM (
  SELECT
    $1,
    METADATA$FILENAME,
    METADATA$FILE_ROW_NUMBER
  FROM @ORDER_EVENTS_STAGE
)
ON_ERROR = 'CONTINUE';
Option or metadataMeaningProduction use
TYPE = JSONTells Snowflake files contain JSON.Required for JSON file parsing.
STRIP_OUTER_ARRAYLoads array elements as separate rows when file is a JSON array.Useful when one file contains [event1, event2, ...].
METADATA$FILENAMEName/path of loaded file.Debug duplicates, bad files, and lineage.
METADATA$FILE_ROW_NUMBERRow number from staged file.Locate bad records inside files.
ON_ERRORHow COPY handles bad rows.Choose deliberately; continuing without audit can hide failures.
Raw load standard
Always keep enough metadata to answer: which file did this record come from, when did we load it, and how would we reload or quarantine it if the payload was wrong?
// Part 10 — COPY validation and bad files

Validate JSON Loads Before Trusting Them

COPY INTO can load data, but loading is not the same as correctness. A file can load successfully while important fields are missing. A file can partially load if you allow row-level errors. A file can be loaded twice if you bypass Snowflake's normal load tracking or change file names. Validation turns a file arrival into a reliable pipeline step.

Validation-oriented loading
-- Inspect whether files are loadable before the actual load.
COPY INTO RAW.ORDER_EVENTS (payload, source_file, source_row_number)
FROM (
  SELECT $1, METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
  FROM @ORDER_EVENTS_STAGE
)
VALIDATION_MODE = RETURN_ERRORS;

-- After loading, check required fields.
SELECT
  source_file,
  COUNT(*) AS rows_loaded,
  COUNT_IF(payload:event_id IS NULL) AS missing_event_id,
  COUNT_IF(payload:order.order_id IS NULL) AS missing_order_id,
  COUNT_IF(payload:line_items IS NULL) AS missing_line_items
FROM RAW.ORDER_EVENTS
WHERE loaded_at >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
GROUP BY source_file
ORDER BY source_file;
Failure typeExampleDetectionResponse
Invalid JSONMalformed braces or quotes.COPY validation errors.Reject file and notify producer.
Missing required keyNo order_id.Post-load quality query.Quarantine rows or fail downstream model.
Unexpected typetotal_usd is an object.TRY_TO_NUMBER null rate spike.Stop publishing Gold metrics until fixed.
Duplicate fileSame payload delivered under new path.Event id uniqueness checks.Deduplicate by source event id or business key.
// Part 11 — Object maps and dynamic keys

Handling Dynamic Object Keys

Sometimes JSON contains an object where the keys themselves are meaningful. For example, a payload might include arbitrary feature flags, product attributes, experiment assignments, or partner-specific fields. These are not arrays, but you can still flatten the object to inspect keys and values.

Flatten a dynamic attributes object
-- Example payload shape:
-- {
--   "product_id": "P-100",
--   "attributes": {
--     "color": "black",
--     "size": "M",
--     "material": "cotton"
--   }
-- }

SELECT
  payload:product_id::STRING AS product_id,
  attr.key::STRING AS attribute_name,
  attr.value::STRING AS attribute_value
FROM RAW.PRODUCT_EVENTS,
LATERAL FLATTEN(input => payload:attributes) attr;
Modeling optionShapeGood forTradeoff
Wide columnsproduct_id, color, size, material.Stable, frequently queried attributes.Schema changes when attributes change.
Key-value tableproduct_id, attribute_name, attribute_value.Dynamic attributes and exploration.Harder for BI and typed metrics.
Keep VARIANTpayload attributes remain nested.Raw replay and rare fields.Repeated parsing and weaker contracts.
Choose based on usage
If a field drives dashboards, joins, filters, or metrics, promote it to a typed column. If it is rare and exploratory, a key-value table or raw VARIANT may be enough.
// Part 12 — Recursive flattening

Recursive FLATTEN Helps Explore Unknown Nested Payloads

When you receive a new payload and do not know its shape, recursive flattening can help you inspect nested keys. This is an exploration tool, not usually the final production model. It helps you map what exists before deciding which fields should become typed columns.

Explore nested JSON paths
SELECT
  f.path,
  TYPEOF(f.value) AS value_type,
  COUNT(*) AS occurrences,
  MIN(f.value::STRING) AS example_value
FROM RAW.ORDER_EVENTS,
LATERAL FLATTEN(input => payload, recursive => TRUE) f
GROUP BY f.path, TYPEOF(f.value)
ORDER BY occurrences DESC, f.path;
Use recursive flatten whenAvoid it when
You are profiling a new API payload.A dashboard needs predictable fast queries.
You need to discover nested paths and types.A typed Silver model already exists.
You are investigating schema drift.You need clean business metrics.
You are writing documentation for source contracts.You are processing huge payloads repeatedly without filters.
// Part 13 — Versioned payloads

Version Your Event Payloads When the Meaning Changes

JSON flexibility is helpful, but it can become dangerous when the same field name changes meaning. If total used to mean order total before discounts and later means after discounts, old and new records cannot be interpreted the same way. Payload versioning makes semantic changes explicit.

Version-aware extraction
SELECT
  payload:event_id::STRING AS event_id,
  payload:schema_version::NUMBER AS schema_version,
  CASE payload:schema_version::NUMBER
    WHEN 1 THEN payload:order.total_before_discount::NUMBER(12,2)
    WHEN 2 THEN payload:order.subtotal_usd::NUMBER(12,2)
  END AS subtotal_usd,
  CASE payload:schema_version::NUMBER
    WHEN 1 THEN payload:order.discount_usd::NUMBER(12,2)
    WHEN 2 THEN payload:order.discount.amount_usd::NUMBER(12,2)
  END AS discount_usd
FROM RAW.ORDER_EVENTS;
  • ✓Add schema_version or event_version to important event payloads.
  • ✓Document what changed between versions.
  • ✓Keep extraction logic explicit instead of hoping one path works forever.
  • ✓Test old and new payload examples in CI or dbt tests.
  • ✓Deprecate old versions with dates and owner approval.
// Part 14 — Deduplication with event ids

Raw JSON Pipelines Need Duplicate Protection

Event and API systems often send duplicates. A producer retries. A file is uploaded twice. A backfill overlaps with a daily feed. JSON flexibility does not solve this. You need stable event identifiers and deduplication rules before data reaches business metrics.

Deduplicate event payloads
CREATE OR REPLACE TABLE SILVER.ORDER_EVENTS_DEDUPED AS
SELECT
  payload:event_id::STRING AS event_id,
  payload:event_type::STRING AS event_type,
  payload:order.order_id::STRING AS order_id,
  payload AS raw_payload,
  source_file,
  loaded_at
FROM RAW.ORDER_EVENTS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY payload:event_id::STRING
  ORDER BY loaded_at DESC, source_file DESC
) = 1;
Key choiceGood whenDanger
event_idEach emitted event has a stable unique id.Missing event_id makes dedupe unreliable.
business keyOnly latest state matters, such as one row per order.May collapse legitimate multiple events.
source file + row numberFile lineage/debugging.Does not dedupe same event in different files.
hash of payloadNo identifier exists and exact duplicate payloads occur.Small payload changes create different hashes.
// Part 15 — Testing semi-structured models

Quality Tests Make JSON Useful Instead of Just Flexible

A semi-structured pipeline needs tests at multiple levels: can files load, do required keys exist, do casts succeed, do flattened children have parents, do duplicates stay controlled, and do sensitive fields stay out of broad Gold models?

Quality gate queries
-- Required keys
SELECT COUNT(*) AS bad_rows
FROM RAW.ORDER_EVENTS
WHERE payload:event_id IS NULL
   OR payload:order.order_id IS NULL;

-- Invalid totals after casting
SELECT COUNT(*) AS invalid_totals
FROM RAW.ORDER_EVENTS
WHERE payload:order.total_usd IS NOT NULL
  AND TRY_TO_NUMBER(payload:order.total_usd::STRING, 12, 2) IS NULL;

-- Child rows without a parent order
SELECT i.order_id, COUNT(*) AS orphan_items
FROM SILVER.ORDER_ITEMS i
LEFT JOIN SILVER.ORDERS o
  ON i.order_id = o.order_id
WHERE o.order_id IS NULL
GROUP BY i.order_id;

-- Duplicate event ids
SELECT event_id, COUNT(*) AS duplicate_count
FROM SILVER.ORDER_EVENTS_DEDUPED
GROUP BY event_id
HAVING COUNT(*) > 1;
TestWhat it protectsTypical severity
event_id not nullLineage and dedupe.High.
order_id not nullBusiness key integrity.High.
valid total_usdRevenue metrics.High.
line item parent existsJoin correctness.Medium to high.
no raw PII in GoldGovernance boundary.High.
// Part 16 — Troubleshooting playbook

When a JSON Pipeline Breaks, Debug in This Order

Semi-structured failures can look mysterious because the payload is flexible. Make the debugging process boring and repeatable. Start at the file/load layer, then raw payload shape, then extraction logic, then modeled tables, then dashboards.

StepQuestionSnowflake check
1Did the file arrive?List the stage or inspect external storage notification logs.
2Did COPY load it?COPY_HISTORY, load audit table, source_file counts.
3Is the JSON valid?VALIDATION_MODE = RETURN_ERRORS.
4Did required paths exist?COUNT_IF(payload:path IS NULL).
5Did casts fail?TRY_TO_* null-rate checks.
6Did FLATTEN multiply or lose rows?Parent counts versus child counts.
7Did Gold metrics change?Compare before/after aggregates and row counts.
Incident notes template
Incident: daily product revenue dropped to zero

Check:
  - Were order event files delivered?
  - Did RAW.ORDER_EVENTS receive rows today?
  - Did payload:event_type still equal 'order_created'?
  - Did payload:line_items remain an array?
  - Did TRY_TO_NUMBER(price_usd) start returning NULL?
  - Did a new schema_version require different paths?

Resolution:
  - Quarantine bad files or update parser with version-aware logic.
  - Backfill Silver and Gold after fix.
  - Add a test that catches this exact failure next time.
// Part 17 — Governance

Raw JSON Can Hide Sensitive Data

Semi-structured payloads often contain more than teams expect: emails, phone numbers, addresses, device identifiers, IP addresses, payment metadata, or free-text notes. A curated table may mask email, while the raw VARIANT payload still exposes the original value. Security reviews must include raw data, not only modeled columns.

RiskWhere it appearsControl
PII hidden in raw payload.payload:customer.email or metadata fields.Limit Raw access; create masked curated views.
Free-text sensitive data.support_note, comment, custom_attributes.Classify, tokenize, or exclude from broad marts.
Unexpected partner fields.New keys added without review.Schema drift monitoring and access reviews.
Over-broad analyst role.SELECT on RAW event tables.Prefer Gold/Silver views for most users.
Security rule
If a sensitive value exists anywhere in the raw JSON, granting access to the raw table grants access to that value unless a separate control prevents it. Curated masking does not protect raw payloads automatically.
// Part 18 — Mistakes

Common Mistakes With JSON in Snowflake

Avoid these
  • ✓Leaving all downstream analytics to parse raw JSON forever.
  • ✓Flattening arrays without preserving parent identifiers.
  • ✓Casting dirty values without checking null rates afterward.
  • ✓Using raw VARIANT tables directly in high-traffic dashboards.
  • ✓Assuming optional JSON keys are always present because a sample file had them.
  • ✓Exposing raw payloads to broad roles even though they contain PII.
  • ✓Treating schema drift as a rare edge case instead of normal source-system behavior.
  • ✓Not storing load metadata such as source file, loaded_at, batch id, or source system.
// Part 19 — Mini project

Build a JSON Orders Model From Raw to Gold

A strong hands-on exercise is to model order-created events. Start with one raw payload table. Build one parent order table, one child line-item table, and one Gold metric table. Add quality checks for required fields and invalid numbers.

Project target model
Input:
  RAW.ORDER_EVENTS(payload VARIANT, source_file STRING, loaded_at TIMESTAMP_NTZ)

Silver outputs:
  SILVER.ORDERS
    order_id STRING
    customer_id STRING
    order_ts TIMESTAMP_NTZ
    total_usd NUMBER(12,2)
    source_event_id STRING

  SILVER.ORDER_ITEMS
    order_id STRING
    line_number NUMBER
    sku STRING
    quantity NUMBER
    price_usd NUMBER(12,2)

Gold output:
  GOLD.DAILY_PRODUCT_REVENUE
    order_date DATE
    sku STRING
    units_sold NUMBER
    revenue_usd NUMBER(12,2)

Quality checks:
  order_id is not null
  total_usd is not null and >= 0
  quantity is not null and > 0
  every order item has a parent order

What this project teaches

  • ✓Why raw JSON and curated relational tables both matter.
  • ✓How VARIANT path notation becomes typed columns.
  • ✓How FLATTEN turns nested arrays into child tables.
  • ✓How schema drift and dirty values show up as quality failures.
  • ✓Why Gold tables should hide JSON complexity from business users.
// Part 20 — Interview answer

How to Explain Semi-Structured Data in a Snowflake Interview

A strong answer sounds like this: Snowflake supports semi-structured data through VARIANT, OBJECT, and ARRAY. I would land raw JSON payloads into a VARIANT column with load metadata, inspect and profile the structure, extract stable fields using path notation, cast them into typed columns in Silver models, and use LATERAL FLATTEN for nested arrays such as line items. I would keep the raw payload for replay and debugging, but expose typed curated tables to analysts. In production, I would monitor schema drift, null rates, invalid casts, cost, and sensitive fields hidden in raw payloads.

Questions you should answer out loud

  • ✓What is VARIANT and when should you use it?
  • ✓How do you extract nested JSON fields in Snowflake?
  • ✓Why should important JSON values be cast into normal SQL types?
  • ✓How does LATERAL FLATTEN work?
  • ✓What is schema drift and how would you detect it?
  • ✓Why can raw JSON be a security risk?

🎯 Key Takeaways

  • ✓VARIANT lets Snowflake store flexible JSON-like payloads.
  • ✓Path notation extracts nested fields, but curated models should cast values into stable SQL types.
  • ✓LATERAL FLATTEN expands arrays and objects into rows.
  • ✓Raw JSON is useful for replay and debugging; Silver and Gold tables should make analytics easier and safer.
  • ✓Schema drift, invalid casts, and hidden PII are production concerns, not edge cases.
  • ✓Semi-structured support is powerful when paired with modeling, quality checks, governance, and cost awareness.

Quick check

What does LATERAL FLATTEN do?

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

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

Continue with GitHub
Loading...