NYC Taxi ELT with Bruin

Overview

An end-to-end NYC taxi ELT pipeline on Bruin. Python assets fetch TLC parquet, yield 150k-row PyArrow batches, and land in DuckDB or MotherDuck. SQL assets filter, join a payment lookup, and deduplicate in staging, then aggregate a trips_report. Same pipeline code, two environments. Zoomcamp module 05 (data platforms).

Problem

A single Arrow IPC payload hits Bruin’s ~256 MB limit on large months (January 2020 yellow is 6.4M rows). Re-downloading parquet on every run wastes time. Invalid fares and duplicate composite keys should not reach the report.

Technical solution

trips.py is a generator: cache parquet locally, prune columns, yield batches, append at ingestion. Staging uses a time_interval incremental strategy, drops invalid rows, and ROW_NUMBER() on a composite key. Production is MotherDuck database nyc_taxi. Re-queried 8 Sep 2026: 6,405,008 ingested rows, 6,370,784 staged, 312 report rows. bruin validate on the local environment reported four assets, no issues.

What is in the repository

• Four assets: ingestion.trips, ingestion.payment_lookup, staging.trips, reports.trips_report
• Chunked PyArrow batches (150k rows) under Bruin’s Arrow IPC limit
• Column checks and uniqueness on staging
• Local DuckDB and MotherDuck production with the same asset SQL

Technical decisions

Append raw, dedupe in staging

The landing zone stays simple. Dedup and quality live where they can be tested.

One pipeline, two backends

Connection name duckdb-default points at a local file in default and at MotherDuck in production.