Hotel booking snowflake schema
This hotel booking snowflake schema records each stay in FactStay with its nights and room revenue. The fact links to check-in date, guest and room. Guest details connect to country, while room details connect through room type to property. These separate levels make it possible to report revenue by property, room type, guest country or arrival period without repeating descriptive values throughout the fact table. The model is useful for hospitality groups that maintain shared property and room-type hierarchies across locations. It also keeps local room details separate from the cross-property categories used for management reporting. This improves consistency when properties share a central reporting model across a portfolio.
Open it in the AI editor with a prompt pre-filled — keep what works, change what doesn't.
Scenario
Hotel stay reporting
Key decisions
- Stay grain: Store one row per guest stay.
- Guest geography: Keep country outside the guest row.
- Room hierarchy: Separate room, type and property.
When to reuse this
Use this for occupancy and revenue reporting across multiple properties.
Frequently asked questions
What is the fact table grain?
Why separate room type from room?
Can this support property reporting?
Tweak it with chat, export PNG/SVG, or fork it for your own use case.