Your First BigQuery Drift Audit with CoreModels

Zero to a recorded schema audit of a BigQuery dataset: one SQL query, two HTTP calls, no credentials shared.

Your BigQuery project already describes itself. INFORMATION_SCHEMA knows every table and view, every column, every declared type, every nullability flag. What it cannot tell you is whether any of that still matches what your team agreed the data means — the model your dashboards, contracts, and AI agents silently depend on. CoreModels (by ARAMAI) closes that gap: you import a one-query extract of your estate into a governed model, and from then on you can audit any fresh extract against it, on demand or in CI.

This tutorial takes you from an empty governing project to your first recorded audit. You need three things:

• a CoreModels project to govern the estate — its id is a 32-character hex string, $PROJECT_ID below;
• a CoreModels token with Admin access on that project ($TOKEN below) — Admin is needed once, for the import; audits only need Viewer;
• the bq CLI or BigQuery console access to your GCP project.

CoreModels never connects to Google Cloud and never holds your Google credentials. You run one query; you upload the result.

Step 1 — Extract the estate

The BigQuery connector consumes exactly one artifact, named information_schema: a JSON export of INFORMATION_SCHEMA.COLUMNS joined to TABLES, with column descriptions stitched in from COLUMN_FIELD_PATHS and table descriptions from TABLE_OPTIONS. This is the documented extraction query — run it once per dataset, substituting your project and dataset:

SELECT c.table_catalog, c.table_schema, c.table_name, t.table_type,
       topt.option_value AS table_description,
       c.column_name, c.ordinal_position, c.data_type, c.is_nullable,
       fp.description
FROM `<PROJECT>.<DATASET>`.INFORMATION_SCHEMA.COLUMNS c
JOIN `<PROJECT>.<DATASET>`.INFORMATION_SCHEMA.TABLES t
  ON t.table_name = c.table_name
LEFT JOIN `<PROJECT>.<DATASET>`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS fp
  ON fp.table_name = c.table_name AND fp.field_path = c.column_name
LEFT JOIN `<PROJECT>.<DATASET>`.INFORMATION_SCHEMA.TABLE_OPTIONS topt
  ON topt.table_name = c.table_name AND topt.option_name = 'description';

Save it as extract.sql and export the result as JSON:

bq query --use_legacy_sql=false --format=json "$(cat extract.sql)" > information_schema.json

In the console, the equivalent is Save results → JSON. The file is a plain array of flat rows — this is the whole artifact contract, so it is worth a look before you upload it:

[
  { "table_catalog": "my-gcp-project", "table_schema": "analytics", "table_name": "customers",
    "table_type": "BASE TABLE", "table_description": "\"Customer dimension.\"",
    "column_name": "customer_id", "ordinal_position": 1, "data_type": "INT64",
    "is_nullable": "NO", "description": "Primary key." },
  { "table_catalog": "my-gcp-project", "table_schema": "analytics", "table_name": "orders",
    "table_type": "BASE TABLE", "table_description": null,
    "column_name": "line_items", "ordinal_position": 3,
    "data_type": "ARRAY<STRUCT<sku STRING, qty INT64>>",
    "is_nullable": "YES", "description": null }
]

Note the doubly-quoted table_description: TABLE_OPTIONS returns option values wrapped in quotes, and the connector strips exactly one layer, so "Customer dimension." arrives as the governed description.

Those two LEFT JOINs are not decoration. Descriptions are first-class governed facts in CoreModels — they become the documentation your model carries, and one of the BigQuery audit rules flags tables that lack them. An extract without descriptions still imports fine; it just tells the auditor something worth knowing.

Step 2 — Import

The interactive HTTP surface lives under graph/integrations/... and uses your normal CoreModels login token. Import requires the Admin role and is additive: existing governed nodes are never mutated, so re-running it later only adds what is new.

Build the request body — the artifact content travels as a string inside the JSON:

jq -n --rawfile info information_schema.json \
  '{artifacts: {information_schema: $info}}' > import-request.json

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/bigquery/import/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @import-request.json

A successful import answers with counts plus two honesty channels:

{ "success": true, "vendor": "bigquery", "projectName": "my-gcp-project",
  "datasetsAdded": 10, "datasetsSkippedExisting": 0, "fieldsAdded": 84,
  "lineageEdgesAdded": 0, "lineageEdgesSkipped": 0, "nodesEnriched": 94,
  "snapshotStored": true, "lossiness": [], "errors": [] }

Reading it: datasetsAdded is tables and views that became governed Types (identity project.dataset.table, materialized views and external tables distinguished); fieldsAdded is columns that became Elements, with is_nullable = NO arriving as a NotNull check and the exact native type string preserved in the Google BigQuery Metadata mixin. nodesEnriched counts nodes that received that vendor metadata. snapshotStored: true means the parsed estate snapshot was persisted, which is what makes later one-call re-audits possible. lossiness is the channel where the connector confesses approximations — for example, NUMERIC columns are governed as Double, and STRUCT/ARRAY/JSON columns are honestly approximated as String rather than silently flattened. errors means could-not-proceed; lossiness never does.

Step 3 — Run the first audit

The audit verb is strictly read-only and needs only the Viewer role. It compares a fresh extract against the governed model and reports three sections: Coverage (what exists in BigQuery but is not governed), Drift (what changed relative to governed meaning), and Conformance (vendor best-practice rules). Passing recordHistory: true adds this run to the project's rolling audit trail — without it, the audit leaves no trace at all.

jq -n --rawfile info information_schema.json \
  '{artifacts: {information_schema: $info}, recordHistory: true}' > audit-request.json

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/bigquery/audit/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @audit-request.json

Step 4 — Read the result

The response carries machine-readable counts (errorCount, warningCount, infoCount), a codes map of finding-code frequencies, the individual findings, and a markdown field holding a human-readable report you can paste straight into a pull request. historyRecorded: true confirms the run joined the trail.

Because you audited the same extract you just imported, expect zero Coverage and zero Drift findings — everything is governed and nothing has moved yet. Conformance findings can still appear, because they judge the estate itself. The BigQuery connector ships two:

• table-no-description (Info) — a table or view with no description; undocumented datasets resist governance and agent grounding.
• semi-structured-column (Warning) — an aggregate per table:

{ "section": "Conformance", "severity": "Warning", "code": "semi-structured-column",
  "subject": "my-gcp-project.analytics.events",
  "message": "2 STRUCT/ARRAY/JSON column(s) carry ungoverned inner schemas.",
  "detail": "payload, line_items" }

That warning is the connector being honest about its own limits: BigQuery's semi-structured columns carry inner schemas the flat INFORMATION_SCHEMA extract cannot see, so their contents are governed only as opaque strings.

The number that matters for automation is errorCount. Info and Warning findings inform; errorCount > 0 is the signal that governed meaning has been violated — the exact condition a CI gate should fail on. Drift findings such as a column disappearing or changing type are what push it above zero later, when the estate and the model start disagreeing.

Step 5 — Make it visible

Because your audit recorded history, the project now has a live status badge:

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

It returns image/svg+xml rendered from the latest recorded run: green for clean, yellow for warnings, red for errors, gray when no runs are recorded yet. Yours should currently be green or yellow — and if it is yellow, the badge just paid for itself by telling you which tables need descriptions.

Where to go next

You now have a governed model, a recorded audit, and a badge. Three natural next moves: wire the same audit into CI on the machine-to-machine v1 surface so schema drift fails pull requests; call the reaudit verb after editing the governed model, which re-checks the stored estate snapshot against the current model with no fresh artifacts needed; and try generate, which writes governed BigQuery DDL back out — CREATE TABLE IF NOT EXISTS statements with NOT NULL and OPTIONS(description=...) carrying your governed meaning. All of them, plus the MCP tools that give your AI agents the same powers, are covered in the Google BigQuery quickstart in the CoreModels documentation.