NYC Taxi Data Engineering Pipeline

Loads 1M+ NYC yellow taxi trip records into a containerized PostgreSQL database, with GCP infrastructure provisioned via Terraform.

What this does

• Ingests NYC TLC yellow taxi data (January 2021) into PostgreSQL
• Runs Postgres and pgAdmin in Docker Compose on a shared network
• Provisions a GCS bucket and BigQuery dataset with Terraform
• Runs SQL analysis on the ingested data — joins, aggregations, NULL checks

Tech stack

Python (pandas) · PostgreSQL · Docker Compose · pgAdmin · Terraform · GCS · BigQuery

Running it

1. Provision GCP infrastructure

cd terraform
terraform init
terraform apply

Creates the GCS bucket and BigQuery dataset. terraform destroy tears it down.

2. Start Postgres and pgAdmin

docker-compose -f khushil_docker_compose.yml up

Postgres on 5432, pgAdmin on 8080 → http://localhost:8080

3. Run the ingestion

URL="https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz"

python pandas_ingest.py \
  --user=root --password=root \
  --host=localhost --port=5432 \
  --db=ny_taxi --table_name=yellow_taxi_trips \
  --url=${URL}

Or containerized:

docker build -f Dockerfile.pandas_ingest -t taxi_ingest:v001 .

docker run -it --network=pg-network taxi_ingest:v001 \
  --user=root --password=root \
  --host=pg-database --port=5432 \
  --db=ny_taxi --table_name=yellow_taxi_trips \
  --url=${URL}

Host changes to pg-database — inside a Docker network, containers address each other by service name.

SQL analysis

pandas_notebook.ipynb covers joins against the taxi zone lookup table, NULL checks on location IDs, aggregations by day, and data quality validation.

Files

terraform/                     # GCS bucket + BigQuery dataset
pandas_ingest.py               # Parameterized ingestion script
Dockerfile.pandas_ingest       # Dockerized ingestion
khushil_docker_compose.yml     # Postgres + pgAdmin
pandas_notebook.ipynb          # Querying and validation

---

Built as part of the DataTalksClub Data Engineering Zoomcamp.