BIGQUERY

A COMPLETE PROFILE

Here is a complete, working output using a service-account keyfile — the auth method that works everywhere in DVT. Copy it, change the values, and dvt debug --all will tell you the moment it connects:

# ~/.dbt/profiles.yml
my_project:
  target: bigquery
  outputs:
    bigquery:
      type: bigquery
      method: service-account
      project: my-gcp-project           # the GCP project queries bill to
      dataset: analytics                # models build here
      keyfile: /secrets/dvt-bigquery.json
      location: US                      # optional — where datasets live
      threads: 4

The keyfile path expands ~ and is checked before any connection is attempted — a missing file is a named error, not a mysterious auth failure. And method: service-account means it: leave the keyfile out and DVT refuses up front with method 'service-account' requires keyfile.

OAUTH / ADC — HONESTLY

dbt-bigquery also speaks method: oauth— Google's Application Default Credentials, the gcloud auth application-default login flow. In DVT that works when BigQuery is your default target — the dbt lane handles it — but the data-movement lane cannot federate with it: federation needs the service-account key itself, as keyfile or keyfile_json, and an output without one is refused the moment it is used in federation.

The practical guidance is simple: give the output a service-account key and every part of DVT works with it. Keep an OAuth output for default-target runs on your own laptop if you like — but the output your federated models point at carries a keyfile.

INLINE KEY JSON — KEYFILE_JSON

For CI systems where secrets arrive as environment variables and nothing may touch disk, paste the service-account key itself into the profile as keyfile_json:

    bigquery:
      type: bigquery
      project: my-gcp-project
      dataset: analytics
      keyfile_json: "{{ env_var('BQ_KEYFILE_JSON') }}"

A nested YAML mapping works too — the whole key, key by key, under keyfile_json:. Either way DVT hands the key body straight to the client; no temporary file is ever written. When both are set, keyfile_json wins over keyfile.

PROJECT, DATASET, AND THEIR ALIASES

BigQuery addresses everything as project.dataset.table — three parts, like a database.schema.table elsewhere. That mapping is baked into the profile fields:

Two behavior notes. Dataset and table names are case-sensitive in BigQuery — spell them exactly as they exist. And location (US, EU, or a region) only matters when something has to be created or a job pinned to a region; it rides along whenever you set it.

READING BIGQUERY IN FEDERATION

To read BigQuery inside federated models, point a source at the output via meta.connection. Sources spell the project as database and the dataset as schema — the standard dbt shape for three-part engines:

# models/sources.yml
sources:
  - name: bq_raw
    meta:
      connection: bigquery      # the profiles.yml output above
    database: my-gcp-project    # the project
    schema: raw_events          # the dataset
    tables:
      - name: events
      - name: users

The connecting identity needs read access on every dataset your sources name — roles/bigquery.dataViewer on the dataset plus roles/bigquery.jobUser on the project is the small, sufficient pair.

INCREMENTAL MODELS — THREE STRATEGIES, AND NO APPEND

BigQuery accepts three strategy names and only three: merge, insert_overwrite and microbatch. append and delete+insert — which most other engines take — are not among them. The check lives in the materialization itself and runs before any SQL is sent, so a wrong name costs you a compile error rather than a query. Leave incremental_strategy out and you get merge; that default does not depend on unique_key.

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

select event_date, user_id, event_type
from {{ source('bq_raw', 'events') }}
{% if is_incremental() %}
where event_date >= date_sub(current_date(), interval 3 day)
{% endif %}

The partition_by above is not decoration — it is a requirement. Both insert_overwrite and microbatch refuse to run without one, and microbatch goes further: its partition granularity must match its batch_size exactly. Three errors, all raised at compile time:

The 'insert_overwrite' strategy requires the `partition_by` config.

The 'microbatch' strategy requires a `partition_by` config.

The 'microbatch' strategy requires a `partition_by` config with the same
granularity as its configured `batch_size`.

That last requirement makes sense once you know what microbatch is on this engine: it is insert_overwrite. The microbatch macro computes its time bounds and then hands the work straight to the insert-overwrite builder, which is why the two share every partition rule.

insert_overwrite itself has two modes, and you pick between them by whether you name the partitions. Give it an explicit partitions list and it runs the static form, replacing exactly those. Leave it out and it runs the dynamic form, discovering the partitions the model produced at runtime with an array_agg(distinct …) over the staged rows. The static form is cheaper and more predictable; the dynamic form is what most people get by default.

copy_partitions is tied to the same pair. Set it under partition_by with the default merge strategy and the model is refused, because there is no way to copy partitions through a MERGE:

The 'copy_partitions' option requires the 'incremental_strategy' option to be
set to 'insert_overwrite' or 'microbatch'.

merge has one quirk of its own, on partition-filtered tables. When the target sets require_partition_filter, the adapter injects an extra null-check predicate so the MERGE satisfies the filter requirement. And the way the unique-key comparison is written changes with a behaviour flag: the more obviously correct IS NOT DISTINCT FROM form defeats partition pruning inside a MERGE, so the null-safe equality is only used when you opt into it. On a large partitioned table that is the difference between scanning a day and scanning the table.

Python models are narrower still. insert_overwrite is not implemented for them at all, and ingestion-time partitioning is unavailable to them regardless of strategy. Both are compile-time refusals, not runtime surprises:

The 'insert_overwrite' strategy is not yet supported for python models.

Python models do not support ingestion time partitioning

Anything outside the three names fails immediately, and the message names the whole accepted set — this is what you see if you bring an append habit over from Postgres:

Invalid incremental strategy provided: append
    Expected one of: 'merge', 'insert_overwrite', 'microbatch'

When BigQuery is the target of a federated f_incremental model, the accepted set inverts — and this catches people. DVT computes the model in DuckDB and lands the result through its data-movement layer, so the strategy is mapped onto a load mode instead of compiling into BigQuery SQL. That lane speaks append, merge and delete+insert: the two names a native BigQuery incremental rejects become legal, and the two BigQuery-specific ones are refused before the run starts:

DVT025  … has no Sling equivalent — f_incremental supports append, merge and delete+insert.

Two more federated facts worth knowing before you configure against them. merge and delete+insert are the same thing on this lane — both become a merge on the unique_key, and there is no delete-then-insert pass. And a missing key is not an error: merge or delete+insert without a unique_key falls back to a full refresh with a warning, as does append without a watermark_column.

One thing DVT does not do here: rewrite your strategy. Its substitution table holds exactly one engine — ClickHouse, whose adapter refuses merge by name — so dvt flip-target-to carries whatever incremental_strategy you wrote across to BigQuery unchanged, even one BigQuery will then reject. An engine DVT has not measured is left alone on purpose.

DRIVERS — ONE COMMAND

dvt sync reads your profiles.yml and installs what each output needs — for BigQuery, the official dbt adapter and the google-cloud-bigquery driver. Then dvt debug --all proves both lanes: the dbt connection and the federation lane, per output.

Trying it out costs nothing: the BigQuery sandbox gives any Google account a free project — no card — which is plenty for a first dvt run.

REFERENCE — EVERY FIELD

Everything a BigQuery output can carry. Other dbt-bigquery settings (like priority or maximum_bytes_billed) pass through to the dbt lane untouched.