Section 5: Store the Facts, Precompute the Questions - Data Modeling

Executive Summary: Behind Section 4's endpoints sit three layers of data. The immutable raw event log is the source of truth. The session table holds facts that exist only once a visit is complete. The cubes are questions answered ahead of time, holding only additive counters and mergeable sketches. The rule holding it together is simple: store what can be summed or merged across any date range, and never store a ratio or a daily-user total. We key and partition each layer, walk the Lisbon hiker through all three, and add the real-time store and the version stamp that separates provisional numbers from final ones.

The interviewer taps the runReport box: "That answers in under two seconds. What is it reading?" "A database" is the weak answer. The strong one starts from access patterns: one firehose writer at ~1.2M hits/s, and three readers. Standard reports want small pre-shaped answers, explorations want flexible scans, and the batch job rebuilds whole days. No single shape serves all three, so we store the same facts at three grains.

Three Layers, Three Grains

Raw events and sessions keep event-level data for 2 or 14 months, cubes outlive them, and the real-time store forgets after 30 minutes.