Capstone: JSON Events to a Sessionized, Incremental, DQ-Gated Medallion Pipeline

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard44 minlakehousemedallion Bronze/Silver/GoldJSON shreddedup ROW_NUMBER keep-latestsessionizationidempotency

The whole level in one build: Bronze JSON to a deduped, sessionized Silver to a reconciling Gold aggregate, idempotent end to end.

One build that ties the whole level together

This capstone is a small take-home: turn a stream of raw JSON events into a clean, sessionized, aggregated set of tables using a medallion architecture. Bronze is the raw landing zone, Silver is the cleaned and modeled layer, and Gold is the business aggregate. You will use most of the techniques from this level in one script.

The three layers

Bronze (raw_events). Landed as-is: a JSON payload, an event_id that can repeat (the same event delivered twice with different versions), and an ingest timestamp. You do not clean Bronze; you keep it exactly as received.
Silver (silver_events). The typed, deduplicated, sessionized clean layer. Shred event_time out of the JSON, keep one row per event_id (the latest version, with ROW_NUMBER keep-latest), and assign a session_id per user with the 30-minute-gap sessionization from earlier in this level.
Gold (gold_daily_metrics). The business aggregate: per day, the number of distinct sessions and the number of events. Gold reads only from Silver, so it inherits Silver's cleanliness.

The build, and idempotency

The whole thing is one script that rebuilds Silver and Gold from Bronze. Lead with DELETE FROM silver_events; and DELETE FROM gold_daily_metrics; so re-running it produces the same tables, the run-twice-same-result property you have enforced all level. The dedup is load-bearing: without ROW_NUMBER keep-latest, the duplicate event_id inflates the event count and can move a session boundary.

Interview nuance: the medallion layers are a naming and quality-gate discipline, not a product feature. Bronze is immutable raw, Silver is the contract every downstream reads, and Gold is the metric. When you narrate a pipeline in a system-design round, mapping your transforms onto Bronze, Silver, and Gold shows you know where cleaning, modeling, and aggregation each belong.

In the warehouse this differs. Each layer is a Delta or Iceberg table, and the transitions run as MERGE or CREATE TABLE AS jobs orchestrated by a workflow (Kafka to Flink sessionization to a dbt incremental model to a dbt test). You are writing the transform SQL that sits inside those layers.

Sample data for this example

CREATE TABLE raw_events (event_id INTEGER, user_id INTEGER, payload TEXT, version INTEGER, ingest_ts TEXT);
INSERT INTO raw_events VALUES
  (2, 100, '{"event_time":"2026-03-01 09:10:00"}', 1, '2026-03-01 09:11:00'),
  (2, 100, '{"event_time":"2026-03-01 09:10:00"}', 2, '2026-03-01 09:12:00');

Worked example (SQL)

-- rn = 1 keeps the latest version of a repeated event_id; the JSON event_time is shredded out.
SELECT event_id, version,
       json_extract(payload, '$.event_time') AS event_time,
       ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY version DESC) AS rn
FROM raw_events
ORDER BY event_id, version DESC;

Apply

Your turn

The task this lesson builds to.

Write a script that builds Silver and Gold from the Bronze raw_events log, over raw_events(event_id, user_id, payload, version, ingest_ts) (JSON payloads, with a duplicate event_id), an empty silver_events(event_id, user_id, event_time, session_id), and an empty gold_daily_metrics(day, session_count, event_count).

Build silver_events by shredding event_time from the JSON payload, keeping one row per event_id (the latest version), and assigning a session_id per user with a 30-minute inactivity gap. Then build gold_daily_metrics as, per day, the count of distinct sessions and the count of events. Lead with a DELETE from both target tables so the rebuild is idempotent.

3 hints and 4 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a script that rebuilds Silver and Gold when a late second batch has landed in Bronze with an out-of-order event and a duplicate event_id, over the same raw_events, silver_events, and gold_daily_metrics tables.

Use the same rebuild as before: shred and dedup to one row per event_id (latest version), sessionize per user by event time so the out-of-order event lands in the right session, and rebuild Gold from Silver. Lead with a DELETE from both targets so the late and duplicate rows are absorbed and re-running leaves the tables unchanged.

3 hints and 3 automated checks are waiting in the workspace.