Automatic Clustering maintaining tables nobody queries

Clustering is a background cost with a foreground benefit

Automatic Clustering reorganises micro-partitions so queries with selective predicates scan less data. Snowflake does that work continuously in the background, consuming credits whenever the table changes.

The benefit only materialises when queries actually run against it. A table being written to and rarely read pays maintenance forever and collects the benefit almost never.

Why the rule is explicitly a heuristic

The source describes it as one, and that framing is honest. Reclustering credits above zero and fewer than 100 queries in 30 days is a proxy for “maintenance dominates query savings”, not a measured comparison.

Snowflake does not report how many credits a query avoided because the table was clustered, so the true comparison cannot be computed from usage views. The rule finds the suspicious pattern; confirming it needs someone who knows the workload.

Suspend rather than drop

The recommended action is ALTER TABLE ... SUSPEND RECLUSTER, not dropping the cluster key.

That distinction matters. Suspending stops the credit burn while keeping the key definition, so re-enabling later is one statement. Dropping the key means re-deciding the clustering strategy and paying a full initial recluster if you bring it back.

For a table whose read pattern is seasonal (quarterly reporting, annual audits), suspend between peaks and resume before them.

Ranking credits in AUTOMATIC_CLUSTERING_HISTORY

SELECT table_name, SUM(credits_used) AS reclustering_credits
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time > DATEADD(day,-30,CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY reclustering_credits DESC;

Then check QUERY_HISTORY for reads against those tables over the same window.

When clustering was never justified

Clustering helps large tables with selective, repeated filters. On a table under a terabyte, or one queried with full scans, it rarely earns its maintenance cost, and this finding is often the first time anyone notices it was enabled by default rather than by decision.