Snowflake Automatic Clustering Cost: Find Tables Burning Credits

To find your Snowflake automatic clustering cost, query SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY and group credits by table over the last 30 days. Usually one or two tables, often dbt incremental models that rewrite old partitions on every run, account for most of the spend. Once you know which tables they are, you can compare reclustering credits against the pruning benefit and then suspend or drop any clustering key that is not paying for itself.

The awkward thing about this cost is where it shows up. Reclustering is reported in AUTOMATIC_CLUSTERING_HISTORY and billed separately from your query warehouses, so it is easy to miss when you read the line items. You can right-size every warehouse, fix your idle credits (we cover that in Snowflake Idle Warehouse Cost: Find the Credits You're Burning), and still watch a serverless line item climb every month.

A pattern we see repeatedly in growth-stage teams: someone adds cluster_by to a big dbt fact model during a performance push, the dashboards get faster, and nobody looks at the background cost again. Six months later the bill has grown and nobody can say why. This post gives you the queries to answer that in about five minutes.

How Do You Find Which Tables Drive Snowflake Automatic Clustering Cost?

Start with the credit view. You need ACCOUNTADMIN, or a role granted access to the SNOWFLAKE database. The view records the credits consumed, bytes updated, and rows updated each time a table is reclustered.

SELECT
    database_name,
    schema_name,
    table_name,
    ROUND(SUM(credits_used), 1) AS credits_30d,
    ROUND(SUM(credits_used) * 3, 0) AS approx_usd_at_3_per_credit,
    ROUND(SUM(num_bytes_reclustered) / POWER(1024, 4), 2) AS tb_reclustered,
    ROUND(SUM(num_rows_reclustered) / 1e9, 2) AS bn_rows_reclustered
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY credits_30d DESC
LIMIT 20;

Swap the 3 for your own contract rate. That figure is only a rough Enterprise-edition reference. Even so, 40 credits a day on one table is about 1,200 credits a month, which is roughly $3,600 at that rate. That is a real number to put in front of whoever owns the budget.

Now add context. A table that burns 200 credits a month is a very different problem if it holds 50 TB than if it holds 200 GB. This query joins the credits to table size:

WITH c AS (
    SELECT database_name, schema_name, table_name,
           SUM(credits_used) AS credits_30d
    FROM snowflake.account_usage.automatic_clustering_history
    WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
    GROUP BY 1, 2, 3
)
SELECT c.database_name, c.schema_name, c.table_name,
       ROUND(c.credits_30d, 1) AS credits_30d,
       ROUND(s.active_bytes / POWER(1024, 4), 3) AS table_tb,
       ROUND(c.credits_30d / NULLIF(s.active_bytes / POWER(1024, 4), 0), 1) AS credits_per_tb
FROM c
JOIN snowflake.account_usage.table_storage_metrics s
  ON s.table_catalog = c.database_name
 AND s.table_schema = c.schema_name
 AND s.table_name = c.table_name
WHERE s.deleted = FALSE
ORDER BY c.credits_30d DESC;

High credits_per_tb on a small table is the classic sign of a key that was never worth adding. One more thing to check: the VERSION column separates Optima Clustering (OPTIMA) from Clustering Classic (CLASSIC). Under Optima, billing is based on the uncompressed data you ingest, scaled by an overlap factor for how much of it needs active clustering. Group by VERSION if you see both in your account, because the two billing models behave differently.

---

📺 Watch: Snowflake - Clustering Cost

---

Why Does Your dbt Project Keep Reclustering the Same Tables?

First, list every table that has a clustering key, including the ones costing nothing yet:

SELECT table_catalog, table_schema, table_name,
       clustering_key, auto_clustering_on,
       ROUND(bytes / POWER(1024, 4), 3) AS table_tb,
       row_count
FROM snowflake.account_usage.tables
WHERE deleted IS NULL
  AND clustering_key IS NOT NULL
ORDER BY bytes DESC;

Cross-reference that list with your dbt project. In our experience the expensive tables nearly always fall into one of three groups.

1. Incremental models that touch old partitions. Automatic clustering scales with the frequency and volume of DML on the table, so frequently updated tables cost more. A merge on a unique_key with no predicate on the target forces Snowflake to scan and rewrite micro-partitions scattered across the whole table's history. Each run then dirties partitions the clustering service has to fix.

2. Out-of-order backfills. GitLab's public data team handbook describes this one well. Backfill jobs that reprocess months in random order leave the table unsorted and can trigger large re-clustering charges.

3. Full-refresh habits. Clustering the same table once on rebuild is fine. Rebuilding it nightly and also leaving background reclustering on pays for the sort twice. When you set cluster_by, dbt already orders the table results by those fields.

To see how much DML your dbt runs actually push at a suspect table, check query history. Replace the table name:

SELECT
    DATE_TRUNC('day', start_time) AS run_day,
    query_type,
    COUNT(*) AS statements,
    ROUND(SUM(COALESCE(rows_inserted,0) + COALESCE(rows_updated,0) + COALESCE(rows_deleted,0)) / 1e6, 1) AS m_rows_written
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND query_type IN ('MERGE', 'INSERT', 'DELETE', 'UPDATE', 'CREATE_TABLE_AS_SELECT')
  AND query_text ILIKE '%fct_events%'
GROUP BY 1, 2
ORDER BY 1 DESC, 4 DESC;

A fact table that receives a few million new rows a day but shows hundreds of millions of rows written is a table being rewritten, not appended to. That is your reclustering driver.

Is the Clustering Key Actually Earning Its Credits?

This is the question most teams skip, and it decides everything else. Clustering is only worth paying for if it improves micro-partition pruning on queries people actually run. Measure it directly:

SELECT
    DATE_TRUNC('day', start_time) AS query_day,
    COUNT(*) AS queries,
    ROUND(AVG(partitions_scanned / NULLIF(partitions_total, 0)), 3) AS avg_scan_ratio,
    ROUND(SUM(bytes_scanned) / POWER(1024, 4), 2) AS tb_scanned
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
  AND query_type = 'SELECT'
  AND partitions_total > 0
  AND query_text ILIKE '%fct_events%'
GROUP BY 1
ORDER BY 1;

As a rough guide, a 2026 practitioner write-up suggests a ratio that used to sit near 0.05 and has drifted to about 0.40 means the layout has degraded. Read it as a trend rather than a threshold. If the scan ratio is already low, or the table is simply small, the key may be buying you little. GitLab's handbook suggests considering clustering when a table is typically over 1 TB, is queried frequently on specific columns, and shows performance issues. If a table fails those tests and still shows up in the first query, that is your candidate for removal.

For tables you want to keep clustered, check how well the layout holds:

SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.marts.fct_events');

Running that query for one table is fine. Checking every model in a project is not. fintel-scan is a free, open-source (MIT) CLI that runs project-wide dbt checks locally, with no warehouse connection: uvx fintel-scan. If you would rather have a specialist audit the warehouse side as well, explore our services.

How Do You Cut Snowflake Automatic Clustering Cost Without Slowing Queries?

Use this decision framework per table. It is the same triage we run in client audits.

• Small table, healthy scan ratio: drop the key. Savings are immediate and query speed barely changes.
• Large, append-only table clustered on a load-date column: keep it. Snowflake's docs note that append-only loads clustered by an ingestion timestamp typically have an overlap factor near zero, so the charge is little or none.
• Large table with heavy updates or backfills: keep the key but stop dirtying old partitions. Fix the dbt model first, then re-measure.
• Nightly full refresh: let dbt's ordering do the work and suspend background reclustering.

The SQL for the first and last cases:

ALTER TABLE analytics.marts.fct_events SUSPEND RECLUSTER;

-- if the key is not worth keeping
ALTER TABLE analytics.marts.fct_events DROP CLUSTERING KEY;

For the heavy-update case, limit how far back each merge reaches. dbt's incremental_predicates config lets you constrain the target side of the merge:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='event_id',
    cluster_by=['event_date'],
    incremental_predicates=[
      "DBT_INTERNAL_DEST.event_date >= DATEADD(day, -3, CURRENT_DATE())"
    ]
) }}

Only the recent partitions get rewritten, so the reclustering service has far less to fix. Pair it with a bounded lookback in your is_incremental() filter. Our earlier post on BigQuery MERGE scans covers the same principle on another warehouse.

One side effect is worth knowing about. Reclustering rewrites micro-partitions, and the replaced ones can linger in retention. If a table is both expensive to recluster and large in storage, read Snowflake Time Travel Storage Cost: Find Bloated Tables next, because you may be paying for the same churn twice.

How Do You Stop Clustering Cost Creeping Back?

Fixing it once is the easy part. These guardrails keep it fixed.

Schedule a daily check in your orchestrator and alert when any single table passes a credit threshold. This version flags tables above 10 credits a day:

SELECT database_name, schema_name, table_name,
       ROUND(SUM(credits_used), 1) AS credits_yesterday
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(day, -1, CURRENT_DATE())
  AND start_time < CURRENT_DATE()
GROUP BY 1, 2, 3
HAVING SUM(credits_used) > 10
ORDER BY credits_yesterday DESC;

Pick a threshold from your own baseline. Account usage views also lag by a few hours, so alert on yesterday rather than the current hour.

Then add two process rules:

1. Treat cluster_by as a reviewed change. Any pull request that adds or changes a clustering key must include the table size, the current scan ratio, and an estimate of reclustering credits. A 2026 guide on clustering keys makes the same cost-versus-saving point: the credit cost of automatic clustering should be less than the savings you expect on query compute.
2. Re-run the audit quarterly. Query patterns change. A key chosen for a dashboard that was retired a year ago is still billing you today.

This is also an ownership problem, not only a SQL problem. In our work with early-stage companies, the teams that stay in control of warehouse spend are the ones where each expensive table has a named owner and a documented reason for its configuration. Undocumented marts make that impossible, which is why we also cover finding dbt models with no description.

Frequently Asked Questions

Q: How do I check Snowflake automatic clustering cost?

A: Query SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY, sum CREDITS_USED, and group by database, schema and table over the last 30 days. You can also use the Cost Management area in Snowsight. Multiply credits by your contract rate to get a dollar figure.

Q: Does Snowflake automatic clustering run on my warehouse?

A: No. It is a serverless background service, so its credits do not appear against your virtual warehouses. That is why warehouse-level monitoring misses it, and why you need to query the clustering history view directly.

Q: How do I stop automatic clustering on a table?

A: Run ALTER TABLE your_table SUSPEND RECLUSTER to pause it while keeping the clustering key. Run ALTER TABLE your_table DROP CLUSTERING KEY to remove the key altogether. Suspend first if you want to measure the effect on query speed before committing.

Q: Why is my dbt incremental model causing so much reclustering?

A: Most often the merge or delete-and-insert touches micro-partitions across the whole table, or backfills reload historical data out of order. Constrain the target side with incremental_predicates, keep the lookback window tight, and run backfills in date order.

Q: When is a Snowflake clustering key worth the cost?

A: When the table is large, queried often on the clustering column, and scan ratios show poor pruning. It is usually a poor trade on small tables or on tables that are heavily rewritten. Compare reclustering credits against the query compute you expect to save.

Conclusion

Snowflake automatic clustering cost is one of the easiest warehouse bills to ignore, because it sits outside the warehouses everyone watches. The challenge is rarely the SQL. It is knowing which of your clustered dbt models still earn their credits, and having guardrails so the answer stays true next quarter. At Fintel Analytics, we have helped fast-growing fintech, payments and e-commerce teams audit their Snowflake and BigQuery spend, rebuild expensive dbt models and put cost governance in place from the first review through to production. If your serverless line is growing faster than your data volume, that is a fixable problem, and the fix usually pays for the audit within a billing cycle or two.