IntermediateSQL · 25 min

Semi-Structured Data: Intermediate

Past basic extraction, semi-structured data engineering is a schema-evolution problem. JSON payloads change as the product evolves: new fields added, optional fields removed, nested structures reshaped. The downstream pipelines have to handle the evolution without breaking. This lesson covers schema-on-read vs schema-on-write, evolving JSON pipelines, array aggregations beyond UNNEST, and the flatten-vs-keep-nested design decision.

Choose between schema-on-read and schema-on-write based on the source's volatility

Build pipelines that handle JSON schema evolution without breaking downstream consumers

Aggregate arrays without UNNEST using ARRAY_AGG, ARRAY_LENGTH, ARRAY_SLICE

Decide when to flatten a JSON column to relational columns and when to keep it nested

UNNEST: Flattening Arrays to Rows

Daily Life

Interviews

Recognize semi-structured data questions: JSON payloads, nested arrays, event properties, API responses.

The question that recurs at this depth: 'we have an event stream where the payload schema changes monthly. Design the warehouse model that lets downstream analysts query it without breaking every time the schema changes.' This is not a JSON extraction question. It is a schema evolution question with extraction as a tactic. The candidate who walks the layers is the one who has built the JSON pipeline; the candidate who writes JSON_VALUE everywhere is the one who has not.

Three design questions this lesson covers

First: schema-on-read vs schema-on-write. Schema-on-read keeps the JSON in the bronze layer and extracts at query time; schema-on-write flattens to typed columns at ingestion. Each has a cost and a use case. Second: evolution handling. When the source schema changes, the pipeline either adapts automatically (schema-on-read absorbs the change) or breaks loudly (schema-on-write fails CI and forces a coordinated migration). Third: nested aggregation. Sometimes UNNEST is wrong; ARRAY_AGG, ARRAY_LENGTH, and per-array predicates without flattening are the right tool when the array structure is part of the query, not just data to aggregate.

You are being tested on intermediate semi-structured when you hear:

▸"the event schema changes monthly; how do we keep the warehouse stable"
▸"the payload has 5 levels of nesting; flatten or keep nested?"
▸"count events where the items array has more than 3 entries"
▸"the new field appeared 3 weeks ago; how do we backfill"
▸"schema-on-read or schema-on-write for this workload"

Nested Aggregation and Reconstruction

Daily Life

Interviews

Extract scalar values from nested JSON using dot-notation and bracket-notation path expressions.

The fundamental architectural decision for JSON pipelines: extract at query time (schema-on-read) or extract at ingest time (schema-on-write). Each is right for a different workload.

Schema-on-read

Keep the JSON as a VARIANT/JSONB column. Downstream queries extract paths at query time. New fields appear in the JSON without any pipeline change; downstream queries that reference them start working as soon as the data is there. Old queries continue to work unchanged.

Schema-on-write

At ingestion, extract the JSON into typed relational columns. Downstream queries reference the columns directly; no extraction syntax in consumer queries. New fields require a pipeline change (add a column, extract the new field). Schema changes are explicit: the pipeline either succeeds with the new column or fails. Right for stable schemas, audit-heavy reporting, and consumer-facing dashboards where read performance matters.

•Schema-on-read wins when

Source schema changes faster than pipeline updates can ship
Exploratory analytics where you don't know what fields will matter
Write throughput matters more than read latency
Audit raw events for replay or reprocessing later

✓Schema-on-write wins when

Schema is stable; new fields are rare events
Read latency matters; consumers query the data many times per write
Consumers prefer typed columns over JSON path syntax
Compliance or audit requires explicit column-level data contracts

The bronze/silver/gold pattern

Most production platforms run a hybrid. Bronze layer keeps the JSON as a VARIANT column (schema-on-read; ingest is flexible). Silver layer extracts known stable fields into typed columns (schema-on-write; downstream consumers query typed). Gold layer aggregates from silver. New fields appear in bronze automatically; the team adds them to silver when consumers need them. This is the layered design that gives both flexibility and read-time performance.

The bronze/silver/gold layered pattern:

▸Bronze: raw JSON column (schema-on-read; absorbs evolution)
▸Silver: typed columns extracted from bronze (schema-on-write; stable contract)
▸Gold: aggregations from silver
▸Consumers query gold; the platform team owns silver; bronze is the source-of-truth

The cost of each consumer doing extraction

If you go pure schema-on-read, every consumer team writes JSON path extraction in their queries. The same field gets extracted in 50 different dashboards. When the path changes (the schema reshapes), every consumer's query breaks. The silver layer pays the extraction cost once; consumers reference the typed column. The platform team owns the silver layer's schema; consumers depend on a stable contract. State this when designing: 'silver layer extracts; gold layer aggregates from silver. Consumers never write JSON path syntax against the bronze layer in production code.'

Lateral Joins Over JSON Arrays

Daily Life

Interviews

Use UNNEST/LATERAL FLATTEN to explode array columns into rows and join back to the parent.

JSON schemas evolve. The product team adds a new event type with a new payload shape; a field gets renamed; a nested object reshapes into a flatter structure. The pipeline has to handle the evolution without breaking. This section covers the migration patterns.

New field appears

The product team adds device.os_version to the events payload. The bronze layer absorbs it (schema-on-read; no change needed). The silver layer doesn't know about it yet; downstream consumers can't query it until the silver layer is updated. The migration: add the os_version column to the silver model, redeploy, backfill the silver layer from bronze for the historical period. The backfill is the operational cost; the bronze layer's flexibility is what makes it cheap (the historical data is already in bronze).

Field renamed

device.platform becomes device.platform_name. The bronze layer has both names over time (old rows have platform; new rows have platform_name). The silver layer's extraction needs to handle both. The pattern: COALESCE(payload:device.platform_name, payload:device.platform) AS platform. Eventually the old field is deprecated; the COALESCE simplifies once enough time has passed. State this when designing: 'COALESCE across the old and new names during the migration window; remove the old name after the deprecation period.'

Nested object reshapes

Sometimes the schema changes substantively: items used to be a flat array; now it's an object with metadata plus an array. The extraction logic for items[*].product_id has to handle both shapes. The pattern is a CASE expression: if the new shape is detected, extract the new way; otherwise extract the old way. Detection is usually by checking for the presence of the new wrapper key. State this when designing: 'shape changes need detection-and-branch logic in the extraction; the bronze layer has both shapes during the migration.'

Schema contract testing

dbt's schema tests can validate that expected fields exist and have expected types. expect_column_to_exist on the silver model catches the case where the silver layer's extraction stopped finding the field (because the source schema changed). The test failure is the alert; the platform team investigates whether the source change is intentional. Without the tests, schema drift surfaces as missing data in dashboards. State this when designing: 'every silver model has schema tests that assert the expected fields exist; the tests are the canary for source schema changes.'

At Anthropic in 2025, the data platform team standardized on a 'bronze raw + silver typed + gold reporting' architecture after a series of dashboard outages caused by upstream schema changes. The bronze layer ingested raw JSON; the silver layer extracted known fields with COALESCE across renamed columns; the gold layer aggregated from silver. New field additions were absorbed by bronze automatically; rename migrations were handled by silver's COALESCE during a documented deprecation window; substantive shape changes triggered a coordinated migration with dual-extraction logic in silver. The runbook line is 'consumers query gold; the platform team owns silver; bronze is the audit-replayable source-of-truth. JSON path extraction in consumer code is a CI lint failure.' The discipline turned schema evolution from a recurring outage into a planned change.

Typing and Casting Extracted Values

Daily Life

Interviews

Aggregate over unnested data and reconstruct arrays/structs using ARRAY_AGG and STRUCT.

UNNEST is the right tool when you need per-element rows. When you need per-row aggregates over an array (length, contains, sum of an array's numeric fields), UNNEST is overkill and slower. Engines provide array functions that operate on the array as a unit without flattening.

Array length without UNNEST


	SELECT *
	FROM events
	WHERE jsonb_array_length(payload -> 'items') > 3 ;


	SELECT *
	FROM events
	WHERE ARRAY_SIZE(payload : items) > 3 ;


	SELECT *
	FROM events
	WHERE ARRAY_LENGTH(JSON_QUERY_ARRAY(payload, '$.items')) > 3 ;

Each engine has a function that returns the array's length without flattening. The filter operates per-row; no UNNEST is needed. This is the right pattern for predicates over array properties (length, presence, sum). State this when designing: 'when the query asks about the array as a unit, use array functions; UNNEST is for per-element processing.'

Array contains and any/all


	SELECT *
	FROM events
	WHERE payload -> 'tags' @ > '"priority_user"' ;


	SELECT *
	FROM events
	WHERE ARRAY_CONTAINS('priority_user' :: VARIANT, payload : tags) ;


	SELECT *
	FROM events
	WHERE 'priority_user' IN UNNEST(JSON_QUERY_ARRAY(payload, '$.tags')) ;

Array containment queries 'does the array include this value' without flattening. Postgres's @> operator, Snowflake's ARRAY_CONTAINS, BigQuery's IN UNNEST all serve this role. The query is one filter, not a flatten-and-group. State this when writing: 'for membership tests, use array contains; for per-element aggregation, use UNNEST.'

Sum and aggregate over array elements

	SELECT
	event_id,
	REDUCE(payload : items, 0, (acc, item) -> acc + item : quantity :: INT) AS total_quantity
	FROM events ;


	SELECT
	event_id,
	(SELECT SUM((item ->> 'quantity') :: INT) FROM jsonb_array_elements(payload -> 'items') AS item) AS total_quantity
	FROM events ;

Some engines (Snowflake) support REDUCE-style functions that aggregate over array elements without flattening to rows. Others (Postgres) require a correlated subquery with UNNEST. The Snowflake form is cleaner; the Postgres form is portable. State this when designing: 'engine-specific array aggregation when supported; correlated UNNEST as the portable fallback.'

The trade-off

UNNEST plus aggregate is more verbose but portable. Array functions without UNNEST are cleaner but engine-specific. For one-off queries, use what's natural on the engine. For pipeline code that might migrate engines, prefer the UNNEST pattern with explicit aggregation. The trade-off is the same as elsewhere in semi-structured: cleaner-on-engine vs portable-across-engines.

Missing Keys and NULL Extraction

Daily Life

Interviews

Discuss with the interviewer when to parse JSON at query time vs materializing into typed columns during ETL.

The last design decision: when do you flatten a JSON column to typed columns, and when do you keep it nested? The choice depends on read patterns, schema stability, and consumer preferences. This section covers the trade-off and the closing.

Flatten when

Flatten the JSON column into typed columns when: the fields are stable (rarely change); consumers query them frequently (read amplification justifies the one-time extraction cost); the dashboard or BI tool prefers typed columns to JSON path syntax; compliance requires explicit column contracts. The flattened table is easier to query, easier to index, easier to optimize. The trade-off is the migration cost when the schema changes.

Keep nested when

Keep the JSON column nested when: the fields are unstable (frequent schema changes); consumers explore the data ad-hoc (don't know what fields they need yet); storage cost matters (flattening expands the table); the source is itself dynamic (different events have different payload shapes). Keeping nested defers the schema decision to query time, which is appropriate when the schema isn't stable enough to commit to.

•Flatten to typed columns

Read latency is lower; no extraction at query time
Typed columns are easier to index and optimize
Consumer code is simpler; no JSON path syntax
Schema changes are explicit; pipeline migrations needed

✓Keep nested as JSON column

Schema evolution is absorbed without pipeline change
Storage is more compact; no per-column overhead
Read pays the extraction cost per query
Consumers need to know the JSON path syntax

The partial-flatten pattern

Most production tables do a partial flatten: extract the high-traffic, stable fields into typed columns; keep the rest nested. The events table might have user_id, event_type, timestamp as typed columns (queried by every consumer) plus a remaining_payload JSONB column (rarely queried, but available for exploration). This is the practical middle ground; the trade-off is per-column. State this when designing: 'partial flatten of stable high-traffic fields; everything else stays in the JSON. The split is based on read frequency.'

The partial-flatten pattern:

▸Extract high-traffic stable fields into typed columns
▸Keep the remaining payload as a JSON column
▸Consumers query the typed columns by default
▸JSON column is available for exploration; not the production path

The closing thought

Intermediate semi-structured work is schema-evolution discipline plus the right tool per use case. Schema-on-read in bronze, schema-on-write in silver, partial-flatten when both worlds apply. Array functions for per-row operations, UNNEST for per-element. The patterns compose; the architecture is layered. The candidate who walks the layers is the candidate who has shipped through schema evolution; the candidate who writes JSON_VALUE in every consumer query is the one who will own the next outage.

Schema-on-read in bronzeSchema-on-write in silverPartial-flatten for the middle groundArray functions vs UNNEST by grainDocumentation as the contract

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at an event-heavy platform. The interviewer asks: 'We have an event stream where the payload schema changes monthly. Design the warehouse model that lets analysts query it without breaking every time the schema changes.'

You name the layers: 'Bronze keeps raw JSON; silver extracts stable fields into typed columns; gold aggregates from silver. Bronze absorbs schema evolution; silver is the contract.'

Migration patterns: 'New fields appear in bronze automatically; we add them to silver when consumers need them. Renames use COALESCE across old and new during a deprecation window. Shape changes need detection-and-branch logic in silver.'

Testing: 'Every silver model has dbt schema tests; missing-field failures are the canary for upstream changes.'

Follow-up: 'Some queries filter on items array length.' You say: 'Use ARRAY_SIZE or jsonb_array_length, not UNNEST. UNNEST is for per-element processing; array functions are for per-row predicates.'

Follow-up: 'Should we flatten the entire payload?' You say: 'Partial flatten: extract high-traffic stable fields into typed columns; keep the rest in a JSON column. The split is by read frequency.'

Closing: 'Schema-on-read in bronze, schema-on-write in silver, partial-flatten when both worlds apply. The layered design absorbs evolution while keeping reads fast.'

KEY TAKEAWAYS

Schema-on-read keeps JSON flexible at ingest and pushes extraction cost onto every consumer; schema-on-write pays extraction once and gives typed columns, so production platforms run both as bronze raw, silver typed, gold aggregated.

Consumers should never write JSON path syntax against bronze in production code, because the same field extracted in 50 dashboards means 50 broken queries when the path reshapes.

A renamed field is handled with COALESCE(payload:device.platform_name, payload:device.platform) through a documented deprecation window; a substantive shape change needs detection-and-branch logic because bronze holds both shapes during the migration.

Schema tests such as expect_column_to_exist on every silver model are the canary for upstream schema drift; without them the drift surfaces as missing data in a dashboard.

Use array functions when the query asks about the array as a unit (jsonb_array_length, ARRAY_SIZE, @>, ARRAY_CONTAINS) and save UNNEST for genuine per-element processing.

Partial flatten is the practical default: promote the stable high-traffic fields to typed columns, leave the rest nested for exploration, and split on read frequency.

JSON columns in SQL interviews separate data engineers from analysts

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: UNNEST: Flattening Arrays to Rows, Nested Aggregation and Reconstruction, Lateral Joins Over JSON Arrays, Typing and Casting Extracted Values, Missing Keys and NULL Extraction

Lesson Sections

UNNEST: Flattening Arrays to Rows (concepts: sqlJsonExtract)
Three design questions this lesson covers First: schema-on-read vs schema-on-write. Schema-on-read keeps the JSON in the bronze layer and extracts at query time; schema-on-write flattens to typed columns at ingestion. Each has a cost and a use case. Second: evolution handling. When the source schema changes, the pipeline either adapts automatically (schema-on-read absorbs the change) or breaks loudly (schema-on-write fails CI and forces a coordinated migration). Third: nested aggregation. Someti
Nested Aggregation and Reconstruction (concepts: sqlJsonExtract)
The fundamental architectural decision for JSON pipelines: extract at query time (schema-on-read) or extract at ingest time (schema-on-write). Each is right for a different workload. Schema-on-read Schema-on-write At ingestion, extract the JSON into typed relational columns. Downstream queries reference the columns directly; no extraction syntax in consumer queries. New fields require a pipeline change (add a column, extract the new field). Schema changes are explicit: the pipeline either succee
Lateral Joins Over JSON Arrays (concepts: sqlJsonExtract)
JSON schemas evolve. The product team adds a new event type with a new payload shape; a field gets renamed; a nested object reshapes into a flatter structure. The pipeline has to handle the evolution without breaking. This section covers the migration patterns. New field appears The product team adds device.os_version to the events payload. The bronze layer absorbs it (schema-on-read; no change needed). The silver layer doesn't know about it yet; downstream consumers can't query it until the sil
Typing and Casting Extracted Values (concepts: sqlArrayOps)
UNNEST is the right tool when you need per-element rows. When you need per-row aggregates over an array (length, contains, sum of an array's numeric fields), UNNEST is overkill and slower. Engines provide array functions that operate on the array as a unit without flattening. Array length without UNNEST Each engine has a function that returns the array's length without flattening. The filter operates per-row; no UNNEST is needed. This is the right pattern for predicates over array properties (le
Missing Keys and NULL Extraction (concepts: sqlJsonExtract)
The last design decision: when do you flatten a JSON column to typed columns, and when do you keep it nested? The choice depends on read patterns, schema stability, and consumer preferences. This section covers the trade-off and the closing. Flatten when Flatten the JSON column into typed columns when: the fields are stable (rarely change); consumers query them frequently (read amplification justifies the one-time extraction cost); the dashboard or BI tool prefers typed columns to JSON path synt