Time, Events & Semi-Structured Data

This chapter is about the kind of tables you meet once data comes from a real product: a stream of events (a user viewed something, checked out, asked for a refund), each stamped with a time and often carrying a little bundle of extra details in JSON.

Two things make these tables tricky. First, an event has two times — when it happened, and when your system actually received it — and those can be days apart. Second, the extra details are not neat columns; they are flexible, nested data you have to reach into. This chapter is about handling both safely.

The live labs run on SQLite, and the text points out where bigger databases (Postgres, BigQuery, Snowflake) spell things differently. The wording changes; the idea does not.

event_time

When the user or system action actually happened.

ingested_at

When your pipeline received the event (which can be later than when it happened).

JSON

A text way of storing data as name:value pairs, e.g. {"device":"ios","campaign":"winter"}. It can nest and hold lists.

semi-structured

Data that is not neat rows and columns. It has some structure (named fields, like JSON) but stays flexible and can be nested.

payload

The bundle of extra details, usually JSON, attached to an event.

late-arriving data

A record that reaches your pipeline after the report for its event_time has already been counted.

select
  event_id,
  event_name,
  event_time,
  ingested_at
from events
order by event_time, ingested_at;

To count events per day, you first chop each timestamp down to just its date — throwing away the exact hour and minute — and then group by that date. Sorting events into day-groups like this is called bucketing, and each group is a "bucket": think of it like sorting timestamped receipts into one pile per day and counting each pile.

The chopping is done by date(event_time), which keeps only the calendar day. So two events at 3am and 10pm on the same day both become the same date, and land in the same bucket.

The one thing to get right: bucket by the timestamp the question is actually about. "How many events happened each day" uses event_time (when it happened), not ingested_at (when your system received it).

select
  date(event_time) as event_day,
  count(*) as events
from events
group by date(event_time)
order by event_day;

event_time is when something happened; ingested_at is when your pipeline received it. When they differ across a day boundary, the event arrived late.

A simple CASE comparing the two dates labels each event on-time or late, which is the first step in any lateness audit.

This distinction is the root of most event-data confusion, so making it explicit in the query pays off.

select
  event_id,
  event_time,
  ingested_at,
  case
    when date(event_time) < date(ingested_at) then 'late'
    else 'on_time'
  end as arrival_bucket
from events
order by event_id;

Timestamps are usually stored in UTC, but reporting days are local. Converting to a local zone before truncating can move an event to a different calendar day.

An early-UTC event can belong to the previous local day. Shifting the timestamp before date() is what gets the day right.

This is a frequent source of “off by one day” discrepancies between teams using different zones.

select
  event_id,
  event_time,
  date(datetime(event_time, '-8 hours')) as pacific_event_day
from events
order by event_id;

Sequencing a user’s events needs a deterministic order. event_time alone can tie (two events in the same second), so add tie-breakers.

A robust order is event_time, then ingested_at, then a unique id. ROW_NUMBER over that order gives each user a stable event sequence.

Without tie-breakers, the same query can number tied events differently on different runs.

select
  user_id,
  event_name,
  event_time,
  row_number() over (
    partition by user_id
    order by event_time, ingested_at, event_id
  ) as event_sequence
from events
order by user_id, event_sequence;

An event's extra details often live inside JSON — the little {"device":"ios"} bundle. To use one of those fields in SQL, you pull it out into its own normal column that you can filter and group by.

You pull it out with json_extract(payload, '$.device'). The '$.device' part is an address into the JSON: the $ means "the whole JSON", and .device means "go to the field called device". Think of the JSON as a labelled set of drawers, and the address tells you which drawer to open.

Give every pulled-out value a clear alias (a name), because the json_extract(...) part is long and noisy — the alias is the tidy column name everyone downstream actually sees.

select
  event_id,
  json_extract(payload, '$.device') as device,
  json_extract(payload, '$.campaign') as campaign
from events
order by event_id;

A value you pull out of JSON is still a normal SQL value, so it needs the same care as any column. Two things can bite you: the field might be missing, and it might come back as the wrong data type.

Missing field: if the JSON does not contain that field, json_extract gives you NULL. Wrap it in COALESCE to swap that NULL for a clear default like 'unknown'. This is the same NULL handling from Chapter 1, just applied to fields that some events do not have.

Wrong type: values pulled from JSON often come back as text — for example when the JSON wrote the number in quotes, like "120". Text sorts and adds up differently from numbers, so when you need real number behaviour you convert it with CAST. (Converting a value from one type to another, like text to a number, is called casting.)

Whatever default you pick ('unknown', 0, false) is a real decision about your data, so make it on purpose, right there in the query.

select
  event_id,
  coalesce(json_extract(payload, '$.campaign'), 'unknown') as campaign_bucket
from events
order by event_id;

-- json_extract can hand back text like "120"; cast makes it a real number
with payloads(event_id, payload) as (
  select 1, '{"cart_value":"120"}' union all
  select 2, '{"cart_value":"90"}'  union all
  select 3, '{"cart_value":"1000"}'
)
select
  event_id,
  json_extract(payload, '$.cart_value')              as as_text,
  cast(json_extract(payload, '$.cart_value') as int) as as_number
from payloads
order by as_number desc;

Some payload fields are arrays — a checkout with several SKUs. To analyze the items, you expand the array into one row per element.

In SQLite, json_each(payload, '$.items') yields one row per array element, which you join to the event. Each warehouse has an equivalent unnest.

After expansion the grain changes: one row per item, not per event — the same grain reasoning as Chapter 2.

select
  e.event_id,
  item.value as sku
from events as e
join json_each(e.payload, '$.items') as item
where e.event_name = 'checkout'
order by e.event_id, sku;

Once you can label lateness, you can audit it: count how many events for each event day were ingested after that day.

A nonzero late count for a “closed” day means the day’s metrics could still change — exactly what a freshness or completeness check watches for.

Grouping the late events by their event day shows which days are still settling.

select
  date(event_time) as event_day,
  count(*) as late_events
from events
where date(ingested_at) > date(event_time)
group by date(event_time)
order by event_day;

Payload shape drifts over time: a key present today may be missing tomorrow. A lightweight guardrail counts missing keys by event type.

Summing a CASE that flags a NULL extraction gives a per-type count of payloads missing a field — a cheap data-quality probe.

Run it on a schedule and a sudden rise in missing keys flags an upstream change before it corrupts metrics.

select
  event_name,
  count(*) as events,
  sum(case when json_extract(payload, '$.device') is null then 1 else 0 end) as missing_device
from events
group by event_name
order by events desc, event_name;

These six problems practice event-time logic and semi-structured extraction in the live SQL workspace, on the events table you have been reading about.

Treat each as a small interview: name the timestamp and grain, write the query, run it to inspect the output, then submit for scored feedback.

select
  event_id,
  json_extract(payload, '$.device') as device
from events
order by event_id;