Batch Data Pipeline: NYC Taxi to PostgreSQL and BigQuery
Data Engineer (solo project) · ~2 months (Sep–Oct 2026) · 2026
A batch pipeline that loads NYC Taxi data into PostgreSQL and BigQuery with MD5 + MERGE deduplication, orchestrated by Kestra and provisioned with Terraform.
Pipeline flow
Source
CSV files (NYC Taxi)
gzipped CSV
Ingest
Python CLI (pandas, SQLAlchemy)
loads in 100,000-row chunks; containerized with Docker + Docker Compose (with pgAdmin)
Ingest
Kestra
monthly cron-scheduled flow
Transform
SQL (MERGE)
deduplication with MD5 row hash + MERGE
Storage
PostgreSQL
initial load target
Storage
BigQuery
via Cloud Storage; bucket & dataset provisioned with Terraform
TL;DR
- A parameterized Python CLI (pandas, SQLAlchemy) loads gzipped CSVs into PostgreSQL in 100,000-row chunks, containerized with Docker Compose alongside pgAdmin.
- Kestra flows load taxi data into PostgreSQL and BigQuery (via Cloud Storage) with MD5 + MERGE deduplication and a monthly cron schedule; Terraform for the bucket and dataset.
Metrics
- 100,000rows per chunk when loading into PostgreSQL
Stack
- Python
- Docker
- Kestra
- Terraform
- GCP
Problem
[situation before the project / motivation — not in the CV, fill in yourself]
Architecture & technical decisions
A parameterized Python CLI for chunked loading; orchestration with Kestra (monthly cron); cross-run deduplication using MD5 row hash + MERGE; infrastructure (GCS bucket, BigQuery dataset) managed with Terraform. [add rationale + trade-offs]
Data model
[short explanation + optional ERD at public/images/projects/nyc-taxi-batch-pipeline-erd.png]
Data quality & monitoring
[what tests, where alerts go, SLA — fill in if any]
Code snippet
# [5–20 lines of the most representative Python/SQL — pick from the repo]
[why this code is interesting]
Challenge
[the hardest problem and how you solved it — fill in yourself]
Result
NYC Taxi data is loaded into PostgreSQL and BigQuery idempotently (no duplicates) on a monthly schedule.
What I learned
[1–2 lessons — fill in yourself]