Project · Intermediate · about 14 hours
Orchestrated monthly pipeline with Airflow
Schedule, validate and backfill a year of taxi data with Airflow 3, with retries for late files and alerts on failure.
Skills:Apache Airflow and orchestration, Data cleaning and quality, ETL and data pipelines, Python
Part of: Data Engineer
Data and licences
- NYC TLC Yellow Taxi Trip Records (2025) ↗NYC Open Data public data (TLC terms of use, no warranty)
Brief
The TLC publishes each month's trips a few weeks late. The team wants a pipeline that picks up new months automatically, retries until the file appears, and can rebuild history after a logic change.
What to build
A DAG with the TaskFlow API, scheduled monthly, that processes the previous month.
Retries with a long delay for late files; a validation task with thresholds.
Load into a DuckDB database (or a warehouse) by replacing the month's partition.
Backfill six months with
airflow backfill create, rerun two of them, and show that counts do not change.A failure callback that writes to a chat webhook or a log, and a runbook.
Deliverables
dags/, a README with backfill evidence,RUNBOOK.md
How it is graded
Reviewers run airflow dags test for two dates and a small backfill. Duplicated rows after a rerun cap the score.
Stretch
Split into two DAGs connected by an asset, so the marts rebuild when new trips land.
Deliverables
- An Airflow DAG with extract, validate, transform and load tasks
- A backfill of at least six months with evidence of idempotent reruns
- A failure alert and a runbook
How a reviewer grades it
| Criterion | Weight | What good looks like |
|---|---|---|
| Tasks are small, idempotent and derive their period from the logical date | 35% | |
| Validation stops bad data before load | 20% | |
| Backfill run safely with limited parallelism and verified counts | 25% | |
| Alerting and runbook | 20% |