case study · personal project
Jersey City Bikeshare
An end-to-end analytics platform over real Citibike trip data (Jersey City, NJ) — ingestion, dbt transformation, Kafka streaming, Airflow orchestration, and infrastructure entirely provisioned via Terraform.
The Problem
Citibike publishes historical trip records for batch analysis, but no public real-time feed exists at trip-level granularity for the Jersey City system. A platform built to inform real pricing and rebalancing decisions needs both: reliable historical patterns and a live operational picture — plus the infrastructure discipline most portfolio data projects skip, like isolated environments, reproducible infra, and tested data quality.
Architecture
Raw + Streaming
CSV Batch & Kafka Events
Validation
dbt Tests Gate
Quarantine
Bad Rows Isolated
Silver
Validated Staging
Gold: Star Schema
fct_trips + Dimensions
Aggregates
Daily, Station, Usage Marts
Raw trip CSVs land in Snowflake via COPY INTO, while a Kafka producer/consumer pair simulates real-time bike status events since no live trip-level feed exists. Both paths converge in one raw source. dbt validates every row — failures are quarantined rather than dropped — before modeling a star schema (fct_trips plus station and date dimensions) and a set of Gold aggregates. Airflow, via Astronomer Cosmos, runs every dbt model and test as its own dependency-aware task, and Terraform provisions all four databases, three warehouses, and every grant from zero.
Key Decisions
Validation + Quarantine Layer
Rows failing business rules — invalid durations, start-after-end timestamps, missing stations — are routed to a dedicated quarantine table instead of silently breaking downstream models. It caught a real sentinel value (birth_year = 1888) Citibike uses for missing data.
Infrastructure as Code with Terraform
Every database, warehouse, service role, and grant is defined in Terraform and reproducible from zero — three isolated dev/CI/prod environments all reading from one shared raw database.
Incremental Merge Strategy
fct_trips uses incremental_strategy='merge' with a unique_key, supporting inserts and updates rather than just append. Verified by withholding two months of data and replaying them through Airflow.
Two Independent CI/CD Pipelines
Separate GitHub Actions workflows for dbt and Kafka — the Kafka workflow spins up a real broker as a service container, produces test events, and fails the build if fewer than expected land in Snowflake.
Results
Two distinct rider profiles fall out of the data cleanly: Subscribers behave like commuters — sharp 7–9 AM and 5–7 PM weekday peaks, ~4–5 minute trips, almost never leaving the Jersey City system. Customers behave like leisure riders — a Sunday 10 AM–3 PM peak, longer and more varied trips, and 9x more likely to end outside the system. Grove St PATH alone accounts for ~7,000 trip starts and ends, confirming its role as the system’s transit hub.