Canonical Tables Ai Agents

Canonical tables are the single tables humans trust as the source of truth for a given entity. AI agents cannot reliably pick the canonical table from name alone — they need an explicit signal derived from query logs, dashboards, and human labels.

In most warehouses there are four or five tables that could answer a revenue question and only one is the source of truth. Agents that guess get it wrong often enough to be dangerous. This guide covers how to identify canonical tables, how to score them, and how to feed that score to agents so they stop guessing. Related: which table is canonical and AI for data infrastructure.

Defining Canonical

A canonical table has three properties: it is the one humans cite in business reviews, it is the one production dashboards depend on, and it has a named owner who updates it. Any table missing one of these properties is not canonical even if it has the right data in it. The definition is about trust and accountability, not schema.

Why Agents Need the Signal

LLMs rank tables by name similarity and column match. Neither signal maps to canonicality. A deprecated table can have the perfect name and a current mart can have an obscure one. Without an explicit canonicality signal, agents flip between the two on every retry and users lose confidence fast.

The fix is to compute canonicality as a continuous score from multiple inputs and pass it to the agent as metadata. The agent sorts candidate tables by the score and prefers canonical tables by default, dropping to deprecated ones only when nothing else matches.

Computing the Score

A canonicality score combines at least these inputs. The exact weights depend on the warehouse but the inputs are universal.

• •Distinct users querying the table in the last 30 days
• •Production dashboards depending on it — weighted higher for executive dashboards
• •Downstream marts derived from it — being upstream of trusted data is a canonical signal
• •Explicit human labels — owner declared canonical, overrides everything
• •Freshness SLA — canonical tables are updated on schedule
• •Test coverage — canonical tables have dbt tests

Agent Integration

The agent queries the catalog for candidate tables and receives a list ranked by canonicality score. It prefers tables above a threshold, falls back to lower-scored tables only when necessary, and reports its choice with a one-line justification. Users who disagree can correct it, and the correction updates the human-label component of the score immediately.

Multiple Canonicals

Some domains have multiple canonical tables for different slices. Finance has one canonical revenue table for booked revenue and another for recognized revenue. The scoring model must allow multiple canonical entries per concept, each with a scope, and the agent must pick based on the question. See fiscal vs calendar quarter for AI agents for an example where scope matters.

Maintaining Canonicality

Canonicality is not static. Tables get deprecated, new marts replace old ones, ownership changes. The scoring pipeline must run continuously — daily at minimum — and the agent must see the updated scores in real time. Stale canonicality scores cause the agent to keep picking tables that nobody trusts anymore.

Common Mistakes

The worst mistake is computing canonicality once and hardcoding it. The second is relying on naming conventions. The third is ignoring human overrides. The fourth is not exposing the score to the agent explicitly, which leaves the LLM guessing even though the signal exists.

Data Workers computes canonicality continuously and exposes it to every downstream agent as metadata. To see the full scoring pipeline against your warehouse, book a demo.

Real-World Deployment

Deploying canonicality scoring to a real warehouse takes a week or two. The first few days are ingestion: read query logs, dashboard metadata, and lineage. The next few days are scoring: run the weighting function, generate initial scores, review top results with the data team. The last few days are integration: expose the score to agents, tools, and catalog UI.

The initial scores usually look mostly right. The data team reviews the top 20 tables per domain and overrides any mistakes. Overrides propagate immediately and the agent picks the right tables on the next query. Within a week the warehouse has a reliable canonicality signal that every downstream tool can consume.

Maintenance is automatic. The scoring pipeline runs daily, picks up new tables, deprecates unused ones, and notifies owners when scores change significantly. The data team spends 30 minutes a week reviewing notifications instead of hours manually curating a catalog. That is the efficiency win canonicality scoring provides.

Integrating With BI Tools

Canonicality is not just for agents. BI tools benefit from the same signal: Tableau, Looker, and Power BI can all consume canonicality metadata to surface the canonical tables first in their table pickers. Users building new dashboards see the canonical mart at the top of the list and pick it without thinking, which reduces downstream mess.

The integration is cheap: most BI tools support custom metadata via their catalog APIs. Push the canonicality score to each tool nightly and the rankings update automatically. Users do not need training — they just start seeing the right tables first, and the old habit of picking whatever looked familiar fades.

Over time the compound effect is significant. New dashboards build on canonical tables. Old dashboards get migrated as their users encounter canonicality scores. The warehouse gradually consolidates around the tables that actually matter, and the deprecated ones fade from usage even though they still exist physically.

Cross-team alignment is the unexpected benefit. When every team sees the same canonicality rankings in their BI tool, arguments about which table to use disappear. New hires learn the canonical sources on day one instead of spending weeks asking around. Data literacy improves because the right answer is always visible and the wrong answer requires deliberate effort to find. Canonicality scoring turns the warehouse from a confusing sprawl into a curated library where the important books are on the front shelf and the deprecated ones are in the archive, clearly labeled.

Canonicality is the single most important signal to give a data agent. Compute it from real usage, update it continuously, allow human overrides, and your agents stop picking the wrong tables.