BigQuery freshness checks that detect partial loads

Suppose the German sales feed loaded this morning but the French feed stopped yesterday. A check on max(order_created_at) across the combined table still passes. It has found a recent order; it hasn’t established that the report contains the data it should.

A useful freshness check starts with the expected inputs and asks how far each one has been successfully processed into the published result. We’d keep that evidence in small operational tables, rather than infer it from whichever business rows arrived most recently.

Track source coverage and publication separately

There are two useful times to record. source_complete_through is the source boundary that the loader has finished processing under its extraction contract. published_at is when the validated result became available to its consumers. A freshly published table can still contain old source data, so neither timestamp replaces the other.

For a batch export, coverage might mean every expected file for a closed interval has arrived and passed validation. For a change feed, it might mean processing all changes through a confirmed source position. Record that position as well as any timestamp derived from it. A timestamp is useful for monitoring age; it isn’t always precise enough to resume extraction safely.

Don’t set coverage to the largest event timestamp in the payload. A late correction to last month’s order can be processed today, and an event with a bad future timestamp can arrive before missing events from yesterday. Streaming watermarks also depend on the source’s guarantees and can permit late data; Apache Beam’s watermark and late-data documentation explains that distinction. Record the source boundary the loader has finished processing. The source can still send corrections to older data.

Make missing inputs visible

Keep one contract for each independently required source and scope. A scope might be a country, store, tenant, or source partition. It should be small enough that one healthy feed can’t hide another feed’s failure, but not so granular that every ordinary quiet period creates noise.

In this example, ops.freshness_contracts and ops.published_loads are application-owned tables, not BigQuery metadata views. The contract table contains source_name, scope_name, max_source_lag_minutes, and max_publication_gap_minutes. Use nonempty source and scope names, positive integer limits, and one contract per pair.

The publication ledger contains those identifiers plus publication_seq, run_id, source_complete_through, and published_at. Allocate a unique, increasing sequence within each source and scope when accepting a publication. This orders accepted releases, not source event times. Keep attempted and rejected loads in a separate run log.

Use string for the names and run ID, int64 for the sequence and minute limits, and timestamp for the two times. Validate required fields and sequence uniqueness in the writer.

The expected scopes must come from reviewed configuration or a reliable source inventory. Building the contract table from today’s delivered rows simply hides the missing French feed again. Check that the inventory itself isn’t empty or missing configured sources.

Query the latest accepted publication for every contract

The following GoogleSQL reports the first failing condition for each scope. Create the two tables with the fields described above, replace the dataset names as needed, and run it in their location. Store the times as timestamp values. Here, the source boundary and publication clock must be comparable; a source with known clock skew needs an explicit tolerance instead of silently accepting future timestamps.

with contracts as (
  select
    *,
    count(*) over (
      partition by source_name, scope_name
    ) as contract_count,

  from
    ops.freshness_contracts
),

latest_publications as (
  select
    source_name,
    scope_name,
    publication_seq,
    run_id,
    source_complete_through,
    published_at,

  from
    ops.published_loads

  qualify
    row_number() over (
      partition by source_name, scope_name
      order by publication_seq desc
    ) = 1
),

checked as (
  select
    c.source_name,
    c.scope_name,
    p.run_id,
    p.source_complete_through,
    p.published_at,
    case
      when
        c.contract_count != 1
        or c.source_name is null
        or trim(c.source_name) = ''
        or c.scope_name is null
        or trim(c.scope_name) = ''
        or c.max_source_lag_minutes is null
        or c.max_source_lag_minutes <= 0
        or c.max_publication_gap_minutes is null
        or c.max_publication_gap_minutes <= 0
        then 'invalid_contract'
      when p.publication_seq is null then 'missing_publication'
      when
        p.publication_seq <= 0
        or p.run_id is null
        or trim(p.run_id) = ''
        or p.source_complete_through is null
        or p.published_at is null
        then 'invalid_publication'
      when
        p.published_at > current_timestamp()
        or p.source_complete_through > p.published_at
        then 'invalid_clock_order'
      when
        p.source_complete_through < timestamp_sub(
          current_timestamp(), interval c.max_source_lag_minutes minute
        )
        then 'source_stale'
      when
        p.published_at < timestamp_sub(
          current_timestamp(), interval c.max_publication_gap_minutes minute
        )
        then 'publication_gap'
      else 'healthy'
    end as status,

  from
    contracts as c

  left join
    latest_publications as p
    using (source_name, scope_name)
)

select
  *,

from
  checked

where
  status != 'healthy'

order by
  source_name, scope_name;

The left join is intentional: a contract without a publication must produce a failure. Checking only the ledger would omit a source that has never loaded. The source-age test also prevents a job from making old data look current just by republishing it.

Validate ledger identity separately: duplicate or missing publication sequences make “latest” ambiguous. The publisher should reject unexplained source-coverage regressions before accepting a release. This query monitors age and metadata consistency; it doesn’t inspect the rows or prove that the loader’s completeness claim is true.

Write the record after the data is ready

Finish loading, run the required completeness and quality checks, publish the result, and then record success. Don’t record success when extraction starts or when a scheduler submits a job.

Where both output changes and the ledger update fit in a supported BigQuery transaction, commit them together. BigQuery’s transaction support documented by Google doesn’t extend to arbitrary permanent-table DDL. For a multi-step publish, use serialized or run-isolated candidates and an explicit release procedure. If publication succeeds but recording it fails, preserve enough run identity to reconcile the ledger; a conservative monitoring failure is preferable to claiming that unpublished data is ready.

A valid empty source interval can advance coverage. Record the successful zero-row check instead of treating the absence of new orders as a broken pipeline. Conversely, a partial load must not advance the published boundary for the missing scope.

Keep operational success and business completeness distinct

BigQuery job and table metadata help diagnose failures, but they answer narrower questions. A job in DONE state can have an error, as Google’s BigQuery JOBS documentation explains. A table modification time doesn’t establish which countries or intervals are complete.

Use manifests, reconciliation totals, expected partitions, or source positions to validate coverage before publication. For Dataform, make the relevant checks actual prerequisites of the serving table; an assertion that runs later doesn’t protect that table.

Test the monitor with a missing scope, a valid empty interval, stale data republished now, an invalid timestamp, and a failed publication. Finally, monitor the monitor’s own execution. A query that stopped running produces no failure rows either.