02 - dbt: BigQuery Adapter

16 min read

dbt: BigQuery Adapter

Quote

“Serverless is a simple but powerful concept when it comes to gigabyte- to petabyte-scale data analysis. It’s a relatively hard engineering problem.”

— Jordan Tigani (founding engineer of BigQuery)

BigQuery Adapter Installation

pip install dbt-core==1.8.* dbt-bigquery==1.8.*
 
# Confirm the adapter is registered
dbt --version
# Should show: - bigquery: 1.8.x

dbt-bigquery depends on google-cloud-bigquery. No additional system-level drivers are required — authentication is handled through ADC or explicit credentials.

profiles.yml

Service Account JSON (CI / GCE without Workload Identity)

# ~/.dbt/profiles.yml
financial_index:
  target: dev
  outputs:
 
    dev:
      type: bigquery
      method: service-account
      project: fi-data-dev
      dataset: dbt_dev
      keyfile: /secrets/dbt-sa-key.json   # path to downloaded SA key
      location: US
      threads: 8
      timeout_seconds: 600
      priority: interactive
 
    prod:
      type: bigquery
      method: service-account
      project: fi-data-prod
      dataset: dbt_prod
      keyfile: "{{ env_var('DBT_BQ_KEYFILE') }}"
      location: US
      threads: 16
      timeout_seconds: 1800
      priority: batch

Prefer attached identities over JSON keys on GCE

On Compute Engine or GKE, prefer attached service accounts or explicit service-account impersonation instead of downloadable JSON keys. That removes key-rotation overhead and reduces long-lived secret sprawl.

Application Default Credentials (local development)

    local:
      type: bigquery
      method: oauth
      project: fi-data-dev
      dataset: dbt_dev_yourname
      location: US
      threads: 4
      timeout_seconds: 300

Before running: gcloud auth application-default login --scopes=https://www.googleapis.com/auth/bigquery.

Service Account Impersonation

    impersonated:
      type: bigquery
      method: oauth
      project: fi-data-prod
      dataset: dbt_prod
      impersonate_service_account: dbt-runner@fi-data-prod.iam.gserviceaccount.com
      location: US
      threads: 8
      timeout_seconds: 600

Requires the caller’s identity to have roles/iam.serviceAccountTokenCreator on the target SA. Useful for developers who need prod-read access without holding a prod SA key locally.

Partitioning

Partitioning is the most impactful BigQuery optimization for time-series financial data. Every mart table keyed by score_date, as_of_date, or trade_date should be partitioned.

-- models/mart/mart_esg_scores.sql
{{
  config(
    materialized = 'table',
    partition_by = {
      "field": "score_date",
      "data_type": "date",
      "granularity": "month"    -- day | month | year
    },
    cluster_by          = ["isin", "provider_code"],
    require_partition_filter = true,
    labels              = {"domain": "esg", "layer": "mart"}
  )
}}
 
select
    score_id,
    isin,
    provider_code,
    score_date,
    environmental_score,
    social_score,
    governance_score,
    composite_score
from {{ ref('int_esg_scores_validated') }}

Partition Granularity Guide

GranularityUse whenPartition count
dayHigh-frequency data updated daily; date-range queries on narrow windowsUp to 4,000 partitions
monthMonthly index rebalancing, ESG scores updated monthlyManageable; good default
yearHistorical archives queried by yearVery few partitions; less pruning benefit

Require partition filter on marts

Enabling require_partition_filter = true on mart tables prevents accidental full-table scans from BI tools. Any query that does not include a filter on the partition column will be rejected with an error. Use it on analyst-facing marts and on large incrementals only after checking the generated SQL still satisfies the partition requirement.

Safe pattern

Set require_partition_filter = true only in config() blocks for mart and incremental models. Leave staging models without this setting. In dbt config: require_partition_filter = true at the mart layer, omit it entirely in staging model configs.

Integer Range Partitioning

For tables without a natural date column — e.g., a universe table partitioned by index code hash:

{{
  config(
    materialized = 'table',
    partition_by = {
      "field": "index_id",
      "data_type": "int64",
      "range": {
        "start": 0,
        "end": 1000,
        "interval": 100
      }
    }
  )
}}

BigQuery Clustering

Clustering sorts data within each partition by the specified columns. BigQuery automatically re-clusters as data accumulates. Clustering is free and has no maintenance overhead.

{{
  config(
    materialized = 'incremental',
    partition_by = {"field": "score_date", "data_type": "date", "granularity": "month"},
    cluster_by   = ["isin", "provider_code", "score_type"]
    -- Up to 4 cluster columns; order matters — most selective first
  )
}}

Clustering vs partitioning

Partitioning prunes at the storage level before any bytes are scanned. Clustering prunes within a partition — it is a secondary optimization. Always partition first, then cluster on the most common filter/join columns.

Incremental Strategy

merge (default)

BigQuery’s native MERGE DML. Safe and correct for most use cases.

{{
  config(
    materialized         = 'incremental',
    unique_key           = 'score_id',
    incremental_strategy = 'merge',
    partition_by         = {"field": "score_date", "data_type": "date", "granularity": "month"},
    cluster_by           = ["isin", "provider_code"]
  )
}}
 
with new_scores as (
    select *
    from {{ ref('stg_esg_raw_scores') }}
    {% if is_incremental() %}
    where score_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    {% endif %}
)
 
select * from new_scores

The adapter generates:

MERGE INTO `fi-data-prod.dbt_prod.mart_esg_scores` AS DBT_INTERNAL_DEST
USING (select * from new_scores) AS DBT_INTERNAL_SOURCE
ON DBT_INTERNAL_SOURCE.score_id = DBT_INTERNAL_DEST.score_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

insert_overwrite (partition swap)

Replaces entire partitions atomically. More efficient than MERGE for large partition-aligned loads — no per-row comparison overhead.

{{
  config(
    materialized         = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by         = {"field": "score_date", "data_type": "date", "granularity": "month"},
    -- No unique_key required — entire partitions are replaced
  )
}}
 
select *
from {{ ref('stg_esg_raw_scores') }}
{% if is_incremental() %}
-- Only process partitions for months that have new/changed data
where DATE_TRUNC(score_date, MONTH) IN (
    select DISTINCT DATE_TRUNC(score_date, MONTH)
    from {{ ref('stg_esg_raw_scores') }}
    where _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
)
{% endif %}

When to use insert_overwrite

  • Source data arrives in complete monthly batches (ESG providers often send full-month corrections).
  • Reprocessing historical partitions is common.
  • The table is too large for MERGE to be economical (MERGE scans the full target table for non-partitioned MERGE keys).

Avoid insert_overwrite when you need row-level upsert semantics within a partition.

BigQuery Slot Estimation and Thread Tuning

BigQuery slots are units of compute. In on-demand projects, dbt queries compete for shared BigQuery compute capacity, and each query consumes slots proportional to its complexity and data volume.

# profiles.yml thread settings
prod:
  threads: 16      # dbt parallelism — concurrent queries
  priority: batch  # batch | interactive
  • interactive: queries compete for slots immediately; subject to fair-use limits; higher priority.
  • batch: queries are queued; start within 24 hours; no slot reservation required. Use for scheduled dbt production runs to avoid slot contention with analysts.

Threads vs BigQuery slots

threads: 16 means dbt submits 16 queries concurrently. Each of those queries may consume hundreds or thousands of slots. Setting threads too high on a shared project can cause slot exhaustion and query queuing. Start with threads: 8 and increase after confirming slot availability via the BigQuery Admin Console.

Safe starting configuration

Begin with threads: 8 and priority: batch in production profiles. Monitor slot utilisation in the BigQuery Admin Console (INFORMATION_SCHEMA.JOBS_BY_PROJECT) for at least one full pipeline cycle before increasing thread count. Reserve slots via BigQuery Reservations if you need guaranteed capacity.

BigQuery Labels for Cost Attribution

Labels propagate to BigQuery job metadata and appear in Cloud Billing exports. Mandatory for multi-team environments.

{{
  config(
    materialized = 'table',
    labels       = {
      "dbt_model":   "mart_esg_scores",
      "domain":      "esg",
      "layer":       "mart",
      "team":        "data-engineering",
      "cost_center": "cc-1234"
    }
  )
}}

Labels can also be set at the project level in dbt_project.yml:

# dbt_project.yml
models:
  financial_index:
    +labels:
      project: financial-index
      managed_by: dbt
    mart:
      +labels:
        layer: mart
    staging:
      +labels:
        layer: staging

Query BigQuery INFORMATION_SCHEMA to track model-level cost:

SELECT
    labels.value                                    AS dbt_model,
    SUM(total_bytes_processed) / POW(10, 12)        AS tb_processed,
    SUM(total_slot_ms) / 1000 / 3600               AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(labels) AS labels
WHERE labels.key = 'dbt_model'
  AND DATE(creation_time) = CURRENT_DATE()
GROUP BY 1
ORDER BY 2 DESC;

BigQuery SQL Patterns

BigQuery SQL — SAFE_DIVIDE

-- Avoids ZeroDivisionError at the SQL engine level — returns NULL instead
SELECT
    isin,
    SAFE_DIVIDE(environmental_score, composite_score) AS env_weight,
    -- Equivalent in SQL Server: environmental_score * 1.0 / NULLIF(composite_score, 0)
    SAFE_DIVIDE(social_score, composite_score)        AS social_weight
FROM {{ ref('int_esg_scores_validated') }}

BigQuery SQL — DATE_TRUNC

-- Truncate to period start
SELECT
    DATE_TRUNC(score_date, MONTH)   AS score_month,
    DATE_TRUNC(score_date, QUARTER) AS score_quarter,
    DATE_TRUNC(score_date, YEAR)    AS score_year,
    DATE_TRUNC(score_date, WEEK)    AS score_week_start   -- Monday
FROM {{ ref('stg_esg_raw_scores') }}
 
-- Date arithmetic with INTERVAL
DATE_ADD(score_date, INTERVAL 1 MONTH)
DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
DATE_DIFF(end_date, start_date, DAY)

BigQuery SQL — STRUCT and ARRAY

Useful for packing provider-level score breakdowns without a separate table:

SELECT
    isin,
    score_date,
    STRUCT(
        environmental_score AS e,
        social_score        AS s,
        governance_score    AS g
    )                       AS esg_components,
    ARRAY_AGG(
        STRUCT(provider_code, composite_score)
        ORDER BY composite_score DESC
    )                       AS provider_scores
FROM {{ ref('int_esg_scores_validated') }}
GROUP BY isin, score_date, esg_components

STRUCT/ARRAY limitations

Nested types work well for analytical queries but are not compatible with dbt-sqlserver. Any model using STRUCT/ARRAY must live in a BigQuery-specific folder or be guarded by target.type checks. See dbt-cross-adapter-patterns for the dispatch pattern.

BigQuery SQL — MERGE DML (manual)

When the dbt incremental MERGE is not granular enough, write explicit MERGE in a post-hook or operation:

MERGE `fi-data-prod.dbt_prod.dim_index_constituents` AS target
USING (
    SELECT isin, index_code, weight, effective_date
    FROM `fi-data-prod.dbt_staging.stg_index_constituents`
    WHERE effective_date = CURRENT_DATE()
) AS source
ON target.isin = source.isin
   AND target.index_code = source.index_code
   AND target.effective_date = source.effective_date
WHEN MATCHED AND target.weight != source.weight THEN
    UPDATE SET target.weight = source.weight,
               target.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (isin, index_code, weight, effective_date, updated_at)
    VALUES (source.isin, source.index_code, source.weight,
            source.effective_date, CURRENT_TIMESTAMP())

Materialized Views and BI Engine

BigQuery Materialized Views

-- models/mart/mv_esg_monthly_avg.sql
{{
  config(
    materialized = 'materialized_view'
  )
}}
 
SELECT
    DATE_TRUNC(score_date, MONTH) AS score_month,
    provider_code,
    isin,
    AVG(composite_score)          AS avg_composite_score,
    COUNT(*)                      AS score_count
FROM {{ ref('mart_esg_scores') }}
GROUP BY 1, 2, 3

BI Engine acceleration

BI Engine accelerates queries on supported tables and materialized views in the same region as the BI Engine reservation. Keep mart datasets and BI Engine reservations aligned by location, and verify support for nested-heavy queries against current BigQuery platform limits.

Cost Control

maximum_bytes_billed

Hard cap per query. Queries exceeding the limit fail with an error rather than incurring unexpected charges.

# profiles.yml
prod:
  maximum_bytes_billed: 107374182400   # 100 GB in bytes

Set this in production to prevent runaway full-table scans during dbt runs. A model that accidentally drops its partition filter will fail fast rather than billing for a full-table scan.

Dry Run via bq CLI

Before running an expensive model, estimate bytes:

bq query --dry_run --use_legacy_sql=false \
  'SELECT * FROM `fi-data-prod.dbt_prod.mart_esg_scores`
   WHERE score_date >= "2024-01-01"'
# Output: Query successfully validated. Assuming the tables are not modified,
# running this query will process 2147483648 bytes.

In dbt, use dbt compile to get the rendered SQL, then pipe it to bq query --dry_run.

BigQuery External Tables with Hive Partitioning

For raw ESG provider files landed in GCS with a Hive-style path structure (see data-loading-and-export for the upstream loading patterns that produce these files):

gs://fi-raw-data/esg_scores/provider=msci/score_year=2024/score_month=01/scores.parquet

# models/sources.yml
sources:
  - name: ext_esg
    schema: raw_external
    tables:
      - name: esg_scores_gcs
        external:
          location: "gs://fi-raw-data/esg_scores/*"
          options:
            format: parquet
            hive_partition_uri_prefix: "gs://fi-raw-data/esg_scores"
            require_hive_partition_filter: false

Manage BigQuery external tables through dbt-external-tables source definitions, not through a normal dbt model materialization. Add the package to packages.yml:

packages:
  - package: dbt-labs/dbt_external_tables
    version: 0.12.1

Then define the source metadata in sources.yml and run dbt run-operation stage_external_sources.

Graph View

Backlinks