A CI Drift Gate for BigQuery: Fail the PR, Not the Dashboard

Turning the CoreModels schema audit into an automated gate — the v1 API, errorCount semantics, the badge, the history trail, and the two directions drift can come from.

Schema drift never announces itself. A Terraform or Dataform pull request retypes a column, a cleanup drops a "temporary" table someone's revenue report reads, a description quietly stops being true — and the failure surfaces weeks later, downstream, expensively. The cheapest place to catch all of it is the pull request itself. CoreModels (by ARAMAI) ships a machine-to-machine audit surface built for exactly that: a read-only endpoint that compares a fresh BigQuery extract against your governed model and returns a number CI can gate on.

The contract: one endpoint, one number

The CI surface lives under v1 and accepts user API keys — no interactive login. Two routes exist for a vendor, both requiring only the Viewer role, because the audit never writes:

• POST v1/{projectId}/integrations/bigquery/audit
• GET v1/{projectId}/integrations/bigquery/badge

The audit response arrives wrapped in an envelope, so everything lives under data.*: counts (data.errorCount, data.warningCount, data.infoCount), a data.codes frequency map, the individual data.findings, a data.markdown report formatted for humans, and data.fingerprint, a short content hash of the audited artifact. The gate condition is deliberately blunt: data.errorCount > 0 means the change violates governed meaning — fail the build. BigQuery's conformance findings — a missing table description, a STRUCT column with an invisible inner schema — arrive as infos and warnings; they inform reviewers without blocking them. What pushes the error count above zero is drift against governed meaning, such as a governed column disappearing or changing type out from under the model.

Setup is three secrets-and-variables: a CoreModels user API key with Viewer access on the governing project, the API base URL, and the 32-character hex project id.

The workflow

BigQuery needs no warehouse connection from CoreModels — CI produces the extract itself with the bq CLI (using whatever GCP auth your runners already have) and uploads it. The extraction SQL is the documented per-dataset query from the quickstart, committed here as ci/extract.sql, pointed at the dataset the PR targets:

name: schema-audit
on:
  pull_request:

jobs:
  coremodels-audit:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Extract the BigQuery schema
        run: |
          bq query --use_legacy_sql=false --format=json \
            "$(cat ci/extract.sql)" > information_schema.json

      - name: CoreModels Schema Audit
        env:
          COREMODELS_API_URL: ${{ vars.COREMODELS_API_URL }}
          COREMODELS_API_KEY: ${{ secrets.COREMODELS_API_KEY }}
          COREMODELS_PROJECT_ID: ${{ vars.COREMODELS_PROJECT_ID }}
        run: |
          set -euo pipefail
          jq -n --rawfile info information_schema.json \
            '{artifacts: {information_schema: $info}, recordHistory: true}' > audit-request.json
          curl -sS -o audit-response.json -X POST \
            "$COREMODELS_API_URL/v1/$COREMODELS_PROJECT_ID/integrations/bigquery/audit" \
            -H "Authorization: Bearer $COREMODELS_API_KEY" \
            -H "Content-Type: application/json" \
            --data-binary @audit-request.json
          test "$(jq -r '.success' audit-response.json)" = "true"
          jq -r '.data.markdown' audit-response.json >> "$GITHUB_STEP_SUMMARY"
          test "$(jq -r '.data.errorCount' audit-response.json)" -eq 0

Four lines do the gating. The first test catches transport- or request-level failure. The jq -r '.data.markdown' line publishes the full human-readable report into the job summary, so a failing check explains itself without anyone reading JSON. The final test is the gate. (If you have met our packaged "CoreModels Schema Audit" GitHub Action: it posts dbt-shaped artifacts today, which is why the BigQuery gate is this plain curl step against the very same endpoint.) Teams that want a stricter posture can additionally gate on data.warningCount — a reasonable escalation once the estate is fully described and semi-structured-column warnings have been consciously accepted or resolved.

recordHistory and the rolling trail

Note the recordHistory: true in the request. The audit verb is strictly read-only by default — it leaves no trace unless asked. With the flag set, each CI run is appended to the project's rolling audit history with trigger ci, and that changes what the audit is: not a point-in-time check but a trail. The interactive surface exposes it:

curl -sS -H "Authorization: Bearer $TOKEN" \
  "https://coremodels.example.com/graph/integrations/bigquery/history/$PROJECT_ID"

Each run records its timestamp, trigger, severity counts, per-code counts, and the artifact fingerprint. The fingerprint earns its place: identical fingerprints across runs mean CI audited byte-identical extracts, so a change in findings between them can only have come from the governed model side. When warnings creep from three to nine over a quarter, the history is where you notice.

The badge

The second v1 route renders the latest recorded run as a shields-style SVG labeled bigquery audit: green when clean, yellow with warnings only, red with errors, gray when nothing has been recorded. It is served with image/svg+xml and requires an authorized request, so a plain hotlink from a public README will not carry the API key — the simple robust pattern is to let a pipeline step fetch and publish it wherever your team looks:

curl -sS -H "Authorization: Bearer $COREMODELS_API_KEY" \
  "$COREMODELS_API_URL/v1/$COREMODELS_PROJECT_ID/integrations/bigquery/badge" \
  -o docs/bigquery-audit.svg

Because CI records history on every PR, the badge is always the verdict of the most recent gated change.

The other direction: reaudit

Everything above catches the estate moving under the model. Drift has a second direction: the model moves. A steward tightens a type, renames an element, adds a NotNull check — and the last-known estate may no longer conform, with no PR anywhere to gate. For that, the interactive surface carries the reaudit verb:

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/bigquery/reaudit/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{}'

No artifacts, no bq run, no GCP access: it replays the estate snapshot stored at import time through the same audit engine, against the governed model as it stands now. Unlike the audit verb, reaudit always records its run (trigger reaudit), so a model edit that breaks conformance shows up in the history and flips the badge red even though nothing happened in CI. The natural habit: end every governance working session with a reaudit.

One honest limit: reaudit depends on the stored snapshot. Very large estates whose encoded snapshot exceeds the storage cap (~1.5 MB) import with snapshotStored: false and a lossiness record saying so — fresh-artifact CI audits are unaffected, but reaudit has nothing to run against.

The heartbeat

The last piece is time itself. A badge that only updates when a PR lands or a steward remembers to reaudit can silently go stale. CoreModels deployments include a scheduled server-side re-audit — configuration-gated under Integrations:ScheduledReaudit and off by default — that periodically re-runs the reaudit for opted-in projects against their stored snapshots and records the runs into the history (trigger scheduled). With it enabled, the loop is fully closed: PRs gate estate-side drift as it is proposed, reaudits catch model-side drift as it is made, the heartbeat catches what both missed, and the badge is never more than one interval old.

Start with the workflow above — it is one file and three repository settings — and add the heartbeat when the badge becomes something people check. The extraction SQL, the full route reference, and the equivalent MCP tool calls are in the Google BigQuery quickstart in the CoreModels documentation.