Model Cumulative Booking Metrics and First and Last Booking Times

Design a data model for booking metrics that supports cumulative reporting and the first and most recent booking times. Explain how the stored facts and derived data would serve these queries without confusing a changing dimension with a booking event. ### Requirements and Constraints For this exercise, assume every booking has a stable booking identifier, an account identifier, and a booking timestamp. For each account, reporting needs the cumulative number of bookings over time, the first booking time, and the most recent booking time. These are explicit practice choices for the entity and metric grain; no additional booking attributes or business rules are assumed. Choose the fact-table grain and any derived tables. Explain when an event fact, a periodic snapshot, an accumulating snapshot, or a slowly changing dimension would be appropriate. Include query and table optimizations for the stated reporting needs. ### Clarifying Questions - Does cumulative reporting mean a value as of each historical date, the current lifetime value, or both? - Do cancellations remove bookings from the metric, and how are corrections or duplicate deliveries represented? - Must reports describe an account using its attributes at booking time or its current attributes? - Can bookings arrive late or with corrected timestamps, and how quickly must past reports be revised? ```hint Separate events, summaries, and descriptive history One booking, one account-day total, and one version of an account's attributes describe different grains. Decide which question each row can answer. ``` ### What a Strong Answer Covers - A declared booking-fact grain and stable identifiers that prevent repeated ingestion from increasing counts. - Correct cumulative counts and first/last timestamps for the chosen cancellation and correction rules. - The distinction between a current per-account summary and historical snapshots or event-based reconstruction. - An explanation of when slowly changing dimensions are useful and why they do not replace the booking facts. - A plan for late arrivals or corrections that can change both historical counts and first/last booking times. - Indexing, partitioning, or aggregation choices tied to account and time-range queries rather than unspecified optimization claims. ### Follow-up Questions 1. What changes if a late-arriving booking has a timestamp earlier than an account's recorded first booking? 2. How would you reconstruct the cumulative count as of a historical date without scanning every account's entire history? 3. If an account changes a descriptive attribute, when would you join a booking to a historical dimension version instead of the current version?