dbt with BigQuery

BigQuery Architecture

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     BIGQUERY + DBT ARCHITECTURE                             │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    BIGQUERY COMPONENTS                               │   │
│  │                                                                     │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐ │   │
│  │  │   PROJECTS   │  │  DATASETS    │  │    TABLES                │ │   │
│  │  │              │  │              │  │                          │ │   │
│  │  │ • GCP        │  │ • Schema     │  │ • Native                 │ │   │
│  │  │   project    │  │   equivalent │  │ • External               │ │   │
│  │  │ • Billing    │  │ • Access     │  │ • Materialized           │ │   │
│  │  │   account    │  │   control    │  │   views                  │ │   │
│  │  └──────────────┘  └──────────────┘  └──────────────────────────┘ │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                              │                                              │
│                              ▼                                              │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    DBT + BIGQUERY                                    │   │
│  │                                                                     │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐ │   │
│  │  │  PARTITION   │  │  CLUSTER     │  │    OPTIMIZATION          │ │   │
│  │  │              │  │              │  │                          │ │   │
│  │  │ • Date       │  │ • Column     │  │ • Slot management        │ │   │
│  │  │ • Timestamp  │  │   ordering   │  │ • Cost control           │ │   │
│  │  │ • Integer    │  │ • Up to 4    │  │ • Query optimization     │ │   │
│  │  └──────────────┘  └──────────────┘  └──────────────────────────┘ │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Partitioning Strategy

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     BIGQUERY PARTITIONING                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    PARTITION TYPES                                   │   │
│  │                                                                     │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐ │   │
│  │  │    DAY       │  │   HOUR       │  │    INTEGER RANGE         │ │   │
│  │  │              │  │              │  │                          │ │   │
│  │  │ 365          │  │ 8760         │  │ Custom                   │ │   │
│  │  │ partitions   │  │ partitions   │  │ ranges                   │ │   │
│  │  │ per year     │  │ per year     │  │                          │ │   │
│  │  └──────────────┘  └──────────────┘  └──────────────────────────┘ │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                              │                                              │
│                              ▼                                              │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    PARTITION PRUNING                                 │   │
│  │                                                                     │   │
│  │  Query: SELECT * FROM table WHERE date = '2024-01-01'              │   │
│  │                                                                     │   │
│  │  Without partitioning:                                               │   │
│  │  ├── Scan: 100M rows                                               │   │
│  │  ├── Cost: $5.00                                                   │   │
│  │  └── Time: 10 seconds                                              │   │
│  │                                                                     │   │
│  │  With partitioning:                                                  │   │
│  │  ├── Scan: 274K rows (1 day)                                       │   │
│  │  ├── Cost: $0.01                                                   │   │
│  │  └── Time: 0.5 seconds                                             │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Clustering Strategy

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     BIGQUERY CLUSTERING                                      │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    CLUSTERING COLUMNS                                │   │
│  │                                                                     │   │
│  │  Table: fct_orders                                                  │   │
│  │  Clustered by: customer_id, order_status, product_category         │   │
│  │                                                                     │   │
│  │  ┌─────────────────────────────────────────────────────────────┐   │   │
│  │  │  Data blocks organized by:                                  │   │   │
│  │  │                                                             │   │   │
│  │  │  Block 1: customer_id=1001, status=active, category=books  │   │   │
│  │  │  Block 2: customer_id=1001, status=active, category=tech   │   │   │
│  │  │  Block 3: customer_id=1002, status=pending, category=books │   │   │
│  │  │  Block 4: customer_id=1002, status=shipped, category=tech  │   │   │
│  │  └─────────────────────────────────────────────────────────────┘   │   │
│  │                                                                     │   │
│  │  Query optimization:                                                │   │
│  │  WHERE customer_id = 1001 AND order_status = 'active'             │   │
│  │  → Only scan Blocks 1-2 (50% reduction)                           │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Detailed Explanation

BigQuery is a serverless, highly scalable data warehouse that integrates seamlessly with dbt for data transformation.

BigQuery + dbt Features

Native Integration: Direct SQL execution
Partitioning: Time and integer-based partitioning
Clustering: Column-based data organization
Materialized Views: Pre-computed aggregations
Slot Management: Compute resource allocation

Partitioning in BigQuery

BigQuery supports several partitioning strategies:

Day-partitioned: Partition by date column
Hour-partitioned: Partition by timestamp (hour granularity)
Integer-range: Partition by integer column
Ingestion-time: Partition by data insertion time

Clustering in BigQuery

Clustering organizes data within partitions:

Up to 4 clustering columns
Automatic re-clustering
Improved query performance
Reduced costs

Cost Optimization

BigQuery pricing factors:

Storage: $0.02/GB/month
Query: $5/TB scanned
Streaming: $0.01/200MB
Slots: Variable pricing

Best Practices for BigQuery

Always partition large tables
Cluster by query patterns - filter/join columns
Use incremental models - minimize data scanned
Partition expiration - manage storage costs
Use materialized views - for frequent aggregations
Monitor slot usage - optimize compute resources
Use dry runs - estimate query costs
Leverage caching - reduce repeated queries

Code Examples

BigQuery Profile Configuration

# profiles.yml
my_profile:
  target: dev
  
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: my-gcp-project
      dataset: dbt_dev
      threads: 8
      timeout_seconds: 300
      location: US
      priority: interactive
      retries: 1
      maximum_bytes_billed: 1000000000  # 1GB limit
    
    prod:
      type: bigquery
      method: service-account
      project: my-gcp-project
      dataset: analytics_prod
      threads: 16
      timeout_seconds: 600
      location: US
      priority: batch
      retries: 3
      maximum_bytes_billed: 100000000000  # 100GB limit

Partitioned Model

-- models/marts/fct_orders.sql
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        partition_by={
            "field": "order_date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['customer_id', 'order_status', 'product_category'],
        incremental_strategy='merge'
    )
}}

with orders as (
    select * from {{ ref('stg_orders') }}
),

final as (
    select
        order_id,
        customer_id,
        order_date,
        order_status,
        product_category,
        amount,
        current_timestamp() as updated_at
    from orders
)

select * from final

{% if is_incremental() %}
where order_date >= date_sub(
    (select max(order_date) from {{ this }}),
    interval 7 day
)
{% endif %}

Materialized View

-- models/marts/mv_daily_revenue.sql
{{
    config(
        materialized='materialized_view',
        partition_by={
            "field": "order_date",
            "data_type": "date"
        },
        enable_refresh=true,
        refresh_interval_minutes=60
    )
}}

with daily_revenue as (
    select
        order_date,
        product_category,
        sum(amount) as total_revenue,
        count(*) as order_count
    from {{ ref('fct_orders') }}
    group by 1, 2
)

select * from daily_revenue

External Table

-- models/staging/stg_external_data.sql
{{
    config(
        materialized='view'
    )
}}

with external_data as (
    select * from {{ source('external', 'gcs_data') }}
)

select * from external_data

BigQuery-Specific Macros

-- macros/bigquery/safe_divide.sql
{% macro safe_divide(numerator, denominator) %}
    safe_divide({{ numerator }}, {{ denominator }})
{% endmacro %}

-- macros/bigquery/array_agg.sql
{% macro array_agg(column_name) %}
    array_agg({{ column_name }} ignore nulls)
{% endmacro %}

-- macros/bigquery/struct.sql
{% macro make_struct(columns) %}
    struct({{ columns | join(', ') }})
{% endmacro %}

Incremental with BigQuery Optimizations

-- models/marts/fct_events.sql
{{
    config(
        materialized='incremental',
        unique_key='event_id',
        partition_by={
            "field": "event_timestamp",
            "data_type": "timestamp",
            "granularity": "hour"
        },
        cluster_by=['user_id', 'event_type'],
        incremental_strategy='merge',
        post_hook=[
            "analyze table {{ this }} compute statistics for all columns"
        ]
    )
}}

with events as (
    select * from {{ ref('stg_events') }}
),

final as (
    select
        event_id,
        user_id,
        event_type,
        event_timestamp,
        event_properties,
        current_timestamp() as updated_at
    from events
)

select * from final

{% if is_incremental() %}
where event_timestamp >= timestamp_sub(
    (select max(event_timestamp) from {{ this }}),
    interval 3 day
)
{% endif %}

Performance Metrics

Metric	Without Optimization	With Optimization	Improvement
Query Time	10s	0.5s	95% faster
Cost	$5.00	$0.01	99.8% cheaper
Storage	100GB	100GB	Same
Scan	100M rows	274K rows	99.7% less

Best Practices

Always partition large tables by date
Cluster by query patterns - filter/join columns
Use incremental models - minimize data scanned
Partition expiration - manage storage costs
Use materialized views - for frequent aggregations
Monitor slot usage - optimize compute resources
Use dry runs - estimate query costs
Leverage caching - reduce repeated queries

dbt with BigQuery

dbt with BigQuery

BigQuery Architecture

Partitioning Strategy

Clustering Strategy

Detailed Explanation

BigQuery + dbt Features

Partitioning in BigQuery

Clustering in BigQuery

Cost Optimization

Best Practices for BigQuery

Code Examples

BigQuery Profile Configuration

Partitioned Model

Materialized View

External Table

BigQuery-Specific Macros

Incremental with BigQuery Optimizations

Performance Metrics

Best Practices

Need Expert dbt Help?