BigQuery

Connection

Non-secret settings in workflow_settings.yaml (committed):

workflow_settings.yaml

warehouse: bigquery
defaultProject: my-gcp-project   # GCP project ID
defaultLocation: US              # BigQuery region
defaultDataset: analytics        # default dataset for actions
sqlanvilCoreVersion: 1.26.2      # pin the release you installed — `sqlanvil init` writes this

Auth lives in .df-credentials.json (gitignored). For BigQuery you typically only need the project and location — credentials resolve via Application Default Credentials (gcloud auth application-default login locally, or an attached service account in CI):

.df-credentials.json

{ "projectId": "my-gcp-project", "location": "US" }

To use an explicit service account instead of ADC, add its key JSON as a credentials field.

For keyless setups, provide a short-lived OAuth2 accessToken instead of a key — SQLAnvil authenticates BigQuery with it directly (precedence: accessToken → credentials key → ADC). This suits callers that already run inside GCP and can mint a token by impersonating a service account (e.g. SQLAnvil Cloud’s runner), so no key is ever written. Tokens are short-lived (≈1h):

.df-credentials.json

{ "projectId": "my-gcp-project", "location": "US", "accessToken": "ya29...." }

Running in production

If you’re coming from Dataform, its workflow configurations let each scheduled workflow run as a service account or an authenticated account. The same shapes map directly onto SQLAnvil’s auth modes — usually with no key ever written:

Where it runs	How it authenticates
Your machine	ADC from gcloud auth application-default login — the secretless { projectId, location } credentials file is all you need (Dataform-local parity).
GCP compute (Cloud Run, GCE, Cloud Build, Composer…)	The attached service account is ADC — the identical secretless file works unchanged. This is the closest analog of a Dataform service-account workflow.
External CI (GitHub Actions…)	Keyless via Workload Identity Federation: the workflow mints a short-lived token (e.g. google-github-actions/auth) and passes it as accessToken. No stored key.
Anywhere, with a key	A service-account key in the credentials field — supported, but prefer the keyless rows above.

Per-workflow splits — different projects, datasets, or identities for dev/test/prod — are named environments: each environment can override defaultDatabase (the GCP project), add a schemaSuffix, and point at its own credentials file, selected at run time with --environment <name>.

Partitioning and clustering

Use partitionBy and clusterBy in the action config block:

-- definitions/orders.sqlx
config {
  type: "table",
  partitionBy: "DATE(created_at)",
  clusterBy: ["customer_id", "region"],
  partitionExpirationDays: 90
}

SELECT
  order_id,
  customer_id,
  region,
  created_at
FROM ${ref("raw_orders")}

Note

partitionBy and clusterBy are BigQuery-only fields. They have no effect when targeting Postgres or Supabase. See PostgreSQL guide for Postgres-native partitioning syntax.

Incremental tables

SQLAnvil generates a BigQuery MERGE statement for incremental tables:

-- definitions/events_incremental.sqlx
config {
  type: "incremental",
  uniqueKey: ["event_id"],
  partitionBy: "DATE(event_timestamp)"
}

SELECT
  event_id,
  event_timestamp,
  user_id,
  event_name
FROM ${ref("raw_events")}
${when(incremental(), `WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM ${self()})`)}

insert_overwrite (1.29+)

When a run recomputes a date range rather than matching individual rows, a MERGE on a uniqueKey is the wrong instrument: it needs a key, and it leaves behind any row the recomputed range no longer produces. incrementalStrategy: "insert_overwrite" replaces every partition the incremental output touches instead — rows that disappear from a reprocessed day disappear from the table.

config {
  type: "incremental",
  incrementalStrategy: "insert_overwrite",
  bigquery: {
    partitionBy: "event_date",
    // Optional: narrow what the replacement is allowed to touch.
    incrementalPredicates: ["T.event_date >= '2026-01-01'"]
  }
}

SELECT event_date, user_id, count(*) AS events
FROM ${ref("raw_events")}
${when(incremental(), `WHERE event_date >= current_date() - 3`)}
GROUP BY 1, 2

partitionBy is required — without a partition column there is nothing to scope the replacement to, and SQLAnvil fails the compile rather than truncate the table. No uniqueKey is needed. In incrementalPredicates, T is the destination and S the staged rows.

BigQuery only. On Postgres, Supabase or MySQL the strategy is a compile error naming that reason — it is not silently downgraded to a merge, because that would be a run that succeeds and writes different rows than you asked for. Use the default merge with a uniqueKey there, or delete the range you are replacing in a pre_operations block.

Governance controls

preserveGovernanceControls: true keeps BigQuery column governance controls (e.g. data policies) attached when SQLAnvil recreates a table. Set it per-table, or as a default in workflow_settings.yaml. BigQuery only.

Labels

Attach BigQuery labels to any action for cost tracking:

config {
  type: "table",
  labels: {
    team: "analytics",
    env: "production"
  }
}
SELECT ...

Materialized views

config {
  type: "view",
  materialized: true,
  partitionBy: "DATE(created_at)"
}
SELECT ...

Caution

Materialized views in BigQuery are auto-refreshed. In Postgres, materialized views require an explicit REFRESH MATERIALIZED VIEW — SQLAnvil handles this via the Postgres adapter.

BigQuery reservations

To run actions on a specific BigQuery reservation (slot commitment):

workflow_settings.yaml

defaultReservation: projects/my-project/locations/US/reservations/my-reservation

Or per-action in YAML:

definitions/actions.yaml

actions:
- table:
    filename: heavy_model.sql
    reservation: projects/my-project/locations/US/reservations/heavy

Additional BigQuery options

Pass arbitrary BigQuery table options via additionalOptions:

config {
  type: "table",
  additionalOptions: {
    kms_key_name: "projects/my-proj/locations/us/keyRings/my-ring/cryptoKeys/my-key"
  }
}
SELECT ...

Assertions

SQLAnvil generates assertion views in BigQuery under your defaultDataset. A failing assertion (one that returns rows) is reported as an error:

config {
  type: "assertion"
}
SELECT * FROM ${ref("orders")} WHERE order_total < 0