Data modeling · Intermediate · 40 min
Booking metrics with changing property details
Choose table grain and preserve the history behind a metric.
Task
Design tables for a booking marketplace. Analysts need booking counts, first and last booking times, and booked value by property and city. A property can move between city classifications, and bookings can be cancelled or refunded after creation.
Requirements
- Support both current property details and the city recorded when a booking was made.
- Define booking, property, and change-history grain and keys.
- Explain which event date a metric uses and how cancellations or refunds change it.
- Show how you prevent duplicate ingestion and joins from multiplying booking value.
Dimensional modelingSCDFact grainMetric definitions
Loading exercise…