Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Handling schema-on-read JSON in a model

Data modeling · Modern Modeling Approaches

Handling schema-on-read JSON in a model

Mediumdata-modeling-55
json-parsingsemi-structuredmedallion-pipelineschema-evolution

Question

Raw events arrive as JSON with fields added over time. How do you model them in the warehouse?

Solution

To model evolving semi-structured JSON, land the raw payload untouched in a bronze table using a native VARIANT or JSON column alongside ingestion metadata. In the silver layer, extract stable and frequently queried attributes into strongly typed columns while preserving the raw JSON payload for future backfills and schema discovery.

Landing raw JSON in bronze

Ingest incoming JSON records directly into an append-only bronze landing table:

CREATE TABLE bronze_events (
  event_id STRING,
  ingested_at TIMESTAMP,
  raw_payload VARIANT
);

Landing raw payloads without strict validation prevents pipeline crashes when upstream application engineers add new keys, modify nesting, or deploy unexpected attributes.

Schema promotion in silver

In the silver layer, transform known, stable business fields into typed relational columns (such as user_id INT64, amount NUMERIC, event_time TIMESTAMP). Retain the raw_payload column on the silver table so downstream models can extract emergent properties without re-ingesting raw bronze files. Use schema discovery queries or metadata monitors to alert the team when new top-level JSON keys appear consistently.

Protecting BI layers from variant drift

Never allow self-service BI tools or business analysts to query raw JSON fields directly. Direct JSON parsing in dashboards creates fragile calculations that break upon minor naming changes, prevents column statistics pruning, and degrades dashboard load times. Expose only curated, strongly typed gold tables or views where field names and data types are governed.

PreviousNext