RAW TO RELIABLE / ANALYTICS ENGINEERING / URBAN MOBILITY
An analytics engineering pipeline for NYC Yellow Taxi trip data using DuckDB, PostgreSQL, dbt, dimensional modeling, data-quality flags, and Power BI reporting.
Historical Q1 2019 trip data, explicit quality flags, two analytical grains, and recorded Power BI reporting. Local PostgreSQL infrastructure runs in Docker.
Focus areas
Technology stack
01 / Executive Overview
Build a consistent analytical foundation for historical trip, revenue, and operational questions.
Engineering challenge
Monthly files contain timestamps, amounts, distances, and location codes. Reporting requires a consistent period, defensible quality rules, and a clear distinction between individual trips and daily measures.
Analytical foundation
DuckDB reads three remote monthly Parquet files into PostgreSQL. SQL models normalize and filter records, derive operational flags, enrich pickup and dropoff geography, and aggregate daily metrics for Power BI reporting.
The trip count appears in both the README and recorded Power BI images. It is a report-level count, not an independently reconciled count of raw imports, filtered staging rows, unique trips, or clean analytical records. No fresh warehouse run was performed for this case study.
02 / System Architecture
Separate implemented data operations, local infrastructure, and documented BI consumption.
Manual ingestion and staging / source-defined operations
01 / Source
January, February, March 2019
02 / Extraction
read_parquet over HTTPS
03 / Loading
Three monthly trip tables
04 / Transformation
UNION ALL, casts, pickup-date filter
Model transformations / continuation from Q1 staging
01 / Input
stg_yellow_tripdata_2019_q1
02 / Enrichment
Duration, speed, validity and clean flags
03 / Analytical outputs
Zone-enriched trips + daily aggregates
Analytics consumption / documented connection, recorded visual output
01 / Warehouse outputs
Trip grain and daily grain
02 / Consumption
Relationships and DAX unavailable
03 / Recorded output
Overview, payments, geography
DuckDB uses its PostgreSQL extension to create remote warehouse tables from Parquet reads. dbt SQL defines normalized staging, core business logic, and two reporting marts. Several downstream reads use hardcoded schema names rather than dbt ref dependencies.
Compose defines a PostgreSQL 15 service, an exposed database port, and a named persistent volume. Execution is manual. The published repository does not provide automated scheduling or a verified clean-checkout dbt run.
03 / DuckDB Ingestion & PostgreSQL Warehouse
Use DuckDB as the bridge between remote Parquet files and PostgreSQL tables.
CREATE TABLE taxi_db.raw.yellow_tripdata_2019_01 AS
SELECT *
FROM read_parquet(
'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2019-01.parquet'
);The script installs and loads the postgres extension, attaches PostgreSQL as taxi_db, and issues CREATE TABLE AS SELECT for January, February, and March 2019. Each read targets the corresponding TLC CloudFront Parquet URL. SELECT * adopts the Parquet result schema; no explicit raw column DDL is supplied.
January uses CREATE TABLE; February and March use IF NOT EXISTS. A rerun can fail on January or skip an existing later table without refreshing it. There is no explicit transaction, duplicate key, incremental watermark, or failure-recovery routine. Connection placeholders and a mixed assignment fragment require adaptation before execution.
DuckDB makes remote Parquet available directly to SQL and a PostgreSQL destination. The repository contains no ingestion benchmark, throughput measurement, or independent raw-to-mart row-count reconciliation.
04 / dbt Layered Transformations
Twelve SQL models express distinct responsibilities across staging, core, and mart layers.
DuckDB creates the three trip tables with SELECT * from remote Parquet. Models also reference a zone lookup; its ingestion script is absent. Raw is a source layer, separate from dbt transformations.
yellow_tripdata_2019_01yellow_tripdata_2019_02yellow_tripdata_2019_03taxi_zone_lookupThe Q1 view directly unions the three raw months, casts and renames fields, and applies timestamp checks. It does not depend on the standalone monthly staging files.
stg_yellow_tripdata_2019_01stg_yellow_tripdata_2019_02stg_yellow_tripdata_2019_03stg_yellow_tripdata_2019_q1stg_taxi_zone_lookupThe fact derives service date, duration, speed, and two quality flags. Dimensions supply a Q1 calendar, four holiday definitions, zone attributes, and payment labels. dim_date reads dim_holiday.
fact_yellow_tripdata_2019_q1dim_datedim_holidaydim_zonedim_payment_typeThe trip view adds pickup and dropoff zone attributes through two left joins. The daily table groups the core fact by service_date alone and publishes counts, amounts, clean miles, clean hours, and ratios.
mart_tripdata_joined_2019_q1mart_daily_trip_metricsMaterialization is explicit for the Q1 staging view, five core tables, trip mart view, and daily mart table. The remaining staging files omit it. Configuration needed to resolve final schemas and run order is incomplete; file existence alone does not establish an executable dbt graph.
05 / Data Quality & Anomaly Handling
Filter impossible period records, retain diagnostic flags, and select a defined clean population for speed.
Pickup must fall on or after January 1 and before April 1, 2019. Both timestamps must exist; dropoff must be at or after pickup. The range applies to pickup, not dropoff. UNION ALL preserves duplicates.
Equal pickup/dropoff timestamps survive staging but fail is_valid_trip. Core also marks null or nonpositive distance, null or negative total_amount, and nonpositive duration invalid. Zero revenue is allowed. Invalid rows remain in the fact.
is_clean_trip requires is_valid_trip and speed BETWEEN 0.1 AND 80 mph, inclusive. Speed is null unless distance and duration are positive. Revenue validity is checked separately; a computed speed alone does not prove a valid trip.
where pickup_ts >= timestamp '2019-01-01'
and pickup_ts < timestamp '2019-04-01'
and pickup_ts is not null
and dropoff_ts is not null
and dropoff_ts >= pickup_tscase
when is_valid_trip = true
and avg_speed_mph between 0.1 and 80
then true
else false
end as is_clean_trip06 / Dimensional Modeling & Metric Grain
Preserve trip-level detail and daily aggregates as separate analytical contracts.
Fact / Intended one row per retained trip record
f.* from fact_yellow_tripdata_2019_q1pu_location_id / do_location_idservice_date / payment_typeis_valid_trip / is_clean_tripNo declared unique trip keypu_location_id = location_id
LEFT JOIN as zpu; supplies pu_borough, pu_zone, pu_service_zone.
location_idborough / zone / service_zonedo_location_id = location_id
LEFT JOIN as zdo; supplies do_borough, do_zone, do_service_zone.
location_idborough / zone / service_zoneDaily aggregate / table
mart_daily_trip_metricsOne row per observed service_date. No borough, zone, or payment grouping. Carries total/valid/clean counts, revenue, clean miles, clean hours, and derived ratios. A complete Q1 calendar has 90 dates; 90 populated daily rows are documented, not independently verified.
Calendar / separate dimension
dim_dateGenerates date_day and an integer YYYYMMDD date_key for January 1–March 31. The fact carries a day-truncated service_date, not that integer key. A date relationship in Power BI is documented; its actual binding is unverified.
Payment / separate dimension
dim_payment_typeDefines payment_type_id and labels for codes 0–6. The fact carries payment_type, but the trip mart does not join its label. The README describes a BI star schema; its key uniqueness, filter direction, and relationship activation require the missing PBIX.
sum(trip_distance) filter (where is_clean_trip) as clean_miles_daily,
sum(trip_duration_hours) filter (where is_clean_trip) as clean_hours_daily,
(sum(trip_distance) filter (where is_clean_trip)) / nullif(sum(trip_duration_hours) filter (where is_clean_trip), 0) as avg_speed_mph_weighted_clean,
-- revenue per trip
(sum(total_amount) / nullif(count(*),0)) as avg_revenue_per_trip_daily07 / Power BI Operational Analytics
Three original report images preserve the recorded analytical output, with metric definitions kept in view.
The overview records trip volume, revenue, daily trends, clean-trip rate, speed, and weekday/holiday comparisons. Daily aggregation is implemented in SQL; the screenshot’s Power BI measures and moving-average definition are unavailable.
Recorded overview: daily revenue and calendar comparisons alongside report-level Q1 KPIs. The screenshot establishes the displayed values; Power BI DAX, filters, and correspondence to the published SQL are unverified.
Source: audited Taxi repository / images/dashboard_overview.jpg. Full original image, uncropped.The recorded view shows 22,611,788 trips, $405,333,881.67 revenue, 98.97% clean trips, and 11.62 mph. The payment image shows $17.93 per trip. These differ from the README’s approximately $357M, $15.63, 99.89%, and 12.66 mph summaries. The images establish displayed values, not independently reproduced DAX, filters, or a reconciled execution revision.
Payment mix, revenue per trip, and tip rate compare groups of individual records. The core retains payment_type, total_amount, and tip_amount. Payment labels exist in a separate lookup; their Power BI binding and tip-rate denominator are unavailable. TLC records electronic tips and excludes cash tips, so the displayed cash-tip zero does not prove an absence of tipping.
Trip-level payment comparisons: revenue, volume, revenue per trip, and tipping measures. Metric bindings and denominators are unavailable without the PBIX; zero recorded cash tips are not evidence of no cash tipping.
Source: audited Taxi repository / images/revenue_payment_analysis.jpg. Full original image, uncropped.Borough and zone comparisons use trip-level detail. SQL prejoins separate pickup and dropoff attributes. The recorded chart title mentions an inactive dropoff relationship; activation logic and the exact borough measure bindings cannot be verified without the semantic model.
Origin and destination analysis at trip grain. SQL supplies distinct pickup and dropoff zone attributes. The image records an inactive-relationship label but does not expose the underlying Power BI relationship or activation measure.
Source: audited Taxi repository / images/geographic_analysis.jpg. Full original image, uncropped.README-described duration and speed analysis. SQL includes duration buckets through 5+ hours and borough-level AVG(avg_speed_mph), a different metric from the clean distance/time ratio. No dedicated screenshot is committed.
The overview records daily trends, weekday/weekend comparisons, and holiday categories. Calendar definitions exist in SQL; the separate time-analysis report page is documented without its own image.
The README splits January versus February–March around a February 1 cutoff. congestion_surcharge is retained in the fact; exploration SQL groups it by month and borough. The monthly query needs a trip_month column absent from the published Q1 mart. No policy screenshot or controlled causal analysis is available.
The README describes a seventh report page. The available SQL defines validity, clean flags, and aggregate measures; its exploratory speed-only clean count is not the same predicate as the core clean flag. A dedicated report screenshot and DAX are absent.
Policy interpretation remains descriptive. The README’s February 1 boundary differs from the NYS notice requiring collection from February 2, 2019. The charge concerns trips beginning, ending, or passing through Manhattan south of and excluding 96th Street; borough-level comparisons cannot isolate that zone. No control for seasonality or other confounders is demonstrated, and no demand change is attributed causally to the policy.
08 / Engineering Decisions & Troubleshooting
Five source-supported decisions connect reporting risks to concrete SQL behavior.
Source + documentation
Source evidence
Source + documentation
Source evidence
Source + documentation
Source evidence
Source + documentation
Source evidence
Source + documentation
Source evidence
09 / Evidence, Limitations & Source Code
Read the implementation, recorded outputs, and reproducibility limits together.
Three monthly Parquet loads, Q1 timestamp filtering, duration/speed logic, validity and clean flags, two geography joins, daily aggregates, dimension definitions, and a Docker PostgreSQL service. All source links are pinned to the inspected revision.
Three original JPEG dashboard images show overview, revenue/payment, and geography views. They corroborate the displayed trip count and visual labels, while their DAX, slicer semantics, and relationship bindings remain unverified. Only these three of seven documented report pages have committed screenshots.
The checkout has no dbt_project.yml, source YAML, automated schema/test definitions, PBIX, or execution logs. Schema setup creates staging but later refers to stg; model SQL reads hardcoded raw, raw_core, and raw_mart relations. The standalone February/March staging files still reference January, although the Q1 model bypasses them.
Diagnostic and exploration queries reference fields absent or differently named in the models, including pickup_datetime, trip_month, payment_type_name, full_date, and avg_speed_mph_weighted. The available queries are analytical intent, not passing test evidence. Raw/filtered/clean counts, unique trip grain, lookup cardinality, and end-to-end replay remain unverified.
Cloud warehouses, Airflow scheduling, Power BI Service publication, and additional automated tests appear as future improvements in the README. They are not implemented capabilities of this Taxi project. No streaming workload, operational deployment, measured speedup, or external business impact is claimed.
Audited source revision: 57f4145cdd271981f40e42d7311f40ff7f56e8f3. SQL excerpts preserve the original expressions and omit database connection setup. Screenshots are the original public files, uncropped. Source credentials, account details, and local paths are not reproduced.