A Complete Answer That Tells You Nothing: The BigQuery Metadata Problem

There is a query most BigQuery engineers can write from memory. Select from INFORMATION_SCHEMA.COLUMNS, join TABLES, and you get a perfect inventory of the estate: every table, every column, every data_type, every is_nullable flag, in order. It is fast, it is free, and it is complete.

It is also nearly useless for the questions people actually ask. Is this the customer table or the one the migration left behind? Which values may status hold this quarter? If I change this column, who breaks? The inventory has no opinion. Warehouses store shape; organisations run on meaning; and the distance between the two is where data teams lose their weeks.

We built the CoreModels BigQuery integration (vendor key bigquery) for that distance. Here is what it looks like from the inside.

The description field is empty on exactly the tables that matter

BigQuery does carry documentation. Table descriptions live in TABLE_OPTIONS, column descriptions in COLUMN_FIELD_PATHS. Both are optional, neither is enforced by anything, and both are reliably blank on the tables that grew fastest — which are the tables everyone depends on.

That is why our audit ships a rule for it. Every table or view without a description produces an Info finding coded table-no-description, with the reasoning stated plainly: undocumented datasets resist governance and agent grounding. Info, because one blank description is not a build break. But when a report comes back with sixty of them, named and counted, documentation debt stops being a feeling and becomes a list you can burn down.

BigQuery enforces no relationships, so nobody records them

Everyone knows orders.customer_key points at customers. That knowledge lives in JOIN clauses, in dashboard SQL, and in the memory of whoever wrote the pipeline. BigQuery enforces no primary or foreign keys — a defensible choice for an analytical engine — which means the relationships that define your business are written down nowhere the platform can check.

So a new engineer joins on the wrong column and the numbers come out five percent low. A column is renamed and a join silently drops rows instead of failing. Two teams reconstruct the same relationship differently and ship two dashboards that disagree, both defensible.

Our connector takes a hard line here, and it is worth being explicit about: because BigQuery enforces no keys, we read none and invent none. Inferring a foreign key from a column name would be the fastest possible way to poison a governed model with confident fiction. A relationship becomes a governed fact when a person states it — and from that moment it is checkable, exportable, and visible to everyone, including your AI tools.

Semi-structured columns are whole schemas hiding inside a single field

STRUCT, ARRAY and JSON columns are among BigQuery's best features and its worst governance problem. One STRUCT<> column can carry a dozen fields with their own types, meanings and lifecycles. To the catalog it is one column. To consumers it is a contract nobody wrote down.

An estate with heavy nesting is one where the inventory is technically complete and practically blind. Our audit raises a Warning coded semi-structured-column for each dataset that has them — aggregated per table, with the offending column names carried in the finding's detail. It is not a scold. It is a map of where the governed model ends and your assumptions begin, which is exactly the map nobody has today.

Change is legal, silent, and discovered downstream

Somebody merges a change to the job that builds an events table. A NUMERIC column becomes a STRING because an upstream system started sending a formatted value. A column four teams read is dropped because one team stopped using it. A dataset is rebuilt under a new name.

None of that is an error in BigQuery. It is all valid DDL. The first sign of trouble is a number that looks wrong in a review three weeks later, by which point the change is buried under six merges and the person who made it has moved on.

This is the pain a drift audit exists to move to the left. Compare a fresh extract against the governed model and CoreModels reports field-type-drift ("Field type changed since the last import", with governed and estate types side by side in the detail), field-removed, and dataset-removed — all Errors, each naming the exact object by its project.dataset.table identity. Coverage findings cover the other direction: dataset-unmapped for estate objects nobody has brought under governance, field-unmapped for columns added since the last import.

Then wire the same call into CI on the pull requests that change your datasets. A nonzero error count fails the build, with a readable report attached. The change still happens if you decide it should. It just stops happening by accident.

Four sources of truth, none authoritative

Most teams hold their meaning in a wiki page that was accurate two years ago, some YAML in a transformation repo, the warehouse itself, and increasingly a chat assistant that infers the rest. Each is authoritative about something. None is authoritative about everything. Ask all four which values status may hold and you will get four answers, and the fourth one was invented from the column name — which is how a promising AI workflow becomes an incident review.

What closing the gap actually costs

Less than teams expect, and specifically no credentials. CoreModels never holds your Google credentials; there is no live GCP connection to configure and nothing to grant. You run one documented query per dataset — it stitches INFORMATION_SCHEMA.COLUMNS and TABLES together with the column descriptions from COLUMN_FIELD_PATHS and the table descriptions from TABLE_OPTIONS — export the result as JSON from the console or bq query, and upload that single artifact, named information_schema.

From there, tables and views become governed Types identified as project.dataset.table, with materialized views and external tables distinguished; columns become Elements with the exact native type string preserved; is_nullable = NO becomes a NotNull check. Import is additive — nodes that are already governed are never mutated — because deciding that meaning has changed is a human act, not a side effect of an upload.

The shape question was never the hard one. This is the other one, finally written down.

The exact extraction query, the import and audit calls, and a CI snippet are in the CoreModels Google BigQuery quickstart.