Hospitality EDW data model
Data Vault 2.0 raw vault for hospitality: hubs (guest/property/reservation/room/rate_plan), links (guest-reservation, reservation-room, reservation-rate, property-room), and satellites (guest PII, property, a reservation-status effectivity satellite, room, rate details, and a transactional folio-charge satellite on the guest-reservation link). Hash keys and load metadata are generated by the DDL layer.
Hospitality enterprise data warehouse modeled as a Data Vault 2.0 raw vault: guest, property, reservation, room, and rate plan hubs; booking and inventory links plus a transactional folio-charge satellite; and descriptive plus effectivity satellites — built for integrating PMS, central-reservation, and revenue-management source systems across a hotel portfolio with full historization and the guest-data lineage (GDPR) that makes Data Vault attractive for multi-property hospitality. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 100
- columns
- 14
- relationships
- 9
- 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
Hubs hold only hospitality business keys
Separating guest_id, property_code, and reservation_id into hubs lets PMS, central reservations, loyalty, and revenue-management source systems attach independently without reworking existing structures — the core agility of Data Vault.
Effectivity satellite for reservation status
A reservation moves through booked → confirmed → checked_in → checked_out (or cancelled/no_show); an effectivity satellite records the valid period of each state for accurate as-of occupancy and pickup reporting.
Transactional folio-charge satellite on the guest-reservation link
Folio charges are immutable financial postings tied to a specific guest-reservation relationship; carrying them as a satellite on the link keeps the stay and its charges together for revenue audit.
GDPR-driven masking on the guest satellite
Passport number is full-masked and email is hashed so the governance report flags the most sensitive guest identifiers; load_date + record_source give the queryable lineage needed for GDPR access and erasure requests across properties.
Tables in the Hospitality EDW 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.
Hubs (5)
hub_guest
Grain: one row per unique hub_guest business key
| Column | Type | Keys |
|---|---|---|
| guest_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_property
Grain: one row per unique hub_property business key
| Column | Type | Keys |
|---|---|---|
| property_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_reservation
Grain: one row per unique hub_reservation business key
| Column | Type | Keys |
|---|---|---|
| reservation_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_room
Grain: one row per unique hub_room business key
| Column | Type | Keys |
|---|---|---|
| room_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_rate_plan
Grain: one row per unique hub_rate_plan business key
| Column | Type | Keys |
|---|---|---|
| rate_plan_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_guest_reservation
Grain: one row per lnk_guest_reservation relationship
Which guest holds which reservation.
| Column | Type | Keys |
|---|---|---|
| guest_hk | binary | |
| reservation_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_reservation_room
Grain: one row per lnk_reservation_room relationship
Which room is assigned to which reservation.
| Column | Type | Keys |
|---|---|---|
| reservation_hk | binary | |
| room_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_reservation_rate
Grain: one row per lnk_reservation_rate relationship
Which rate plan a reservation was booked under.
| Column | Type | Keys |
|---|---|---|
| reservation_hk | binary | |
| rate_plan_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_property_room
Grain: one row per lnk_property_room relationship
Which property a room belongs to.
| Column | Type | Keys |
|---|---|---|
| property_hk | binary | |
| room_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_guest_details
Grain: one row per guest_hk per change
Guest descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| guest_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| home_address | varchar | PII |
| passport_number | varchar | PII |
| date_of_birth | date | PII |
| loyalty_tier | varchar | |
| nationality | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_property_details
Grain: one row per property_hk per change
Property descriptive history.
| Column | Type | Keys |
|---|---|---|
| property_hk | binary | PK |
| load_date | timestamp | PK |
| property_name | varchar | |
| brand | varchar | |
| star_rating | smallint | |
| city | varchar | |
| country | varchar | |
| total_rooms | integer | |
| timezone | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_reservation_status
Grain: one row per reservation per status change
Effectivity satellite tracking reservation state transitions over time.
| Column | Type | Keys |
|---|---|---|
| reservation_hk | binary | PK |
| load_date | timestamp | PK |
| reservation_state | varchar | |
| check_in_date | date | |
| check_out_date | date | |
| channel | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_room_details
Grain: one row per room_hk per change
Room descriptive history.
| Column | Type | Keys |
|---|---|---|
| room_hk | binary | PK |
| load_date | timestamp | PK |
| room_number | varchar | |
| room_type | varchar | |
| floor | integer | |
| bed_configuration | varchar | |
| max_occupancy | smallint | |
| is_accessible | boolean | |
| housekeeping_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_rate_details
Grain: one row per rate_plan_hk per change
Rate plan descriptive history.
| Column | Type | Keys |
|---|---|---|
| rate_plan_hk | binary | PK |
| load_date | timestamp | PK |
| rate_plan_name | varchar | |
| rate_category | varchar | |
| base_rate | decimal | |
| currency | varchar | |
| is_refundable | boolean | |
| includes_breakfast | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_folio_charge
Grain: one row per folio charge on a guest reservation
Transactional folio-charge satellite on the guest-reservation link.
| Column | Type | Keys |
|---|---|---|
| guest_reservation_hk | binary | PK |
| load_date | timestamp | PK |
| charge_id | varchar | |
| charge_ts | timestamp | |
| charge_type | varchar | |
| amount | decimal | |
| currency | varchar | |
| posted_by | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_units | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_guest_reservation.guest_hk → hub_guest (many-to-one)
- lnk_guest_reservation.reservation_hk → hub_reservation (many-to-one)
- lnk_reservation_room.reservation_hk → hub_reservation (many-to-one)
- lnk_reservation_room.room_hk → hub_room (many-to-one)
- lnk_reservation_rate.reservation_hk → hub_reservation (many-to-one)
- lnk_reservation_rate.rate_plan_hk → hub_rate_plan (many-to-one)
- lnk_property_room.property_hk → hub_property (many-to-one)
- lnk_property_room.room_hk → hub_room (many-to-one)
- sat_guest_details.guest_hk → hub_guest (many-to-one)
- sat_property_details.property_hk → hub_property (many-to-one)
- sat_reservation_status.reservation_hk → hub_reservation (many-to-one)
- sat_room_details.room_hk → hub_room (many-to-one)
- sat_rate_details.rate_plan_hk → hub_rate_plan (many-to-one)
- sat_folio_charge.guest_reservation_hk → lnk_guest_reservation (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.