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.24.0      # 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...." }

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()})`)}

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