CI Verified · 229 / 229 Tests Passing DuckDB 2.0.0 PyArrow 26.0.1 SQLite CAS Control Dagster 1.13.30 Streamlit 1.67.0

HVFHV Monthly Data Reliability Platform

A single-machine batch data engineering platform built over official NYC TLC High-Volume For-Hire Vehicle trip datasets — combining immutable content-addressed Parquet acquisition, native DuckDB transformations, atomic SQLite compare-and-swap (CAS) releases, Dagster asset lineage, and a read-only Streamlit console without cloud overhead.

Official Trips Processed
41,474,517
2024-12 (24-col) & 2025-01 (25-col)
Automated Gate Suite
229 PASS
Gates G0–G5 · 0 skipped · 0 failed
Storage & WAL Seal
20.75 GiB
80 GiB budget · 0 WAL · 0 spill files
Read-Only Query Latency
1.923 s
Cold ReleaseReader under 1 GiB cap

System Architecture & Reliability Guarantees

Designed around strict separation between raw evidence, analytical compilation, publication authority, and read-only consumption.

1. Acquisition HTTPS download, full SHA-256 verification, PyArrow Parquet/CSV decode into content-addressed raw store.
→
2. Transform & Gate DuckDB 2.0 SQL candidate build, 24→25 column schema adapter, Type 7 quantiles, rules S01–S08 & Q01–Q09.
→
3. Atomic Release CHECKPOINT zero-WAL seal, SHA-256 manifest, SQLite CAS generation swap & Option A retention.
→
4. Orchestration & UI Dagster 1.13 lineage & gap checks; read-only Streamlit console with out-of-cache eligibility guard.

SQLite CAS Publication Authority

SQLite (DELETE journal, synchronous=EXTRA, FK enforcement) atomically promotes verified releases via expected_parent, expected_generation, and idempotent operation_key. POSIX fcntl.flock serializes writers and bounds readers.

Option A Storage Retention (ADR 0006)

12-month projections showed 4 full DuckDB snapshots (~98.13 GiB) would breach the 80 GiB workstation cap. Option A retains current + 1 prior verified database while keeping immutable raw Parquet files and JSON manifests for deterministic rebuilds.

24 → 25 Column Schema Evolution

Bridges the 2025 NYC Congestion Relief Zone fee introduction explicitly with cbd_fee_field_present so pre-2025 NULL entries in 2024-12 are never silently coerced into $0.00 alongside 2025-01 records.

Exact Type 7 Quantiles & Partition

Evaluates exact p50 and p95 via DuckDB quantile_cont over microsecond integer deltas without subgroup percentile averaging, preserving total = eligible_nonnegative + negative_recorded_interval + missing_timestamp.

UI & Lineage Preview

Captured directly from the local Streamlit analytical console and Dagster webserver running against the published 41.47M-row release (rel_2024_12_2025_01_f2d153b3).

Provenance metadata, source month coverage truth, Post-Seal S08 attestation banner, quality rules Q01–Q09, full-scan column profiles, and live SQLite control-plane state.

Streamlit Data Reliability Overview

Verified Real-Data Benchmarks

Measured end-to-end on official NYC TLC releases (fhvhv_tripdata_2024-12.parquet, fhvhv_tripdata_2025-01.parquet, and taxi_zone_lookup.csv).

Metric / Stage Release 1 (2024-12 Slice) Release 2 (2024-12 + 2025-01 Window)
Total Trips Ingested 21,068,851 41,474,517
Interval Partition 20,817,586 eligible / 251,265 neg / 0 miss 41,023,464 eligible / 451,053 neg / 0 miss
Eligible Quantiles (Type 7) p50 = 268.0s / p95 = 716.0s p50 = 249.0s / p95 = 666.0s
Build Wall Time 20.61s 32.63s (incremental)
Peak Writer RSS (≤ 8,192 MB) 6,227.20 MB 7,634.23 MB
DuckDB Artifact Size 992.76 MiB (0 WAL, 0 spill) 1,957.76 MiB (0 WAL, 0 spill)
Total Project Footprint — 20.75 GiB (59.25 GiB headroom under 80 GiB)
Consumer Cold Query Latency — 1.923s (headless ReleaseReader)
CBD Congestion Fee Transition Observed (S02 / Q06): Across 20,405,666 trips in 2025-01, 14,105,454 recorded $0.00 and 6,300,212 recorded positive fees up to $3.00 (p95 = $1.50), while all 21,068,851 pre-2025 rows retain explicit NULL (cbd_fee_field_present = false).

Quickstart & Technical Documentation

Reproduce the locked Python 3.11–3.14 environment and verify all 229 automated tests locally:

git clone https://github.com/emilianodesu/nyc-mobility-data-platform.git
cd nyc-mobility-data-platform
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.lock && pip install --no-deps -e .
pytest -q