Case 6 (Capstone) — Production Engineering: End-to-End Pipeline Platform

# Case 6 (Capstone) — Production Engineering: End-to-End Pipeline Platform ## Executive Context & Business Prompt This capstone represents your primary portfolio centerpiece for Senior Data Engineering technical interviews. You will design, build, test, and deploy a complete, production-grade Modern Data Stack platform from scratch. You are the Founding Data Engineer at **MetroMobility**, a fast-growing urban mobility startup. The leadership team needs an end-to-end analytical data platform to track vehicle trips, passenger revenues, driver payouts, and maintenance scheduling. Currently, data exists in three fragmented locations: - Public transit telemetry CSVs / APIs. - Operational database dumps of rider accounts. - Maintenance maintenance logs in cloud storage. CTO Executive Mandate: > *"Deliver a production GitHub repository demonstrating our full data engineering lifecycle. It must ingest source data with automated retries, stage and model data using dbt, orchestrate the entire DAG with Airflow or Dagster, enforce strict data quality gates in CI/CD, and publish a clean analytical mart."* --- ## Explicit Portfolio Deliverables 1. **Production GitHub Repository Structure:** - Modular layout: ``` ├── extraction/ # Python ingestion scripts with retry logic ├── dbt_transforms/ # dbt project (staging, intermediate, marts) ├── orchestration/ # Airflow DAGs / Dagster Assets ├── tests/ # Custom data quality & schema assertion tests ├── .github/workflows/ # CI/CD pipeline running tests on PR ├── docker-compose.yml # Local one-click reproducible environment └── README.md # Architecture diagram, runbook & documentation ``` 2. **End-to-End Orchestrated Pipeline DAG:** - Extracts raw data into cloud warehouse/lakehouse storage with metadata envelopes. - Executes layered dbt transformations (raw -> staging -> marts). - Enforces data quality assertions; halts execution on critical integrity failures. - Exposes clean analytical models (`fct_trips`, `dim_riders`, `fct_daily_revenue`). 3. **Production Runbook & Defense Document:** - Documents failure recovery, backfilling instructions, data dictionary, and cost trade-offs. --- ## 100-Point Grading Rubric | Criteria | Points | Passing Standard | |---|:---:|---| | **1. Architecture & Repository Engineering** | 25 pts | Production-grade repository structure with Docker containerization, clear separation of concerns, and clean dependency management. | | **2. Resilient Ingestion & Idempotency** | 25 pts | Ingestion pipeline handles rate limits, schema drift, and network retries; warehouse writes are strictly idempotent. | | **3. Dimensional Modeling & dbt Architecture** | 25 pts | Follows layered modeling best practices (staging views, dimensional marts); includes comprehensive documentation and column descriptions. | | **4. Orchestration, Observability & CI/CD** | 25 pts | Implements automated workflow scheduling with backfilling capabilities; integrates automated testing in GitHub Actions CI/CD. |