Hospitality / Hotel Analytics data model
Kimball warehouse for hospitality: an accumulating reservation snapshot (booked -> stayed -> checked out), a daily occupancy periodic snapshot for ADR/RevPAR, and transaction-grain folio revenue postings over conformed guest/property/room-type/rate-plan/channel dimensions. Guest PII is classified and masked.
Hotel and resort analytics covering the reservation lifecycle, daily occupancy and RevPAR snapshots, and folio revenue postings over conformed guest, property, room-type, rate-plan, and channel dimensions — a Kimball star schema with SCD2 guest history and regulated guest PII (loyalty number, contact details) masked. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 114
- columns
- 14
- relationships
- 8
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for reservations
A reservation has well-defined milestones (booking, arrival, departure, cancellation) and a slowly-evolving status — an accumulating snapshot captures lifecycle lag and conversion.
Daily occupancy snapshot separate from reservations
Occupancy, ADR, and RevPAR are semi-additive daily metrics needed at property/room-type grain independent of individual reservations.
Folio revenue as a transaction-grain fact
Revenue postings arrive continuously across revenue centers (room, F&B, spa); a transaction grain supports flexible revenue analysis and reconciliation against the folio.
SCD2 on dim_guest
Loyalty tier and VIP status change over time; SCD2 preserves the guest attributes that were in effect at each stay.
Masking on loyalty number and contact PII
GDPR requires restricting access to personal identifiers; guest email is hashed and contact fields are partially masked.
Tables in the Hospitality / Hotel Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_reservation
Grain: one row per reservation (accumulating snapshot booked->stayed->checked_out)
Accumulating snapshot tracking the reservation lifecycle milestones.
| Column | Type | Keys |
|---|---|---|
| reservation_key | bigint | PK |
| guest_key | bigint | |
| property_key | bigint | |
| room_type_key | bigint | |
| rate_plan_key | bigint | |
| channel_key | bigint | |
| booking_date_key | bigint | |
| arrival_date_key | bigint | |
| departure_date_key | bigint | |
| cancellation_date_key | bigint | |
| confirmation_number | varchar | |
| reservation_status | varchar | |
| nights_booked | smallint | |
| nights_stayed | smallint | |
| adults_count | smallint | |
| children_count | smallint | |
| average_daily_rate | decimal | |
| total_room_revenue | decimal | |
| taxes_amount | decimal | |
| deposit_amount | decimal | |
| lead_time_days | integer | |
| is_cancelled | boolean | |
| is_no_show | boolean |
fct_occupancy_snapshot
Grain: one row per property per room type per day (periodic snapshot)
Daily occupancy snapshot — semi-additive ratios for RevPAR reporting.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| property_key | bigint | |
| room_type_key | bigint | |
| rooms_available | integer | |
| rooms_sold | integer | |
| rooms_out_of_order | integer | |
| guest_count | integer | |
| occupancy_rate | decimal | |
| adr | decimal | |
| rev_par | decimal | |
| room_revenue | decimal |
fct_folio_revenue
Grain: one row per folio revenue posting line (transaction)
Transaction-grain folio postings across all revenue centers.
| Column | Type | Keys |
|---|---|---|
| folio_line_key | bigint | PK |
| date_key | bigint | |
| guest_key | bigint | |
| property_key | bigint | |
| reservation_key | bigint | |
| revenue_type_key | bigint | |
| folio_number | varchar | |
| posting_description | varchar | |
| quantity | integer | |
| unit_price | decimal | |
| gross_amount | decimal | |
| discount_amount | decimal | |
| tax_amount | decimal | |
| net_amount | decimal | |
| is_comp | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | varchar | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean | |
| season | varchar |
dim_guest
Grain: one row per guest per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| guest_key | bigint | PK |
| guest_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| date_of_birth | date | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| nationality | varchar | |
| loyalty_tier | varchar | |
| loyalty_number | varchar | PII |
| vip_flag | boolean | |
| marketing_opt_in | boolean |
dim_property
Grain: one row per hotel property
| Column | Type | Keys |
|---|---|---|
| property_key | bigint | PK |
| property_id | varchar | BK |
| property_name | varchar | |
| brand | varchar | |
| property_type | varchar | |
| star_rating | smallint | |
| total_rooms | integer | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| region | varchar | |
| timezone | varchar |
dim_room_type
Grain: one row per room type
| Column | Type | Keys |
|---|---|---|
| room_type_key | bigint | PK |
| room_type_id | varchar | BK |
| room_type_name | varchar | |
| bed_configuration | varchar | |
| max_occupancy | smallint | |
| square_feet | integer | |
| view_type | varchar | |
| is_smoking | boolean | |
| is_accessible | boolean |
dim_rate_plan
Grain: one row per rate plan
| Column | Type | Keys |
|---|---|---|
| rate_plan_key | bigint | PK |
| rate_plan_id | varchar | BK |
| rate_plan_name | varchar | |
| rate_category | varchar | |
| is_refundable | boolean | |
| includes_breakfast | boolean | |
| min_stay_nights | smallint | |
| advance_purchase_days | smallint |
Lookup tables (2)
dim_channel
Grain: one row per booking channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar | |
| channel_group | varchar | |
| commission_rate | decimal |
dim_revenue_type
Grain: one row per folio revenue type
| Column | Type | Keys |
|---|---|---|
| revenue_type_key | bigint | PK |
| revenue_type_code | varchar | BK |
| revenue_type_name | varchar | |
| revenue_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_reservation.guest_key → dim_guest (many-to-one)
- fct_reservation.property_key → dim_property (many-to-one)
- fct_reservation.room_type_key → dim_room_type (many-to-one)
- fct_reservation.rate_plan_key → dim_rate_plan (many-to-one)
- fct_reservation.channel_key → dim_channel (many-to-one)
- fct_reservation.booking_date_key → dim_date (many-to-one)
- fct_occupancy_snapshot.date_key → dim_date (many-to-one)
- fct_occupancy_snapshot.property_key → dim_property (many-to-one)
- fct_occupancy_snapshot.room_type_key → dim_room_type (many-to-one)
- fct_folio_revenue.date_key → dim_date (many-to-one)
- fct_folio_revenue.guest_key → dim_guest (many-to-one)
- fct_folio_revenue.property_key → dim_property (many-to-one)
- fct_folio_revenue.reservation_key → fct_reservation (many-to-one)
- fct_folio_revenue.revenue_type_key → dim_revenue_type (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.