Which Table Is Canonical

A canonical table is the one humans actually trust for a given question. In most warehouses there are three or four tables that could answer a revenue question, and only one is blessed. Deciding which one is canonical requires query logs, dashboard references, and explicit human labels — not just naming conventions.

If you have ever asked an AI agent about revenue and gotten a number that did not match the finance dashboard, you have run into the canonical table problem. The agent picked a table that looked right but was not the one the business trusts. This guide walks through how to identify canonical tables, how to score them automatically, and how to feed that signal to an agent so it stops guessing. Related reading: canonical tables for AI agents and AI for data infrastructure.

Why Canonicality Is Hard

Most warehouses grow by accretion. Someone builds a staging table, someone else builds a mart, a third person builds a cache for a dashboard, and nobody deletes any of them. Three years later a new analyst asks about revenue and finds four tables named orders, fct_orders, mart_orders_daily, and orders_dashboard_v2. All four have a revenue column. Only one is the source of truth.

The legacy fix was naming conventions and a README in Confluence that nobody read. That never scaled. The modern fix is to derive canonicality from actual usage: what tables do dashboards point at, what tables do finance queries touch, what tables do senior analysts trust. That data lives in query logs and catalog metadata, and it can be scored automatically.

Signals That Indicate Canonicality

Four signals together produce a reliable canonicality score. None of them alone are enough, but combined they rank tables with remarkable accuracy.

• •Query frequency — how many distinct users queried this table last 30 days
• •Dashboard references — how many production dashboards depend on it
• •Downstream mart lineage — is it upstream of trusted marts
• •Human labels — did the data team explicitly mark it canonical
• •Freshness — is it actually updated on schedule
• •Test coverage — does dbt or SQLMesh have tests against it

Scoring Canonicality

A simple scoring function works. Give each table a score from zero to one hundred, weighted by the signals above. Senior users pulling data into BI dashboards count for more than anonymous automation. Explicit human labels override everything else. The result is a canonicality score per table that an agent can consume alongside the schema.

The scoring does not need to be perfect — it just needs to be better than the agent guessing. In practice a ranking that correctly surfaces the canonical table as top one or top three is enough for a downstream agent to make the right choice. Perfect identification is not the goal; avoiding catastrophic wrong picks is.

How an Agent Uses the Score

When a question comes in, the agent retrieves a shortlist of candidate tables by semantic similarity, then re-ranks them by canonicality score. Tables below a threshold are dropped unless nothing else matches. The agent prefers canonical tables for answers and deprecated tables only for archaeology questions about historical values.

The agent should also explain its choice. When a user asks about revenue, the agent answers with the table it used and a one-line justification: used fct_revenue because it is the canonical mart, referenced by 14 production dashboards and owned by the finance data team. That transparency lets humans catch bad choices quickly.

What Breaks Canonicality

Two things break canonicality in practice: silent forks and silent deprecation. A silent fork happens when someone builds mart_orders_v2 next to mart_orders, promising to migrate dashboards later, and then forgets. A silent deprecation happens when the canonical table stops being updated but nobody removes it. Both fail the signals above if the signals are computed continuously, which is why scoring must run on a schedule rather than as a one-time exercise.

Human Override

Automation is not perfect. The data team needs a one-click override to mark a table canonical or deprecated, and that label must outweigh any automated signal. The override becomes part of the catalog metadata and gets passed through to every agent that queries it. Without human override, rare but important tables (quarterly close data, legal holds) get mis-scored and agents route around them.

Common Mistakes

The worst mistake is trusting table names. Names drift over time and do not reflect current usage. Another mistake is computing canonicality once and caching it forever — usage patterns shift quarterly and the score must be refreshed. A third is ignoring freshness; a beautifully scored table that has not been updated in 30 days is worse than a less popular one that runs every hour.

Data Workers computes canonicality continuously from query logs, dashboard lineage, and human labels, and feeds the score to every downstream agent automatically. The result is that text-to-SQL agents, schema agents, and quality agents all agree on which table is the truth. To see the full pipeline, book a demo.

Rolling Out the Score Across Teams

Rolling canonicality scoring across a large organization is a change-management project as much as a technical one. Domain teams have strong opinions about which tables they trust, and scores that contradict those opinions must be explained. The rollout should start with one domain, validate the scoring against the domain team explicit beliefs, and iterate until the team is comfortable. Only then roll to the next domain.

The change also affects downstream tooling. BI tools, pipeline managers, and other agents all need to consume the new canonicality signal. A clean API that exposes scores as structured metadata lets every tool adopt them without custom integration. Teams that design the API carefully see adoption accelerate across their stack.

Governance matters. Canonicality scores can be overridden by humans, and the overrides must be auditable. Who marked this table canonical, when, and why. Without audit trails, overrides become untraceable and the system drifts. Data Workers enforces audit on every override and surfaces the history in the catalog UI so governance stays visible.

Auditability and Transparency

Every table in a production catalog should expose its canonicality score alongside the raw score components. Users who disagree with the score can see exactly which signal drove it and which they believe is wrong. That transparency is critical for trust: a black-box score that contradicts user intuition destroys confidence faster than any other catalog error.

The score history also matters. When a table drops from canonical to deprecated, the transition should be visible with a timestamp and a reason. Was it because queries dried up. Did dashboards migrate to a new mart. Did a human explicitly deprecate it. Each reason leads to a different action, and the history makes the reason discoverable.

Transparency plus history also helps new data engineers ramp up. A new hire can look at any canonical table and see why it earned the label, which tables preceded it, and which teams depend on it. That context would take weeks to reconstruct through Slack archeology; the scoring pipeline provides it instantly.

Canonicality is not a naming convention — it is a score derived from real usage, maintained continuously, and overridable by humans. Give your agents that signal and they stop picking the wrong table.