BigQuery

import { Aside } from ‘@astrojs/starlight/components’;

Connection

In workflow_settings.yaml:

warehouse:
  kind: bigquery
  project: my-gcp-project       # GCP project ID
  location: US                   # BigQuery region
  defaultDataset: analytics      # default dataset for actions

Credentials are resolved via Application Default Credentials. Run gcloud auth application-default login locally, or attach a service account in CI.

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")}

`partitionBy` and `clusterBy` are **BigQuery-only** fields. They have no effect when targeting Postgres or Supabase. See [PostgreSQL guide](/guides/postgres/) 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 ...

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