Semi-Structured Data at Scale: JSON/VARIANT, Nested & Repeated Fields Across Dialects

Semi-structured data is the JSON payload, the event blob, the nested API response — data that carries its own keys and shapes instead of fitting a fixed grid of columns, and that lands in your warehouse by the billion whether or not anyone modelled it first. The hard problem was never "store the JSON"; every modern engine will happily keep a blob. The problem is that a JSON document nests objects inside objects and repeats arrays inside rows, so the moment an analyst asks a flat question — sum revenue by region, count items per order — someone has to decide where the shape gets resolved: parse it live on every query, or shred it once on ingest into typed columns you can scan cheaply.

This guide is the senior-data-engineering walkthrough for handling semi-structured data at scale — framed the way interviewers actually probe it: what schema-on-read buys you and what it costs, when to shred a payload into columns versus keep it raw, and how the same JSON is typed and queried across four dialects. It covers JSON/VARIANT access in Snowflake, BigQuery, Postgres, and Spark; nested fields — the typed struct you reach with dot access and evolve as new keys appear; repeated fields — the array you flatten with UNNEST, FLATTEN, jsonb_array_elements, or explode without triggering a fan-out bug; and the patterns that keep it fast — shredding hot keys, indexing JSONB, and pruning the bytes each query scans. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the JSON practice library →, rehearse flatten and reshape moves on the data transformation practice library →, and sharpen the modelling axis with the system design practice library →.

---

On this page

• Why semi-structured data at scale is its own problem
• JSON/VARIANT across dialects
• Nested (struct) fields — dot access, typing, evolution
• Repeated (array) fields — UNNEST, FLATTEN, LATERAL, explode
• Patterns at scale — schema evolution, shredding, indexing, cost
• Cheat sheet — semi-structured data recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why semi-structured data at scale is its own problem

The shape gap — a table has fixed columns; a JSON payload nests objects and repeats arrays

The one-sentence invariant: semi-structured data is data that carries its own schema inline — keys, nested objects, and repeated arrays inside each record — instead of conforming to a fixed set of typed columns, and the entire engineering problem is deciding where the shape is resolved: schema-on-read parses and casts the payload on every query (flexible, but you pay per read and per engine), while schema-on-write shreds it once on ingest into typed columns (cheap to scan, but rigid), so at scale every design reduces to which keys are hot enough to promote to columns and which stay in the raw blob for the long tail. Store a billion JSON events untouched and every dashboard re-parses them forever; shred all of them eagerly and you break the moment a producer adds a key — the craft is choosing per key, not per table.

The four axes interviewers actually probe.

• Typing. Is the value typed or does it arrive untyped? A warehouse VARIANT/JSON column holds anything and defers typing to read time; a struct column declares a type per field. The senior answer names when the cast happens — at ingest into a typed column, or at every read of a raw blob — because that is what decides both correctness (bad-cast handling) and cost.
• Access path. How do you reach a nested value? Every dialect spells it differently — a colon path, a dot, a JSON function, an UNNEST. The senior answer knows the payload's shape (scalar, object, array) dictates the operator, not the engine's marketing.
• Evolution. What happens when a producer adds, renames, or removes a key? Schema-on-read tolerates additive keys silently; a rigid typed column needs a migration. The senior answer designs for additive-only change and tolerant readers.
• Cost. What does a query actually scan? Reading one shredded column touches a few bytes per row; re-parsing the whole blob to pull one field touches the entire payload. The senior answer ties access frequency to storage layout — shred the hot keys, keep the cold tail raw.

Schema-on-read vs schema-on-write — where the parse cost lives.

• Schema-on-read. The raw payload is stored as-is; each query parses and casts the fields it needs. You gain flexibility (new keys need no migration) and lose per-query cost and consistency (every query re-implements the parse, and a bad value fails at read time).
• Schema-on-write. The payload is parsed and validated on ingest into typed columns. You gain cheap scans and enforced types, and lose flexibility (a new key is dropped or errors until you evolve the schema).
• The hybrid that wins at scale. Shred the hot keys — the ones most queries filter and aggregate on — into typed columns on ingest, and keep the whole raw payload in a VARIANT/JSONB column for the long tail of rare fields. Most queries hit cheap columns; the occasional deep question still has the raw data.

The shred-vs-keep-raw decision.

• Shred when. A key is queried often, needs a type/constraint, drives partitioning or clustering, or must be indexed — promote it to a column.
• Keep raw when. A key is rare, exploratory, high-cardinality-nested, or still churning shape — leave it in the blob so a new producer field never breaks ingest.
• Never. Force every possible key into a column (a 400-column table that breaks on the next payload change) or leave every query re-parsing multi-kilobyte blobs to read one scalar.

What interviewers listen for.

• Do you name the schema-on-read vs schema-on-write trade-off and say where the parse cost lives? — required answer.
• Do you propose a hybrid — shred hot keys, keep the raw payload — rather than an all-or-nothing? — senior signal.
• Do you tie the storage layout to access frequency and cost (bytes scanned), not to aesthetics? — senior signal.
• Do you flatten a repeated field while preserving the parent and the element order, and spot the fan-out risk? — required answer.
• Do you design for additive schema evolution with tolerant readers? — senior signal.

Worked example — the shred-vs-keep-raw decision table

Detailed explanation. The single most useful artifact for a semi-structured-data interview is a memorised mapping of key → storage decision. Every senior discussion converges on it: given a key's access pattern, do you shred it into a typed column, index it, or leave it in the raw blob? Walk through building the table for a stream of order events whose payload has a stable core and a churning tail.

• The payload. { order_id, region, total_cents, created_at, items: [...], experiments: {...}, device: {...} } — a stable core plus nested and volatile sections.
• The tension. Shredding everything is rigid and breaks on new keys; shredding nothing makes every query re-parse a multi-kilobyte blob.
• The rule. Promote a key to a typed column when it is hot, typed, filtered, or indexed; otherwise keep it raw.

Question. For each key, decide the storage layout — typed column, typed column + index, or raw blob — and justify it by access pattern.

Input.

Key	Access pattern	Decision
order_id, region, total_cents, created_at	filtered/aggregated on every query	shred to typed columns
region, created_at	drive filters + partitioning	shred + index/partition
items[]	flattened sometimes, per-item analysis	keep raw + flatten on demand
experiments, device	rare, exploratory, churning shape	keep raw in the payload blob

Code.

-- Hybrid layout: shred the HOT keys into typed columns, KEEP the raw payload.
-- Most queries read cheap typed columns; the long tail still has the full JSON.
CREATE TABLE events.orders_shredded (
    order_id      bigint       NOT NULL,     -- hot: joined/filtered everywhere
    region        text         NOT NULL,     -- hot: filter + partition key
    total_cents   bigint       NOT NULL,     -- hot: aggregated
    created_at    timestamptz  NOT NULL,     -- hot: partition + range filter
    payload       jsonb        NOT NULL      -- the WHOLE raw event, kept for the cold tail
);

-- Index the hot filter key so region lookups are index scans, not full scans.
CREATE INDEX ON events.orders_shredded (region, created_at);

-- The cold-tail question still works, straight off the raw payload:
SELECT payload->'device'->>'os' AS os, count(*)
FROM events.orders_shredded
WHERE created_at >= now() - interval '7 days'
GROUP BY 1;

Step-by-step explanation.

1. The four hot keys — order_id, region, total_cents, created_at — become real typed columns, so the queries that run thousands of times a day scan a few bytes per row and get type enforcement for free instead of re-parsing JSON.
2. The whole raw event is also kept in the payload jsonb column, so nothing is lost: the rare device.os breakdown still has its data even though device was never promoted to a column.
3. The index on (region, created_at) turns the most common filter into an index scan — the hot path never scans the table, and it never touches the payload blob at all.
4. The cold-tail query reads payload->'device'->>'os' directly: it pays the parse cost, but only on the rare occasion someone asks, and only for the 7-day slice the filter admits.
5. The mistake at both extremes: shred experiments/device into 40 columns and the next producer change breaks ingest; shred nothing and the daily region-revenue query re-parses kilobytes per row forever. The table is the antidote — layout follows access pattern, per key.

Output.

Access pattern	Right layout	Wrong layout (common mistake)
Hot filter/aggregate key	typed column (+ index)	leave in raw blob, re-parse every query
Partition/cluster driver	typed column	JSON path in the partition expression
Rare exploratory key	keep in raw payload	shred into a column that breaks on change
Churning-shape section	keep raw, tolerant reader	rigid struct that needs a migration per key

Rule of thumb. Decide storage per key, not per table: promote the hot, typed, filtered, indexed keys to columns and keep the whole raw payload for the long tail. The blob is your insurance against schema churn; the columns are your speed and cost control.

Worked example — what interviewers actually probe

Detailed explanation. The senior semi-structured interview has a predictable escalation: an ambiguous opener ("we get JSON events, model them"), then progressive narrowing to test whether you understand schema-on-read cost, flattening, and evolution. The candidates who name shred-vs-raw, the fan-out bug, and additive evolution score highest.

• Ambiguous opener. "Marketing sends nested JSON events. Land them in the warehouse."
• Follow-up 1. "The daily revenue query is slow and expensive. Why?" — probes schema-on-read cost / shredding.
• Follow-up 2. "Each event has an array of items. Revenue per item?" — probes flattening + fan-out.
• Follow-up 3. "Producers just added three keys. Did ingest break?" — probes schema evolution.
• Follow-up 4. "Snowflake, BigQuery, or Postgres — does the answer change?" — probes cross-dialect fluency.

Question. Draft a 5-minute senior answer that pre-empts all four follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Land the events	"dump JSON in one text column"	"raw blob + shred the hot keys to typed columns"
Slow revenue query	"add compute"	"stop re-parsing the blob; read a shredded column"
Per-item revenue	"loop in the app"	"flatten the array with UNNEST/FLATTEN, one row per item"
New keys added	"migrate the table"	"schema-on-read tolerates additive keys; blob absorbs them"
Which engine	"depends"	"same model; only the access syntax differs"

Code.

Senior semi-structured answer template (5 minutes)
==================================================

Minute 1 — name the shape gap and the hybrid
  "These are nested JSON events. I land the raw payload in a VARIANT/JSONB
   column AND shred the hot keys — region, total, timestamp — into typed
   columns on ingest. Cheap scans for the common queries, raw blob for the tail."

Minute 2 — schema-on-read cost
  "The slow revenue query re-parses the whole blob to read one field. I read
   the shredded typed column instead, so the scan touches bytes, not kilobytes,
   and prunes by the partition key."

Minute 3 — flattening a repeated field
  "Per-item revenue means flattening the items array: one row per element with
   UNNEST (BigQuery), LATERAL FLATTEN (Snowflake), jsonb_array_elements
   (Postgres), or explode (Spark) — keeping the parent order_id, and NOT
   flattening a second array in the same step or I fan out the rows."

Minute 4 — schema evolution
  "New keys don't break ingest: schema-on-read is additive-tolerant and the
   raw blob captures everything. I promote a new key to a column only when it
   becomes hot. Tolerant readers, additive-only changes."

Minute 5 — cross-dialect
  "The model is identical across engines; only the access syntax differs —
   colon paths in Snowflake, JSON_VALUE in BigQuery, ->> in Postgres,
   from_json/variant_get in Spark. I pick per key by access frequency and cost."

Step-by-step explanation.

1. Minute 1 frames the whole answer around the hybrid — raw blob plus shredded hot keys — which signals you understand the trade-off rather than defaulting to one extreme.
2. Minute 2 names where the cost lives: re-parsing a blob to read one field scans the entire payload, while a shredded column scans a few bytes and prunes by partition — the single most senior cost point.
3. Minute 3 pre-empts the array follow-up and, crucially, volunteers the fan-out risk before the interviewer sets the trap — flattening two arrays at once multiplies rows.
4. Minute 4 shows you design for change: additive keys are free under schema-on-read, the blob captures everything, and promotion to a column is a deliberate, later decision.
5. Minute 5 closes on cross-dialect fluency — same model, different syntax — which is the sentence that separates someone who has shipped on one warehouse from someone who has modelled semi-structured data everywhere.

Output.

Grading criterion	Weak score	Senior score
Names the shred-vs-raw hybrid	rare	mandatory
Locates the schema-on-read cost	occasional	mandatory
Flattens without fan-out	rare	senior signal
Designs additive evolution	rare	senior signal
Speaks the four dialects	rare	senior signal

Rule of thumb. The senior semi-structured answer is a 5-minute monologue covering the shape gap, the shred-vs-raw hybrid, the schema-on-read cost, flattening without fan-out, and additive evolution — without waiting for the follow-ups. Rehearse it once; deploy it every interview.

Worked example — schema-on-read vs schema-on-write for the same event

Detailed explanation. A common interview trap is "schema-on-read or schema-on-write for these events?" The weak answer picks one on principle. The senior answer picks per key and explains where each pays. Walk the same event through both models and show why the hybrid dominates.

• The schema-on-read read. Query time parses payload:total_cents::number on every request — flexible, but every query re-parses and a bad value fails late.
• The schema-on-write read. Ingest parsed total_cents into a bigint column — cheap and typed, but a malformed value fails ingest and a new key is dropped.
• The decision. Read-model the churning tail; write-model the hot, typed core.

Question. Contrast schema-on-read and schema-on-write for an order event on cost, type safety, and tolerance to new keys, and state which key goes where.

Input.

Dimension	Schema-on-read (raw blob)	Schema-on-write (typed column)
When parse happens	every query	once, at ingest
Per-query cost	high (re-parse blob)	low (scan column)
Type safety	at read (late failure)	at write (early failure)
New key added	absorbed silently	dropped/errors until migration
Best for	rare, churning keys	hot, stable, typed keys

Code.

-- SCHEMA-ON-READ: keep the raw event, parse per query. Flexible, re-parses every time.
SELECT
    payload:region::string        AS region,        -- Snowflake colon-path + cast
    payload:total_cents::number   AS total_cents,   -- parsed on EVERY query
    count(*)                      AS orders
FROM events.raw_orders                              -- payload is a VARIANT column
GROUP BY 1, 2;

-- SCHEMA-ON-WRITE: shred hot keys once, at ingest, into a typed table.
INSERT INTO events.orders_typed (region, total_cents, created_at, payload)
SELECT
    payload:region::string,
    payload:total_cents::number,      -- cast ONCE here; every later query is a plain scan
    payload:created_at::timestamp_ntz,
    payload                            -- still keep the raw blob for the cold tail
FROM events.raw_orders;

-- Later queries read TYPED COLUMNS — no JSON parsing, prunes by partition/cluster.
SELECT region, sum(total_cents) FROM events.orders_typed
WHERE created_at >= dateadd(day, -7, current_timestamp) GROUP BY 1;

Step-by-step explanation.

1. The schema-on-read query is maximally flexible — it needs no migration when a producer adds a key — but it parses payload:total_cents::number on every execution, so a hot dashboard pays the JSON parse cost repeatedly.
2. The schema-on-write path casts each hot key once at ingest into events.orders_typed, so every downstream query is a plain columnar scan with type enforcement and partition pruning — orders of magnitude cheaper at query time.
3. Critically, the write-model still keeps the raw payload, so it is not a lossy choice: the hot keys are fast columns and the rare keys remain reachable in the blob — the hybrid, not a binary.
4. Type safety moves earlier: a malformed total_cents fails at ingest (where you can quarantine and alert) rather than surfacing as a late, per-query cast error in a dashboard.
5. The senior framing is not "read vs write" but "read the tail, write the core": model the hot, typed, frequently-queried keys on write and leave the churning, rare keys on read — which is exactly what the hybrid table encodes.

Output.

Question	Schema-on-read	Schema-on-write
Add a new producer key	works instantly	needs a column (or stays in blob)
Cost of the daily revenue query	re-parse per query	scan a typed column
Where a bad value fails	at read (late)	at ingest (early)
Best assigned key	experiments, device	region, total_cents, created_at

Rule of thumb. Do not pick schema-on-read or schema-on-write for the whole table — pick per key. Write-model the hot, typed core into columns so common queries are cheap scans, and read-model the churning tail from the raw blob so new keys never break ingest.

Senior interview question on modelling semi-structured data at scale

A senior interviewer often opens with: "Marketing streams nested JSON order events — a stable core plus a churning tail of experiment and device keys, and each event has an array of line items. Land them in the warehouse so the daily revenue-by-region query is cheap, per-item analysis is possible, new producer keys don't break ingest, and the design is portable across Snowflake, BigQuery, and Postgres. Walk me through the storage layout, where the parse cost lives, and how you flatten the items."

Solution Using a hybrid shred-and-keep-raw layout, schema-on-read tail, and on-demand flatten

-- Step 1 — hybrid layout: typed hot columns + the WHOLE raw payload for the tail.
CREATE TABLE events.orders (
    order_id     bigint       NOT NULL,
    region       text         NOT NULL,     -- shredded hot key (filter + partition)
    total_cents  bigint       NOT NULL,     -- shredded hot key (aggregate)
    created_at   timestamptz  NOT NULL,     -- shredded hot key (partition + range)
    payload      jsonb        NOT NULL      -- raw event: items[], experiments{}, device{}
);
CREATE INDEX ON events.orders (region, created_at);       -- hot filter -> index scan
CREATE INDEX ON events.orders USING gin (payload);        -- cold-tail JSON containment

-- Step 2 — the HOT query never touches JSON: it scans typed columns and prunes.
SELECT region, sum(total_cents) AS revenue
FROM events.orders
WHERE created_at >= now() - interval '1 day'
GROUP BY region;

-- Step 3 — per-item analysis: flatten the items array ON DEMAND, keep the parent.
SELECT o.order_id,
       item->>'sku'                  AS sku,
       (item->>'qty')::int           AS qty
FROM events.orders o,
     jsonb_array_elements(o.payload->'items') AS item   -- one row per element
WHERE o.created_at >= now() - interval '1 day';

-- Step 4 — a new producer key does NOT break ingest; it is absorbed by the blob.
--          Promote it to a column later, only if it becomes hot.
SELECT payload->'device'->>'os' AS os, count(*)
FROM events.orders
WHERE payload @> '{"device":{"os":"iOS"}}'                -- GIN-indexed containment
GROUP BY 1;

Step-by-step trace.

Decision	Before (one JSON blob)	After (hybrid layout)
Daily revenue query	re-parse blob per row	scan typed columns, prune partition
Per-item analysis	app-side loop	flatten array, one row per item
New producer key	migration or breakage	absorbed by the raw payload
Cold-tail question	full scan	GIN-indexed containment
Portability	engine-specific parse	same model, dialect-specific syntax
Type safety	late, per query	early, on the shredded columns

After the rollout, the daily revenue-by-region query reads three typed columns and prunes by created_at, never parsing JSON; per-item analysis flattens payload->'items' on demand into one row per line item while keeping order_id; a new device.os key lands in the raw payload without touching ingest and is queryable via a GIN-indexed containment filter; and the whole design ports to Snowflake (VARIANT + LATERAL FLATTEN) or BigQuery (JSON/STRUCT + UNNEST) by swapping only the access syntax.

Output:

Metric	Before (raw blob only)	After (hybrid)
Daily revenue query cost	re-parse full payload/row	scan 3 typed columns
Per-item revenue	not possible in SQL	one flatten, one row/item
New-key ingest risk	breakage or migration	zero (blob absorbs it)
Cold-tail lookup	full table scan	GIN-indexed containment
Cross-engine portability	rewrite	swap syntax only

Why this works — concept by concept:

• Shred hot, keep raw — the frequently filtered and aggregated keys become typed columns so common queries are cheap scans, while the entire raw payload survives in a VARIANT/JSONB column so the churning tail is never lost. Speed for the core, flexibility for the tail.
• Schema-on-read tail — rare and volatile keys stay in the blob and are parsed only when queried, so a new producer field is absorbed silently instead of breaking ingest or demanding a migration.
• On-demand flatten — the repeated items array is expanded to one row per element only when a per-item question is asked, preserving the parent order_id and paying the flatten cost exactly when needed.
• Portable model — the layout is identical across engines; only the access syntax (colon path, JSON_VALUE, ->>, variant_get) and the flatten operator (FLATTEN, UNNEST, jsonb_array_elements, explode) change, so the design survives a warehouse migration.
• Cost — the hot path scans a handful of typed columns and prunes by partition, versus re-parsing a multi-kilobyte blob per row; the cold path pays JSON cost only on rare queries. The eliminated cost is a full-payload parse on every dashboard load — O(columns) scans instead of O(payload) parses per request.

Design
Topic — design
Design problems on modelling semi-structured data

Practice →

Data processing Topic — data-processing Data processing problems on JSON ingest and shredding

Practice →

---

2. JSON/VARIANT across dialects

One payload, four dialects — the model is the same; only the access syntax and the cast differ

The mental model in one line: every warehouse stores semi-structured data in a single flexible column — Snowflake's VARIANT, BigQuery's JSON, Postgres's JSONB, Spark's parsed struct or new VARIANT — and reading a value out of it is always the same three moves: navigate a path to the value, choose whether you want it as JSON or as a scalar, and cast it to a real type, so the only thing that changes across engines is the spelling (a colon v:key, a dot, a JSON_VALUE, a ->>, a variant_get) — get the path-and-cast pattern into muscle memory once and you can read any payload on any engine. The trap is confusing "extract as JSON" with "extract as scalar," which is why every dialect gives you two operators, not one.

Snowflake — VARIANT.

• The column. VARIANT holds any JSON value; PARSE_JSON(str) builds one from text, and semi-structured files load straight into it. Snowflake automatically sub-columnarises frequently accessed paths under the hood.
• The path. Colon navigates keys (v:region), dot and brackets go deeper (v:address.city, v:items[0]). The result is still VARIANT until you cast.
• The cast. :: coerces to a real type: v:total_cents::number, v:region::string. Without the cast you get a JSON value (a quoted string), not a SQL scalar.
• The functions. GET, GET_PATH(v, 'a.b'), TRY_CAST/:: for safe typing, and IS_.../TYPEOF to inspect a value's JSON type.

BigQuery — JSON (and native STRUCT/ARRAY).

• The column. The native JSON type stores a parsed document; you can also model nested/repeated data as first-class STRUCT and ARRAY columns.
• The path. Dot and bracket access on a JSON value (j.address.city, j.items[0]), or the functions JSON_VALUE (scalar → STRING) and JSON_QUERY (a JSON subtree).
• The cast. JSON_VALUE returns a STRING you cast (CAST(JSON_VALUE(j,'$.total_cents') AS INT64)), or use typed accessors INT64(j.total_cents), STRING(j.region), FLOAT64(...).
• Legacy note. JSON_EXTRACT/JSON_EXTRACT_SCALAR are the older string-based functions; prefer JSON_VALUE/JSON_QUERY and the native JSON type.

Postgres — JSONB.

• The column. JSONB stores a decomposed, indexed binary JSON; JSON (text) preserves formatting but is slower — use JSONB for analytics.
• The path. -> returns a jsonb child (keep navigating), ->> returns text (the scalar); #>/#>> take a path array ('{address,city}').
• The cast. Chain a cast onto the ->> text: (j->>'total_cents')::int, (j->>'created_at')::timestamptz.
• The power tools. Containment @> (j @> '{"region":"EU"}'), existence ?, and jsonb_path_query for JSONPath — all GIN-indexable.

Spark / Databricks.

• Parsed struct. from_json(col, schema) turns a JSON string into a typed struct/array you access with dots (e.address.city); schema_of_json infers a schema; to_json serialises back.
• Path functions. get_json_object(col, '$.region') and json_tuple(col, 'a', 'b') pull fields from a JSON string without a full parse.
• VARIANT. Databricks/Spark now offer a VARIANT type: parse_json(str) to build it, variant_get(v, '$.region', 'string') to extract-and-cast, and the colon accessor v:region::string in Databricks SQL.

The failure modes senior engineers pre-empt.

• String vs typed extraction. Using the "as JSON" operator where you meant "as scalar" yields a quoted string ("EU" with quotes) that breaks joins and comparisons. Mitigation: ->>/JSON_VALUE/::type for scalars; reserve ->/JSON_QUERY for subtrees.
• Silent NULL on a bad cast. A malformed value cast with TRY_CAST/JSON_VALUE returns NULL, quietly dropping rows from an aggregate. Mitigation: validate on ingest, count NULLs, and alert on unexpected type drift.
• Scanning the whole blob for one field. Pulling one scalar out of a raw payload still reads the entire document per row. Mitigation: shred hot keys to columns (section 5); rely on sub-columnarisation/indexes for the rest.

Common interview probes on JSON/VARIANT.

• "Difference between -> and ->> in Postgres?" — -> returns jsonb (navigate), ->> returns text (scalar); cast the text.
• "How do you extract a scalar in Snowflake?" — colon path plus ::type, e.g. v:total_cents::number.
• "JSON_VALUE vs JSON_QUERY in BigQuery?" — JSON_VALUE returns a scalar STRING, JSON_QUERY returns a JSON subtree.
• "How does Spark read a JSON string into typed columns?" — from_json with an explicit or inferred schema.

Worked example — the same scalar, extracted and cast in four dialects

Detailed explanation. The portable skill is the path-and-cast pattern. Take one payload and pull region (a string) and total_cents (a number) out of it in Snowflake, BigQuery, Postgres, and Spark — identical semantics, four spellings.

• The payload. {"region":"EU","total_cents":4200,"items":[...]}.
• The moves. Navigate to the key, take it as a scalar, cast to a real type.
• The proof. All four return the SQL value 'EU' and the integer 4200, not a quoted JSON string.

Question. Extract region as a string and total_cents as an integer from a semi-structured column, in each of the four dialects.

Input.

Dialect	Column type	Scalar accessor
Snowflake	VARIANT	v:key::type
BigQuery	JSON	JSON_VALUE(j,'$.key') + CAST
Postgres	JSONB	j->>'key' + ::type
Spark	VARIANT / struct	variant_get(v,'$.key','type')

Code.

-- Snowflake (VARIANT): colon path + :: cast
SELECT v:region::string        AS region,
       v:total_cents::number   AS total_cents
FROM events.raw_orders;

-- BigQuery (JSON): JSON_VALUE returns a scalar STRING; cast the number
SELECT JSON_VALUE(j, '$.region')                 AS region,
       CAST(JSON_VALUE(j, '$.total_cents') AS INT64) AS total_cents
FROM events.raw_orders;

-- Postgres (JSONB): ->> returns text; chain a cast
SELECT j->>'region'            AS region,
       (j->>'total_cents')::int AS total_cents
FROM events.raw_orders;

-- Spark / Databricks (VARIANT): variant_get extracts AND casts in one call
SELECT variant_get(v, '$.region', 'string') AS region,
       variant_get(v, '$.total_cents', 'int') AS total_cents
FROM events.raw_orders;

Step-by-step explanation.

1. In Snowflake, v:region navigates the key but is still a VARIANT (a quoted JSON string); the ::string cast turns it into a real SQL VARCHAR, and ::number turns total_cents into a numeric — the cast is what makes it comparable and aggregatable.
2. In BigQuery, JSON_VALUE deliberately returns the scalar as a STRING (unlike JSON_QUERY, which would return the JSON subtree), so you CAST(... AS INT64) the numeric field explicitly.
3. In Postgres, ->> returns text — the scalar form — where -> would return a jsonb value you would then have to cast; chaining (... )::int yields the integer.
4. In Spark, variant_get(v, '$.path', 'type') folds navigation, scalar extraction, and casting into one call, returning a typed column directly; the older from_json/get_json_object route works on JSON strings.
5. The invariant across all four: navigate → take-as-scalar → cast. Every engine separates "give me the JSON subtree" from "give me the scalar value," and forgetting the cast is what leaves you with a quoted "EU" that silently fails to join to a plain EU.

Output.

Dialect	region result	total_cents result
Snowflake	EU (VARCHAR)	4200 (NUMBER)
BigQuery	EU (STRING)	4200 (INT64)
Postgres	EU (text)	4200 (int)
Spark	EU (string)	4200 (int)

Rule of thumb. Memorise the path-and-cast pattern, not the engine: navigate to the key, take it as a scalar (->>, JSON_VALUE, ::type, variant_get(...,type)), then cast to a real type. The "as JSON" operator is for subtrees; the "as scalar" operator plus a cast is for values.

Worked example — querying an unpredictable key set (schema-on-read)

Detailed explanation. The whole point of a VARIANT/JSONB column is that you can query keys you never declared. When the key set is unpredictable — feature flags, per-tenant custom attributes — schema-on-read lets you reach any key without a migration. Query a payload whose attributes object holds arbitrary keys.

• The payload. {"attributes":{"plan":"pro","beta_x":true,"seats":25}} — keys vary per record.
• The need. Filter by attributes.plan and, separately, discover which attribute keys exist.
• The tool. Scalar extraction for a known key; key-enumeration functions for discovery.

Question. Filter records where attributes.plan = 'pro', and separately list all distinct keys present under attributes, without declaring a schema.

Input.

Task	Snowflake	Postgres
Filter known key	v:attributes.plan::string = 'pro'	j->'attributes'->>'plan' = 'pro'
Enumerate keys	LATERAL FLATTEN(input => v:attributes) → .key	jsonb_object_keys(j->'attributes')
Contains key	v:attributes:seats IS NOT NULL	j->'attributes' ? 'seats'

Code.

-- Snowflake: filter a known key, and enumerate the UNKNOWN key set via FLATTEN.
SELECT count(*) AS pro_accounts
FROM events.accounts
WHERE v:attributes.plan::string = 'pro';

SELECT f.key AS attribute_key, count(*) AS seen
FROM events.accounts,
     LATERAL FLATTEN(input => v:attributes) f   -- one row per key/value in the object
GROUP BY 1 ORDER BY 2 DESC;

-- Postgres: same two questions on JSONB, no schema declared.
SELECT count(*) AS pro_accounts
FROM events.accounts
WHERE j->'attributes'->>'plan' = 'pro';

SELECT k AS attribute_key, count(*) AS seen
FROM events.accounts,
     LATERAL jsonb_object_keys(j->'attributes') AS k   -- enumerate arbitrary keys
GROUP BY 1 ORDER BY 2 DESC;

Step-by-step explanation.

1. Filtering the known key is ordinary path-and-cast: v:attributes.plan::string (Snowflake) or j->'attributes'->>'plan' (Postgres) reaches a nested scalar and compares it — no schema needed for a key you can name.
2. Discovering the unknown keys is where schema-on-read earns its keep: FLATTEN over an object (Snowflake) and jsonb_object_keys (Postgres) enumerate whatever keys each record actually has, so you can audit a payload whose shape you do not control.
3. The enumeration is per-record, so grouping by key gives you the distribution of attribute keys across the whole table — how you find out that beta_x appeared last week without anyone telling you.
4. Because nothing is declared, a producer adding a new attribute key needs no migration: it simply shows up in the enumeration and is filterable immediately — the flexibility that a rigid column schema cannot offer.
5. The senior caveat: this flexibility is exactly why the tail should stay raw and the hot keys should be shredded — schema-on-read is perfect for the churning, discoverable tail, and wasteful for a key every query filters on.

Output.

Query	Result
attributes.plan = 'pro' filter	count of pro accounts (typed compare)
key enumeration	plan, beta_x, seats, ... with counts
new key next week	appears automatically, no migration
hot key (plan) long-term	candidate to shred into a column

Rule of thumb. Use schema-on-read to reach and enumerate keys you never declared — perfect for a churning, per-record attribute tail. Reach for object-flatten / jsonb_object_keys to discover the key set, and promote any key that becomes hot into a typed column.

Senior interview question on cross-dialect JSON access

A senior interviewer might ask: "The same nested JSON event lands in Snowflake, BigQuery, and Postgres across three teams. Write the extraction for a scalar field and a nested field in each, explain the string-versus-scalar trap that breaks joins, and say how you would keep the three implementations consistent so a downstream model behaves identically regardless of engine."

Solution Using the path-and-cast pattern with scalar accessors and a shared contract

-- Snowflake (VARIANT): scalar + nested, always cast to a real type.
SELECT
    v:region::string                 AS region,          -- scalar
    v:customer.tier::string          AS customer_tier,   -- nested scalar
    v:total_cents::number            AS total_cents
FROM events.raw_orders;

-- BigQuery (JSON): JSON_VALUE for scalars (NOT JSON_QUERY, which keeps the quotes).
SELECT
    JSON_VALUE(j, '$.region')                    AS region,
    JSON_VALUE(j, '$.customer.tier')             AS customer_tier,
    CAST(JSON_VALUE(j, '$.total_cents') AS INT64) AS total_cents
FROM events.raw_orders;

-- Postgres (JSONB): ->> for the scalar, #>> for a nested path, then cast.
SELECT
    j->>'region'                          AS region,
    j#>>'{customer,tier}'                 AS customer_tier,   -- nested scalar via path
    (j->>'total_cents')::int              AS total_cents
FROM events.raw_orders;

# A shared field contract so all three engines produce IDENTICAL typed columns.
fields:
  region:        { path: "$.region",        type: string }
  customer_tier: { path: "$.customer.tier",  type: string }
  total_cents:   { path: "$.total_cents",    type: int }
rule: "always extract as SCALAR (->> / JSON_VALUE / ::type), never as JSON subtree"
rule: "cast at the leaf; a NULL after cast means quarantine, not silently drop"

Step-by-step trace.

Field	Snowflake	BigQuery	Postgres
scalar region	v:region::string	JSON_VALUE(j,'$.region')	j->>'region'
nested customer.tier	v:customer.tier::string	JSON_VALUE(j,'$.customer.tier')	j#>>'{customer,tier}'
numeric total_cents	v:total_cents::number	CAST(JSON_VALUE(...) AS INT64)	(j->>'total_cents')::int
trap avoided	not v:region (VARIANT)	not JSON_QUERY (quoted)	not -> (jsonb)

After standardising on the contract, all three engines extract each field as a scalar and cast it to the declared type, so region is the bare value EU everywhere — never the quoted "EU" that JSON_QUERY, a bare colon path, or -> would return — and the downstream model joins and aggregates identically no matter which warehouse produced the column. A post-cast NULL is treated as a data-quality signal to quarantine, not a row to drop silently.

Output:

Concern	Ad-hoc per team	Shared path-and-cast contract
Scalar vs JSON	mixed (quotes leak)	always scalar + cast
Join correctness	breaks on "EU" vs EU	consistent bare values
Type of total_cents	sometimes string	always int
Bad value	silently dropped	quarantined + alerted
Cross-engine parity	drifts	identical output

Why this works — concept by concept:

• Path-and-cast pattern — navigating to a key, taking it as a scalar, and casting to a real type is the one portable skill; every dialect implements it, so the model is engine-independent and only the spelling changes.
• Scalar over subtree — choosing ->>/JSON_VALUE/::type over ->/JSON_QUERY/a bare colon returns the bare value instead of a quoted JSON string, which is what keeps joins and comparisons correct across engines.
• Cast at the leaf — coercing to int/INT64/number at the point of extraction enforces the type once, so downstream code never re-parses and a type drift surfaces immediately.
• Shared contract — declaring each field's path and type once and enforcing "scalar, cast, quarantine-on-NULL" makes three engines produce byte-identical typed columns, so a downstream model is portable.
• Cost — the contract adds no runtime cost; it prevents the expensive class of bugs (silent quote leakage, dropped rows, per-engine drift) that surface late in production. The eliminated cost is a data-quality incident per engine — O(1) discipline instead of O(engines) debugging.

JSON
Topic — json
JSON problems on extraction and path access

Practice →

Type handling Topic — type-handling Type handling problems on casting and scalar coercion

Practice →

---

3. Nested (struct) fields — dot access, typing, evolution

A struct is a typed record; reach a leaf by dot, and let new keys land under schema-on-read

The mental model in one line: a nested field is a struct — a record with named, typed sub-fields nested inside a column — and there are two flavours: a typed struct (BigQuery STRUCT<region STRING, revenue INT64>, a Spark StructType, a Snowflake OBJECT) whose fields are declared and checked, and an untyped object inside a VARIANT/JSON/JSONB value whose fields are resolved and cast at read, so you reach a leaf the same way in both — dot down the path (order.customer.address.city) — but they diverge on schema evolution: an additive key is free on the untyped side and a schema change on the typed side. Dot access is the constant; typing is the trade — declared-and-checked versus flexible-and-late.

What a struct is.

• A named-field record. A struct groups related fields under one column: customer holds {id, tier, address:{city, country}}. It models a one-to-one nested relationship without a separate table or a join.
• Dot access to a leaf. You drill in with dots: customer.address.city. Each hop selects a sub-field; the last hop is the scalar leaf you cast (on the untyped side) or read directly (on the typed side).
• Not a join. A struct is co-located with its parent row, so reading a nested field is a projection, not a join — the reason nested modelling is cheap to read.

Typed struct vs untyped object.

• Typed struct. BigQuery STRUCT<...>, Spark StructType, Snowflake OBJECT (with structured-type typing): fields and types are declared, so access is checked, columnar, and needs no cast — but adding a field is a schema change.
• Untyped object. A JSON object inside VARIANT/JSON/JSONB: any field, any depth, resolved and cast at read — additive-tolerant, but you own the casting and the null-vs-missing handling.
• The choice. Type the stable, hot nested fields you query constantly; leave the churning nested tail untyped in the blob — the same shred-vs-raw logic, one level down.

Schema evolution of nested fields.

• Additive keys are free (untyped). A new customer.loyalty_tier key appears in the JSON and is immediately queryable; missing on old rows it reads as NULL — no migration, no backfill required to keep ingesting.
• Typed columns need a change. Adding a field to a STRUCT column is a DDL change; some engines allow adding nested struct fields, others require a rewrite — plan additive evolution and avoid renames/removals.
• Null vs missing. A field that is null in the JSON and a field that is absent can both surface as SQL NULL; when the distinction matters, test existence (?, IS_NULL_VALUE, JSON_TYPE) before casting.

The failure modes senior engineers pre-empt.

• Null-vs-missing confusion. Treating "key absent" and "key present but null" as identical hides producer bugs and miscounts. Mitigation: use existence tests where the difference is meaningful; monitor the ratio.
• Casting at the wrong hop. Casting a mid-path object instead of the leaf, or forgetting the leaf cast entirely, yields errors or quoted strings. Mitigation: cast only the final scalar; keep intermediate hops as JSON/struct.
• Over-nesting the model. Burying a hot field five levels deep makes every query verbose and defeats pruning. Mitigation: shred hot leaves to top-level columns; keep nesting for genuinely hierarchical, cold data.

Common interview probes on nested fields.

• "How do you read a nested field?" — dot down the path to the leaf; cast the leaf if the source is untyped.
• "Typed struct vs a JSON object — when each?" — typed for stable hot fields (checked, columnar), untyped for the churning tail (additive-tolerant).
• "A producer adds a nested key — does ingest break?" — not under schema-on-read; the key is absorbed and reads as NULL where absent.
• "Null vs missing — do they differ?" — both can read as NULL; test existence when the distinction matters.

Worked example — access a deeply nested field across three engines

Detailed explanation. The portable move is dot access to a leaf. Read customer.address.city from a nested record in BigQuery (typed STRUCT), Spark (StructType), and Snowflake (VARIANT object) — same path, three typings.

• The shape. order.customer.address.city — three hops to a string leaf.
• The typed read. BigQuery/Spark declare the struct, so the dot path returns a typed value directly.
• The untyped read. Snowflake's VARIANT object needs a ::string cast at the leaf.

Question. Select the nested customer.address.city leaf and filter on it, in each engine.

Input.

Engine	Column	Leaf access
BigQuery	STRUCT	o.customer.address.city
Spark	StructType	col("customer.address.city")
Snowflake	VARIANT object	v:customer.address.city::string

Code.

-- BigQuery: typed STRUCT — dot access returns a STRING, no cast needed.
SELECT o.order_id,
       o.customer.address.city AS city
FROM events.orders o
WHERE o.customer.address.city = 'Berlin';

# Spark: StructType — dot path inside col(), or nested column access.
from pyspark.sql.functions import col
(orders
   .select("order_id", col("customer.address.city").alias("city"))
   .filter(col("customer.address.city") == "Berlin"))

-- Snowflake: VARIANT object — same dot path, but CAST the leaf to a real type.
SELECT v:order_id::number AS order_id,
       v:customer.address.city::string AS city
FROM events.orders
WHERE v:customer.address.city::string = 'Berlin';

Step-by-step explanation.

1. In BigQuery the customer column is a declared STRUCT, so o.customer.address.city is a checked, columnar projection returning a STRING directly — the engine already knows the type, so no cast and no parse.
2. In Spark the same nesting is a StructType; col("customer.address.city") drills the same path and the Catalyst optimizer prunes to just that leaf, reading only the nested column from Parquet/Delta, not the whole struct.
3. In Snowflake the object lives inside a VARIANT, so the identical dot path v:customer.address.city reaches the leaf but returns a VARIANT — the ::string cast turns it into a real VARCHAR for the filter and the projection.
4. The filter behaves the same in all three: because the nested field is co-located with the row, filtering on ...city = 'Berlin' is a projection-plus-predicate, not a join to another table.
5. The only real difference is the cast: typed structs (BigQuery/Spark) hand you the typed leaf; the untyped VARIANT object hands you a JSON value you must cast at the leaf — the exact same distinction as section 2, one level of nesting deeper.

Output.

Engine	Returned type	Cast needed
BigQuery STRUCT	STRING	no (declared)
Spark StructType	string	no (declared)
Snowflake VARIANT	VARIANT → VARCHAR	yes (::string)
all	one leaf, pruned	co-located, no join

Rule of thumb. Reach a nested field by dotting down to the leaf, identically across engines; cast the leaf only when the source is an untyped VARIANT/JSON object. Nested access is a projection, not a join — which is why co-locating one-to-one data in a struct is cheap to read.

Worked example — evolve a struct without breaking existing queries

Detailed explanation. The reason semi-structured modelling survives real producers is additive evolution. Show a new nested key (customer.loyalty_tier) appearing, and how schema-on-read absorbs it while a typed struct needs a deliberate, additive DDL — and why you never rename or remove.

• The change. Producers add customer.loyalty_tier to new events; old events lack it.
• The untyped path. Query it immediately; old rows read NULL. No migration.
• The typed path. Add the nested field with additive DDL; backfill NULL.

Question. Make customer.loyalty_tier queryable on both new and old events without breaking any existing query, on the untyped and typed sides.

Input.

Side	New key handling	Old rows	Existing queries
Untyped (VARIANT/JSONB)	queryable instantly	read NULL	unaffected
Typed (STRUCT column)	additive DDL	backfill NULL	unaffected if additive
Forbidden	rename/remove a key	breaks readers	breaks queries

Code.

-- UNTYPED (schema-on-read): the new nested key is queryable with NO migration.
-- Old events that lack it simply return NULL for that column.
SELECT
    v:customer.id::number            AS customer_id,
    v:customer.loyalty_tier::string  AS loyalty_tier   -- new key: NULL on old rows
FROM events.orders;

-- TYPED (BigQuery): evolve the STRUCT additively — add the nested field, never rename.
ALTER TABLE events.orders_typed
  ADD COLUMN customer STRUCT<id INT64, tier STRING, loyalty_tier STRING>;
-- (or ADD a nested field where the engine supports it) — old rows read NULL for the addition.

-- Existing queries that never referenced loyalty_tier keep working unchanged.
SELECT customer.id, customer.loyalty_tier   -- new field available; old queries untouched
FROM events.orders_typed;

Step-by-step explanation.

1. On the untyped side, v:customer.loyalty_tier::string works the instant producers emit the key — schema-on-read resolves it at query time, so there is no ingest migration and no pipeline change.
2. Old events that never carried loyalty_tier return SQL NULL for that projection, so the column is well-defined for the whole history without a backfill — additive change is genuinely free.
3. On the typed side, the field is added with additive DDL; the engine treats pre-existing rows as NULL for the new field, so the change is backward-compatible.
4. Crucially, every existing query that never referenced loyalty_tier is untouched in both models — additive evolution does not disturb the existing contract, which is exactly why it is safe.
5. The forbidden moves are rename and remove: renaming tier→grade or dropping a key breaks every reader that referenced the old name, which is why mature producers only ever add — deprecating a key by leaving it in place and ignoring it, never by deleting it.

Output.

Operation	Untyped	Typed	Safe?
add nested key	instant, NULL on old	additive DDL, NULL on old	yes
read on old rows	NULL	NULL	yes
existing queries	unaffected	unaffected	yes
rename/remove key	breaks readers	breaks readers	no

Rule of thumb. Evolve nested schemas additively only: new keys are free under schema-on-read and an additive DDL on a typed struct, both reading NULL on old rows and leaving existing queries untouched. Never rename or remove a key in place — deprecate by ignoring, so no reader breaks.

Senior interview question on nested-field modelling and evolution

A senior interviewer might ask: "Your events carry a nested customer object with an address sub-object, and product keeps adding fields to it. Show how you read a deeply nested leaf, decide which nested fields to type versus leave in the raw blob, and evolve the schema as new keys arrive — all without breaking a single existing downstream query."

Solution Using dot access, a typed hot core, an untyped tail, and additive-only evolution

-- 1. Type the STABLE, HOT nested fields into a struct column (checked, columnar).
CREATE TABLE events.orders_typed (
    order_id  INT64,
    customer  STRUCT<id INT64, tier STRING, address STRUCT<city STRING, country STRING>>,
    payload   JSON               -- the raw event: the churning nested tail stays here
);

-- 2. Read a deeply nested leaf from the TYPED struct — no cast, pruned to the leaf.
SELECT order_id,
       customer.address.city    AS city,
       customer.tier            AS tier
FROM events.orders_typed
WHERE customer.address.country = 'DE';

-- 3. Read a CHURNING nested key from the UNTYPED tail — schema-on-read, cast the leaf.
SELECT order_id,
       JSON_VALUE(payload, '$.customer.loyalty_tier') AS loyalty_tier   -- new key, no DDL
FROM events.orders_typed;

-- 4. Evolve ADDITIVELY when a tail key turns hot: promote it to the typed struct.
ALTER TABLE events.orders_typed
  ADD COLUMN customer_loyalty_tier STRING;    -- additive; old rows NULL; queries untouched

Step-by-step trace.

Nested field	Where it lives	How it's read	On evolution
customer.address.city	typed struct	dot access, no cast	stable, checked
customer.tier	typed struct	dot access, no cast	stable, checked
customer.loyalty_tier	raw payload (tail)	JSON_VALUE, cast leaf	free (additive)
promoted hot key	new typed column	plain column	additive DDL
any existing query	unchanged path	unchanged	untouched

After the design, the stable hot nested fields (address.city, tier) live in a typed STRUCT that is checked and column-pruned, so common queries dot straight to the leaf with no cast; the churning tail (loyalty_tier and whatever comes next) stays in the raw payload and is read via schema-on-read with no migration; and when a tail key becomes hot it is promoted with additive DDL that leaves every existing query untouched. New producer keys never break ingest, and the model is a projection — no joins — the whole way down.

Output:

Metric	Rigid all-typed struct	Hybrid typed core + tail
New nested key	breaks/needs migration	absorbed by raw payload
Hot leaf read	fast, checked	fast, checked
Cold leaf read	forced into schema	schema-on-read from blob
Existing queries on change	at risk	untouched (additive only)
Cast burden	none	only on the untyped tail

Why this works — concept by concept:

• Dot access to a leaf — nested fields are co-located with the row, so drilling to a leaf is a pruned projection rather than a join, which is what makes one-to-one nested modelling cheap to read.
• Typed hot core — declaring the stable, frequently-queried nested fields as a STRUCT gives checked types, column pruning, and cast-free reads for the queries that run constantly.
• Untyped churning tail — leaving volatile nested keys in the raw payload lets schema-on-read resolve them at query time, so a new producer field is queryable instantly with no migration.
• Additive-only evolution — new keys read NULL on old rows and never disturb existing queries, and promotion of a hot key is an additive DDL — renames and removals are banned because they break readers.
• Cost — the hot path prunes to a typed leaf (a few bytes), the tail pays JSON cost only when queried, and evolution never triggers a full rewrite. The eliminated cost is a migration-and-backfill per producer change — O(1) additive change instead of O(history) rewrites.

Parsing
Topic — parsing
Parsing problems on nested paths and dot access

Practice →

Type handling Topic — type-handling Type handling problems on struct typing and null-vs-missing

Practice →

---

4. Repeated (array) fields — UNNEST, FLATTEN, LATERAL, explode

One array in, one row per element out — keep the parent, keep the index, and never flatten two at once

The mental model in one line: a repeated field is an array column — one parent row holding many child elements — and turning it into rows you can filter and aggregate is always the same operation under four names: BigQuery's UNNEST, Snowflake's LATERAL FLATTEN, Postgres's jsonb_array_elements, and Spark's explode all take an array and emit one row per element while keeping the parent, so the two things that separate a correct flatten from a broken one are preserving the parent key (a correlated/lateral join, not a cross product) and preserving the element order (WITH ORDINALITY, posexplode, FLATTEN.index) — and the classic bug is flattening two arrays in the same step, which multiplies rows into a fan-out. Flatten is a join between a row and its own array; treat it like one.

What a repeated field is.

• An array column. items: [{sku, qty}, {sku, qty}, ...] — one order row, many line items, stored inline. It models a one-to-many relationship without a child table.
• Flatten to rows. Analysis wants one row per element, so you flatten (a.k.a. unnest / explode): the parent row is repeated once per array element, with the element exposed as a column.
• A self-join, correlated. Flatten correlates each parent to its own array, so the parent columns ride along — it is not a cross join to some other table.

The flatten operator by dialect.

• BigQuery — UNNEST. FROM orders, UNNEST(items) AS item (an implicit CROSS JOIN that is correlated to the row) gives one row per item with order_id preserved; UNNEST(...) WITH OFFSET AS pos adds the index.
• Snowflake — LATERAL FLATTEN. FROM orders, LATERAL FLATTEN(input => items) f exposes f.value (the element), f.index (position), and f.key (for objects); the parent columns are available because it is lateral.
• Postgres — jsonb_array_elements. FROM orders, jsonb_array_elements(payload->'items') AS item (a lateral set-returning function) emits one item per element; WITH ORDINALITY adds the 1-based position.
• Spark — explode. df.select("order_id", explode("items").alias("item")) emits one row per element; posexplode also returns the position, and explode_outer keeps parents whose array is empty/null.

Preserving the parent and the index.

• Keep the parent. Because flatten is correlated/lateral, the parent's columns (order_id, region) stay on every exploded row — which is what lets you re-aggregate per parent afterwards.
• Keep the index. When element order matters (a ranked list, a sequence), capture the position: WITH OFFSET, f.index, WITH ORDINALITY, or posexplode — otherwise the order is not guaranteed after the flatten.
• Keep empty parents. A plain flatten drops parents whose array is empty or null; use the outer variant (LEFT JOIN UNNEST, explode_outer, LEFT JOIN LATERAL ... ON true) when you must retain them.

The failure modes senior engineers pre-empt.

• The two-array fan-out. Flattening items and tags in the same query cross-multiplies them: |items| × |tags| rows per order, silently inflating every downstream sum. Mitigation: flatten one array per step; aggregate before combining, or flatten in separate CTEs.
• Lost parent rows. A plain (inner) flatten drops orders with an empty items array, so counts undercount. Mitigation: the outer variant when empty parents must survive.
• Double-counting after explode. Summing a parent-level measure after flattening multiplies it by the array length. Mitigation: aggregate the exploded rows, or divide the measure out, or sum it from the un-flattened parent.

Common interview probes on repeated fields.

• "How do you get one row per array element?" — UNNEST/LATERAL FLATTEN/jsonb_array_elements/explode, correlated to the parent.
• "Two arrays in one query — what happens?" — a fan-out; rows multiply, sums inflate. Flatten separately.
• "How do you keep orders whose array is empty?" — the outer variant (LEFT JOIN UNNEST, explode_outer).
• "How do you keep element order?" — capture the index (WITH OFFSET/f.index/WITH ORDINALITY/posexplode).

Worked example — flatten a line-items array and re-aggregate per order

Detailed explanation. The canonical repeated-field task: an order holds an items array, and you want revenue per SKU and item-count per order. Flatten to one row per item, keep order_id, then aggregate — in all four dialects.

• The shape. order{order_id, items:[{sku, qty, price_cents}, ...]}.
• The flatten. One row per item, order_id preserved.
• The aggregate. Sum qty*price_cents per SKU; count items per order.

Question. Produce revenue per SKU from an array of line items, keeping the parent order, in BigQuery, Snowflake, Postgres, and Spark.

Input.

Step	Operation
flatten	one row per items element
keep	order_id (correlated parent)
compute	qty * price_cents per row
aggregate	sum(...) grouped by sku

Code.

-- BigQuery: UNNEST is a correlated cross join to the row's own array.
SELECT item.sku,
       SUM(item.qty * item.price_cents) AS revenue_cents
FROM events.orders o,
     UNNEST(o.items) AS item                     -- one row per line item, order preserved
GROUP BY item.sku;

-- Snowflake: LATERAL FLATTEN exposes value/index/key; cast the leaves.
SELECT f.value:sku::string AS sku,
       SUM(f.value:qty::number * f.value:price_cents::number) AS revenue_cents
FROM events.orders o,
     LATERAL FLATTEN(input => o.items) f          -- f.value = the element
GROUP BY 1;

-- Postgres: jsonb_array_elements is a lateral set-returning function.
SELECT item->>'sku' AS sku,
       SUM((item->>'qty')::int * (item->>'price_cents')::int) AS revenue_cents
FROM events.orders o,
     jsonb_array_elements(o.payload->'items') AS item   -- one row per element
GROUP BY 1;

# Spark: explode the array, then group. posexplode would also give the position.
from pyspark.sql.functions import explode, col
(orders
   .select("order_id", explode("items").alias("item"))
   .select("order_id", col("item.sku").alias("sku"),
           (col("item.qty") * col("item.price_cents")).alias("rev"))
   .groupBy("sku").sum("rev"))

Step-by-step explanation.

1. Each flatten operator correlates the parent order to its own items array and emits one row per element — so an order with three items becomes three rows, each still carrying order_id (and any other parent column you select).
2. On the typed sides (BigQuery item.qty, Spark col("item.qty")) the element fields are already typed; on the untyped sides (Snowflake f.value:qty::number, Postgres (item->>'qty')::int) you cast each leaf, exactly as in section 2.
3. The per-row measure qty * price_cents is computed on the exploded rows, so it is the line-item revenue — correct because the parent was preserved and the row granularity is now one-per-item.
4. GROUP BY sku re-aggregates across the exploded rows to revenue per SKU; because order_id rode along, you could equally GROUP BY order_id for item-count per order without a second pass over the source.
5. The senior point: this is a single-array flatten, so there is no fan-out — the row count out equals the total number of line items, and every sum is correct. Add a second array to the same FROM and that guarantee breaks (next example).

Output.

SKU	rows after flatten	revenue_cents
WIDGET-1	one per occurrence	sum(qty × price)
GADGET-9	one per occurrence	sum(qty × price)
total rows	= total line items	(no fan-out)
parent kept	order_id on each	yes

Rule of thumb. Flatten a repeated field by correlating the parent to its own array (UNNEST/LATERAL FLATTEN/jsonb_array_elements/explode), cast the element leaves if untyped, then re-aggregate. One array per flatten keeps the row count equal to the element count — and every sum correct.

Worked example — the two-array fan-out bug and its fix

Detailed explanation. The most common repeated-field bug: an order has both items and tags, and flattening both in one query cross-multiplies them, inflating every sum by the other array's length. Show the bug and the correct separate-flatten fix.

• The shape. order{items:[3 elements], tags:[2 elements]}.
• The bug. Flatten both → 3 × 2 = 6 rows per order; sum(total) counts each order 6 times.
• The fix. Flatten each array in its own step (CTE), aggregate, then join on the parent.

Question. Compute total revenue and tag counts per order when each order has both an items array and a tags array, without inflating either.

Input.

Approach	Rows per order	Revenue sum
both arrays in one FROM	`	items
separate flatten + join	{% raw %}`	items
aggregate before join	1 per order	correct

Code.
{% raw %}

-- WRONG: both arrays flattened together -> fan-out. 3 items x 2 tags = 6 rows/order.
SELECT o.order_id,
       SUM(item.price_cents) AS revenue,   -- INFLATED: each item counted once per tag
       COUNT(tag)            AS tag_count   -- INFLATED: each tag counted once per item
FROM events.orders o,
     UNNEST(o.items) AS item,
     UNNEST(o.tags)  AS tag                -- <-- second array in the same FROM = bug
GROUP BY o.order_id;

-- RIGHT: flatten each array in its OWN CTE, aggregate, then join on the parent.
WITH item_rev AS (
  SELECT o.order_id, SUM(item.price_cents) AS revenue
  FROM events.orders o, UNNEST(o.items) AS item
  GROUP BY o.order_id
),
tag_cnt AS (
  SELECT o.order_id, COUNT(tag) AS tag_count
  FROM events.orders o, UNNEST(o.tags) AS tag
  GROUP BY o.order_id
)
SELECT i.order_id, i.revenue, t.tag_count
FROM item_rev i
JOIN tag_cnt  t USING (order_id);           -- one row per order, both sums correct

Step-by-step explanation.

1. In the wrong query, listing UNNEST(o.items) and UNNEST(o.tags) in the same FROM produces the Cartesian product of the two arrays per order: an order with 3 items and 2 tags becomes 6 rows.
2. On those 6 rows, SUM(item.price_cents) counts each item's price twice (once per tag), inflating revenue by the tag count; COUNT(tag) counts each tag three times (once per item) — both aggregates are silently wrong, not errored.
3. The fix flattens each array independently: item_rev explodes only items (3 rows/order) and aggregates to one revenue per order; tag_cnt explodes only tags (2 rows/order) and aggregates to one count per order.
4. Because each CTE has already collapsed back to one row per order_id, the final JOIN ... USING (order_id) combines them at parent granularity — no cross product, both measures correct.
5. The senior heuristic: never flatten two independent arrays in the same scope. Either flatten one at a time, or aggregate each array to a scalar before combining — the fan-out is a granularity bug, and the fix is to restore parent granularity before you join.

Output.

order_id	wrong revenue	right revenue	wrong tag_count	right tag_count
1001 (3 items, 2 tags)	2× actual	actual	3× actual	actual
any 2-array order	inflated	correct	inflated	correct
row granularity	items×tags	parent (after agg)	—	—

Rule of thumb. Never flatten two independent arrays in the same scope — that is the fan-out bug, and it inflates every sum silently. Flatten each array in its own CTE, aggregate back to parent granularity, then join on the parent key.

Worked example — preserving element order and empty parents

Detailed explanation. Two flatten details decide correctness: keeping the element index when order matters, and keeping empty-array parents when counts must be complete. Flatten a ranked steps array preserving position, and keep funnels that have no steps yet.

• The order. steps: ["view","cart","checkout"] — position is the funnel stage.
• The index. Capture 0/1-based position with WITH OFFSET/ORDINALITY/posexplode.
• The empties. Keep sessions whose steps array is empty via the outer variant.

Question. Flatten a ranked array keeping each element's position, and retain parent rows whose array is empty.

Input.

Need	BigQuery	Postgres	Spark
element index	UNNEST(...) WITH OFFSET	WITH ORDINALITY	posexplode
keep empty parents	LEFT JOIN UNNEST	LEFT JOIN LATERAL ... ON true	explode_outer

Code.

-- BigQuery: WITH OFFSET keeps the position; LEFT JOIN UNNEST keeps empty-array parents.
SELECT s.session_id,
       step,
       pos                                    -- 0-based element position
FROM events.sessions s
LEFT JOIN UNNEST(s.steps) AS step WITH OFFSET AS pos   -- LEFT keeps sessions with no steps
ORDER BY s.session_id, pos;

-- Postgres: WITH ORDINALITY gives a 1-based index; LEFT JOIN LATERAL keeps empties.
SELECT s.session_id,
       step.value  AS step,
       step.ord    AS pos                      -- 1-based position
FROM events.sessions s
LEFT JOIN LATERAL jsonb_array_elements_text(s.payload->'steps')
     WITH ORDINALITY AS step(value, ord) ON true   -- ON true keeps empty-array sessions
ORDER BY s.session_id, pos;

# Spark: posexplode gives (pos, col); explode_outer keeps rows with empty/null arrays.
from pyspark.sql.functions import posexplode_outer
(sessions
   .select("session_id", posexplode_outer("steps").alias("pos", "step"))
   .orderBy("session_id", "pos"))

Step-by-step explanation.

1. WITH OFFSET (BigQuery), WITH ORDINALITY (Postgres), and posexplode (Spark) each return the element's position alongside its value, so a ranked array keeps its order after flattening — without this the row order post-flatten is not guaranteed.
2. BigQuery's offset is 0-based and Postgres's ordinality is 1-based — a small but real difference to normalise if you compare positions across engines.
3. Switching the inner flatten to its outer form — LEFT JOIN UNNEST, LEFT JOIN LATERAL ... ON true, posexplode_outer — keeps parent rows whose array is empty or null, emitting one row with a NULL element instead of dropping the parent.
4. That matters for counts: a plain (inner) flatten would silently drop sessions with zero steps, so COUNT(DISTINCT session_id) would undercount the funnel; the outer variant makes the parent population complete.
5. The senior discipline is to decide two things up front for every flatten — does order matter (capture the index) and must empty parents survive (use the outer variant) — because both are silent correctness bugs, not errors, if you get them wrong.

Output.

session_id	step	pos	note
S1	view	0/1	ordered element
S1	cart	1/2	ordered element
S2 (empty steps)	NULL	NULL	kept by outer flatten
plain inner flatten	—	—	S2 dropped (bug)

Rule of thumb. For every flatten, decide two things: does element order matter (capture the index with WITH OFFSET/ORDINALITY/posexplode), and must empty-array parents survive (use the outer variant). Both are silent miscounts if you skip them, never errors.

Senior interview question on flattening repeated fields correctly

A senior interviewer might ask: "Each order event has an items array and a tags array. Produce, per order, total revenue from items and the number of tags, while keeping orders that have no items yet and preserving item order for a receipt view. Explain the fan-out risk, how you avoid it, and how the query ports from BigQuery to Snowflake and Postgres."

Solution Using per-array flatten in separate CTEs, an outer join for empties, and index preservation

-- 1. Flatten ONLY items, keep position (receipt order), keep orders with no items.
WITH items_flat AS (
  SELECT o.order_id,
         item.sku,
         item.qty * item.price_cents AS line_cents,
         pos                                    -- receipt position
  FROM events.orders o
  LEFT JOIN UNNEST(o.items) AS item WITH OFFSET AS pos   -- LEFT keeps empty-item orders
),
item_rev AS (
  SELECT order_id, SUM(line_cents) AS revenue_cents      -- back to ONE row per order
  FROM items_flat GROUP BY order_id
),
-- 2. Flatten ONLY tags in a SEPARATE scope (no fan-out with items).
tag_cnt AS (
  SELECT o.order_id, COUNT(tag) AS tag_count
  FROM events.orders o, UNNEST(o.tags) AS tag
  GROUP BY o.order_id
)
-- 3. Combine at PARENT granularity — both arrays already aggregated to one row/order.
SELECT o.order_id,
       COALESCE(r.revenue_cents, 0) AS revenue_cents,    -- 0 for orders with no items
       COALESCE(t.tag_count, 0)     AS tag_count
FROM events.orders o
LEFT JOIN item_rev r USING (order_id)
LEFT JOIN tag_cnt  t USING (order_id);

-- Snowflake port: LATERAL FLATTEN with f.index for position; OUTER => TRUE for empties.
WITH items_flat AS (
  SELECT o.order_id, f.value:sku::string AS sku,
         f.value:qty::number * f.value:price_cents::number AS line_cents, f.index AS pos
  FROM events.orders o, LATERAL FLATTEN(input => o.items, outer => true) f
)
SELECT order_id, SUM(line_cents) AS revenue_cents FROM items_flat GROUP BY 1;

Step-by-step trace.

Requirement	Technique	Why
revenue per order	flatten items, sum per order	line-item granularity → parent
tag count per order	flatten tags in a separate CTE	avoid items×tags fan-out
keep no-item orders	LEFT JOIN UNNEST / outer => true	complete order population
receipt order	WITH OFFSET / f.index	preserve element position
combine	join aggregated CTEs on order_id	parent granularity, no product

After the design, items is flattened once (with position, and with a LEFT join so empty-item orders survive) and aggregated to one revenue row per order; tags is flattened in a separate CTE and aggregated to one count per order; and the two are joined at parent granularity with COALESCE filling zeros — so no order is dropped, no sum is inflated by the other array, and the receipt view keeps item order. The Snowflake and Postgres ports change only the flatten operator and the leaf casts.

Output:

Metric	Naive two-array flatten	Per-array CTE design
rows per order (mid-query)	items × tags	items, then parent
revenue correctness	inflated ×tags	exact
tag-count correctness	inflated ×items	exact
orders with no items	dropped	kept (revenue 0)
element order	lost	preserved (index)

Why this works — concept by concept:

• One array per flatten — flattening items and tags in separate scopes keeps each explosion at its own granularity, so the Cartesian fan-out that silently inflates both sums never happens.
• Aggregate before combining — collapsing each flattened array back to one row per order_id before the join means the final combine is at parent granularity, a plain key join rather than a product.
• Outer flatten for empties — the LEFT JOIN UNNEST / outer => true variant keeps orders whose items array is empty, so the order population stays complete and counts do not silently drop rows.
• Index preservation — WITH OFFSET / f.index carries each element's position through the flatten, so a receipt or ranked view keeps its order, which a plain flatten does not guarantee.
• Cost — each array is scanned and exploded once and immediately re-aggregated, versus a fan-out that materialises |items| × |tags| rows per order and then has to be de-duplicated. The eliminated cost is the quadratic row blow-up — O(|items| + |tags|) work instead of O(|items| × |tags|) per order.

Data transformation
Topic — data-transformation
Data transformation problems on flattening and reshaping arrays

Practice →

Parsing Topic — parsing Parsing problems on array elements and unnest

Practice →

---

5. Patterns at scale — schema evolution, shredding, indexing, cost

Shred the hot keys, keep the raw tail, index what you filter, and scan bytes not blobs

The mental model in one line: making semi-structured data cheap at scale is four patterns working together — shredding the hot keys into typed columns while keeping the raw payload for the tail, tolerant schema evolution so additive keys never break ingest, indexing the keys you filter on (a Postgres GIN or expression index, Snowflake's automatic sub-columns, BigQuery clustering on an extracted column), and cost control by pruning and partitioning so a query scans a few projected columns instead of re-parsing every blob — and the through-line is always the same: move work from query time to ingest time for the hot path, and pay the flexible schema-on-read cost only on the cold tail. Storage layout is the lever; bytes scanned is the bill.

Shredding — promote the hot keys.

• What it is. Extract the frequently-queried keys from the payload into typed top-level columns on ingest, and keep the whole raw payload alongside them.
• Why. The hot path scans a few typed columns (cheap, prunable, indexable) instead of re-parsing a multi-kilobyte blob per row; the tail is still reachable in the raw column.
• How. A view or an ingest transform that writes region, total_cents, created_at, ... as columns plus payload as the raw blob — the hybrid table from section 1.

Schema evolution — additive and tolerant.

• Additive-only. Producers add keys, never rename or remove; readers tolerate unknown keys (schema-on-read ignores them until queried). This keeps ingest unbreakable.
• Versioned payloads. A schema_version field lets a reader branch on shape when a breaking change is unavoidable, so old and new coexist during a migration.
• Promote on demand. When a tail key turns hot, shred it into a column with an additive DDL and backfill from the raw payload — no reprocessing of the source events.

Indexing semi-structured data.

• Postgres. A GIN index on a JSONB column accelerates containment (@>) and existence (?) queries; an expression index on a specific extracted key (((payload->>'region'))) makes a single-key filter an index lookup.
• Snowflake. Automatic sub-columnarisation stores frequently-accessed paths in columnar form under the hood, so common path reads are pruned without manual indexes; clustering keys on extracted columns help large tables.
• BigQuery. Cluster and partition on extracted columns (shred first, then cluster) so filters prune; SEARCH/search indexes accelerate token lookups in JSON/text.

Cost — scan bytes, not blobs.

• Projection pruning. Reading a shredded column touches only that column's bytes; pulling one field out of a raw blob reads the entire payload per row. Shredding is a cost decision as much as a speed one.
• Partition/cluster pruning. Partition on a shredded timestamp and cluster on a shredded key so a filtered query skips most of the table before it ever parses JSON.
• Compression and layout. Columnar formats compress typed columns far better than an opaque JSON blob; shredding hot keys shrinks both scan bytes and storage.

The failure modes senior engineers pre-empt.

• Re-parsing on the hot path. Every dashboard re-parsing a blob to read one scalar is the number-one semi-structured cost sink. Mitigation: shred the hot keys once at ingest.
• Indexing the blob but filtering a key. A GIN index does not help a ->>'region' = 'EU' equality filter; an expression index does. Mitigation: match the index to the query shape.
• Unpruned scans. Partitioning/clustering on a JSON path expression (not a materialised column) often defeats pruning. Mitigation: shred the pruning key into a real column first.

Common interview probes on scaling semi-structured data.

• "Why is the JSON dashboard query slow?" — it re-parses the whole blob per row; shred the hot keys.
• "How do you index a JSONB key filter?" — expression index on the extracted key; GIN for containment.
• "How do you make BigQuery prune a JSON filter?" — shred the key to a column, then partition/cluster on it.
• "Shred everything or nothing?" — neither; shred the hot keys, keep the raw tail.

Worked example — shred a JSON event stream into a typed columnar table

Detailed explanation. The scaling workhorse: an ingest transform that reads raw JSON events and writes a typed columnar table with the hot keys as columns plus the raw payload retained. Build it so the daily query never parses JSON.

• The source. raw_events(payload VARIANT/JSONB) — the untouched stream.
• The target. Typed columns for the hot keys + a payload column for the tail.
• The win. The daily query scans typed columns and prunes; the tail stays reachable.

Question. Write an ingest transform that shreds the hot keys into a typed, partitioned table while keeping the raw payload, and show the cheap daily query it enables.

Input.

Key	Target column	Role
region, total_cents, created_at	typed columns	hot: filter/aggregate/partition
payload (whole event)	raw VARIANT/JSONB	cold tail
partition	created_at (shredded)	pruning

Code.

-- Snowflake: shred hot keys into a typed table; keep the raw VARIANT; cluster for pruning.
CREATE TABLE events.orders_shredded (
    order_id     number,
    region       string,
    total_cents  number,
    created_at   timestamp_ntz,
    payload      variant                     -- raw event retained for the cold tail
) CLUSTER BY (created_at, region);           -- pruning on shredded columns

INSERT INTO events.orders_shredded
SELECT
    payload:order_id::number,
    payload:region::string,
    payload:total_cents::number,
    payload:created_at::timestamp_ntz,
    payload                                  -- parse ONCE here, at ingest
FROM events.raw_events;

-- The daily query now scans TYPED COLUMNS and prunes by cluster key — no JSON parsing.
SELECT region, sum(total_cents) AS revenue
FROM events.orders_shredded
WHERE created_at >= dateadd(day, -1, current_timestamp)   -- prunes micro-partitions
GROUP BY region;

-- The cold tail still works, off the retained raw payload, only when asked.
SELECT payload:device.os::string AS os, count(*)
FROM events.orders_shredded
WHERE created_at >= dateadd(day, -7, current_timestamp)
GROUP BY 1;

Step-by-step explanation.

1. The ingest INSERT parses each hot key once (payload:region::string, etc.) and writes it to a typed column, so the expensive JSON parse happens a single time at load rather than on every downstream query.
2. The whole payload is retained in a VARIANT column, so shredding is lossless — the cold-tail device.os question still has its data even though device was never promoted.
3. CLUSTER BY (created_at, region) orders the micro-partitions on the shredded columns, so the daily query's WHERE created_at >= ... prunes away most of the table before scanning — pruning works because the key is a real column, not a JSON path.
4. The daily revenue query touches only region, total_cents, and created_at — three typed, compressed columns — so its scan bytes are a tiny fraction of the raw payload size, which is the actual cost win.
5. The cold-tail query still pays JSON parse cost, but only for the rare device.os breakdown and only within the pruned 7-day window — schema-on-read cost is confined to where flexibility is genuinely needed.

Output.

Query	Bytes scanned	JSON parsed
daily revenue (shredded)	3 typed columns, pruned	none
same on raw blob	whole payload/row	every row
cold-tail device.os	pruned window, blob	only that query
storage	typed + compressed + raw	raw only

Rule of thumb. Shred the hot keys into typed, clustered/partitioned columns at ingest and retain the raw payload for the tail. Parse once at load, then let every hot query scan a few compressed columns and prune — the cheapest way to serve high-volume semi-structured data.

Worked example — index a JSONB key so a filter is a lookup, not a scan

Detailed explanation. When you must filter on a key inside a JSONB blob (before shredding, or for a semi-hot key), the right index turns a full scan into a lookup. Contrast a GIN index (containment) with an expression index (single-key equality) and show which each query needs.

• The containment filter. payload @> '{"region":"EU"}' — a GIN index accelerates it.
• The equality filter. payload->>'region' = 'EU' — needs an expression index on the extracted key.
• The lesson. Match the index type to the query shape; a GIN does not help a plain ->> equality.

Question. Make two JSONB filters — a containment query and a scalar-equality query — index-backed instead of sequential scans.

Input.

Query shape	Index type	Why
payload @> '{...}'	GIN on payload	containment/existence
payload->>'region' = 'EU'	expression index on ((payload->>'region'))	scalar equality
payload ? 'device'	GIN on payload	key existence

Code.

-- GIN index: accelerates containment (@>) and existence (?) on the whole JSONB.
CREATE INDEX orders_payload_gin ON events.orders USING gin (payload);

-- Now this is index-backed, not a seq scan:
SELECT count(*) FROM events.orders
WHERE payload @> '{"region":"EU"}';           -- containment -> GIN

-- Expression index: a single extracted key for EQUALITY filters (GIN won't help here).
CREATE INDEX orders_region_expr ON events.orders (((payload->>'region')));

-- Now this scalar-equality filter is an index lookup:
SELECT count(*) FROM events.orders
WHERE payload->>'region' = 'EU';              -- equality on extracted key -> expr index

Step-by-step explanation.

1. The GIN index decomposes the JSONB into its keys and values, so a containment predicate payload @> '{"region":"EU"}' and an existence predicate payload ? 'device' can be answered from the index instead of scanning and parsing every row.
2. Crucially, a GIN index does not accelerate a plain payload->>'region' = 'EU' equality — that operator is not a containment, so Postgres would fall back to a sequential scan despite the GIN existing.
3. The expression index ((payload->>'region')) materialises the extracted scalar into a B-tree, so the equality filter becomes a normal index lookup — the same speed as if region were a real column, without shredding it.
4. The two indexes serve different query shapes: containment/existence → GIN; single-key equality/range → expression index. Building the wrong one leaves the query on a seq scan even though "an index exists."
5. The senior framing: an expression index is a lightweight alternative to shredding for a semi-hot key — you keep the key in the blob but make one filter fast — while genuinely hot keys still deserve a real shredded column plus partitioning.

Output.

Filter	Without index	With right index
@> '{"region":"EU"}'	seq scan + parse	GIN lookup
->>'region' = 'EU'	seq scan + parse	expression-index lookup
? 'device'	seq scan	GIN lookup
wrong index chosen	seq scan anyway	—

Rule of thumb. Match the index to the query shape: GIN on the JSONB for containment (@>) and existence (?), an expression index on the extracted key for scalar equality/range. An expression index makes a semi-hot key fast without shredding; a truly hot key still earns a real column.

Worked example — the precompute/shred cost decision under a query budget

**Detailed explanation. **The scaling decision is per-key economics: does re-parsing the blob per query cost more than shredding the key once? A stated query volume and payload size resolves it. Walk three keys through the bytes-scanned math and place each.

• Key A. region — filtered by 10k queries/day, whole table.
• Key B. device.os — queried 5 times/day, ad-hoc.
• Key C. experiments.* — churning shape, exploratory.

Question. For each key, decide shred-to-column, expression-index, or leave-raw, justified by query volume against scan cost.

Input.

Key	Query volume	Shape	Decision
A: region	10k/day	stable	shred to column + partition/cluster
B: device.os	5/day	stable-ish	leave raw (optionally expression index)
C: experiments.*	rare, ad-hoc	churning	leave raw (schema-on-read)

Code.

Bytes-scanned economics — shred when volume x blob-parse-cost > shred-once cost.

Key A (region)  10,000 queries/day, filters the whole table
  raw:    each query re-parses the FULL payload/row  -> 10k x (rows x blob_bytes)
  shred:  parse once at ingest; queries scan 1 typed column, pruned
  -> SHRED. High volume amortises the one-time parse instantly; pruning compounds it.

Key B (device.os)  5 queries/day, ad-hoc
  raw:    5 x (pruned rows x blob_bytes)  -> small absolute cost
  shred:  a column that's read 5 times/day barely earns its storage + DDL churn
  -> LEAVE RAW (add an expression index only if a filter on it gets slow).

Key C (experiments.*)  rare, shape still changing
  shred:  would break on the next producer change
  -> LEAVE RAW. Schema-on-read; never shred a churning shape.

Invariant: the hot path must not re-parse blobs; the cold path may.

-- Key A shredded + pruned (the 10k/day filter); B and C stay in the raw payload.
SELECT region, sum(total_cents)
FROM events.orders_shredded                 -- region is a typed, clustered column
WHERE created_at >= current_date - 1
GROUP BY region;                            -- scans 1 column, prunes; no JSON parse

Step-by-step explanation.

1. Key A's 10k/day volume means the one-time ingest parse is amortised almost immediately; leaving it raw would re-parse the whole payload 10,000 times a day, so shredding plus pruning is overwhelmingly cheaper.
2. Key B's 5/day volume is the opposite: the total scan cost of re-parsing the blob a handful of times is trivial, and a dedicated column would add storage and DDL churn for almost no benefit — leave it raw, and add an expression index only if a specific filter gets slow.
3. Key C's shape is still changing, so shredding it into a column would break ingest on the next producer change; schema-on-read is exactly the right tool for an exploratory, churning key.
4. The decision is a simple inequality: shred when query_volume × blob_parse_cost exceeds the one-time shred cost plus the column's storage — which is why volume, not importance, drives the call.
5. The invariant across all three: the hot path must never re-parse blobs (shred A), while the cold path is allowed to (B and C stay raw) — the same move as fronting a warehouse with a serving store, applied to JSON parsing.

Output.

Key	Layout	Optimises	Trades
A: region	shredded column + prune	hot-path cost	ingest parse (once)
B: device.os	raw (± expr index)	flexibility, low churn	occasional parse
C: experiments.*	raw (schema-on-read)	evolution safety	per-query parse
all	never re-parse on hot path	—	—

Rule of thumb. Shred a key when its query volume times the blob-parse cost beats the one-time shred cost — high-volume filter keys, always; rare or churning keys, never. Volume drives the decision, and the hot path must never re-parse a blob.

Senior interview question on scaling semi-structured data cost

A senior interviewer might ask: "Your JSON event table is huge and the dashboards are slow and expensive — every query re-parses a multi-kilobyte payload to read a couple of fields. Redesign it for cost: what you shred, how you index and prune, how you keep the design tolerant to new producer keys, and how you decide per key whether to promote it to a column — tied to query volume and bytes scanned."

Solution Using shredding, partition/cluster pruning, matched indexes, and additive evolution

-- 1. Shred hot keys into a typed, partitioned/clustered table; keep the raw payload.
CREATE TABLE events.orders_shredded (
    order_id     bigint,
    region       text,
    total_cents  bigint,
    created_at   timestamptz,
    payload      jsonb                       -- raw tail retained
) PARTITION BY RANGE (created_at);           -- prune by time before any parse

-- 2. Match indexes to query shape: expression index for equality, GIN for containment.
CREATE INDEX ON events.orders_shredded (region, created_at);        -- hot equality/range
CREATE INDEX ON events.orders_shredded USING gin (payload);         -- cold containment

-- 3. Hot path: scans typed columns, prunes partitions, NEVER parses JSON.
SELECT region, sum(total_cents) AS revenue
FROM events.orders_shredded
WHERE created_at >= now() - interval '1 day'   -- partition prune
GROUP BY region;                               -- typed-column scan

-- 4. Cold tail + evolution: new keys queryable with no migration; promote if they turn hot.
SELECT payload->'device'->>'os' AS os, count(*)
FROM events.orders_shredded
WHERE payload @> '{"device":{"os":"iOS"}}'     -- GIN-indexed containment
  AND created_at >= now() - interval '7 days'  -- still prunes
GROUP BY 1;
-- when device.os turns hot: ALTER TABLE ... ADD COLUMN device_os text; backfill from payload.

Step-by-step trace.

Problem	Fix	Effect
re-parse blob per query	shred hot keys to columns	hot query never parses JSON
full-table scans	partition/cluster on shredded time	prune before scan
slow key equality	expression/composite index	filter → index lookup
slow containment	GIN on payload	cold-tail lookup
new producer keys	keep raw payload, additive DDL	ingest never breaks
bytes scanned	typed compressed columns	scan bytes, not blobs

After the redesign, the hot dashboard query scans three typed, compressed columns and prunes by the partitioned created_at, never parsing JSON; equality filters on region hit a composite index; the cold tail is reachable via a GIN-indexed containment filter on the retained payload; new producer keys land in the blob without touching ingest and are promoted to columns only when volume justifies it. The table went from re-parsing kilobytes per row per query to scanning a few bytes and pruning.

Output:

Metric	Before (raw blob)	After (shredded + indexed)
Hot-query bytes scanned	full payload/row	3 typed columns, pruned
JSON parses on hot path	every row, every query	zero
Key-equality filter	seq scan	index lookup
New producer key	migration/breakage	absorbed by blob
Cold-tail filter	full scan	GIN containment
Storage	opaque blob	compressed columns + raw

Why this works — concept by concept:

• Shredding — parsing the hot keys once at ingest into typed columns moves the parse cost off the hot path, so high-volume queries scan a few compressed bytes instead of re-parsing a multi-kilobyte payload per row.
• Partition/cluster pruning — ordering the table on a shredded timestamp lets a filtered query skip most of the data before it reads anything, which only works because the pruning key is a real column, not a JSON path expression.
• Matched indexes — an expression/composite index answers scalar equality and range filters while a GIN answers containment and existence, so each query shape gets a lookup instead of a scan — mismatching them silently falls back to a seq scan.
• Additive evolution — retaining the raw payload means new producer keys are absorbed with no migration, queryable via schema-on-read, and promoted to a column only when volume justifies the cost.
• Cost — the hot path is O(projected columns) with pruning versus O(payload) re-parsing per row, and the cold path pays JSON cost only on rare, pruned queries. The eliminated cost is a full-payload parse on every dashboard load — bytes scanned, the actual warehouse bill, drops by orders of magnitude.

Data transformation
Topic — data-transformation
Data transformation problems on shredding JSON to columns

Practice →

Design
Topic — design
Design problems on storage layout and indexing for scale

Practice →

---

Cheat sheet — semi-structured data recipes

• The shape gap. Semi-structured data carries its own keys, nested objects, and repeated arrays inline; the whole job is deciding where the shape is resolved — schema-on-read (parse per query, flexible) or schema-on-write (shred on ingest, cheap to scan). At scale the answer is a hybrid: shred the hot keys, keep the raw blob for the tail.
• Schema-on-read vs schema-on-write. Read = raw payload parsed per query: flexible, additive-tolerant, expensive per read, late type failures. Write = typed columns parsed once at ingest: cheap scans, enforced types, early failures, needs migration for new keys. Decide per key by access frequency, not per table.
• VARIANT / JSON access syntax. Path-and-cast, four spellings: Snowflake v:key.sub::type; BigQuery JSON_VALUE(j,'$.key') (+ CAST) / JSON_QUERY for subtrees; Postgres j->>'key' (scalar) vs j->'key' (jsonb) vs j#>>'{a,b}' (path); Spark from_json(col, schema) / get_json_object / variant_get(v,'$.key','type'). Always take the scalar form and cast — the "as JSON" operator leaves quotes that break joins.
• Nested (struct) fields. A struct is a typed record; dot to the leaf (order.customer.address.city). Typed struct (BigQuery STRUCT, Spark StructType, Snowflake OBJECT) = checked, columnar, cast-free but rigid; untyped object in a VARIANT/JSONB = flexible, cast at the leaf. Access is a projection, not a join. Watch null-vs-missing.
• Repeated (array) fields. One row per element: BigQuery UNNEST(arr), Snowflake LATERAL FLATTEN(input => arr), Postgres jsonb_array_elements(arr), Spark explode(arr). Keep the parent (it's correlated/lateral, not a cross join), keep the index (WITH OFFSET / f.index / WITH ORDINALITY / posexplode), keep empty parents (outer variant: LEFT JOIN UNNEST, explode_outer).
• The fan-out bug. Flattening two arrays in the same scope makes |A| × |B| rows and inflates every sum silently. Fix: flatten each array in its own CTE, aggregate back to parent granularity, then join on the parent key.
• Schema evolution. Additive-only: producers add keys, never rename/remove; readers tolerate unknowns. New keys read NULL on old rows and never break existing queries. Use a schema_version for unavoidable breaking changes; promote a tail key to a column with additive DDL only when it turns hot.
• Shred-vs-keep-raw. Shred a key when it's hot, typed, filtered, partitioned, or indexed. Keep raw when it's rare, exploratory, or churning shape. Never shred every key (breaks on change) or leave every hot query re-parsing blobs.
• Indexing. Postgres: GIN for containment (@>) / existence (?); expression index ((payload->>'key')) for scalar equality/range — a GIN does not help ->> equality. Snowflake: automatic sub-columnarisation + clustering on extracted columns. BigQuery: shred, then partition/cluster on the extracted column; SEARCH for tokens.
• Cost — scan bytes, not blobs. Reading a shredded column touches its bytes; pulling one field from a raw blob reads the whole payload per row. Shred the hot keys, partition/cluster on shredded columns to prune, and confine schema-on-read parse cost to the cold tail. Bytes scanned is the bill.
• Cross-dialect portability. The model is identical everywhere — flexible column, path-and-cast reads, dot for nested, flatten for repeated, shred for scale. Only the syntax changes per engine, so design the model once and swap the spelling.

Frequently asked questions

What is semi-structured data and why is it different at scale?

Semi-structured data is data that carries its own schema inline — JSON documents, event blobs, and nested API responses with keys, nested objects, and repeated arrays inside each record — rather than conforming to a fixed grid of typed columns. It is different at scale because the shape has to be resolved somewhere: a flat question like "revenue by region" over a nested payload means either parsing and casting the blob on every query (schema-on-read) or shredding it once into typed columns on ingest (schema-on-write). At a billion records the choice is a cost decision as much as a modelling one — re-parsing a multi-kilobyte payload per row per query is the number-one cost sink — which is why mature designs shred the hot keys into columns and keep the raw payload only for the churning tail.

Schema-on-read or schema-on-write — which do I choose?

Choose per key, not per table. Schema-on-write (shredding a key into a typed column at ingest) is right for the hot, stable keys that most queries filter and aggregate on: you parse once, get type enforcement and partition pruning, and every downstream query is a cheap columnar scan. Schema-on-read (keeping the raw payload and parsing at query time) is right for the rare, exploratory, or still-churning keys: it needs no migration when a producer adds a field, at the cost of re-parsing on each read. The design that wins at scale is the hybrid — shred the hot core into columns and keep the whole raw payload in a VARIANT/JSONB column for the long tail — so common queries are fast and new keys never break ingest.

VARIANT vs JSON vs JSONB vs struct — what's the difference?

VARIANT (Snowflake), JSON (BigQuery), and JSONB (Postgres) are all untyped semi-structured columns that hold any document and defer typing to read time — you reach values with a path (v:key, JSON_VALUE, ->>) and cast at the leaf. A struct (BigQuery STRUCT, Spark StructType, Snowflake OBJECT) is a typed record whose sub-fields and their types are declared, so dot access returns a typed value with no cast — but adding a field is a schema change. The practical difference is where typing and flexibility sit: the untyped columns are additive-tolerant and cast-at-read; the typed struct is checked, columnar, and cast-free but rigid. JSONB also differs from Postgres JSON (text) by storing a decomposed binary form that supports indexing — always prefer JSONB for analytics.

How do I flatten a repeated (array) field without exploding my row count?

Flatten one array at a time, and treat the flatten as a correlated join to the row's own array: UNNEST (BigQuery), LATERAL FLATTEN (Snowflake), jsonb_array_elements (Postgres), or explode (Spark) each emit one row per element while keeping the parent columns. The row count only explodes when you flatten two independent arrays in the same scope — that produces |A| × |B| rows per parent and silently inflates every sum. The fix is to flatten each array in its own CTE, aggregate back to one row per parent, then join on the parent key. Also decide two things up front: capture the element index (WITH OFFSET / WITH ORDINALITY / posexplode) if order matters, and use the outer variant (LEFT JOIN UNNEST, explode_outer) if parents with empty arrays must survive.

Should I shred JSON into columns or keep the raw payload?

Both — that is the point. Shred the hot keys (the ones filtered, aggregated, partitioned, or indexed by most queries) into typed columns so the common path scans a few compressed bytes and prunes, and keep the whole raw payload in a VARIANT/JSONB column so the rare and churning keys are still reachable via schema-on-read with no migration. Decide each key by query volume against parse cost: a key filtered ten thousand times a day easily repays a one-time ingest parse, while a key queried five times a day barely earns a column. Never force every possible key into a rigid column (it breaks on the next producer change) and never leave every hot query re-parsing kilobyte blobs to read one scalar.

How do I keep semi-structured queries cheap and fast?

Move parse work from query time to ingest time for the hot path, and prune aggressively. Shred the hot keys into typed columns so queries scan bytes, not blobs; partition and cluster on those shredded columns (not on a JSON path expression) so a filtered query skips most of the table before parsing anything. Index to the query shape: in Postgres, a GIN index for containment (@>) and existence (?), an expression index on the extracted key for scalar equality; in Snowflake, lean on automatic sub-columnarisation and clustering; in BigQuery, shred then cluster. Confine schema-on-read cost to the cold tail, and never let a dashboard re-parse a multi-kilobyte payload to read a couple of fields — that single anti-pattern is the usual reason a JSON table is slow and expensive.

Practice on PipeCode

• Drill the JSON practice library → for the extraction, path-access, and VARIANT/JSONB problems that Snowflake, BigQuery, and Postgres make concrete.
• Rehearse flatten and reshape moves on the data transformation practice library → for the UNNEST/FLATTEN/explode, fan-out, and shred-to-columns scenarios where nested and repeated fields earn their keep.
• Sharpen the modelling axis with the system design practice library → for the schema-on-read-vs-write, storage-layout, indexing, and cost trade-offs a semi-structured table must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the parsing, type-handling, and nested/repeated-field patterns against real graded inputs — JSON extraction, struct access, array flattening, and cost-aware shredding.

Lock in semi-structured data muscle memory

Docs explain `VARIANT`, `JSON`, and `FLATTEN`. PipeCode drills explain the decision — when to shred a key versus keep it raw, when `schema-on-read` beats a rigid column, when a `flatten` needs its own CTE to dodge the fan-out, and when an index beats a scan. Pipecode.ai is Leetcode for Data Engineering — semi-structured practice tuned for the production trade-offs senior data engineers actually face.

Practice JSON problems →
Practice data transformation problems →