bigquery

dbt and BigQuery for Analytics

Analytics engineering project built with dbt and the dbt-bigquery 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.

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.yaml 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 environment variables for dbt-bigquery:

export DBT_BIGQUERY_PROJECT=iobruno-gcp-labs
export DBT_BIGQUERY_SOURCE_DATASET=raw_nyc_tlc_trip_data
export DBT_BIGQUERY_TARGET_DATASET=nyc_tlc_trip_data
export DBT_BIGQUERY_DATASET_LOCATION=us-central1

3.3. Since we're doing oauth authentication for our Development env, run:

gcloud auth login

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-bigquery:latest . --no-cache

2. Start a container with it:

docker run -d --rm \
  -e DBT_BIGQUERY_PROJECT=iobruno-gcp-labs \
  -e DBT_BIGQUERY_SOURCE_DATASET=raw_nyc_tlc_trip_data \
  -e DBT_BIGQUERY_TARGET_DATASET=nyc_tlc_trip_data \
  -e DBT_BIGQUERY_DATASET_LOCATION=us-central1 \
  -v /PATH/TO/YOUR/gcp_credentials.json:/secrets/gcp_credentials.json \
  --name dbt-bigquery \
  dbt-bigquery

TODO's:

• PEP-517: Packaging and dependency management with uv
• Bootstrap dbt with BigQuery Adapter (dbt-bigquery)
• Generate and serve docs and Data Lineage Graphs locally
• Add dbt macro to configure target schemas dinamically
• Run dbt-core in Docker
• Implement Data Quality metrics it with dbt-expectations
• Implement Data Observability with elementary-data