Streamlining Data Pipelines: My Week 5 Journey with Bruin & DuckDB

In the world of data engineering, we often associate “robust pipelines” with heavy infrastructure — spinning up Airflow clusters, managing cloud warehouses, and wrestling with complex configuration files. But Week 5 of the Data Engineering Zoomcamp took a different turn. We shifted gears to a local-first approach, building a production-grade ELT pipeline right on our laptops using Bruin and DuckDB.

Here is a look at how we built a declarative data pipeline for NYC Taxi data, the challenges we faced, and why this stack feels like a breath of fresh air.

The Stack: Lightweight Powerhouses

The core philosophy this week was simplicity without sacrificing power.

1. Bruin: A declarative data build tool (think “dbt meets Airflow”). Instead of writing DAG definitions in Python, you define assets. An asset is just a SQL or Python file with a simple header describing its dependencies. Bruin figures out the execution order for you.
2. DuckDB: An in-process SQL OLAP database. It’s like SQLite but optimized for analytics. It allowed us to process millions of taxi records locally without spinning up a cloud instance.

The Pipeline: Ingestion to Insights

We structured the pipeline into three logical acts: Ingestion, Staging, and Reporting.

Act I: Dynamic Ingestion with Python

Real-world data is rarely clean. We needed to fetch NYC Taxi parquet files from a public S3 bucket. The catch? The schema changes over time (e.g., Yellow vs. Green taxis), and we needed to handle date ranges dynamically.

We wrote a Python asset (ingestion.trips) that:

1. Reads environment variables for the start and end dates.
2. Generates the URLs for the specific months required.
3. Fetches the Parquet files and standardizes column names on the fly.
4. Loads the result directly into DuckDB.

The beauty of Bruin here is that this Python script is treated just like a SQL table. It has a name, it has dependencies, and it outputs a table.

Act II: Staging and Deduplication

Once the raw data was in, we switched to SQL. The staging.trips asset was responsible for cleaning. Since distributed data often contains duplicates, we implemented a robust deduplication strategy using QUALIFY and ROW_NUMBER() over a composite primary key.

We also joined our raw trips with a static CSV seed file (payment_lookup) to make the data human-readable.

/* @bruin
name: staging.trips
type: duckdb.sql
depends:
  - ingestion.trips
...
@bruin */

SELECT ...
FROM ingestion.trips t
QUALIFY ROW_NUMBER() OVER (PARTITION BY t.pickup_datetime, ... ) = 1

Act III: Incremental Reporting

The final step was aggregating the data. We built a reports.trips_report asset to calculate daily metrics like total trips and average fare.

Crucially, we used Bruin’s **time_interval materialization strategy**. This means if we re-run the pipeline for just "January 2022," Bruin only deletes and re-calculates data for that specific month, rather than dropping the whole table. It’s efficient and idempotent.

The “Gotchas”: It Wasn’t All Smooth Sailing

Building locally exposes you to constraints you might ignore in the cloud — specifically, RAM.

When I first tried to backfill 3 years of data, my pipeline crashed with an ArrayMemoryError. The Python ingestion asset was trying to load dozens of Parquet files into a single pandas DataFrame before writing to DuckDB.

The Fix: We implemented a guardrail in our Python asset:

1. Added a max_months_per_run variable to limit batch sizes.
2. Trimmed the DataFrame to only strictly required columns before concatenation.
3. Wrote a PowerShell script to chunk the backfill into manageable 3-month intervals.

Why This Matters

This week demonstrated that you don’t need a massive cloud bill to build good data engineering habits. By using Bruin, we treated our data pipeline as code — modular, version-controlled, and testable. By using DuckDB, we iterated fast.

The result is a pipeline that is easy to read, easy to run, and surprisingly powerful.

Check out the full code and the pipeline configuration in the repository here: [Link to Your GitHub Repository]