NYC Taxi Analytics Pipeline
From trips to trusted metrics.
The starting point.
Raw taxi trips contain missing zone IDs, negative fares, and inconsistent records. Reliable borough-level analytics starts with an explicit quality audit and clear metric definitions.
What I built.
I integrated my SQL, dbt, dashboard and Airflow coursework into a local PostgreSQL pipeline. I added transactional monthly ingestion from public TLC files, a trip-level mart, daily metric reconciliation and payment filters in Streamlit.
Follow the flow.
Load a public TLC month or synthetic fixture into PostgreSQL. Replace only the requested month in one transaction, preserving other months.
A local January 2024 public-data load produced 56,549 raw records and 56,367 accepted trips, passing all 14 dbt data checks. The sample workflow also verifies repeat loads, rollback and dashboard filters.
Decisions that matter.
- 01
Define the grain before the metric.
The trip mart and daily borough mart have distinct grains. Reconciliation checks verify that daily trip and fare totals agree with the accepted trips they summarize.
- 02
Use the logical month, not today.
Airflow derives the input partition from its data interval. Transactional month replacement and constrained concurrency make retries and historical-month loads predictable.
- 03
Keep the sample reproducible.
A small synthetic fixture exercises rejected records, repeat loads, rollback and payment filters without cloud credentials. Separate saved public-data results show the same pipeline running against a TLC month.
The work is open.
Implementation, decisions, and review history.
Built with
SQL / Python / PostgreSQL / dbt / dbt_utils / Metabase / Streamlit / Airflow / Docker / GitHub Actions