BQBrains | BigQuery Data Quality: Dataplex Scans, SQL Assertions, and Alerting That Works

Every BigQuery estate eventually hits the same wall: the pipelines all succeed, the dashboards all render, and the numbers are still wrong. Freshness slipped by six hours, a source system started sending NULL in a column that used to be mandatory, or a currency column silently switched from cents to dollars. Nothing failed — which is exactly the problem.

This tutorial builds a practical data-quality layer for BigQuery in three tiers: Dataplex auto data quality scans for declarative rules and profiling, in-pipeline SQL assertions that stop bad data before it lands, and alerting plus a scorecard so somebody actually notices. Everything here uses BigQuery, Dataplex Universal Catalog, and Cloud Monitoring — no third-party observability tool required.

The three tiers, and what each is good for

Tier	Runs	Catches	Cost
Dataplex data profile scan	On a schedule, on a whole table or partition	Distribution drift, unexpected nulls, cardinality changes you did not think to test	Billed as BigQuery slots against your project (or a chosen reservation)
Dataplex data quality scan	On a schedule or via API after a load	Declared rule violations: nulls, ranges, uniqueness, regex, referential integrity, custom SQL	Same
In-pipeline assertion	Inside the transformation, before publish	Anything that must block a release: row-count collapse, duplicate keys, failed reconciliation	A cheap query per check

Rule of thumb: assert what must never happen, scan what you want to watch. Assertions gate the pipeline; scans give you a trend line and find the problems nobody wrote a rule for.

1. Profile first, then write rules

Do not start by inventing thresholds. Profile the table and let the data tell you what "normal" looks like. A profile scan reports, per column, null and distinct percentages, min/max/average, standard deviation, quartiles, and top values.

gcloud dataplex datascans create data-profile orders-profile \
  --location=us-central1 \
  --data-source-resource="//bigquery.googleapis.com/projects/my-proj/datasets/analytics/tables/orders" \
  --on-demand \
  --sampling-percent=25 \
  --row-filter="order_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)"

Two flags do most of the cost control. --sampling-percent trades precision for slots, and --row-filter keeps the scan on recent partitions instead of five years of history. On a partitioned table, always filter on the partition column — otherwise your quality checks become one of the most expensive queries in the estate, which is the sort of thing that shows up in a cost audit later.

Publish the results to the catalog (--publishing-enabled) so analysts see profile statistics on the table page in BigQuery Studio, then read the same numbers in SQL by exporting scan results to a BigQuery table.

2. Declare the rules that matter

Now turn the profile into rules. A quality scan definition is YAML; keep it in the same repository as your transformation code so rules are reviewed like any other change.

# orders-quality.yaml
rules:
  - column: order_id
    dimension: UNIQUENESS
    uniquenessExpectation: {}
  - column: order_id
    dimension: COMPLETENESS
    nonNullExpectation: {}
    threshold: 1.0
  - column: status
    dimension: VALIDITY
    setExpectation:
      values: ["pending", "paid", "shipped", "refunded", "cancelled"]
    threshold: 0.999
  - column: order_total_eur
    dimension: VALIDITY
    rangeExpectation:
      minValue: "0"
      maxValue: "250000"
    threshold: 0.999
  - column: customer_email
    dimension: VALIDITY
    regexExpectation:
      regex: '^[^@\s]+@[^@\s]+\.[^@\s]+$'
    threshold: 0.99
  - dimension: FRESHNESS
    sqlAssertion:
      sqlStatement: |
        SELECT 1
        FROM `my-proj.analytics.orders`
        HAVING MAX(order_ts) < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)
  - dimension: INTEGRITY
    sqlAssertion:
      sqlStatement: |
        SELECT o.customer_id
        FROM `my-proj.analytics.orders` o
        LEFT JOIN `my-proj.analytics.customers` c USING (customer_id)
        WHERE c.customer_id IS NULL
        GROUP BY 1

gcloud dataplex datascans create data-quality orders-dq \
  --location=us-central1 \
  --data-source-resource="//bigquery.googleapis.com/projects/my-proj/datasets/analytics/tables/orders" \
  --data-quality-spec-file=orders-quality.yaml \
  --schedule="0 * * * *"

Three things worth internalising:

sqlAssertion rules pass when they return zero rows. Write them to select the offending rows, not the healthy ones. This is the opposite of how most people first guess, and it is the single most common mistake in a first scan definition.
threshold is a pass ratio, not a tolerance for badness. 0.999 means the scan fails when more than one row in a thousand violates the rule. Primary keys and money columns get 1.0; free-text validity rules get slack, or your team learns to ignore red.
Dimensions matter. COMPLETENESS, UNIQUENESS, VALIDITY, FRESHNESS, TIMELINESS, ACCURACY, CONSISTENCY roll up into a per-dimension score. That roll-up is what you show an executive; individual rule results are what an engineer debugs.

Two nice extras once the basics run: --incremental-field=order_ts scans only new rows since the last run, and rule recommendations generated from a profile scan give you a starting YAML instead of a blank file.

3. Assert inside the pipeline, before publish

Scheduled scans find yesterday's problem. To stop bad data reaching a dashboard at all, gate the publish step. The pattern below writes to a staging table, checks it, and only then swaps it into place — all in one BigQuery script, so a failed check leaves the published table untouched.

BEGIN
  CREATE OR REPLACE TABLE `analytics.orders_staging` AS
  SELECT * FROM `raw.orders_incoming`;

  -- Check 1: no duplicate keys
  IF (SELECT COUNT(*) FROM (
        SELECT order_id FROM `analytics.orders_staging`
        GROUP BY order_id HAVING COUNT(*) > 1)) > 0 THEN
    RAISE USING MESSAGE = 'orders_staging: duplicate order_id detected';
  END IF;

  -- Check 2: row count within 20% of the trailing 7-day average
  IF (SELECT ABS(
        (SELECT COUNT(*) FROM `analytics.orders_staging`) /
        NULLIF((SELECT AVG(row_count) FROM `analytics.orders_load_history`
                WHERE load_date >= CURRENT_DATE() - 7), 0) - 1) > 0.20 THEN
    RAISE USING MESSAGE = 'orders_staging: row count outside expected range';
  END IF;

  -- Check 3: revenue reconciles with the source ledger to within a cent
  IF (SELECT ABS(SUM(order_total_eur) -
        (SELECT SUM(amount_eur) FROM `raw.ledger_daily`
         WHERE ledger_date = CURRENT_DATE() - 1))
      FROM `analytics.orders_staging`) > 0.01 THEN
    RAISE USING MESSAGE = 'orders_staging: revenue does not reconcile with ledger';
  END IF;

  -- All checks passed: publish atomically
  CREATE OR REPLACE TABLE `analytics.orders` COPY `analytics.orders_staging`;

  INSERT INTO `analytics.orders_load_history` (load_date, row_count, loaded_at)
  SELECT CURRENT_DATE(), COUNT(*), CURRENT_TIMESTAMP()
  FROM `analytics.orders`;

EXCEPTION WHEN ERROR THEN
  INSERT INTO `analytics.dq_failures` (table_name, failed_at, message)
  VALUES ('analytics.orders', CURRENT_TIMESTAMP(), @@error.message);
  RAISE USING MESSAGE = @@error.message;
END;

If you already run Dataform, express the same checks as assertions on the table's config block and let dependency resolution stop downstream models. The important property is identical either way: downstream consumers never see a table that failed its checks.

4. Make failures visible

A scan that fails quietly is decoration. Wire up three outputs:

Export results to BigQuery. Set the scan's export table and every run appends rule-level results with pass/fail counts. That table is the raw material for a scorecard: pass rate by dimension by day, by domain, by team.
Alert from logs. Dataplex scan completions land in Cloud Logging. A log-based alerting policy on resource.type="dataplex.googleapis.com/DataScan" with jsonPayload.dataQuality.passed=false routes to email, Slack, or PagerDuty via a notification channel — with the table name in the message so on-call knows what broke.
Publish scores where data is chosen. With catalog publishing enabled, quality scores appear on the table page. Combined with column descriptions and policy tags, that is what turns "I think this table is fine" into "this table scored 100% on completeness at 07:00 today".

Two operational habits keep it honest. Give quality scans their own reservation or project label so their cost is visible and never competes with analyst queries during business hours. And review rules quarterly: a rule that has never failed in a year is either protecting you or lying to you, and only reading it will tell you which.

A sane rollout order

Pick the five tables that feed your most-viewed dashboards. Not fifty.
Profile them, on recent partitions only, with sampling.
Write completeness and uniqueness rules for keys, freshness rules for every table with an SLA, and range rules for money and quantity columns.
Add in-pipeline assertions only where a bad publish would be expensive to unwind.
Turn on alerting to a channel a human reads, then add the scorecard.
Expand table by table, and delete rules that generate noise instead of muting them.

Data quality work fails when it starts as a platform project and never reaches a table anybody cares about. Start narrow, make one dashboard trustworthy, and let demand pull you outward.

If you want a quality and observability layer designed around your own SLAs, our data governance and compliance and data engineering teams do exactly this work — get in touch with a description of the tables you cannot afford to get wrong.