Data engineering
Bookings Star Schema with Price History
- Company
Airbnb- Job positions
- Data EngineerAnalytics EngineerBI Analyst
- Topics
- Data modelingStar schemaSlowly changing dimensionsPoint-in-time joinsSQL
The scenario
Airbnb: Model bookings as a star schema that keeps listing price history, so past revenue stays correct when hosts change prices.
Airbnb's finance analysts report booking revenue by city and room type. Hosts change nightly prices, and sometimes even room types, all the time. Today's reports join bookings to the latest listing details, so last quarter's revenue shifts every time a host edits a listing. You're a data engineer on the analytics platform team, and you've been asked to fix this in the data model.
Your task
Design and build a star schema that keeps listing history, so every booking is valued at the price and room type that applied on the day it was booked.
Instructions
- 1Explore the daily listing snapshots. Find what changes over time, which days are missing, and which listings are added or removed during the quarter.
- 2Build dim_listing as a type 2 slowly changing dimension: start a new version only when the room type or nightly price changes, with a surrogate key, valid_from, valid_to and is_current. A day with no snapshot is not a change.
- 3Build dim_host from the hosts file and a dim_date table that covers every date in the bookings.
- 4Build fact_bookings with one row per booking: the listing version key, date keys, nights, nightly_price_at_booking and booking_value, which is nights times that price.
- 5Report confirmed revenue by check-in month and room type from your model, and compare it with what joining bookings to the latest listing details would show.
- 6Add tests that every booking matches exactly one listing version and that a listing's versions never overlap.
- 7Document the model with a diagram and explain your design choices in a README.
Datasets
All files come in one download.
listing_snapshots.csv
One row per active listing per day, July 1 to September 30, 2025, as exported by a daily snapshot job.
2,585 rows · 6 columns · 107 KB
| Column | Type | Description |
|---|---|---|
| snapshot_date | date | Day the snapshot was taken. |
| listing_id | integer | The listing. |
| host_id | integer | The host who owns the listing. |
| city | text | City the listing is in. |
| room_type | text | Entire home, Private room or Shared room. |
| nightly_price | integer | Price per night on that day, in USD. |
Preview the first 5 rowsHide preview
| snapshot_date | listing_id | host_id | city | room_type | nightly_price |
|---|---|---|---|---|---|
| 2025-07-01 | 3001 | 201 | Lisbon | Entire home | 184 |
| 2025-07-02 | 3001 | 201 | Lisbon | Entire home | 184 |
| 2025-07-03 | 3001 | 201 | Lisbon | Entire home | 184 |
| 2025-07-04 | 3001 | 201 | Lisbon | Entire home | 184 |
| 2025-07-05 | 3001 | 201 | Lisbon | Entire home | 184 |
hosts.csv
One row per host.
12 rows · 4 columns · 1 KB
| Column | Type | Description |
|---|---|---|
| host_id | integer | Unique ID of the host. |
| host_name | text | Host's name. |
| joined_date | date | Date the host joined. |
| is_superhost | text | Whether the host is a Superhost: yes or no. |
Preview the first 5 rowsHide preview
| host_id | host_name | joined_date | is_superhost |
|---|---|---|---|
| 201 | Ana Costa | 2016-11-12 | yes |
| 202 | Pierre Martin | 2017-04-24 | no |
| 203 | Yuki Tanaka | 2022-02-13 | no |
| 204 | Rui Silva | 2019-08-10 | yes |
| 205 | Camille Dubois | 2023-05-05 | yes |
bookings.csv
One row per booking made during the quarter. The price is not stored on the booking.
900 rows · 7 columns · 52 KB
| Column | Type | Description |
|---|---|---|
| booking_id | integer | Unique ID of the booking. |
| listing_id | integer | The listing that was booked. |
| guest_id | integer | The guest who booked. |
| booked_at | date | Date the booking was made. |
| check_in | date | First night of the stay. |
| check_out | date | Day the guest leaves. |
| status | text | confirmed or canceled. |
Preview the first 5 rowsHide preview
| booking_id | listing_id | guest_id | booked_at | check_in | check_out | status |
|---|---|---|---|---|---|---|
| 70101 | 3001 | 5188 | 2025-07-01 | 2025-07-04 | 2025-07-05 | confirmed |
| 70142 | 3018 | 5508 | 2025-07-01 | 2025-07-28 | 2025-07-29 | confirmed |
| 70157 | 3026 | 5295 | 2025-07-01 | 2025-08-12 | 2025-08-14 | confirmed |
| 70210 | 3020 | 5553 | 2025-07-01 | 2025-07-03 | 2025-07-10 | confirmed |
| 70211 | 3004 | 5495 | 2025-07-01 | 2025-07-12 | 2025-07-17 | canceled |
Hints
A listing that stops appearing before the last snapshot day was removed. When every listing is missing on the same day, that is a gap in the snapshot job, not a change.
To find the listing version that applied when a booking was made, match on listing and take the latest version that started on or before the booking date.
pd.merge_asof(
bookings.sort_values("booked_at"),
versions.sort_values("valid_from"),
left_on="booked_at",
right_on="valid_from",
by="listing_id",
)Deliverable
A public GitHub repo with your model code (SQL, dbt or Python), CSV outputs of dim_listing and fact_bookings, a monthly revenue report, your tests, and a README with a schema diagram and your design choices.
When you're done, post your repo in the Solutions tab to share it with other learners.
What grading checks
Use this checklist to review your own work before you post and share it.
- Submitted GitHub repo is public and reachable.
- Repo contains model code in SQL, dbt or Python.
- dim_listing has the expected versions for every listing, with correct valid_from and valid_to dates.
- fact_bookings values every booking at the nightly price that applied on its booking date.
- Monthly confirmed revenue by room type matches the expected totals.
- Tests check that each booking matches one listing version and that versions never overlap.
- README includes a schema diagram and compares the result with joining to the latest listing details.