NYC Taxi Orchestration with Kestra

Kestra scheduled ETL: monthly NYC TLC CSVs into PostgreSQL with staging COPY and MERGE.

Overview

A Kestra flow (postgres_taxi_scheduled, namespace zoomcamp) that downloads a selected TLC month, COPYs it into a staging table, derives a deterministic MD5 row key, and MERGEs into yellow_tripdata or green_tripdata. Docker Compose runs Kestra, its metadata store, PostgreSQL and pgAdmin. Zoomcamp orchestration module.

Interface

Kestra execution overview showing the January 2021 taxi pipeline completed successfully

Kestra topology for the successful PostgreSQL staging and merge execution

Problem

Monthly taxi files must land in PostgreSQL without duplicating rows when a month is re-run, and yellow vs green schemas differ.

Technical solution

Inputs select taxi type and YYYY-MM period, making manual and scheduled runs deterministic. Branching tasks create typed tables, COPY into staging, then MERGE on unique_row_id. A January 2021 yellow run was re-verified successfully in Kestra 1.1.20 and loaded 7,774,773 rows into PostgreSQL.

What is in the repository

Kestra standalone server with Postgres metadata store
Staging COPY + MERGE skip-duplicates load
Separate yellow and green DDL
Monthly cron triggers and backfill label
pgAdmin on port 8085 against ny_taxi
Verified January 2021 execution: SUCCESS, 7,774,773 yellow-trip rows