BQBrains | dbt on BigQuery: Incremental Strategies, Partition Configs, and Slim CI

Dataform ships inside BigQuery, and we have written about it. But most data teams that arrive at BigQuery from Redshift, Snowflake, or a pile of scheduled queries already run dbt — and dbt on BigQuery behaves differently enough from dbt on a cluster-based warehouse that copied-over patterns quietly burn money. This tutorial covers the parts that are BigQuery-specific: connection profiles, incremental strategies, partition and cluster configs, cost guardrails, and a CI pipeline that does not rescan your whole history on every pull request.

Assumed: dbt Core 1.8 or newer (or dbt Cloud on a comparable version) with dbt-bigquery installed, and a BigQuery project you can create datasets in.

1. A profile that will not surprise you

profiles.yml for BigQuery has a handful of options that matter far more than the docs' ordering suggests:

bqbrains_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth              # service-account / service-account-json / oauth-secrets in CI
      project: acme-analytics-dev
      dataset: dbt_jane          # personal dev schema
      location: US               # must match your source datasets' location
      threads: 8
      priority: interactive
      job_execution_timeout_seconds: 900
      job_retries: 2
      maximum_bytes_billed: 500000000000   # 500 GB hard stop
    prod:
      type: bigquery
      method: service-account
      keyfile: /secrets/dbt-prod.json
      project: acme-analytics-prod
      dataset: analytics
      location: US
      threads: 16
      priority: batch
      maximum_bytes_billed: 20000000000000 # 20 TB

Four of these deserve emphasis:

location must match the region of every dataset you touch. BigQuery will not join across regions, and the error arrives late.
maximum_bytes_billed is the single cheapest insurance policy in the file. A malformed where clause on a 400 TB event table fails in seconds instead of billing. Set it in every target, including dev — especially dev.
threads is dbt's concurrency, not BigQuery's. On on-demand pricing, more threads mostly means more parallel scans. On an Editions reservation, threads beyond your slot capacity just queue; see our editions guide for how that capacity is billed.
priority: batch in production lets BigQuery schedule jobs when capacity exists rather than failing on concurrency limits. Use interactive in dev where you are waiting on the result.

2. Partitioning and clustering belong in the model config

On BigQuery, physical layout is the performance lever. Every model of any size should declare it:

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {
      'field': 'event_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by = ['customer_id', 'event_name'],
    partition_expiration_days = 1095,
    require_partition_filter = true,
    labels = {'team': 'analytics', 'domain': 'events', 'dbt': 'true'}
) }}

Notes that cost people real money:

require_partition_filter = true forces every downstream consumer — including the analyst with the ad-hoc query and the BI tool — to filter on event_date. It is the most effective anti-full-scan control BigQuery offers. Be aware that dbt's own incremental logic must then also filter, which insert_overwrite does for you.
For integer or timestamp partitioning, use data_type: 'int64' with a range block, or data_type: 'timestamp' with granularity: hour|day|month|year. Hourly partitioning is rarely worth the 10,000-partition-per-table ceiling unless you genuinely query by hour.
cluster_by accepts up to four columns and is ordered — put the column you filter on most first. Clustering is free to declare and is re-sorted automatically as data is written.
labels propagate to the BigQuery job and table. Combine them with your billing export to attribute spend per team; this is the plumbing behind most usable chargeback models.
partition_expiration_days on event tables is the quietest storage saving available. Physical bytes drop without anyone writing a deletion script.

If you are still deciding between the two levers, our partitioning vs. clustering decision guide works through the thresholds.

3. Choosing an incremental strategy

dbt-bigquery offers four strategies. The choice determines whether your nightly run scans one partition or the whole table.

Strategy	How it writes	Best for	Watch out for
`merge` (default)	One `MERGE` on `unique_key`	Dimensions, slowly-changing entities, late-arriving updates	Scans all partitions of the target unless you constrain it
`insert_overwrite`	Deletes and replaces whole partitions	Large append-mostly fact and event tables	Requires `partition_by`; loses rows in a partition you do not re-emit
`microbatch`	dbt splits the run into per-period batches	Very large backfills, wide date ranges	Requires `event_time`, `batch_size`, `begin`; more jobs, better retryability
`append`	Plain `insert`	Immutable logs where duplicates are impossible	No deduplication whatsoever

`insert_overwrite` done right

The dynamic form is the default and is fine for most pipelines:

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'event_date', 'data_type': 'date', 'granularity': 'day'}
) }}

select
    date(event_timestamp)                 as event_date,
    event_timestamp,
    customer_id,
    event_name,
    payload
from {{ source('raw', 'events') }}
{% if is_incremental() %}
  -- only rescan the trailing window of the source
  where date(event_timestamp) >= date_sub(current_date(), interval 3 day)
{% endif %}

dbt writes the selected rows to a temp table, reads back the distinct partitions present, and atomically replaces exactly those partitions in the target. The two failure modes to internalize:

You must re-emit every row for a partition you touch. If your where clause returns a single corrected row for last Tuesday, insert_overwrite replaces Tuesday's whole partition with that one row. Always filter on a date window, never on a single changed record.
The source-side filter is what saves money, not the strategy. The strategy controls the write; your is_incremental() predicate controls the scan. A three-day trailing window is a good default because it absorbs late-arriving data without rescanning years.

For the cheaper static form, declare partitions explicitly and let dbt skip the discovery query entirely:

{% set partitions_to_replace = [
  'current_date()',
  'date_sub(current_date(), interval 1 day)',
  'date_sub(current_date(), interval 2 day)'
] %}

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'event_date', 'data_type': 'date', 'granularity': 'day'},
    partitions = partitions_to_replace
) }}

And if your source is already partitioned identically, add copy_partitions: true to the partition_by config. dbt then uses the BigQuery table-copy API instead of a query job — no bytes billed for the write at all. This is one of the largest single wins available to a mature dbt-on-BigQuery project, and almost nobody switches it on.

Constraining `merge`

merge is the right strategy for dimensions, but an unconstrained MERGE scans every partition of the target. Tell BigQuery where to look:

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'merge',
    unique_key = 'customer_id',
    partition_by = {'field': 'updated_date', 'data_type': 'date'},
    incremental_predicates = [
      "DBT_INTERNAL_DEST.updated_date >= date_sub(current_date(), interval 14 day)"
    ],
    on_schema_change = 'append_new_columns'
) }}

incremental_predicates is appended to the MERGE join condition and prunes the destination scan. Without it, the merge cost grows with table history forever, which is exactly the "why did our costs creep 4% a month" pattern we see most often in cost optimization engagements.

on_schema_change is worth setting deliberately: ignore (default) silently drops new source columns, append_new_columns is usually what people want, sync_all_columns also removes dropped ones, and fail is the honest choice for regulated pipelines.

4. Materialized views and other BigQuery-only materializations

dbt-bigquery supports materialized_view as a first-class materialization:

{{ config(
    materialized = 'materialized_view',
    on_configuration_change = 'apply',
    enable_refresh = true,
    refresh_interval_minutes = 30,
    max_staleness = 'INTERVAL 1 HOUR',
    partition_by = {'field': 'event_date', 'data_type': 'date'}
) }}

select event_date, customer_id, count(*) as events
from {{ ref('fct_events') }}
group by 1, 2

BigQuery keeps it incrementally refreshed and — importantly — will automatically rewrite matching queries against the base table to use the MV. That makes MVs a dashboard-latency tool as much as a modelling one; the mechanics are in our sub-second dashboards guide. The constraints are real, though: limited SQL surface (no outer joins, no window functions), and MVs on top of insert_overwrite models get invalidated whenever a partition is replaced wholesale.

5. Tests, unit tests, and contracts

Three layers, cheapest first:

models:
  - name: fct_events
    config:
      contract: {enforced: true}
    columns:
      - name: event_id
        data_type: string
        constraints: [{type: not_null}]
        data_tests: [unique]
      - name: event_date
        data_type: date
    data_tests:
      - dbt_utils.recency:
          datepart: hour
          field: event_timestamp
          interval: 6
    unit_tests:
      - name: sessionisation_splits_on_30_minute_gap
        given:
          - input: ref('stg_events')
            rows:
              - {customer_id: 'c1', event_timestamp: '2026-01-01 10:00:00'}
              - {customer_id: 'c1', event_timestamp: '2026-01-01 10:45:00'}
        expect:
          rows:
            - {customer_id: 'c1', session_count: 2}

Contracts (enforced: true) make dbt build the model with an explicit schema, so a type change fails at build time instead of breaking a downstream BI field.
Unit tests run on fixture rows, not warehouse data. They are near-free to run and are the only practical way to test gnarly window-function logic on every commit.
Data tests run against real data and do cost bytes. Put where filters on recency-style tests so they scan one partition, and reserve full-table uniqueness tests for a nightly job rather than every PR.

6. Slim CI that does not scan your warehouse

The standard failure is a CI job that runs dbt build over everything on every pull request, at production data volumes. The fix has three parts.

One: a per-PR dataset. Set dataset: dbt_ci_pr_{{ env_var('PR_NUMBER') }} in the CI target and drop it after the run. No collisions between concurrent PRs.

Two: state deferral. Upload manifest.json from every production run to GCS, then in CI:

gsutil cp gs://acme-dbt-artifacts/prod/manifest.json ./state/manifest.json

dbt build \
  --select 'state:modified+' \
  --defer --state ./state \
  --favor-state \
  --target ci

dbt builds only the changed models and their descendants, and resolves every unchanged ref() to the production relation. A 900-model project typically builds 5 to 30 models per PR.

Three: bound the data. Use a dbt variable that CI sets and production does not:

{% if target.name == 'ci' %}
  where event_date >= date_sub(current_date(), interval {{ var('ci_days', 3) }} day)
{% endif %}

Combine that with maximum_bytes_billed on the CI target and a runaway PR becomes a failed job instead of an invoice. Finish the pipeline with dbt build --select state:modified+ --exclude tag:expensive and a nightly full dbt build on production.

7. dbt or Dataform?

Both compile to BigQuery SQL and both give you dependency graphs, tests, and version control. In practice:

Dataform is free, GA inside BigQuery, needs no separate runtime or scheduler, and is what Google's Data Engineering Agent generates. Choose it for greenfield BigQuery-only estates that want one fewer thing to operate.
dbt has the deeper ecosystem (packages, dbt_utils, semantic layer, unit tests, contracts, thousands of engineers who already know it) and stays portable if BigQuery is not your only warehouse. Choose it if you already run dbt anywhere, or if you need the package ecosystem.

Running both is a legitimate transition state, but pick one owner per model. Two tools writing the same table is how you get silent overwrites.

Checklist before you call it production

maximum_bytes_billed set on every target, dev included.
Every model over ~10 GB has partition_by, and most have cluster_by.
Every incremental model's is_incremental() branch filters the source on a bounded window.
merge models have incremental_predicates; insert_overwrite models re-emit whole partitions.
copy_partitions: true evaluated wherever source and target share a partition scheme.
labels on every model, flowing into billing-export-based cost attribution.
CI uses state:modified+ with deferral, a disposable dataset, and a bounded date window.
on_schema_change chosen deliberately rather than left at ignore.

Need a second pair of eyes on a dbt-on-BigQuery project? BQBrains reviews and rebuilds BigQuery ELT — incremental strategy, partition design, CI pipelines, and the cost model underneath them. See our BigQuery data engineering and cost optimization services, or get in touch with a description of your project.

dbt on BigQuery: Incremental Strategies, Partition Configs, and Slim CI