Clustering Key Choice on a Continuously Loaded Fact Table

No to the resize, no to the key.

The symptom (70-85% of partitions scanned, near-zero spilling) says the cost is in reading micro-partitions, not in computing the result. A larger warehouse adds parallel compute; it does not reduce bytes or partitions read. You would burn 4x the credits per hour for a modest latency gain and the pruning ratio would be unchanged.

The key is the actual defect. Micro-partitions are ordered by the leading column. With REGION_CODE first, all 18 regions are interleaved across the whole table, so a date-only predicate can only prune inside each region band - which is exactly the ~75% scan rate observed. Guidance:

• The leading column must be the most frequent, reasonably selective predicate. Here that is ORDER_DATE (or TO_DATE(order_ts) if the predicate is a cast).
• A low-cardinality leading column such as REGION_CODE gives almost no pruning value.
• Put REGION_CODE second only if measurement shows it helps the one-third of queries that use both.
• Continuous Snowpipe ingestion constantly reorders incoming rows, so any key will drift. Pair the new key with automatic clustering, or the improvement decays within days.

Work backwards from the profile, not from intuition:

Evidence in the profile	Candidate fix
High partitions scanned / total, no spill	Clustering (or a pre-aggregate)
Spill to local or remote disk, time dominated by join/aggregate	Scale up the warehouse, or fix the join
Selective point lookups on a high-cardinality column (e.g. ORDER_ID)	Search optimization service
The same 30-day aggregate recomputed hundreds of times a day	Materialized view or a task-refreshed summary table

For clustering specifically, run SYSTEM$CLUSTERING_INFORMATION before and after to see average depth and overlap, and budget the maintenance cost from SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY - automatic clustering is billed as serverless credits and scales with table size and DML volume. On a table receiving 40M rows a day that bill can exceed the savings, which is why the team suspended it.

Note that the result cache will not save this workload: the continuous load invalidates cached results for ORDER_FACT almost immediately.

Recommendation: test a (ORDER_DATE) key with automatic clustering for two weeks, and in parallel pilot a materialized view for the trailing-30-day aggregate that drives most dashboard traffic. Compare credits per query in QUERY_HISTORY, not just latency.

Clustering buys nothing when pruning is impossible in principle:

• Queries that scan the full date range (all-time totals, cohort analyses) cannot prune on ORDER_DATE.
• Queries dominated by compute - large joins, window functions, sorts - do not care how the rows are ordered on disk.
• Tables that are small relative to warehouse memory gain little from pruning.

If the dominant pattern really is "always the trailing 30 days," then the cheapest architecture is usually a narrow, pre-aggregated table (or a materialized view) holding only that window, maintained by a scheduled task. That reduces bytes scanned by an order of magnitude without paying to re-cluster 2.4 billion rows.

To prove it worked, do not rely on anecdote:

1. Capture a fixed 50-query workload from QUERY_HISTORY and store the SQL.
2. Record baseline: partitions scanned, bytes scanned, elapsed time, credits per query.
3. Replay after the change under the same warehouse size and concurrency.
4. Watch AUTOMATIC_CLUSTERING_HISTORY over at least one full ingestion week, because re-clustering cost lags the key change.

Also account for the fact that changing a clustering key is not free - the initial re-cluster of a large table consumes credits in one burst, so schedule it deliberately.