The Gaps Between Clicks

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
mid
Asked in
Google

Problem

We run a consumer web and mobile product that emits a high-volume clickstream: every page view, tap, and scroll lands as an event tied to a device, and a visitor is usually anonymous for a while before they sign in. Analysts need visit-level analysis (how long a session runs, how many events it holds, where it drops off) as well as daily engagement rolled up by page and device, where a session is activity with no more than a thirty-minute gap between consecutive events. Design a model that serves both the visit-level questions and the daily rollups, keeps a person's anonymous and signed-in activity connected, and credits each visit to whoever held the device during the window when that activity happened, since a shared device can pass from one person to another and each of them owns it only for a bounded stretch of time.

A visit is a story with a start and an end. Draw the model that finds where one ends and who was really there.

Worked solution and explanation

Why this problem exists in real interviews

This is sessionization plus identity stitching dressed up as clickstream analytics. Anyone can draw an event table with a user_id and a session_id column. The two things that separate candidates: recognizing that a session has its own grain (it is derived, not given), and that identity is time-bounded, not a current-state foreign key. Miss the second one and the first time a shared device gets reassigned, every old visit silently re-attributes to the new owner and your retention numbers lie.

Break down the requirements

Step 1: Declare the event grain

fact_clickstream_events is one row per raw event: a tap, a scroll, a page view, tied to a device and a page at a timestamp. This is the atomic substrate for journeys and drop-off funnels. Keep it lean; it carries billions of rows a day.

Step 2: Derive the session, do not query for it

Order events per device by timestamp and open a new session whenever the gap to the previous event exceeds thirty minutes. That logic runs once in the pipeline and produces a session_key. Recomputing this window on every dashboard query is the slow, fragile path that breaks the moment events arrive out of order.

Step 3: Promote the session to its own fact

fact_sessions is one row per session: start and end timestamps, duration, event count, entry page, exit page. Visit-level metrics are now additive and the table is orders of magnitude smaller than the event fact, so routine dashboards never touch the raw stream.

Step 4: Stitch identity with a dated map

identity_map links device_key to user_key with valid_from and valid_to. At processing time you resolve the user for each event using the window it falls in, then snapshot that user_key onto the session rows. A device handed to a new user opens a new mapping row; old activity keeps its original owner.

The reference model

Two facts over conformed dimensions. The session_key is the spine: it is the primary key of fact_sessions and a foreign key on every event, so the same gap rule binds both grains. The resolved user_key is snapshotted onto the session fact rather than joined live, which is what keeps anonymous and signed-in history connected without letting reassignment rewrite the past.

`session_key` binds the two grains; `user_key` is snapshotted onto `fact_sessions` from a time-bounded `identity_map`, so device reassignment never rewrites history.
  • dim_date: date_key INT PK, full_date DATE, day_of_week INT, is_weekend BOOLEAN
  • dim_pages: page_key INT PK, path TEXT, page_type TEXT, section TEXT
  • dim_devices: device_key BIGINT PK, device_id TEXT, platform TEXT, os_version TEXT, app_version TEXT
  • dim_users: user_key BIGINT PK, signup_at TIMESTAMP, account_tier TEXT, home_country TEXT
  • identity_map: map_id BIGINT PK, device_key BIGINT FK, user_key BIGINT FK, valid_from TIMESTAMP, valid_to TIMESTAMP
  • fact_sessions: session_key BIGINT PK, device_key BIGINT FK, user_key BIGINT FK, start_date_key INT FK, entry_page_key INT FK, exit_page_key INT FK, started_at TIMESTAMP, ended_at TIMESTAMP, event_count INT, duration_seconds INT
  • fact_clickstream_events: event_id BIGINT PK, session_key BIGINT FK, device_key BIGINT FK, user_key BIGINT FK, page_key INT FK, date_key INT FK, event_type TEXT, event_ts TIMESTAMP
  • fact_clickstream_events references dim_date
  • fact_clickstream_events references dim_pages
  • fact_clickstream_events references dim_devices
  • fact_clickstream_events references dim_users
  • fact_clickstream_events references fact_sessions
  • fact_sessions references dim_devices
  • fact_sessions references dim_users
  • fact_sessions references dim_date
  • fact_sessions references dim_pages
  • identity_map references dim_devices
  • identity_map references dim_users

Trade-offs and alternatives

Session as its own fact
Visit metrics are precomputed and additive. * Dashboards read a small table * Entry and exit pages are first-class * One extra ETL step to maintain
Session as columns on the event fact
No second table to build. * Every visit metric is a window over billions of rows * Out-of-order events corrupt the boundary mid-query * Daily session counts require COUNT(DISTINCT) on the hot path

Common follow-up questions