A Framework For Running A Usage Intelligence Agent In Production
Scope, inputs, scoring thresholds, approval tiers, failure modes, rollout and metrics for a who-queried-what agent
A usage intelligence agent should start as a read-only reader of your warehouse's query and access logs, score every table as hot or cold, and only propose deprecations that a human approves. The raw material already exists: Snowflake's ACCESS_HISTORY view retains 365 days of who-read-which-column records, and BigQuery's INFORMATION_SCHEMA.JOBS views keep 180 days of job history. This page lays out the six steps we recommend for turning those logs into a production agent.
Key Takeaways
- •A usage intelligence agent answers one question well: who queried what, and when did anyone last do it.
- •Its inputs are warehouse access and query logs plus lineage and ownership context. Without lineage, a table with no direct reads can still feed a dashboard.
- •Scoring should be explicit and tunable. We suggest a default of no reads for 90 days as cold, then override by table tier.
- •Reports and tickets are autonomous. Tagging is a low-risk write. Dropping or revoking always needs a human approval.
- •Measure the agent by accepted deprecations, reversed deprecations (which should be zero) and storage reclaimed, not by the number of tables flagged.
Why Usage Intelligence Needs A Framework, Not A Script
Most teams already have a script somewhere that lists tables with no queries in the last N days. It works once, then drifts. Nobody owns the threshold, the output goes to a spreadsheet, and the one time someone acts on it a quarterly finance job breaks because that table is only read four times a year. The problem is not the query. It is that scoring, ownership, approval and measurement were never defined.
An agent changes the economics because it runs continuously and can chain into neighbouring work. The Usage Intelligence Agent reads who queried what across the warehouse, scores tables cold or hot, and produces deprecation candidates. It chains with cost, catalog and governance agents, which means a flagged table can carry its storage cost, its owner and its policy tags in the same report. That same reach is why the boundaries need to be written down before it runs.
The framework below is a design proposal, not a certification. The tier names and default thresholds are our recommendations, and you should tune them to your own query patterns before trusting any of them.
Step 1: Define The Scope
Scope the agent to questions about consumption. Examples: what tables haven't been queried in 90 days, who has been reading a given schema, which gold tables are hot, which users query PII columns. Everything else, such as fixing broken pipelines, rewriting models or changing grants, belongs to other agents or to people.
Write the scope as a short list of allowed questions and a longer list of out-of-scope ones. The out-of-scope list matters more. It should state that the agent never executes DDL on production objects, never changes grants, and never reads row-level data. Usage analysis needs metadata about queries, not the contents of the tables.
Also decide the perimeter: which accounts, databases and schemas are in play. Start with one analytics database. A narrow perimeter makes the first review of candidates short enough that a real person reads every line.
Step 2: Specify Inputs And Context
The primary input is the warehouse's own audit trail. In Snowflake that means ACCESS_HISTORY and QUERY_HISTORY in the ACCOUNT_USAGE schema. The Snowflake documentation states a 365-day retention for ACCESS_HISTORY, which sets the upper bound on how far back any cold score can look. On BigQuery, the JOBS views cover the past 180 days, so a 365-day policy needs you to export the logs to your own table.
Retention limits create a quiet failure: a table read once a year looks cold if your window is shorter than that cycle. If your warehouse retains less history than your slowest business cycle, snapshot the logs into a table you control before you let the agent score anything.
The second input is context. We want four things beside the logs: lineage (does a downstream model or dashboard depend on this table), ownership (who answers for it), tier (gold, silver, raw) and policy tags (does it hold PII). A table with zero direct reads but a live downstream view is not cold. Context turns a list of unused tables into a list of safe-to-question tables.
Step 3: Score Tables Cold Or Hot
Define scoring so a reviewer can reproduce it. Our suggested starting point, which you should treat as a default and not a benchmark: a table is cold when it has no reads from any user or service account for 90 days, hot when it has reads on at least 20 of the last 30 days, and warm in between. The 90-day figure is a convention, not a measured result. Quarterly and annual tables need their own rule.
A sample Snowflake query for the cold check, illustrative and to be adapted: select the object names from ACCESS_HISTORY by flattening BASE_OBJECTS_ACCESSED, take max(QUERY_START_TIME) per table, and keep rows where that max is older than 90 days or absent. Join to INFORMATION_SCHEMA.TABLES for creation date so you can exclude tables created in the last 90 days, which would otherwise look unused simply because they are new.
An illustrative candidate row in the agent's report might read: table ANALYTICS.MARTS.ORDERS_LEGACY, last read 143 days ago, readers in window 0, downstream dependents 0, owner data-platform, tier silver, recommendation archive. The values are an example of the shape, not data from a customer. Every row should show its evidence so the approver can disagree with the score.
Add exclusions before the first run: tables tagged with a retention or legal-hold label, tables read only by month-end or year-end jobs, and tables whose only readers are the agent's own queries. Filtering out the agent's own reads is easy to forget and otherwise makes every scanned table look warm.
Step 4: Set Autonomy Tiers
Decide what the agent may do alone, what needs approval, and what it may never do. The MCP specification says that for trust and safety there should always be a human in the loop with the ability to deny tool invocations. We read that as the baseline: tiered permissions are how you put the human in the loop without making them approve every report.
| Tier | Action | Who approves | Example |
|---|---|---|---|
| 0 (autonomous, read-only) | Run usage queries, build reports, answer questions | Nobody | What tables haven't been queried in 90 days? |
| 1 (autonomous, low-risk write) | Add a comment or tag marking a table as a deprecation candidate; open a ticket | Nobody, but logged | Tag ORDERS_LEGACY with candidate_for_deprecation |
| 2 (approval required) | Move a table to an archive schema, revoke a grant, lower retention | Table owner | Archive ORDERS_LEGACY after owner sign-off |
| 3 (never autonomous) | Drop a table, change a role hierarchy, touch legal-hold objects | Human only, outside the agent | Final drop after the archive window |
Read-only mode is a legitimate permanent configuration. The agent can answer who has been querying PII-tagged columns or which tables are cold from the logs alone, provided the connection grants it access to the audit views. It cannot execute tier 1 to 3 actions until you grant write permissions, and each write should be a separate grant, not a bundle.
Step 5: Plan For Failure Modes
Usage data lies in predictable ways. List the failure modes before rollout and assign each a control. A table can look cold because the window is too short, because a service account queries it through a view that hides the base table from the log, or because a process reads it via a copy operation that does not register as a query. Each of these produces a false candidate, and a false candidate that gets dropped is an outage.
| Failure mode | Symptom | Control |
|---|---|---|
| Window shorter than business cycle | Quarterly table flagged cold | Per-table override; snapshot logs beyond retention |
| Reads hidden behind views | Base table shows no reads | Resolve lineage; count view reads as base-table reads |
| Log latency | Recent reads missing from a run | Ignore the last several hours; Snowflake documents view latency |
| Agent's own queries counted | Everything looks warm | Exclude the agent's role from scoring |
| Stale ownership | Approval goes to a departed employee | Fall back to team owner; flag unowned tables |
| Over-trusting the score | Drop proposed without review | Archive-first; no tier 3 action in the agent |
The archive-first control is the most important. Moving a table to an archive schema is reversible in minutes. Dropping it is only reversible within whatever recovery window your warehouse offers. Make the archive step mandatory and give the archive a fixed holding period of your choice before a human removes anything.
Step 6: Roll Out And Measure
Roll out in four stages. First, read-only on one database, with a person reviewing every candidate for two to four weeks. Second, add tier 1 tagging and tickets so owners start to see the output. Third, enable tier 2 archive moves for tables whose owners have approved at least a few candidates. Fourth, widen the perimeter. Move to the next stage only when the previous one has had no reversed actions.
Measure outcomes that cannot be gamed by flagging more. Track accepted candidates divided by proposed candidates, reversed actions (the target is zero), time from flag to owner decision, storage reclaimed in the warehouse's own billing units, and the number of tables with no owner. Report these weekly in the same place as the candidates.
Be honest about what you will and will not know. We do not have a published benchmark for acceptance rates across customers, and you should not borrow one. Set your own baseline during the read-only stage, when you compare the agent's cold list against what your senior engineers would have flagged by hand, then track movement from there.
Frequently Asked Questions
What is a usage intelligence agent?
It is an agent that reads warehouse access and query logs to answer who queried what and when, then scores tables as hot or cold and proposes deprecation candidates. It works from metadata about queries, not table contents, which keeps its data exposure small.
Can the agent run without write access?
Yes, and we suggest it starts that way. In read-only mode it can answer questions such as which tables haven't been queried in 90 days or who queried a PII-tagged schema, as long as the connection can read the audit views. Tagging, tickets and archive moves are separate permissions you add later, one at a time.
Should a usage intelligence agent be allowed to drop tables?
No. We treat drops as a human-only action outside the agent. The agent can archive a table after the owner approves, and a person can drop it once the holding period has passed with no complaints. This keeps every irreversible step with a named human.
How many days without reads should count as cold?
Ninety days is a reasonable default, not a standard. Choose a window longer than your slowest legitimate business cycle, and make sure your warehouse's log retention covers it. If it does not, export the logs to a table you control, and set longer windows for quarterly or annual tables.