Design a Booking-Lifecycle Metrics Model
Model cumulative booking metrics together with first and latest booking times, and make historical dimension semantics an explicit design choice.
Problem statement
Design the analytical data model and update pipeline for cumulative booking metrics that must also expose the first and latest booking time for each reporting entity.
Clarify whether each report should use current dimension attributes or the attributes effective when a booking occurred, then choose and defend a dimension-history strategy; slowly changing dimensions are one option, not a required answer.
For this exercise, assume immutable booking lifecycle events can arrive late, be duplicated, or be corrected; analysts query daily snapshots and current cumulative totals by listing, host, market, and selected dimension attributes. Operational booking writes, payments, and ad hoc arbitrary joins are outside scope.
What to build
- The platform can idempotently ingest booking creation, cancellation, completion, and correction events.
- Analysts can query cumulative counts and value plus first and latest booking times by supported dimensions.
- Reports apply one documented and reproducible current-attribute or event-time dimension policy.
- Operators can recompute affected partitions after late events or corrections with auditable progress.
At scale
- For this exercise, assume one billion booking lifecycle events per day and 50 billion retained fact rows.
- For this exercise, common daily and cumulative reports should complete within five seconds at p95 for supported dimensions.
- For this exercise, events may arrive seven days late and explicit corrections may affect data up to 90 days old.
- For this exercise, daily snapshots are retained for seven years and every published result names its data and metric versions.