Use Case — YellowLine NYC

Fictional yellow-taxi operator · NYC TLC-inspired data

Story Animation

Client

YellowLine NYC — a fictional NYC yellow-taxi operator (inspired by TLC public trip data, not a real company). They operate across all five boroughs and compete with ride-hail on price, wait time, and driver earnings.

Business challenge

Trip volume is stable, but revenue per mile and driver utilization vary by hour, borough, and route. Leadership suspects they are:

  • Over-supplying taxis in low-demand zones at the wrong times
  • Under-pricing or mis-allocating fleet during peak windows
  • Losing visibility because reports are built manually from spreadsheets

They need an analytics platform their SQL-heavy internal team can maintain after MHP leaves.

Success criteria

# Criterion How the training proves it
1 Ingest TLC trip data reliably Bronze layer in all three tool tracks
2 Clean, enriched trip records Silver with quality rules + zone lookup
3 Answer Priya’s five business questions via Gold KPIs 12 aggregated kpi_* tables (identical KPIs across platforms)
4 Executive dashboard Power BI on Gold tables
5 Team can extend pipelines Snowflake SQL + dbt maintainability story
6 Documented lineage and quality dbt docs, tests, lineage graph
7 Informed platform choice Module 7 open discussion

Data source

Azure ADLS2: mhpdeworkshopsa / nyc-taxi-data
├── raw/trips/          → yellow_tripdata_{YYYY}-{MM}.parquet  (one workshop month)
└── raw/lookup/         → taxi_zone_lookup.csv

The workshop uses one TLC Parquet month under raw/trips/ (filename pattern yellow_tripdata_{YYYY}-{MM}.parquet). Pipelines read the whole folder — do not hardcode a filename in notebooks.

Technical detail: Architecture · Data model

Priya’s five KPI questions

  1. When are our peak revenue hours?
  2. Which pickup zones drive the most trips?
  3. Which routes are most popular?
  4. How does revenue vary by borough and distance band?
  5. Are we losing money on bad data (null fares, zero-distance trips)?

Priya asks five business questions. Bob materialises twelve Gold kpi_* tables so each question has the right grain (hour, zone, route, borough, band, quality). Trainees build Priya’s five-page Power BI dashboard in Exercise: Power BI.

# Priya’s question Gold tables Power BI page How the pipeline answers it
1 Peak revenue hours kpi_revenue_by_hour, kpi_trips_by_hour Time Analysis · Revenue & Payments Hourly revenue totals; peak_status on trips-by-hour
2 Top pickup zones kpi_top_pickup_zones Map Top 20 zones by trip count
3 Popular routes kpi_popular_routes Map Top 20 zone-to-zone pairs
4 Revenue by borough and distance band kpi_borough_analysis, kpi_distance_bands Map · Trip Efficiency Borough pairs + five distance bands
5 Bad data skewing decisions Silver quality rules + kpi_data_quality_metrics Overview Silver drops null fares, zero-distance trips, invalid passengers; Gold reports records_removed, retention_rate_pct, data_quality_score

Full table definitions: Data model — KPI catalog · Priya’s five questions.

The Three Constraints

TipThree constraints to weigh all day

Marcus will judge every recommendation against three orthogonal dimensions. Keep them in your head from the story introduction to Module 7 — Elena revisits them in the wrap-up.

Constraint Marcus’s question Where it lands
Cost Can YellowLine NYC afford this in year 3? Module 7 Round 2 Card B
Performance Fast enough for live dispatch later? Module 8 streaming
Compliance Audit-ready lineage by Q3? Module 4 dbt; Module 7 Card D

By end of day you should answer all three — not just “which tool is best?”

Continue

  1. Meet the cast in Characters & Roles
  2. Start Module 1: DE Fundamentals