Usage and query-log mining
What can query logs tell you for free, and why can they never prove disuse?
This is the highest-leverage mechanism in the social layer, for one reason: query logs are a by-product the system must produce anyway, so the marginal cost of collecting them, in effort from people, is zero. Everything derivable from them is therefore free and true.
A great deal is derivable:
- real lineage, from executed plans;
- real usage ranking;
- column-level access frequency, which guides the choice of clustering keys (how to order data on disk) and decisions to drop columns;
- join patterns, which show what to denormalize (pre-join) for speed;
- cost attribution (which team's queries cost what);
- deprecation candidates;
- access patterns for security review.
Most organizations have this sitting in a table and use none of it.
The sharp boundary
- Query logs see only what ran through the logged engine. That is the coverage problem again.
- They record behaviour, not requirement. "Zero reads in 90 days" does not mean "unused." Quarterly reports, annual regulatory extracts and disaster-recovery paths are all legitimately idle for longer than any threshold you would pick. (This is Hana's problem 2 in this article.)
So telemetry-driven deprecation has a false-positive rate set by the longest legitimate access interval, which is unknown and unobservable.
A symmetry worth writing down
Two cleanup decisions here rest on the same shape:
| Decision | Assumption it cannot verify |
|---|---|
| Collect orphan files after N hours | no writer is slower than N hours |
| Retire tables unused for N days | no consumer is more dormant than N days |
In both, the assumption is invisible until it breaks, and the person who pays is not the person who set the threshold. The writer of the 8-hour job loses their output. The tax team loses its table.
Query logs are free, true metadata about everything except what has not run yet. And what has not run yet is the whole risk.