TaxiFlow

TaxiFlow is a reproducible lakehouse for NYC TLC trip data that covers both historical batch backfill and change-data-capture streaming. One local stack lands both sources in Delta bronze, models the batch path with dbt on Trino, orchestrates it in Airflow, and gates every run on data quality.

Why TaxiFlow?

Most portfolio data projects stop at “I loaded a CSV into a warehouse.” That proves you can follow a tutorial; it does not prove the pipeline is trustworthy.

TaxiFlow is different:

Pinned inputs — the TLC file is locked by URL, byte size, SHA-256, and row count, and the loader verifies all four before Spark reads a row
Explicit contracts — per-service schemas, typed nulls for missing columns, and a drift report for unknown ones
Deterministic identity — every trip has a replay-stable trip_id derived from provenance and content, not a row number
Replay-safe writes — replaceWhere for a month of batch data, MERGE on a source key for CDC, no bare appends
Typed streaming — Avro with Schema Registry and BACKWARD_TRANSITIVE compatibility
Quality with a veto — a failed check blocks promotion and writes an alert row
One snapshot for metrics — counts and freshness describe one committed Delta version, not two racing queries

I built it to practice the parts that make a pipeline operable rather than merely runnable: idempotency, schema evolution, checkpoint recovery, freshness, reconciliation, and the discipline of proving each layer before starting the next.

Architecture

TaxiFlow Architecture

Technology

Storage and compute

Tool	What it is	How TaxiFlow uses it
MinIO	S3-compatible object storage	Holds the `taxiflow` bucket with separate `bronze`, `silver`, and `gold` prefixes
Delta Lake	ACID table format with schema enforcement and time travel	Stores every layer; provides `replaceWhere` for batch replay, `MERGE` for CDC latest state, and `versionAsOf` for consistent metrics
Apache Spark 3.5.1	Distributed batch and streaming engine	The batch job reads TLC Parquet and writes Delta; Structured Streaming reads Kafka and merges CDC rows into Delta
Trino 476	Distributed SQL engine	Queries Delta through its native S3 filesystem and Delta connector; serves dbt and is the SQL endpoint for Superset
Hive Metastore 3.1.2	Table and schema catalog	Gives Trino the metadata it needs to expose Delta tables as SQL relations

Transformation and streaming

Tool	What it is	How TaxiFlow uses it
dbt-trino	SQL transformation framework with tests and lineage	Builds staging views, one reusable intermediate enrichment table, and gold marts; runs 66 data tests and reconciliation assertions
PostgreSQL 15.19	Relational database	Hosts the synthetic OLTP table `iot.taxi_nyc_time_series`, Airflow’s metadata, and the `ops.pipeline_runs`/`ops.pipeline_alerts` audit tables
Debezium 2.5.1	Change-data-capture connector for PostgreSQL	Reads the WAL, flattens the change envelope with `ExtractNewRecordState`, and publishes inserts, updates, and deletes as Avro
Apache Kafka 7.5.0	Durable, partitioned event log	Buffers the CDC topic `taxiflow.iot.taxi_nyc_time_series` so Spark can consume at its own pace and resume from committed offsets
Schema Registry 7.5.0 + Avro	Schema store and typed serialization	Stores the `TaxiTripEvent` contract and policy; the Confluent serializer embeds a schema id in every message, and `BACKWARD_TRANSITIVE` guards evolution

Orchestration, quality, and serving

Tool	What it is	How TaxiFlow uses it
Apache Airflow 2.9.3	Workflow orchestrator	Runs the linear `batch → dbt_build → quality → promote` DAG; a custom image supplies Spark, Java, dbt, GX, and the Trino client
Great Expectations	Data-quality expectation framework	Checks column-level rules on 50,000-row bronze and silver samples; full-table SQL reconciliation covers counts and sums
Apache Superset 4.1.1	BI and dashboard platform	Pinned in the `bi` profile as the dashboard layer over the gold tables; the dashboard itself is the serving step
pytest + Ruff	Test runner and linter/formatter	48 unit and 45 contract tests plus integration markers; one Ruff configuration handles lint and format

Layer Notes

Bronze

What it is: The raw, append-only ingestion layer. Rows stay source-shaped and carry provenance.

Purpose: Keep a replayable copy of the source so every result can be traced back and reprocessed.

Explicit StructType per service: missing columns become typed nulls, unknown columns go to a drift report.
trip_id = SHA-256(service, source_file, source_row_number, content_hash); content_hash covers the canonical row.
Spark session runs in UTC; silver interprets the TLC wall clock as America/New_York.
Writes use replaceWhere on service/year/month; rejected rows go to a quarantine table with a reason.
2024-01: 2,964,624 source rows → 2,964,606 accepted + 18 quarantined.

Silver

What it is: The cleaned and conformed layer: typed, deduplicated, timezone-normalized, and joined to the zone dimension.

Purpose: Provide one trusted relation for analytics, with cleaning and enrichment applied once instead of in every query.

Staging views rename and cast the bronze columns.
One intermediate table enriches each trip with pickup and dropoff zone and borough from the 265-zone seed.
No second dedupe; the models rely on the tested bronze trip_id. Passenger counts are averaged.

Gold

What it is: The business-facing layer: facts and aggregates at defined grains.

Purpose: Answer the project’s questions without repeating joins or business logic in dashboards and ad-hoc SQL.

fct_trips at trip grain; dim_zones at LocationID.
Aggregates for daily trips, zone-day demand, hourly demand, and tip rate, plus freshness and quarantine summaries.
Marts use on_table_exists: drop because Trino cannot rename managed tables.
Build: 79/79 nodes, including 66 data tests.

CDC bronze

What it is: A latest-state bronze table fed by Postgres change events, with one row per trip and a delete flag.

Purpose: Make recent inserts, updates, and deletes queryable in the same lake as the batch data.

Postgres owns trip_id; REPLICA IDENTITY FULL carries the full row on delete.
A custom Debezium image supplies the Confluent Avro converter; events publish through Schema Registry.
Spark reads Kafka with foreachBatch, strips the 5-byte Confluent header, partitions by event_date from __source_ts_ms, and MERGEs on trip_id with a source-timestamp guard.
Deletes remain as tombstones; the checkpoint sits beside the Delta table.
Metrics read one versionAsOf snapshot.

Orchestration and quality

What it is: The control layer: an Airflow DAG plus the quality gate around the batch path.

Purpose: Enforce order, record what ran, and prevent unvalidated data from being promoted.

Airflow DAG: batch → dbt_build → quality → promote; promote requires ALL_SUCCESS.
Task wrappers write TAXIFLOW_METRICS= and run metadata to ops.pipeline_runs; failures go to ops.pipeline_alerts.
Quality combines full-table SQL reconciliation with GX samples (9/9 per suite).
Freshness: 24 hours for batch, 60 seconds for CDC.
CDC is not yet in dbt or the batch DAG.

What I Learned

Most of this stack was new to me, so I learned it one layer at a time, with a working gate before each next step.

Spark and Delta: started with a six-row fixture and replayed the same batch twice before the 3M-row slice; replaceWhere and MERGE became clear once the second write could fail.
Trino and dbt: ran dbt on the serving engine, wrote staging as views and one enrichment table, then added marts; the adapter’s rename limitation set the materialization, and a Trino memory error removed the second dedupe.
Kafka, Debezium, and Avro: registered the schema, ran the connector, and had to inspect the message bytes to see the magic byte and schema id before the Spark decoder made sense.
Airflow and GX: wrapped commands that already worked from the terminal, then used airflow dags test to learn the DAG’s state rules; GX handled column-level samples while SQL handled full-table reconciliation.