Skip to content
Back to challenges
Grain and KeysConcept Challengeintermediate20 min

Clickstream event grain

Design a clickstream fact where one row means one observed page event rather than one browser session.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

fact-table-grain

The primary modeling idea this problem reinforces.

Requirements

4

Business needs the model must satisfy.

Read the concept guide: Fact table grain
Scenario

A product analytics team wants to debug funnels, page latency, and experiment exposure. The raw stream has page_view, click, and impression events, but the existing dashboard rolls everything up to sessions and hides the sequence that product managers need to inspect.

Why this matters

Clickstream models are tempting to aggregate too early. If the fact grain is one session, analysts cannot reconstruct step-level behavior, isolate broken pages, or join experiments to the exact event where a user saw a variant.

Requirements
  • Create a page-event fact where one row represents one clickstream event.
  • Keep the source event id on the fact as a degenerate dimension.
  • Add an event timestamp and a key constraint that protects event-level grain.
  • Connect events to page and visitor context through explicit relationships.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Clickstream event starter workspace

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.