Pipeline to Dashboardnihalsulaiman.com

Week 3 · Orchestrate with Dagster › Day 21

Day 21. Proof 3: hotel-dagster, a self-running hotel pipeline

⏱ 4h · build 150m · README 60m · review 30m🏷 hospitality (five hotels)Before: Days 15 to 20

By the end of today you can

  • Ship a partitioned, sensor-driven Dagster project with checks and a backfill story
  • Produce an on-the-books and pace dashboard from its outputs
  • Record a 2-minute demo video of the pipeline running

1. What you're shipping

hotel-dagster: SFTP (or folder) → raw → stg_bookings (latest version) → fct_bookings_daily (by created date) → otb_by_stay_date → pace_vs_last_year → report, all in Dagster with schedules, a file sensor, retries, asset checks and Docker Compose.

Acceptance criteria

# Requirement Proof
1 Asset graph with groups, owners, descriptions screenshot
2 Daily partitions by business date, idempotent replace writes rerun test
3 File sensor with run keys; .partial ignored demo video
4 Retry policy on transient sources run log screenshot
5 ≥5 asset checks, 2+ blocking screenshot of a blocked run
6 Backfill story: rule change → 60-day backfill, time and cost recorded README section
7 Dashboard: occupancy on the books for the next 90 days, pace vs same time last year, cancellations by channel screenshot
8 Docker Compose with Postgres storage, just up README

2. The hospitality metrics you'll show

  • On the books (OTB): confirmed room nights for a future stay date, as of today.
  • Occupancy OTB \(= \text{OTB room nights} / \text{rooms available}\).
  • Pace: OTB today vs OTB at the same lead time last year (with only 60 days of synthetic history, compare to 28 days ago instead and say so).
  • ADR \(= \text{room revenue} / \text{room nights sold}\); RevPAR \(= \text{room revenue} / \text{rooms available}\) \(= \text{Occ} \times \text{ADR}\).
  • Cancellation rate by channel: cancelled / created, by created date.
Maths and principle

Principle: ratios are non-additive. Compute them from sums at the level you report, never by averaging ratios.

Equation: group ADR \(= \dfrac{\sum_h R_h}{\sum_h N_h}\), not \(\dfrac{1}{H}\sum_h \dfrac{R_h}{N_h}\). They're equal only if every hotel sold the same number of nights.

Analogy: average speed over a trip is total distance over total time, not the mean of speeds per leg. Where it breaks: nowhere. This one always holds.

Try it: H1 sold 100 nights for $20,000; H2 sold 300 nights for $45,000. Group ADR? (65,000 / 400 = $162.50; the average of 200 and 150 = $175 is wrong.)

Your story

"Five hotels push booking extracts to SFTP overnight, sometimes late or half-uploaded. Bookings change status after the fact, so each file is a change feed, not a snapshot. I modelled it in Dagster as assets partitioned by business date with a sensor keyed on file names, blocking checks on duplicate bookings, and replace-only writes. When I changed the cancellation rule I backfilled 60 days in N minutes with no manual steps." Your backfill number goes in for N.

Build checklist

  1. Finish the graph and the pace asset.
  2. Dashboard in Streamlit (or Evidence) reading the marts.
  3. Record a 2-minute screen video: drop a file → sensor fires → run → check passes → dashboard updates. Upload it with the repo later (GitHub supports video in README via drag-and-drop in issues/PRs, or link to YouTube unlisted).
  4. README: summary + asset-graph screenshot, problem, architecture, run in 5 minutes, design decisions (partitions, sensors, checks, replace writes), backfill story, tests, next steps.

Check yourself

1. H1: 50 nights, $10,000. H2: 150 nights, $22,500. Group ADR?

2. What's the best evidence in a README that your pipeline is production-shaped?

My notes