Property Booking Platform

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L6

Problem

We're building the analytics warehouse for a property booking marketplace. Hosts list properties, guests book stays, and we need to support revenue reporting, host performance, and guest satisfaction analysis. Design the data model.

Five-star listing. Three-star reality.

Worked solution and explanation

Why this problem exists in real interviews

Hospitality models are a Type 2 SCD probe hidden inside a booking question. Property attributes like price, amenities, and host ownership change over time, and analysts must be able to reconstruct what a listing looked like at the moment the booking was made. The signal is whether the candidate reaches for an effective-dated dimension rather than trusting point-in-time joins on natural keys.

Break down the requirements

Step 1: Declare the booking grain

fact_bookings is one row per confirmed reservation. Check-in and check-out are separate date_sk FKs so occupancy and nights can be computed either way.

Step 2: Type 2 the property dimension

dim_properties carries valid_from, valid_to, and is_current. A rate change, amenity change, or host transfer creates a new row with a new property_sk, preserving the old version for historical bookings.

Step 3: Version-lock the fact

fact_bookings.property_sk points at the version of the property that was live at booking time. This is what lets "revenue per 4-bedroom listing" stay stable even after 4-bedrooms get reclassified.

Step 4: Separate reviews from bookings

fact_reviews is its own fact with a one-to-one (or one-to-zero) relationship to bookings. Reviews arrive late, have their own grain, and their own ingestion SLA.

Step 5: Add a conformed geography dimension

Lat/lon and city descriptors live in dim_geography. Multiple facts (bookings, reviews, search impressions) share it so "supply in Barcelona" has one definition.

The solution

Below is one conceptually sound design. The Type 2 dim_properties is the anchor; it guarantees that historical bookings report the attributes the guest actually saw.

`dim_properties` is Type 2 SCD; `fact_bookings`.property_sk locks each booking to the property version at reservation time
  • dim_guests: guest_sk BIGINT PK, guest_id TEXT, signup_date DATE, country TEXT
  • dim_hosts: host_sk BIGINT PK, host_id TEXT, superhost_status BOOLEAN, onboarded_at TIMESTAMP
  • dim_properties: property_sk BIGINT PK, property_id TEXT, host_sk BIGINT FK, property_type TEXT, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN
  • dim_dates: date_sk INT PK, calendar_date DATE, season TEXT
  • dim_geography: geography_sk BIGINT PK, city TEXT, country TEXT, lat DECIMAL, lon DECIMAL
  • fact_bookings: booking_id BIGINT PK, guest_sk BIGINT FK, property_sk BIGINT FK, checkin_date_sk INT FK, checkout_date_sk INT FK, geography_sk BIGINT FK, nights INT, total_amount DECIMAL
  • fact_reviews: review_id BIGINT PK, booking_id BIGINT FK, guest_sk BIGINT FK, property_sk BIGINT FK, rating INT, submitted_at TIMESTAMP
  • dim_hosts references dim_properties
  • dim_guests references fact_bookings
  • dim_properties references fact_bookings
  • dim_dates references fact_bookings
  • dim_geography references fact_bookings
  • fact_bookings references fact_reviews
  • dim_guests references fact_reviews
  • dim_properties references fact_reviews

The analysis pattern

Revenue per property version with current-version lookup
SELECT
    p.property_id,
    p.property_type,
    SUM(b.total_amount) AS historical_revenue,
    COUNT(*) AS bookings,
    AVG(r.rating) AS avg_rating,
    BOOL_OR(p.is_current) AS includes_current
FROM fact_bookings b
JOIN dim_properties p ON p.property_sk = b.property_sk
LEFT JOIN fact_reviews r ON r.booking_id = b.booking_id
JOIN dim_dates d ON d.date_sk = b.checkin_date_sk
WHERE d.calendar_date >= '2025-01-01'
GROUP BY p.property_id, p.property_type
ORDER BY historical_revenue DESC

Trade-offs and alternatives

Type 2 SCD on properties
Historical accuracy, clean surrogate joins, versioned reviews. Cost: more dimension rows, ETL complexity around change detection, separate logic to find the current version.
Type 1 with a snapshot fact
Simpler dimension, always-current attributes. Cost: historical bookings silently report today's attributes, and any audit of past revenue loses fidelity unless a parallel snapshot table is maintained.

Common follow-up questions