Validating Data from Google BigQuery¶

This page covers Google BigQuery-specific setup for Data validation. For workflow field definitions, see Data validation configuration reference.

Prerequisites¶

Worker client: Same pre-installed google-cloud-bigquery client as Migrating Data from Google BigQuery. You don’t install an ODBC or JDBC driver.
Authentication: A service-account key file or Application Default Credentials, the same as migration.
Live BigQuery access for L1 and L2. Schema and metrics validation always run SQL against BigQuery.
L3 object storage (Recommended): Use EXPORT DATA (extraction.strategy: export_data) so signatures land on GCS, with the same Worker export_data_* TOML and Snowflake external stage as migration. See Object storage backends and Google Cloud Storage.
Privileges: Read access on the dataset and bigquery.jobs.create in the billing project. See Google BigQuery.

Connectivity¶

Reuse [connections.source.bigquery] Worker TOML from data migration:

[connections.source.bigquery]
project_id = "my-gcp-project"
dataset = "sales"
location = "US"
credentials_file = "/secrets/bigquery-sa.json"
export_data_gcs_bucket = "my-migration-bucket"
export_data_gcs_prefix = "bigquery-validation"

Set sourcePlatform: bigquery in the validation workflow YAML.

Row validation supports export_data and regular. With a GCS bucket, prefer export_data and set extraction.externalStage to a stage on the same prefix. Without a bucket, regular extracts the signatures through the Worker to the internal stage. cloud_direct isn’t supported. See L3 extraction.

Example workflow excerpt:

sourcePlatform: bigquery
targetDatabase: TARGET_DB
schemaMappings:
  sales: PUBLIC
validationConfiguration:
  schemaValidation: true
  metricsValidation: true
  rowValidation: false
  extraction:
    strategy: export_data
    externalStage: TARGET_DB.PUBLIC.GCS_EXPORT_STAGE
tables:
  - fullyQualifiedName: sales.customers
    columnNamesToPartitionBy:
      - customer_id
  - fullyQualifiedName: sales.orders
    columnNamesToPartitionBy:
      - order_id
    validationConfiguration:
      rowValidation: true

Data type mappings¶

The target types are the same as Migrating Data from Google BigQuery. L3 compares each value in a canonical text form, so the notes below describe how a value has to match.

BigQuery type	Snowflake type	Supported for validation	Notes
`INT64` and its aliases	`INT`	Yes
`NUMERIC`, `DECIMAL`	`NUMBER(p,s)`	Yes	Compared at the column’s scale
`BIGNUMERIC`, `BIGDECIMAL`	`DECFLOAT` or `NUMBER(p,s)`	Yes	A `DECFLOAT` target is compared as exact decimal text. A value past 38 digits is a mismatch. Precision above 38 or scale above 37 is an L1 warning, not a failure
`FLOAT64`	`FLOAT`	Yes	Compared as the IEEE-754 value. A difference that survives in the binary value is a mismatch
`BOOL`	`BOOLEAN`	Yes
`STRING`	`VARCHAR`	Yes
`BYTES`	`BINARY`	Yes	Compared as uppercase hex
`DATE`	`DATE`	Yes
`TIME`	`TIME`	Yes	Compared at the fractional precision both sides keep
`DATETIME`	`TIMESTAMP_NTZ`	Yes
`TIMESTAMP`	`TIMESTAMP_TZ`	Yes	Compared as the UTC instant
`JSON`, `STRUCT`, `ARRAY`	`VARIANT`, `ARRAY`	Yes	Compared as JSON text with object keys sorted, so key order doesn’t cause a mismatch
`GEOGRAPHY`	`GEOGRAPHY`	Yes	Compared as WKT, with spacing differences between BigQuery and Snowflake removed
`INTERVAL`	`VARCHAR`	Yes	Compared as text

Numeric values with more than 28 integer digits can’t be rendered in the shared comparison format and report as a mismatch. This limit applies to every source platform.

Validation levels¶

Level	Google BigQuery behavior
L1 schema	Compares the mapped Snowflake type. BigQuery doesn’t expose numeric precision and scale as columns, so AIM DMV parses them from the declared type.
L2 metrics	Column metrics for every mapped type, including `JSON`, `STRUCT`, `ARRAY`, `GEOGRAPHY`, and `INTERVAL`.
L3 rows	Supported, including hybrid mode: a row hash, then cell drill-down only where the hash mismatches. `FLOAT64` uses IEEE-754. A `DECFLOAT` target uses exact decimal text.

Platform-specific considerations¶

Reuse the same [connections.source.bigquery] Worker block as migration.
Set sourcePlatform: bigquery.
Schema, metrics, and row validation are all available. Ask for row validation per table.
Prefer export_data for row validation when a GCS bucket and a matching external stage exist. Otherwise use regular.
Tables, table clones, and table snapshots validate as tables. Views and materialized views validate as views, so supply their keys as described in Keys for source views. Temporary tables aren’t in the dataset, so they’re treated as missing, and external tables aren’t supported. See How catalog kinds are classified.
Validation queries run and are billed in project_id.
Apache Iceberg™ targets: Schema, metrics, and row validation run on tables migrated to Iceberg. Declare the same tableType and icebergConfig profile the migration used. Row validation compares timestamps and other values as EXPORT DATA wrote them, so a VARCHAR timestamp column compares as ISO text with six fractional digits. See Validating spec v2 and spec v3 tables.
Anti-locking: BigQuery reads don’t take locks, so no hint is added.

Prompt:

Run cloud data validation for my BigQuery tables, with schema and metrics validation on all tables and row-level validation on orders using export_data