Clustering key choice on a 4 TB tenant-partitioned fact table
EVENTS_FACT is 4.2 TB (roughly 150,000 micro-partitions) loaded in near-event-time order, with 2,000 tenant_id values. 85% of production queries filter a single tenant_id plus a 7-day event_date range and should touch well under 1 GB; in practice they scan 40–60 GB. The other 15% are cross-tenant 30-day aggregates for finance and run acceptably today. The table is currently clustered on (event_date), automatic clustering costs about 8 credits/day, and clustering depth is low. A proposal is circulating to change the clustering key to (tenant_id, event_date). Finance has refused to accept slower reports, and the platform's budget for a one-time recluster is capped at roughly 200 credits.
1. Diagnose why single-tenant queries scan 40–60 GB despite an effective date clustering key.
Read the worked answer
Pruning is driven by micro-partition min/max metadata. The date predicate prunes well - that is why depth is low and finance is happy - but within the surviving 7 days of partitions, rows for all 2,000 tenants are interleaved in arrival order. The min/max of TENANT_ID in each of those partitions spans the full domain, so no partition can be eliminated on tenant. Every surviving partition is read in full.
Ordering inside the clustering key is the crux: only the leading column gets the strong pruning benefit. With (event_date) alone, tenant is invisible to pruning. Even with (tenant_id, event_date), the date predicate prunes within the selected tenant's partitions rather than pruning the table.
Do not accept the low depth as evidence of health - depth measures consistency of the existing key, not whether that key matches the workload. Confirm the diagnosis with SYSTEM$CLUSTERING_INFORMATION and by comparing partitions scanned against partitions total for a representative 7-day single-tenant query.
2. Evaluate (tenant_id, event_date), and at least one alternative, against the cost cap.
Read the worked answer
Option 1 - rekey to (tenant_id, event_date). Best fit for the 85%: a 7-day single-tenant slice should prune to a small partition set. Risk is the 15%: finance scans 30 days across all tenants, and with tenant leading, that predicate cannot prune, so finance may end up scanning more of the table than it does today. This must be measured, not assumed - and finance's refusal is a constraint on the answer, not a footnote.
Option 2 - keep (event_date) and add Search Optimization Service on tenant_id. Good for selective equality lookups returning few rows. A 7-day aggregate over a tenant's rows is a scan, not a point lookup, so SOS is unlikely to deliver the 40–60 GB → <1 GB change. Cheap to trial, though.
Option 3 - rekey and give finance a pre-aggregated table built by a scheduled task. This removes the conflict instead of trading it: finance queries a small aggregate, and the fact table is optimised for tenant access.
Budget: a full recluster of 4.2 TB is a real cost; a 200-credit cap makes a trial-then-revert strategy attractive if the wall-clock recluster fits.
3. How would you decide and roll out, given you cannot simply test on production for weeks?
Read the worked answer
Decide with a bounded, instrumented experiment rather than a full commitment.
- Capture a query set - say 20 representative tenant queries and 5 finance queries - with current scanned-bytes and elapsed-time baselines from query history.
- Clone and rekey a sample. Use a zero-copy clone of a time slice, or a subset table, reclustered on the candidate key, and run the same query set. Compare partitions scanned, which predicts production behaviour better than elapsed time on a small slice.
- Test both orders - (tenant_id, event_date) and (event_date, tenant_id) - because the answer is workload-specific and the difference is not predictable from cardinality alone.
- Estimate recluster credits against the 200-credit cap before enabling automatic clustering on the full table; if the initial recluster exceeds the cap, rekey in time-range chunks or narrow the key.
Roll out with automatic clustering enabled and watch AUTOMATIC_CLUSTERING_HISTORY: ongoing recluster cost must stay proportionate to the 8 credits/day the team already pays, or you have converted a query problem into a bigger cost problem.