Building a Data Lakehouse with DuckDB and dbt

December 29, 202415 min read

Data EngineeringdbtDuckDBTutorial

This is a data lakehouse that runs on a laptop. DuckDB for storage and compute, dbt for transformations, and the medallion architecture (Bronze → Silver → Gold) laid over three months of NYC Taxi Trip Records: over twelve million trips.

By the end you'll have incremental processing, data quality tests, and CI that runs the whole thing on every push. No cluster, no warehouse bill. The database is one file on your disk.

Why DuckDB + dbt?

Snowflake and BigQuery are built for scale most projects don't have yet. For learning, prototyping, or anything that fits on one machine, DuckDB does the same job without the invoice:

Zero infrastructure: one file on disk, no server to run
Fast on analytics: columnar storage, vectorized execution
Native Parquet: query raw files without loading them first
Real SQL: window functions, CTEs, and extras like exclude

Put dbt on top and the transformations get version control, tests, and generated docs. Same practices as a cloud warehouse, minus the warehouse.

Project Setup

Prerequisites

Python 3.11+
UV package manager (or pip)
~10GB disk space for raw data

Clone and Install

bash

# Clone the repository
git clone https://github.com/AlharbiAbdullah/data-lakehouse
cd data-lakehouse

# Install dependencies with UV
uv sync

# Or with pip
pip install -r requirements.txt

Project Structure

Ingestion (Python) and transformation (dbt) live in separate trees and never reach into each other:

text

data-lakehouse/
|-- data/
|   |-- raw/                    # Parquet files (gitignored)
|   +-- warehouse/              # DuckDB database
|
|-- dbt_project/
|   |-- models/
|   |   |-- staging/            # Bronze layer
|   |   |-- intermediate/       # Silver layer
|   |   +-- marts/              # Gold layer
|   |-- macros/                 # Reusable SQL
|   +-- seeds/                  # Reference data
|
|-- scripts/
|   |-- download_data.py        # Fetch NYC Taxi data
|   |-- setup_warehouse.py      # Initialize DuckDB
|   +-- run_pipeline.py         # Orchestrate everything
|
+-- .github/workflows/
    +-- dbt_ci.yml              # CI/CD pipeline

Architecture: The Medallion Pattern

Medallion splits the pipeline into three layers. Each one owes something specific to the next:

Bronze (Staging): the raw file, barely touched. Cast types, standardize column names, attach a deterministic ID. No business logic.

Silver (Intermediate): joins with reference tables, derived fields, and the filters that throw out impossible trips.

Gold (Marts): aggregates. A dashboard hits one small table here instead of scanning every trip.

Bronze Layer: Raw Ingestion

Data Sources

We're using NYC Taxi & Limousine Commission (TLC) trip data for January–March 2024:

Yellow Taxi: ~3M trips/month (Manhattan-centric)
Green Taxi: ~50-60K trips/month (outer boroughs)
FHV: ~1M trips/month (community livery and black car; the Uber/Lyft volume lives in the separate FHVHV dataset)

TLC publishes one Parquet file per type per month. download_data.py pulls them concurrently:

bash

# Download all data
uv run python scripts/download_data.py

# Set up DuckDB warehouse
uv run python scripts/setup_warehouse.py

Staging Models

Each trip type gets its own staging model, because each one names its columns differently. Yellow calls its pickup timestamp tpep_pickup_datetime; green calls it lpep_pickup_datetime. Staging is where that stops mattering:

stg_yellow_trips.sql

{{
    config(
        materialized='view'
    )
}}

with source as (
    select * from {{ source('raw', 'yellow_tripdata') }}
),

with_base_hash as (
    select
        -- Deterministic ID from key fields
        {{ generate_trip_id(
            'tpep_pickup_datetime',
            'tpep_dropoff_datetime',
            'PULocationID',
            'DOLocationID',
            'fare_amount',
            'trip_distance',
            'passenger_count'
        ) }} as base_hash,

        -- Trip type identifier
        'yellow' as trip_type,

        -- Timestamps
        tpep_pickup_datetime as pickup_datetime,
        tpep_dropoff_datetime as dropoff_datetime,

        -- Locations
        PULocationID as pickup_zone_id,
        DOLocationID as dropoff_zone_id,

        -- Trip details
        cast(passenger_count as integer) as passenger_count,
        cast(trip_distance as double) as trip_distance,

        -- Fare components
        cast(fare_amount as double) as fare_amount,
        cast(tip_amount as double) as tip_amount,
        cast(total_amount as double) as total_amount,

        -- Metadata
        current_timestamp as loaded_at

    from source
    where tpep_pickup_datetime is not null
      and tpep_dropoff_datetime is not null
),

staged as (
    select
        -- Unique trip ID: base_hash + row number for duplicates
        base_hash || '_' || cast(row_number() over (
            partition by base_hash order by pickup_datetime
        ) as varchar) as trip_id,
        * exclude (base_hash)
    from with_base_hash
)

select * from staged

Key Pattern: Deterministic IDs

TLC data ships without a trip identifier. So we build one: MD5 over the fields that describe the trip, which makes it reproducible across runs instead of tied to load order.

macros/generate_trip_id.sql

{% macro generate_trip_id(pickup_datetime, dropoff_datetime, pickup_zone,
                          dropoff_zone, extra_field_1, extra_field_2, extra_field_3) %}
    md5(
        coalesce(cast({{ pickup_datetime }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ dropoff_datetime }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ pickup_zone }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ dropoff_zone }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ extra_field_1 }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ extra_field_2 }} as varchar), '') ||
        '|' ||
        coalesce(cast({{ extra_field_3 }} as varchar), '')
    )
{% endmacro %}

Same trip, same ID, every run. Two genuinely identical rows still collide, which is why the staging model appends a row number to the hash. Without a stable key, incremental merges and reconciliation both fall apart.

Silver Layer: Cleaning & Enrichment

Unioning Trip Types

Staging already made the column names agree, so int_trips_unioned.sql is a plain union of all three trip types.

Zone Enrichment

Then join the TLC zone lookup twice, once for pickup and once for dropoff, and compute the fields nobody wants to rewrite in every query: duration, average speed, tip percentage.

int_trips_enriched.sql

{{
    config(
        materialized='view'
    )
}}

with trips as (
    select * from {{ ref('int_trips_unioned') }}
),

zones as (
    select * from {{ ref('stg_taxi_zones') }}
),

enriched as (
    select
        t.trip_id,
        t.trip_type,
        t.pickup_datetime,
        t.dropoff_datetime,
        t.pickup_zone_id,
        t.dropoff_zone_id,
        t.passenger_count,
        t.trip_distance,
        t.fare_amount,
        t.tip_amount,
        t.total_amount,
        t.loaded_at,

        -- Pickup zone info
        pz.zone_name as pickup_zone_name,
        pz.borough as pickup_borough,

        -- Dropoff zone info
        dz.zone_name as dropoff_zone_name,
        dz.borough as dropoff_borough,

        -- Calculated fields
        datediff('minute', t.pickup_datetime, t.dropoff_datetime)
            as trip_duration_minutes,

        -- Average speed (mph)
        case
            when t.trip_distance > 0
                 and datediff('minute', t.pickup_datetime, t.dropoff_datetime) > 0
            then t.trip_distance / (datediff('minute', t.pickup_datetime,
                                             t.dropoff_datetime) / 60.0)
            else null
        end as avg_speed_mph,

        -- Tip percentage
        case
            when t.fare_amount > 0 and t.tip_amount is not null
            then (t.tip_amount / t.fare_amount) * 100
            else null
        end as tip_percentage

    from trips t
    left join zones pz on t.pickup_zone_id = pz.zone_id
    left join zones dz on t.dropoff_zone_id = dz.zone_id
)

select * from enriched

Data Validation

int_trips_validated.sql drops rows that can't be real:

Trip duration between 1–180 minutes
Positive fares (for yellow/green)
Valid zone IDs
Non-negative distances

Gold Layer: Analytics-Ready

Gold holds pre-aggregated metrics. The part worth copying is the incremental config.

fct_daily_trips.sql

{{
    config(
        materialized='incremental',
        unique_key=['trip_date', 'pickup_borough', 'trip_type'],
        incremental_strategy='merge'
    )
}}

with trips as (
    select * from {{ ref('int_trips_validated') }}

    {% if is_incremental() %}
    -- 3-day lookback for late-arriving data
    where pickup_datetime >= (
        select dateadd('day', -3, max(trip_date))
        from {{ this }}
    )
    {% endif %}
),

daily_aggregates as (
    select
        cast(date_trunc('day', pickup_datetime) as date) as trip_date,
        pickup_borough,
        trip_type,

        -- Trip counts
        count(*) as total_trips,
        sum(coalesce(passenger_count, 0)) as total_passengers,

        -- Distance metrics
        sum(coalesce(trip_distance, 0)) as total_distance_miles,
        avg(trip_distance) as avg_distance_miles,

        -- Fare metrics
        sum(coalesce(fare_amount, 0)) as total_fare,
        avg(fare_amount) as avg_fare,
        sum(coalesce(tip_amount, 0)) as total_tips,
        avg(tip_percentage) as avg_tip_percentage,

        -- Duration metrics
        avg(trip_duration_minutes) as avg_duration_minutes,

        -- Metadata
        current_timestamp as updated_at

    from trips
    where pickup_borough is not null
    group by 1, 2, 3
)

select * from daily_aggregates

Key Pattern: 3-Day Lookback

The first run builds the whole table. Every run after that, the is_incremental() block trims the scan to the last three days, and the merge strategy overwrites those days instead of appending. Late-arriving trips land in the right bucket.

Skip this and you pick one of two bad options: miss the late rows, or rebuild the table every night. Three days is a guess about how late your source runs. Measure yours before you copy the number.

Data Quality Testing

Tests live in YAML next to the models they guard:

_staging.yml

version: 2

models:
  - name: stg_yellow_trips
    columns:
      - name: trip_id
        tests:
          - unique
          - not_null
      - name: pickup_datetime
        tests:
          - not_null
      - name: dropoff_datetime
        tests:
          - not_null
      - name: trip_type
        tests:
          - accepted_values:
              values: ['yellow']

Run tests with:

bash

cd dbt_project
uv run dbt test

Duplicate IDs, null timestamps, a trip type that shouldn't exist: the run fails at staging instead of surfacing as a strange number in a dashboard three weeks later.

CI/CD with GitHub Actions

Every push runs the pipeline end to end against a single month of data. A broken model fails the pull request, not the morning:

.github/workflows/dbt_ci.yml

name: dbt CI

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  dbt-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install UV
        run: curl -LsSf https://astral.sh/uv/install.sh | sh

      - name: Install dependencies
        run: uv sync

      - name: Download January data (CI subset)
        run: uv run python scripts/download_data.py --months 1

      - name: Setup warehouse
        run: uv run python scripts/setup_warehouse.py

      - name: Run dbt
        run: |
          cd dbt_project
          uv run dbt deps
          uv run dbt seed
          uv run dbt run
          uv run dbt test

Running the Pipeline

One command runs everything:

bash

# Full pipeline
uv run python scripts/run_pipeline.py

# Or step by step:
uv run python scripts/download_data.py   # ~10GB download
uv run python scripts/setup_warehouse.py

cd dbt_project
uv run dbt deps     # Install dbt packages
uv run dbt seed     # Load reference data
uv run dbt run      # Execute transformations
uv run dbt test     # Validate data quality

Sample Queries

Then query the gold layer:

sql

-- Daily trip summary by borough
SELECT
    trip_date,
    pickup_borough,
    trip_type,
    total_trips,
    avg_fare,
    avg_duration_minutes
FROM marts.fct_daily_trips
WHERE trip_date >= '2024-01-01'
ORDER BY trip_date, total_trips DESC;

-- Busiest zones
SELECT
    z.zone_name,
    z.borough,
    SUM(m.pickups) as total_pickups
FROM marts.fct_zone_metrics m
JOIN marts.dim_zones z ON m.zone_id = z.zone_id
GROUP BY z.zone_name, z.borough
ORDER BY total_pickups DESC
LIMIT 10;

Conclusion

The stack is small. The patterns are not:

Deterministic IDs: hash the fields that describe the row, and reruns stop inventing duplicates
Lookback windows: reprocess three days, not three months
Tests at every layer: bad rows die in staging, not in a dashboard
CI on the pipeline: the pull request breaks instead of production

Most of it moves to Snowflake or BigQuery with a new profile and some dialect edits. Which is the point: you learn the patterns on a laptop for nothing, and pay for compute only when the data actually outgrows it.

Next Steps

Connect a BI tool like Evidence or Metabase to the gold layer
Join weather or event data and see what actually moves trip demand
Add dbt snapshots if you need to track how the zone lookup changes

Check out the full source code on GitHub.