1.8 Billion Events and 900,000 Small Files

Replace partitioning with liquid clustering on (event_date, merchant_id). The query mix is the argument: 85% of queries prune on a date range and group by merchant, which is exactly the pair of columns clustering optimizes. Partitioning on event_date alone already gives date pruning, so partitioning is not why queries are slow - the 900,000 files are. Every partition holds hundreds of files, so any scan opens hundreds of small files, and the nightly 7-day rewrite creates a fresh crop each night without compacting the rest. Partitioning also caps you at one pruning dimension, so merchant-level queries cannot be pruned at all.

Liquid clustering addresses both: clustering keys can include the merchant key alongside the date, and compaction is incremental rather than requiring a full table rewrite. Note the two are mutually exclusive - you cannot partition and cluster on the same table - so this is a conversion, not an addition.

Migration path, in order:

1. ALTER TABLE silver.events CLUSTER BY (event_date, merchant_id); on the existing table.
2. Trigger compaction so existing data is laid out by the new keys; the 900,000 files will not reorganize themselves, and until compaction runs, query performance is unchanged and people will conclude clustering "didn't work."
3. Enable predictive optimization at the schema or catalog level so the nightly 7-day rewrite is followed by maintenance automatically instead of relying on someone remembering.
4. Stop the nightly job from writing many tiny files - add a repartition or a compaction step at the end of the write, since clustering cannot rescue a writer that emits hundreds of files per batch.

What I would not do: repartition by event_date and merchant_id together. That multiplies partitions into the tens of thousands, reintroduces the small-file problem at a larger scale, and still cannot serve the 15% cohort queries well. Accept that cohort queries scan more; they are the minority and should not drive the layout.

Fix the grain before anything else. The disagreement is almost certainly a fan-out, not a computation error: someone joins events to a dimension with multiple rows per key, or unions an events table with an orders table at different grains, and totals inflate. Publish two facts with explicitly documented grains - fct_merchant_daily at one row per merchant per day (the pre-aggregated workhorse for the 85%) and fct_event at event grain for drill-down - plus conformed dim_merchant, dim_date, dim_country. State the grain in the table comment; Genie, BI authors, and analysts all read comments.

Serving mechanism: a scheduled incremental table, not a materialized view over 18 months. With a 30-minute freshness tolerance and 200 queries/hour, what matters is that queries are cheap and refresh cost is predictable. Refresh fct_merchant_daily every 15 minutes by recomputing only the affected recent days and merging on (merchant_id, date_key). That is cheap, restartable, and bounded regardless of table growth. A materialized view is attractive on paper, but whether its refresh is incremental depends on the query it wraps; an aggregation across the full history may refresh substantially more data than the 15-minute budget allows, and refresh cost grows with the table. Choose the MV only for definitions that are demonstrably incrementally refreshable, and check refresh history rather than assuming.

Handling the private wide tables with empathy, not prohibition. The analysts built them because the star schema was slow and the metric definitions were unclear. Ship the aggregate, then publish a small metrics dictionary defining net revenue, sessions, and bounce, and reconcile the official dashboard and one analyst table against the same date range together. If after the aggregate ships their numbers still differ, the difference is a definition, and a definition found by disagreement is worth more than one found by inspection. Do not simply revoke access to the underlying table - that converts a fixable disagreement into shadow IT.