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

Assignment Instructions

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

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

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).
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.