Skip to main content

Connect a BigQuery datasource

Goal​

You'll add a Google BigQuery project (and an optional dataset scope) to Measured Cloud, confirm the connection is reachable, and add its tables so you can start authoring rules.

Prereqs​

  • A running Measured Cloud (see Self-host).
  • A Google Cloud project containing the dataset(s) you want to validate.
  • A service account with at least:
    • roles/bigquery.dataViewer on the dataset (or per-table grants), and
    • roles/bigquery.jobUser on the project (so the service account can run query jobs against INFORMATION_SCHEMA.TABLES and against your tables).
  • The service-account JSON key file downloaded locally. Measured Cloud encrypts it at rest before storing — but treat the file like a password until you've uploaded and deleted it.

The Test Connection probe talks to bigquery.googleapis.com over HTTPS via the GCP SDK, so the SSRF guard skips its private-IP check for bigquery:// DSNs — you don't need to add anything to DATASOURCE_NETWORK_ALLOWLIST.

Steps​

  1. Log in as a workspace editor or higher.

  2. Go to Data Assets → Add source.

  3. Fill in the form:

    • Name — prod-bigquery (or any readable label).
    • Type — bigquery.
    • Project ID — your GCP project (case-sensitive — BigQuery project IDs may contain hyphens, e.g. my-org-prod).
    • Dataset — optional. If set, the table list scopes to this dataset; if left blank, Measured Cloud walks every dataset visible to the service account. Pick a dataset for production datasources so the picker stays focused.
    • Location — optional. The BigQuery region (US, EU, asia-northeast1, …) the project's data lives in. Leave blank for multi-region defaults.
    • Service-account JSON — paste the entire contents of the JSON key file into the secret field. Measured Cloud encrypts it (AES-256-GCM at rest) and never displays it again.

  4. Click "Test connection". Measured Cloud opens a BigQuery client using the service-account credentials, runs SELECT 1 FROM (SELECT 1) (BigQuery rejects a bare SELECT 1), and reports either a green check with the round-trip latency or a red X with the error.

    • Common error: "Permission denied" — the service account is missing roles/bigquery.jobUser on the project. Grant it via gcloud projects add-iam-policy-binding <project> --member="serviceAccount:<sa-email>" --role="roles/bigquery.jobUser" and re-test.
    • Common error: "Invalid project_id" — the project ID is wrong, or the service account belongs to a different project and isn't shared with this one. Project IDs are case-sensitive.
    • Common error: "Could not parse service-account JSON" — the pasted JSON is malformed or truncated. Re-paste from the file directly; some editors mangle the trailing newline.

  5. Save the datasource. The window moves on to step 2, Add tables. You can also add tables later from the source's Manage source window (click the source's name on Data Assets).

  6. Add tables. Measured Cloud queries <project>.<dataset>.INFORMATION_SCHEMA.TABLES (or walks INFORMATION_SCHEMA.SCHEMATA first if no dataset was set) and lists every table, view, and materialized view. Tick the ones you want and click Add N tables.

Verify​

  • The source shows up on Data Assets as a group header with a green connection badge.
  • The tables you added appear as data assets under that source, and read Added in its Manage source window.
  • You can now add rules to one of those assets — see Add rules to a data asset.

Run your rules​

When a validation runs against a BigQuery asset the worker (direct mode) or on-prem agent (agent mode) opens a SQLAlchemy engine on top of the sqlalchemy-bigquery dialect and Great Expectations' add_bigquery fluent API. Both dialects ship inside the production worker / agent Docker images by default — operators don't have to install anything extra.

If you're running the worker or agent from source (not the published image), uv sync --extra bigquery pulls in sqlalchemy-bigquery and google-cloud-bigquery. The standard uv sync deliberately omits them so devs on machines without a GCP setup get a clean install.

Caveats​

  • The test-connection endpoint times out after 5 seconds by default. BigQuery's first cold-start probe from a fresh service account can take 1–3 seconds; bump DATASOURCE_TEST_TIMEOUT_SECONDS if you see intermittent timeouts on a freshly-provisioned project.
  • The table list is cached for 15 minutes. If you create a new table and don't see it, that's why. Click Refresh tables in the toolbar (or wait).
  • Service-account JSON contains a private key; treat it like a password. Measured Cloud applies the same AES-256-GCM encryption it uses for SQL passwords.
  • BigQuery slot-pricing applies to every validation query Measured Cloud runs. A daily validation over a 100 GB asset will incur on-demand or slot-reservation costs the same way a bq query would; budget for it the way you would any other recurring analytical query.
  • Measured Cloud doesn't yet support workload-identity federation (running on GKE / Cloud Run with a metadata-server-issued token). Service-account JSON is the only auth path in v1 — workload-identity is tracked as a follow-up.