How to validate data in BigQuery

Q: What permissions does a validation tool need?

`roles/bigquery.dataViewer` on the datasets you want checked, plus `roles/bigquery.jobUser` on the project so it can run queries. No write access is required — every check is an aggregate `SELECT`.

A practical guide to validating data quality in Google BigQuery — the six checks every table needs, how to write them without scanning terabytes, and how to turn them into a versioned data contract that runs on a schedule.

2026-08-29 · 7 min read

To validate data in BigQuery, write each expectation as an aggregate query returning a violation count and run the batch after each load. The six checks that catch most breakage are nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale partitions. What makes BigQuery different is that every check costs money: you are billed per byte scanned, so a naive validation suite that reads a full fact table on every run is a line item, not just a latency problem. Almost all of the work is in making the checks cheap.

Why BigQuery validation is a cost problem first

BigQuery has no indexes and no row-level access paths. A WHERE clause on an unpartitioned column does not reduce bytes scanned — it filters after the read. Two mechanisms actually reduce cost:

Partition pruning. A filter on the partitioning column (or _PARTITIONTIME / _PARTITIONDATE for ingestion-time partitioning) restricts which partitions are read. This is the big lever.
Column pruning. BigQuery is columnar, so SELECT COUNT(*) FROM t WHERE col IS NULL reads only col, not the row. Never write SELECT * in a check.

A third lever is free: INFORMATION_SCHEMA.PARTITIONS and the table metadata carry row counts and last-modified times without scanning any data at all.

Set a hard ceiling regardless. Every validation query should run with maximum_bytes_billed, so a mistyped filter fails the job instead of scanning 40 TB.

The six checks every table needs

Assume orders is partitioned on DATE(created_at) and you are validating the last day's load.

1. Completeness — nulls in required columns

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND order_id IS NULL;

The partition filter is doing the real work here — without it this reads every byte in the table.

2. Uniqueness — duplicate business keys

SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM `proj.sales.orders`
    WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
      AND order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
);

BigQuery has no primary keys that it enforces, and streaming inserts are at-least-once. Duplicate keys are not an edge case here — they are the expected failure mode of a retried load, and this check is often the highest-value rule in the whole contract.

A caveat worth knowing: a duplicate check scoped to one partition will not see a row duplicated across two days. If your keys must be globally unique, that check has to scan the whole table, so run it daily rather than hourly and budget for it.

3. Conformity — values outside an allowed set

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

NULL NOT IN (...) is NULL in GoogleSQL as everywhere else, so keep the IS NOT NULL guard or nulls vanish from the count.

4. Accuracy — numbers outside a plausible range

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Use NUMERIC (or BIGNUMERIC) for money. FLOAT64 is IEEE 754 and will disagree with a bound at the boundary.

5. Conformity — malformed identifiers

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND reference IS NOT NULL
  AND NOT REGEXP_CONTAINS(reference, r'^ORD-[0-9]{6}
#39;);

REGEXP_CONTAINS uses RE2, so there are no backreferences or lookarounds — but everything else you are likely to write works, and RE2's linear-time guarantee means a pathological pattern cannot hang the query.

6. Timeliness — freshness

The cheap version reads no table data at all:

SELECT COUNT(*) AS violations
FROM `proj.sales.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'orders'
  AND last_modified_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND partition_id = FORMAT_DATE('%Y%m%d', CURRENT_DATE());

The row-level version — counting rows whose created_at is older than the threshold — measures something subtly different: not "did the table load", but "is the data in it recent". Both are worth having. The metadata check catches a pipeline that did not run; the row check catches a pipeline that ran and produced stale rows.

One BigQuery-specific gotcha: rows in the streaming buffer are queryable but do not immediately update last_modified_time, so a freshness check based purely on metadata can lag on a streaming table.

A read-only service account

Validation needs two roles on the project holding the data:

roles/bigquery.dataViewer — read table data and metadata
roles/bigquery.jobUser — run query jobs

Grant dataViewer at dataset rather than project level if you want to scope access to specific tables. Then set the cost ceiling on the connection so no single check can run away:

-- Enforced per job by the client, not in SQL:
--   maximum_bytes_billed = 10_000_000_000   (10 GB)

Catalyst connects with a service account key, applies a bytes-billed ceiling per check, and reports a check that exceeds it as an error rather than a fail — an infrastructure limit should never be reported as a data problem.

From ad-hoc SQL to a data contract

The queries above are correct and unmaintainable: the partition filter is copy-pasted six times, the thresholds are invisible, and nothing tells you which checks still run. Declaring the expectations fixes that. Catalyst uses the Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 2.0.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: NUMERIC
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}#39;"
      - name: created_at
        logicalType: timestamp
        physicalType: TIMESTAMP
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst imports the schema from INFORMATION_SCHEMA.COLUMNS, compiles each rule to GoogleSQL, and records the outcome of every check with a sample of the failing rows. Because the YAML is the source of truth and round-trips through the visual builder unchanged, the contract reviewed in a pull request is exactly the one that runs.

BigQuery gotchas worth knowing

Trap	What happens	What to do
No partition filter	Every check scans the full table	Filter on the partition column
`SELECT *` in a check	Reads every column	Reference only the checked column
At-least-once streaming	Silent duplicate keys	Always include a `duplicateCount` rule
Streaming buffer	Metadata freshness lags	Pair metadata and row-level freshness
`FLOAT64` money	Bounds disagree at the edge	Use `NUMERIC`
RE2 regex	No lookarounds or backreferences	Rewrite the pattern, or use `customSql`
No enforced keys	Constraints are declarative only	Validate uniqueness explicitly

Scheduling and alerting

Trigger validation from the end of the load, not from a clock. In BigQuery this matters twice over: a check that runs before the load has finished both reports a false failure and bills you for the scan.

Split the suite by cost. Cheap partition-scoped checks can run on every load; expensive whole-table checks — global uniqueness, cross-table referential integrity — belong on a daily schedule. Alert on the transition from pass to fail rather than repeating the state, and keep pattern checks at warning severity so they inform rather than page.

Frequently asked questions

How much does data validation cost in BigQuery?

It is the bytes each check scans, billed at the on-demand rate, or slot time on a reservation. A partition-scoped check on a single column is typically megabytes; the same check without a partition filter can be terabytes. Write partition filters into every rule, cap each query with maximum_bytes_billed, and use INFORMATION_SCHEMA for anything that only needs metadata.

Can I validate BigQuery data without writing SQL?

Yes. Declare the expectation and let the tool generate GoogleSQL. Catalyst imports columns and types from INFORMATION_SCHEMA, proposes a baseline set of rules, and compiles them per dialect — you only write SQL for genuinely bespoke logic through a customSql rule.

What permissions does a validation tool need?

roles/bigquery.dataViewer on the datasets you want checked, plus roles/bigquery.jobUser on the project so it can run queries. No write access is required — every check is an aggregate SELECT.

Why do I get duplicate rows in BigQuery even with a primary key declared?

BigQuery's primary and foreign key constraints are unenforced metadata used by the query optimiser; they do not reject duplicate inserts. Combined with at-least-once delivery on the streaming API, that makes duplicates a normal outcome of a retried load rather than a rare bug. A duplicateCount rule is not optional here.

Does this work with BigQuery views and external tables?

Views validate fine — the check runs against the view's result. External tables (Cloud Storage, Sheets, BigLake) also work, but there is no partition pruning on most of them, so every check reads the whole source. Budget accordingly, or materialise the external table first.