Case 1 — Build an End-to-End Pipeline (Ingest → dbt → Dashboard)

# Case 1 — End-to-End Pipeline ## The brief Build a complete data pipeline that delivers a daily dashboard. Source: a public dataset (NYC taxi rides or similar). Destination: a Looker Studio / Metabase / Grafana dashboard. In between: ingestion, warehouse loading, dbt transformations. ## The setup Use what you have access to: - **Warehouse**: BigQuery free tier, Snowflake trial, or Postgres locally. - **Orchestrator**: Airflow (Docker Compose), Prefect, or just cron + Python for simplicity. - **dbt**: dbt Core (CLI) with a profile pointing at your warehouse. - **Dashboard**: Looker Studio (free, BigQuery), Metabase (open source), or Grafana. ## The task Source data: NYC TLC Taxi Trip data (publicly available, multiple months). - Trips: pickup/dropoff datetime, location, fare, tip, passenger_count. - Lookup: zone names for locations. ### Step 1: Ingestion (30 min) Write a Python script (or Airbyte connector) that: - Downloads one month of trip data (Parquet file, ~50MB). - Loads it into your warehouse as `raw.taxi_trips`. - Loads the zone lookup CSV as `raw.taxi_zones`. Make it idempotent: re-running for the same month replaces, doesn't duplicate. ### Step 2: dbt models (30 min) Build: - `stg_trips`: cleaned trips (rename columns, cast types, filter junk rows). - `stg_zones`: cleaned zone lookup. - `fct_trips`: trips joined with pickup/dropoff zone names. - `mart_daily_trips`: daily aggregate (trips, revenue, avg distance, avg tip %). Add dbt tests: - not_null + unique on `trip_id` in stg_trips. - not_null on fare_amount in fct_trips. - accepted_values on payment_type. Run dbt: `dbt run && dbt test`. ### Step 3: Orchestration (15 min) Schedule the pipeline. Use Airflow, Prefect, or cron + Python: - Daily at 3am: ingestion → dbt run → dbt test. - Failure alerting to Slack or email. ### Step 4: Dashboard (15 min) Build a dashboard with: - Line chart: daily trips over the month. - Bar chart: top 10 pickup zones by trip count. - Big-number tile: total revenue today. - Table: top zones by avg tip %. Save it; share the link. ## Deliverables - GitHub repo with the pipeline code (ingestion script, dbt project, orchestration DAG). - README with setup instructions. - Screenshot of the dashboard. - A `MISTAKES.md` listing 3 things that broke during the build and how you fixed them. ## Rubric - Pipeline is idempotent (re-runs cleanly). - dbt models are layered correctly (staging → marts). - Tests exist and pass. - Orchestration is in place; not run manually. - Dashboard is real (not a screenshot of mock data). ## What you'll learn This is the "rite of passage" build that every DE has done. Doing it once teaches you: - Real ingestion pain (vendor APIs are messy; schemas change). - dbt's role and limits. - Orchestration is harder than it looks. - Dashboards reveal data bugs. - End-to-end debugging when something breaks.