case study · personal project
NYC Taxi Data Pipeline
A production-shaped medallion pipeline over the NYC TLC's public trip record archive — built to prove that a Bronze/Silver/Gold architecture with real data-quality gating can run at 320M-row scale for the cost of a coffee.
The Problem
The NYC TLC publishes the full yellow-cab archive as public monthly Parquet files — millions of rows a month, seven years deep, with the usual real-world mess: malformed fares, impossible timestamps, trips that never happened. Most public demos stop at a notebook and a groupby. The goal here was to build it the way you'd actually run it in production instead.
source: NYC TLC Public Trip Record Archive — Monthly Parquet Files, 7 Years Deep
Architecture
Raw / Landing
TLC Parquet Files
Bronze
Raw Load
Quarantine
dbt Tests Gate
Rejected Rows
Logged, Not Dropped
Silver
Cleaned & Conformed
Gold
Aggregated Marts
Raw Parquet lands untouched in Bronze. Every row passes through a quarantine gate — implemented as dbt tests, not a side script — before Silver. Rows that fail are written to a quarantine table instead of being dropped or failing the whole run. Gold aggregates the clean Silver layer into the marts the analysis actually queries.
Key Decisions
A Quarantine Layer, Not Just dbt Tests
Failing rows are diverted to a quarantine table rather than failing the whole run or vanishing silently — the pipeline stays observable even when upstream data quality slips.
Snowflake Usage-Based Compute + Auto-Suspend
Warehouses auto-suspend the moment a load finishes, so 320M rows across 7 years processed for well under $10 total — the same architecture you would run in production, just switched off between runs.
dbt For Transformation-As-Code
Every Bronze→Silver→Gold transform is a version-controlled dbt model with tests as first-class citizens — the quarantine gate is failing tests routed to a table, not a bolted-on script.
Medallion Architecture For Auditability
Bronze keeps the raw, untouched load so any Silver/Gold model can be rebuilt from scratch if a transform bug is found — reprocessability was worth the extra storage layer.