duckdb

dbt and DuckDB for Analytics

Analytics engineering project built with dbt and the dbt-duckdb adapter that transforms NYC TLC Trip Record Parquet data into a Kimball dimensional model — from raw ingestion through staging views to materialized dimensions and fact tables covering zone-level revenue, fare percentiles, travel time distributions, and year-over-year growth across Yellow Taxi, Green Taxi, and For-Hire Vehicle services. Supports sourcing data from GCS, S3, or local filesystem.

Getting Started

1. Install dependencies from pyproject.toml and activate the created virtualenv:

uv sync && source .venv/bin/activate

2. (Optional) Install pre-commit:

brew install pre-commit

# From root folder where `.pre-commit-config.yaml` is located, run:
pre-commit install

3. Setup dbt profiles.yaml accordingly (use the profiles.tmpl.yml as template)

3.1. By default, the profiles_dir is the user '$HOME/.dbt/'

mkdir -p ~/.dbt/
cat profiles.tmpl.yml >> ~/.dbt/profiles.yml

3.2. Set the auth methods (when appliable) and the ENV variables for DuckDB source and destination:

Google Cloud Storage (gcsfs) - when attempting to read data from gcs, first you must authenticate with:

gcloud auth login

export DBT_DUCKDB_SOURCE_PARQUET_BASE_PATH="gs://iobruno-lakehouse-raw/nyc_tlc_dataset/"

AWS S3 (s3fs) - when attempting to read data from s3, first you must set these AWS ENV VARS:

export AWS_ACCESS_KEY=
export AWS_SECRET_ACCESS_KEY=

export DBT_DUCKDB_SOURCE_PARQUET_BASE_PATH="s3://iobruno-lakehouse-raw/nyc_tlc_dataset/"

Local FS - no additional config required

export DBT_DUCKDB_SOURCE_PARQUET_BASE_PATH="/path-to/nyc_tlc_dataset/"

3.3. Set the env var for where DuckDB should store its internal data

export DBT_DUCKDB_TARGET_PATH=~/.duckdb/dbt.duckdb

3.4. (Optional) you can also set the DuckDB schemas where the dbt staging & core models should land on:

# DuckDB schema for the `dim_` and `fct_ models` - defaults to 'main' if not set
export DBT_DUCKDB_TARGET_SCHEMA=analytics=

# DuckDB for the stg_ models - defaults to 'main' if not set
export DBT_DUCKDB_STAGING_SCHEMA=stg_analytics=

4. Install dbt dependencies and trigger the pipeline

4.1. Run dbt deps to install dbt plugins

dbt deps

4.2. Run dbt build to trigger the dbt models to run

dbt build

# Alternatively you can run only a subset of the models with:

## +models/staging: Runs the dependencies/preceding models first that lead 
## to 'models/staging', and then the target models
dbt [build|run] --select +models/staging

## models/staging+: Runs the target models first, and then all models that depend on it
dbt [build|run] --select models/staging+

5. Generate the Docs and the Data Lineage graph with:

dbt docs generate
dbt docs serve

Access the generated docs at:

open http://localhost:8080

Containerization

1. Build the Docker Image with:

docker build -t dbt-duckdb:latest . --no-cache

2. Start a container with it:

docker run -d --rm \
  -e DBT_DUCKDB_SOURCE_PARQUET_BASE_PATH="gs://iobruno-lakehouse-raw/nyc_tlc_dataset/" \
  -e DBT_DUCKDB_TARGET_PATH=/duckdb/dbt.duckdb \
  -e DBT_DUCKDB_TARGET_SCHEMA=analytics \
  -v ~/.duckdb:/duckdb \
  -v PATH/TO/YOUR/gcp_credentials.json:/secrets/gcp_credentials.json \
  --name dbt-duckdb \
  dbt-duckdb

TODO's:

• PEP-517: Packaging and dependency management with uv
• Bootstrap dbt with DuckDB Adapter (dbt-duckdb)
• Configure dbt-duckdb with fsspec and read from gcsfs
• Configure dbt-duckdb with fsspec and read from s3fs