Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Event Log Pipeline

Build a clickstream pipeline: land raw events, de-duplicate, sessionize with window functions, compute funnels and retention, and check data quality in SQL.

Most BigQuery data in the real world is not neat order tables. It is event logs: page views, clicks, searches, purchases, each a row with a user, a name and a timestamp. This project builds a small pipeline over such a log: raw events in, clean events, sessions, a conversion funnel, retention, and data-quality checks out.

The pipeline runs in SQLite here, so every query is runnable. The design (partitioning, clustering, scheduling) is written as BigQuery DDL, read-only, and a note marks where BigQuery's SQL spells something differently. Build the sample log first, then each stage reads the one before it.

The architecture

flowchart LR
  A["raw_events<br/>(append-only, partitioned)"] --> B["events_clean<br/>(deduplicated)"]
  B --> C["sessions"]
  C --> D["funnel_daily"]
  B --> E["retention"]
  B --> Q["quality_checks"]

Each stage is a table rebuilt from the previous one, so a bug fix is one rerun.

The raw table is append-only and keeps everything, including duplicates, as it arrived:

SQL
CREATE TABLE analytics.raw_events (
  event_id    STRING,
  user_id     STRING,
  event_name  STRING,
  event_ts    TIMESTAMP,
  received_at TIMESTAMP,
  properties  JSON
)
PARTITION BY DATE(event_ts)
CLUSTER BY event_name, user_id
OPTIONS (partition_expiration_days = 400, require_partition_filter = TRUE);

Stage 0: a sample log

This sample has two users, a duplicate event and a few sessions. Create it once, then reuse the statements as a base for the next stages.

SQL Raw events with a duplicate

Press Run (or Ctrl + Enter) to see the result here.

Ten rows but nine distinct events: the first quality problem.

Stage 1: clean and de-duplicate

Keep one row per event_id, the first one received. In BigQuery you can write the same logic with QUALIFY ROW_NUMBER() OVER (...) = 1, which filters without a subquery.

SQL events_clean

Press Run (or Ctrl + Enter) to see the result here.

Stage 2: sessionize

A session is a run of events by one user where no gap exceeds 30 minutes. The classic method has three steps: look at the previous event time with LAG, mark a new session when the gap is over 30 minutes (or there is no previous event), then take a running sum of the marks to number the sessions.

SQL Sessions with LAG and a running sum

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery use TIMESTAMP_DIFF(event_ts, LAG(event_ts) OVER (...), MINUTE) for the gap. User u2 has two sessions because the 10:03 to 14:00 gap is over 30 minutes.

Stage 3: a conversion funnel

A funnel counts how many users reach each step: view, then add to cart, then purchase. Per user, check whether each event ever happened that day:

SQL Daily funnel

Press Run (or Ctrl + Enter) to see the result here.

On 2026-03-03 there are no cart users, so the percentage is NULL; in BigQuery use SAFE_DIVIDE(a, b) to get the same result without a division error.

Stage 4: retention

Retention asks: of the users who showed up on day 0, how many returned on a later day? Compute each user's first day, then see who has events on another day.

SQL Returning users

Press Run (or Ctrl + Enter) to see the result here.

Stage 5: data-quality checks

A pipeline you do not test will break silently. Put checks in a table and fail the run when any is bad (Dataform assertions or a scheduled query that raises an error do this). Typical checks: duplicate keys, nulls, impossible values, events from the future, and freshness.

Python Quality checks as a table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In BigQuery, a scheduled script can end with ASSERT (SELECT COUNT(*) FROM ...) = 0 AS 'duplicate event ids found';, which stops the job and sends an alert.

Stage 6: schedule and protect

Wire the stages together: a Pub/Sub BigQuery subscription (or batch loads) feeds raw_events; a Dataform or scheduled query rebuilds events_clean for the last three days (to absorb late events), then sessions and the marts; quality checks run before publishing. Set require_partition_filter, a 400-day expiration on the raw table, and budget alerts. Hide raw user_id behind a policy tag or a masked view (security).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you start a new session in the sessionization method shown?

    LAG gives the previous event time; a large gap (or NULL) marks a new session, and a running sum numbers them.
  2. 2. Why rebuild the last three days of events_clean instead of only today?

    Events arrive late, so a small rolling window keeps recent days correct while staying cheap.
  3. 3. Which BigQuery function avoids division-by-zero errors in a conversion rate?

    SAFE_DIVIDE returns NULL when the denominator is zero instead of raising an error.
  4. 4. What is the purpose of data-quality assertions in a pipeline?

    Checks catch duplicates, nulls and stale data before dashboards and models consume them.

Summary

  • Keep an append-only raw table, then build clean, session, funnel and retention tables from it.
  • De-duplicate with ROW_NUMBER() (or QUALIFY), sessionize with LAG plus a running sum.
  • Funnels and retention are per-user flags aggregated by day.
  • Rebuild a rolling window for late data, and gate publishing on quality checks.
  • Partition, cluster, expire and mask data as in the earlier lessons.

Next: Practice: BigQuery SQL Exercises