BigQuery Connector
Google BigQuery is a serverless, fully managed data warehouse on Google Cloud that runs standard SQL over data stored in its columnar storage and in external sources. Teams use it to centralize analytics workloads without provisioning or sizing any infrastructure.
The Qualytics BigQuery connector uses the Simba BigQuery JDBC driver 1.8.1.1003 with a Google Cloud service account. Point it at a project and its datasets, and Qualytics will profile tables, run scheduled scans, and surface record- and schema-level anomalies alongside the rest of your governed sources.
Notes
Enrichment support. BigQuery can be used for enrichment datastores. When linked to a source, the BigQuery datastore becomes that source's enrichment destination for anomalies, remediation outputs, and source records.
Temp dataset. The connection accepts an optional temporary dataset for intermediate query results. It is resolved in the project billed for queries, which may differ from the project containing your source data. Use the same location as the queried data and a short table expiration; see Create a BigQuery Temp Dataset.
Large results. The driver retrieves large result sets through the BigQuery Storage Read API, which is why the service account needs the read session role listed on the Permissions page.
Cost model. BigQuery on-demand pricing bills per data scanned, so profile and scan operations directly affect your Google Cloud bill. Tables that are partitioned and clustered scan less data than unpartitioned ones; preferring these for the tables Qualytics monitors keeps scan cost predictable.
Data and Billing Projects
The Project ID under Location identifies the data Qualytics analyzes. The optional Billing Project ID under Connection Properties selects the project billed for query processing. The service account's own project identifies where the account was created; it does not determine which project pays for queries.
| Setting | Purpose | Example |
|---|---|---|
| Project ID | Project containing the datastore's data. | acme-analytics |
| Dataset ID | Dataset within the data project. | sales_curated |
| Billing Project ID | Project billed for queries. Leave blank to use each datastore's Project ID. | acme-billing |
| Temp Dataset ID | Optional dataset for intermediate results, in the project billed for queries. Enter only the dataset name. | qualytics_temp |
With these example values, Qualytics reads acme-analytics.sales_curated, bills queries to acme-billing, and uses acme-billing.qualytics_temp for intermediate results when needed. The service account needs permissions in both projects; see Permissions.
Leaving Billing Project ID blank, or clearing a saved value, uses each datastore's Project ID for billing.
Billing is shared by a connection. Setting Billing Project ID applies to every datastore using that connection. Their data projects and datasets stay unchanged. To use different billing projects, create separate connections. To change a saved value, use Manage Connections, review the temp dataset requirements, and test the connection again.
Alation matching uses the data project. If you use the Alation integration, associate the datasource with the datastore's Project ID, even when query billing uses a different project.
Computed Tables
Computed tables are unaffected by Billing Project ID. They continue to use the datastore's Project ID and Dataset ID to locate data.
Deep Dive
-
Permissions
The BigQuery roles a service account needs for source (read-only) and enrichment (read-write) datastores, plus the recommended temp dataset setup.
-
Authentication
How the Service Account Key works: creating the JSON key, what it contains, and keeping it valid.
-
Troubleshooting
Common BigQuery connection errors and how to resolve them: credentials, roles, or project and dataset configuration.
How-tos
-
Add Source Datastore
Step-by-step walkthrough for adding BigQuery as a source datastore, with new or existing connections.
-
Create a Temp Dataset
Set up the temporary dataset in the BigQuery console that the connection's Temp Dataset ID field points at.
-
Create via API
REST payload examples for creating BigQuery source and enrichment datastores.