NYC Taxi Data Platform

Every month, NYC's Taxi & Limousine Commission (TLC) publishes new trip data — millions of rows describing individual taxi rides. Your project automatically fetches that data, checks it for problems, loads it into a cloud database, and shows the results on a dashboard — without anyone manually downloading files or running scripts.

Why this matters: doing this by hand every month is tedious and error-prone — someone could load the same file twice (duplicates), miss that a file was empty or corrupted, or not notice when TLC changes its file format. Automating it removes that risk and that repetitive labor.

Data

• Yellow Taxi -> Trip Data -> Parquet -> Monthly records of yellow cab rides (pickup/dropoff time, fare, distance, etc.)

• Green Taxi Trip Data -> Parquet -> Same, but for green (outer-borough) cabs

• Taxi Zone -> Lookup -> CSV A reference table mapping location IDs to real place names/boroughs

Parquet is a column-oriented file format built for large datasets — instead of storing data row-by-row like a CSV, it groups values by column, which makes it much faster to read and much smaller to store when you're dealing with millions of rows.

• Kestra — an open-source workflow orchestration tool. Think of it as the "conductor" that runs your pipeline steps in the right order, on schedule, and retries or alerts if something fails. Workflows in Kestra are defined as YAML files (your flows/ folder).

• Google Cloud Storage (GCS) — cloud file storage. This is where the raw downloaded Parquet/CSV files land before processing.

• BigQuery — Google's cloud data warehouse. This is the final destination where the cleaned, validated data lives and can be queried with SQL.

• Terraform — an "infrastructure as code" tool. Instead of manually clicking through the Google Cloud console to create a storage bucket or a BigQuery dataset, you write down what you want in config files, and Terraform creates (and can later destroy) exactly that infrastructure. This makes the setup reproducible and version-controlled.

• Docker / Docker Compose — lets you run Kestra (and its dependencies) in isolated, pre-configured containers, so it works the same way on any machine without manual installation of each dependency.

• Authenticate with Google Cloud (gcloud auth application-default login) — lets your local scripts and Terraform act on your behalf against your GCP project.

• Clone the repo and install dependencies — uv is a fast Python package manager; uv sync --locked installs exactly the dependency versions specified in the lockfile, so behavior is reproducible.

• Provision cloud resources with Terraform — running terraform init then terraform apply reads your terraform.tfvars (project ID, bucket names, etc.) and creates the GCS bucket, BigQuery dataset, and a service account (a non-human identity Kestra uses to authenticate itself to GCP) with the correct permissions.

• Configure environment files — .env and .env.secrets hold configuration values (project IDs, credentials) that Kestra and your scripts read at runtime, separated so secrets aren't accidentally committed to source control.

• Encode secrets (encode_secrets.sh) — prepares sensitive values (like credentials) in the format Kestra expects for its local environment.

• Start Kestra (docker compose up -d) — launches the orchestrator; the -d flag runs it in the background ("detached"). It's now reachable at localhost:18081.

• Bootstrap the environment (bootstrap_env.sh) — pushes the Terraform outputs (bucket name, dataset ID, etc.) into Kestra so the workflows know which cloud resources to use.

• Run the pipelines — either manually triggered in the Kestra UI, or on their built-in monthly schedule (Taxi Zone → Green Taxi → Yellow Taxi, staggered an hour apart on the 5th of each month).

This is the core logic, step by step:

1. Download — Kestra fetches the latest file directly from NYC TLC's public source.

2. Land in GCS — the raw file is stored in Google Cloud Storage as a landing zone, so you always have the original file even if later steps fail.

3. Load to a staging table — the data is loaded into a temporary BigQuery table first, not the main one. This is a safety buffer.

4. Data quality checks — SQL checks run against the staging table to confirm it isn't empty and doesn't contain duplicate unique_row_id values (a synthetic ID generated for each row specifically to detect duplicates). If checks fail, the pipeline stops before bad data reaches the main table.

5. Merge into the main table — valid records are inserted into the permanent BigQuery table using a MERGE operation. MERGE is a SQL command that says "insert this row only if it doesn't already exist" (matched on unique_row_id) — this is what makes reruns safe: if you accidentally run the same month's data twice, you won't get duplicate rows.

6. Clean up — the temporary staging table is deleted once processing is done.

7. Expose to the dashboard — the dashboard queries the main BigQuery tables to show current stats.

8. Alerting — if a run fails or produces a warning, Kestra automatically sends a Gmail alert, so you don't have to check manually.

• ADD COLUMN IF NOT EXISTS — TLC occasionally adds new columns to their data. This SQL pattern lets your table absorb new expected columns automatically instead of the pipeline breaking or requiring a manual schema rebuild.

• Concurrency & scheduling — Kestra has settings that limit how many instances of a flow can run at once. You learned that this can silently block a scheduled run from firing if a previous run is still considered "active," which is a useful thing to know when debugging why a monthly job didn't trigger.

• The dashboard is the human-facing layer: it shows how many files/rows have been ingested, when the last successful load happened, Yellow vs. Green volume comparisons, and how many rows were flagged by the quality checks (broken into issue types like negative fares, negative trip durations, or zero-fare trips). It can be filtered by file, taxi type, and load date.

• Important nuance you noted: flagged ≠ invalid. The 72,392 anomalies are rows that rule-based checks thought were worth a second look — not rows that were rejected. This is a meaningful distinction for a portfolio write-up because it shows you understand data quality flagging as a triage/investigation tool, not a hard filter.

• 3 months of data processed, 0 failed runs — the pipeline ran reliably without needing manual intervention.

• 11.31 million rows ingested — a genuinely large-scale dataset, which is a good thing to highlight (shows you can handle production-scale volume, not toy data).

• 72,392 anomalies flagged — demonstrates the quality-check layer is actually catching things, not just a no-op.