# Google BigQuery — Quickstart: 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 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: ```sql 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 `.`.INFORMATION_SCHEMA.COLUMNS c JOIN `.`.INFORMATION_SCHEMA.TABLES t ON t.table_name = c.table_name LEFT JOIN `.`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS fp ON fp.table_name = c.table_name AND fp.field_path = c.column_name LEFT JOIN `.`.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: ```bash 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: ```json [ { "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>", "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 JOIN`s 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: ```bash 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: ```json { "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. ```bash 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: ```json { "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: ```bash 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.