Diagnosing a Query That Got Slower After 'Better' Partitioning

1. Why it got worse

Partition cardinality multiplies across partition columns, not adds. Before, cardinality was roughly "number of days." After, it's approximately days × 195 countries × 40 device types — even if many combinations don't actually occur, the realistic combination count is still enormous relative to the ~50 GB/day of actual data. Spreading a fixed amount of data across that many partitions means each partition holds very little data, so files shrink from a healthy 200–500 MB down to a few hundred KB–MB — squarely in small-file territory. Two separate costs compound: (a) the catalog now has to track roughly 700,000 partitions, and enumerating/pruning that metadata before a query can even start reading data is itself slow; (b) each surviving small file carries the same fixed per-file overhead (open, footer read, task scheduling) as a large file, but that overhead is now amortized over almost no data, so the query spends more time on overhead than on actual scanning. The team optimized for finer-grained pruning and got a net loss because the multiplied cardinality broke two other things (catalog performance, file size) worse than the pruning gain helped.

2. Better layout

Partition by event_date alone (matching the original, coarser scheme) — this keeps partition cardinality proportional to the number of days, so each partition again holds a healthy, well-sized amount of data. To serve the country filter well without partitioning by it, cluster/sort the data within each day's files by country (e.g. via a periodic OPTIMIZE ... ZORDER BY country if on Delta Lake, or an equivalent sort-and-rewrite compaction step otherwise). This makes each row group's country min/max range narrow, so Parquet's predicate pushdown can prune most row groups for a country = 'X' filter within the day's partition, without paying the metadata/small-file cost of partitioning by country directly. The combination — coarse partitioning on the highly selective, always-filtered, low-cardinality column (date), plus clustering on the next most common filter column (country) — gets most of the pruning benefit the team wanted with none of the partition-explosion cost.

3. Supporting device_type without repeating the mistake

If a separate, real query pattern filters heavily on device_type, the fix is still not to add it as a third partition column (same multiplication problem). Options, in order of preference: (a) if device_type queries are also commonly combined with a date range, multi-column clustering (Z-order on country, device_type together, or two separate compaction/clustering passes tuned to the dominant query pattern) can give both columns reasonable pruning within the date partitions, accepting that neither is as tightly sorted as a single-column sort would be; (b) if device_type queries are genuinely a distinct, high-volume access pattern that clustering doesn't serve well enough, consider a separate materialized/derived table pre-aggregated or pre-filtered by device_type for that specific workload, rather than distorting the primary table's layout to serve two very different query shapes equally badly. Either way, the decision should be driven by measuring the actual query patterns (which filters dominate, how selective they are) rather than adding partition columns speculatively.