The Anonymous Majority

A medium 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
medium
Seniority
L5

Problem

We run a large e-commerce marketplace where shoppers browse on web and mobile, often switching devices before they buy. Capture every interaction as a raw event that records the page, the specific on-page element touched, and the kind of action it was, then roll those events up into per-session summaries that trace the path to purchase. At 600 million events a day kept for 13 months, the raw layer has to stay prunable by day. Design the data model.

Millions of clicks, mostly anonymous.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate can design a two-level fact model and reason about identity resolution at scale. Six hundred million events per day is a partitioning signal; cross-device stitching is a dimension signal; sessionization is a windowing signal. Missing any one of the three is a flag.

Break down the requirements

Step 1: Declare two grains

fact_events is one row per raw event (click, page view, product interaction). It carries event_type, the event timestamp, a page_key, and an element_id for the specific on-page element touched. fact_sessions is one row per session, derived from events with a 30-minute inactivity window.

Step 2: Partition raw events by event_date

At 600M rows per day, pruning by date is the difference between a ten-second query and a ten-hour query. event_date is the partition key; any downstream job that skips the predicate is a flag.

Step 3: Resolve identity on dim_devices

Anonymous traffic arrives keyed by device_id. Once the user logs in, the device inherits a user_id. Store that mapping with first_seen_at and resolved_user_key on dim_devices so retroactive attribution can run.

Step 4: Sessionize in a batch job

The session boundary is defined by a 30-minute gap. Compute sessions downstream of the raw events using a window function and write the result to fact_sessions. Do not try to assign sessions at ingest time; late events would invalidate the boundary.

Step 5: Conform dim_users and dim_pages

Both fact tables share the user and page dimensions, which keeps pre-login and post-login reporting on the same page taxonomy.

The solution

Below is one conceptually sound approach: a two-level fact star partitioned by date. The grain split anchors the storage and query plan.

`fact_events` partitioned by `event_date`; `fact_sessions` derived from events with a 30-minute inactivity window.
  • dim_users: user_key BIGINT PK, user_id TEXT, signup_date DATE, country TEXT
  • dim_devices: device_key BIGINT PK, device_id TEXT, platform TEXT, resolved_user_key BIGINT FK, first_seen_at TIMESTAMP
  • dim_pages: page_key INT PK, url_path TEXT, page_type TEXT, section TEXT
  • fact_events: event_id BIGINT PK, event_date DATE, user_key BIGINT FK, device_key BIGINT FK, page_key INT FK, element_id TEXT, event_type TEXT, event_ts TIMESTAMP
  • fact_sessions: session_id BIGINT PK, user_key BIGINT FK, device_key BIGINT FK, session_start TIMESTAMP, session_end TIMESTAMP, event_count INT, had_purchase BOOLEAN
  • dim_users references dim_devices
  • dim_users references fact_events
  • dim_devices references fact_events
  • dim_pages references fact_events
  • dim_users references fact_sessions
  • dim_devices references fact_sessions

The analysis pattern

Purchase funnel by session
SELECT
    DATE(s.session_start) AS day,
    COUNT(*) AS sessions,
    SUM(CASE WHEN s.had_purchase THEN 1 ELSE 0 END) AS converting_sessions,
    AVG(s.event_count) AS avg_events_per_session
FROM fact_sessions s
JOIN dim_users u ON u.user_key = s.user_key
WHERE s.session_start >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(s.session_start)
ORDER BY day

Trade-offs and alternatives

Two-level star with partitioning
Cheap reads on derived sessions, date partitioning prunes the raw table. Cost: two ETL jobs to maintain, and sessions lag raw events by the batch cadence.
Single event table with session window at query time
Only one table; compute sessions on the fly with window functions. Cost: every dashboard pays the windowing cost, and at 600M rows per day the query tax compounds.

Common follow-up questions