BigQuery
Access configuration details to connect Soda to a Google Cloud BigQuery data source.
Connection configuration reference
Install the following package:
pip install soda-bigqueryData source YAML
Provide exactly one of the following:
account_info_json– inline BigQuery service account JSONaccount_info_json_path– path to a BigQuery service account JSON file
Provide account_info_json:
type: bigquery
name: my_bigquery
connection:
account_info_json: '{
"type": "service_account",
"project_id": "dbt-quickstart-44203",
"private_key_id": "fe0a60e9cb7d4369f73f7b5691ce397d1e",
"private_key": "-----BEGIN PRIVATE KEY-----<insert-private-key>-----END PRIVATE KEY-----\n",
"client_email": "dbt-user@dbt-quickstart-448203.iam.gserviceaccount.com",
"client_id": "114963712293161062",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/dbt-user%40dbt-quickstart-44803.iam.gserviceaccount.com",
"universe_domain": "googleapis.com"
}' # example service account JSON string, exported from BQ
dataset: ${env.BQ_DATASET_NAME}
# optional
project_id: ${env.BQ_PROJECT_ID} # Defaults to the one embedded in the account JSON
storage_project_id: ${env.BQ_STORAGE_PROJECT_ID}
location: ${env.BQ_LOCATION} # Defaults to the specified project's location
auth_scopes:
- https://www.googleapis.com/auth/bigquery
- https://www.googleapis.com/auth/cloud-platform
- https://www.googleapis.com/auth/drive
client_options: <options-dict-for-bq-client>
labels: <labels-dict-for-bq-client>
impersonation_account: <name-of-impersonation-account>
delegates: <list-of-delegates-names>
##use_context_auth: false # whether to use Application Default Credentials
Provide account_info_json_path:
Create the config file:
The data source configuration YAML should look like the following:
See BigQuery's locations documentation to learn more about
location.
Connection test
Required permissions
Create a dedicated GCP service account and grant it the following IAM roles at the project level:
roles/bigquery.user
Execute scan queries
roles/bigquery.dataViewer
Read table and view data
roles/bigquery.metadataViewer
List and inspect dataset/table metadata via INFORMATION_SCHEMA
roles/bigquery.resourceViewer
(see note)
Query INFORMATION_SCHEMA.JOBS (required for Metric Monitors and Contract Auto-Creation)
(see note)
Download the service account JSON key and reference it in the connection configuration.
Limitations
DATE vs TIMESTAMP partition columns
Soda expects a
TIMESTAMP-based partition column for full compatibility with partition-based monitoring and freshness calculations in BigQuery.If your dataset uses a
DATEcolumn as the partition key, you must explicitly cast the column toTIMESTAMPto ensure valid SQL execution.
Recommended approaches:
Preferred: Use a
TIMESTAMPcolumn as the time partition column when onboarding BigQuery datasets.Alternative: If a
DATEcolumn must be used, apply an explicit cast toTIMESTAMPin filters or expressions.
Sharded tables
Soda Cloud treats each date-sharded table in BigQuery as an independent dataset. In practice, tables named with date suffixes (for example, events_20240617, events_20240618) will:
Count separately in the UI, appearing as distinct entries in the Datasets list.
Count separately toward Soda pricing, since each shard is billed as its own dataset.
Maintain independent schemas, so any structural differences between shards must be managed manually (for example, by aligning column names and types before onboarding).
There is no built-in metadata or grouping mechanism in Soda Cloud to unify shards under a “main” logical table.
Recommended migration to native partitions
BigQuery now recommends using time-partitioned tables instead of legacy sharding. Partitioned tables offer better performance, lower cost, and a single logical table for both queries and metadata. To migrate:
Follow BigQuery’s guide to convert date-sharded tables into a partitioned table.
In Soda Cloud, onboard the new partitioned table once. Soda will treat it as a single dataset.
Short-term workarounds
If migration to partitioned tables is not yet feasible for you:
Table-name patterns: Onboard your shards as separate datasets, but use consistent naming conventions and Dashboard filters to group them visually.
External catalog consolidation: If you use a metadata layer, such as Atlan, to virtualize shards into a single logical asset, you can point Soda at that consolidated view. Keep in mind Soda will still count it as one dataset only if it surfaces as a single table name in BigQuery.
Last updated
Was this helpful?
