Google BigQuery Integration Guide

Connection Configuration

BurnLedger connects to BigQuery using a service account key with read-only permissions.

Configuration fields:

These are the exact JSON keys the connector reads — snake_case in both SDKs. projectId, credentialsJson, or readOnly are silently ignored, and the connection then fails with "project_id is required".

TypeScript

const system = await bl.registerSystem({
  name: "analytics-users",
  connectorType: "bigquery",
  connectionConfig: {
    project_id: "myproject-123",
    dataset: "user_analytics",
    credentials_json: process.env.BQ_CREDENTIALS_B64,
    read_only: true,
  },
  subjectQuery: "SELECT * FROM `user_analytics.users` WHERE email = @subject",
});

Python

system = bl.register_system(
    name="analytics-users",
    connector_type="bigquery",
    connection_config={
        "project_id": "myproject-123",
        "dataset": "user_analytics",
        "credentials_json": os.environ["BQ_CREDENTIALS_B64"],
        "read_only": True,
    },
    subject_query="SELECT * FROM `user_analytics.users` WHERE email = @subject",
)

Query Template Format

Standard BigQuery SQL with @subject as the named parameter placeholder. The identifier is bound as a named query parameter — never string-interpolated.

Not validated at registration. BigQuery templates are not checked when the system is registered: BurnLedger does not verify that the template contains @subject, has a WHERE clause, or is a read-only SELECT. Make sure @subject is present — a template without it queries every row regardless of subject.

-- Simple lookup
SELECT * FROM `user_analytics.users` WHERE email = @subject

-- Cross-table join
SELECT u.id, u.email, e.event_type, e.timestamp
FROM `user_analytics.users` u
JOIN `user_analytics.events` e ON e.user_id = u.id
WHERE u.email = @subject

Service Account Setup

BurnLedger inspects the credential's access to the dataset — without ever writing. It reads the dataset's IAM Access entries and checks whether the service account principal holds a WRITER or OWNER role on the dataset.

Two things about this check are important:

1. A detected WRITER/OWNER grant refuses the connection, with WRITE_ACCESS_DETECTED, exactly as every other connector does. This guide used to say the opposite, and it was true once: BigQuery warned and connected anyway, which made it the one backend where a credential positively observed holding WRITER or OWNER could be registered and have a deletion certified against it. Nothing was lost by closing it — the branch fires only on evidence read straight off the dataset ACL, so the refusal costs exactly the credentials BigQuery itself told us can write.
2. The dataset ACL still can never prove read-only. Project-level IAM roles (e.g. roles/bigquery.dataEditor on the project) and grants via groups or domains confer write access without ever appearing in the dataset ACL. So every outcome other than a positive WRITER/OWNER match — principal absent, listed only as READER, no access list at all, or a credentials_json whose client_email could not be parsed — is treated as inconclusive.

An inconclusive result is where the fail-closed gate lives: set "read_only": true in the connector config to assert the credential is read-only (BurnLedger only ever reads regardless). Without the assertion, construction is refused with CONNECTION_FAILED and the message "cannot verify the credential is read-only". In practice this means most BigQuery configurations need read_only: true.

Create a custom role

gcloud iam roles create burnledger_bq_readonly \
  --project=myproject-123 \
  --title="BurnLedger BigQuery Read-Only" \
  --permissions=bigquery.datasets.get,bigquery.tables.get,bigquery.tables.getData,bigquery.jobs.create

Create a service account

gcloud iam service-accounts create burnledger-bq-ro \
  --display-name="BurnLedger BigQuery Read-Only"

gcloud projects add-iam-policy-binding myproject-123 \
  --member="serviceAccount:burnledger-bq-ro@myproject-123.iam.gserviceaccount.com" \
  --role="projects/myproject-123/roles/burnledger_bq_readonly"

gcloud iam service-accounts keys create burnledger-bq-key.json \
  --iam-account=burnledger-bq-ro@myproject-123.iam.gserviceaccount.com

base64 -i burnledger-bq-key.json

Do not grant bigquery.tables.delete, bigquery.tables.update, or bigquery.tables.updateData.

Restorable copies

A BigQuery deletion cannot be certified until the dataset's time-travel window has passed over it.

Every BigQuery dataset keeps a time-travel window, and it cannot be switched off: max_time_travel_hours accepts 48 to 168 hours and defaults to 168. Inside that window FOR SYSTEM_TIME AS OF reads the table as it was, and CREATE TABLE … CLONE or a copy job turns that read back into a live table. So rows deleted this morning are still readable, and still restorable, for as long as the window lasts — while the ordinary SELECT BurnLedger runs returns nothing.

When a subject query returns no rows, BurnLedger therefore establishes that no restorable point inside the window would have answered differently:

1. It dry-runs the same statement — free, scans nothing, needs only the bigquery.jobs.create the query itself needs — and takes BigQuery's own referencedTables list, resolved through views and cross-project references. Table names are never parsed out of the SQL.
2. It reads each referenced table's dataset for the window (bigquery.datasets.get) and each table's last-modified time (bigquery.tables.get).
3. A table last modified before its window opened has the same content at every restorable point, so the zero holds throughout and is reported. A table modified inside the window might have been modified by the deletion, so the attestation is refused with S3_VERSIONING_CONFLICT and mechanism: time travel.

Anything that cannot be established — a dry run that names no tables, a view or external table whose last-modified time describes the definition rather than the data, a query reading 50 or more tables (BigQuery stops promising a complete list there), a metadata call the credential is refused — is refused with CONNECTION_FAILED, never read as a "no".

To certify a BigQuery deletion: wait for the dataset's time-travel window to pass over the delete, or lower max_time_travel_hours toward the 48-hour minimum to shorten the wait:

ALTER SCHEMA `my_project.my_dataset` SET OPTIONS (max_time_travel_hours = 48);

Note that BigQuery keeps a further seven days of fail-safe storage after the time-travel window, recoverable only by Google Cloud Support. That is outside what a customer credential can restore and is not checked here.

Nothing is checked when the query finds rows — a non-zero count fails the deletion claim on its own.

The permissions this needs (bigquery.jobs.create, bigquery.datasets.get, bigquery.tables.get) are already in the custom role above.

BigQuery-Specific Considerations

Query costs

BigQuery charges per bytes scanned. BurnLedger queries should target specific partitions and columns to minimize cost. Use partitioned tables and cluster on the subject identifier column.

Streaming buffer

Recently streamed rows may not appear in query results immediately. Wait for the streaming buffer to flush (typically under a few minutes) before creating an attestation.

Table expiration

If tables or partitions have expiration policies, data may disappear without explicit deletion. BurnLedger proves absence regardless of cause.

Troubleshooting