A lakehouse table partitioned by hour and customer_id has millions of partitions — what breaks?

## Diagnosing it, not guessing Before prescribing anything, get three numbers: total partition count, average bytes per partition, and — from a representative query's plan — how long planning took versus execution, plus how many partitions survived pruning. Over-partitioning has a distinctive signature: pruning works *beautifully* (a handful of partitions survive) and the query is still slow, because the time went into deciding which handful. If planning is a small fraction of runtime and the surviving partitions are large, you have a different problem and should stop here. ## Why hour x customer_id explodes Partition count is the product of the cardinalities of the partition fields. A year of hours is about 8,760 values; ten thousand customers turn that into tens of millions of possible partitions, and even a sparse table materialises millions of them. Two independent growth axes is the structural mistake: the count grows as history accumulates *and* as the business acquires customers. ## What actually degrades **Planning cost.** Every query begins by narrowing the partition and file list. Whether that list lives in a metastore or in the table's own metadata, filtering millions of entries takes real time and memory, and it happens before a single byte of data is read. This cost is paid per query, including by queries that end up reading almost nothing — which is why dashboards with tight filters feel inexplicably slow. **Tiny files.** A partition receiving one customer's events for one hour may hold a few kilobytes. Reading it costs a request, a footer read, and decoder setup, all to return a handful of rows. The per-file constant dominates, and the effect compounds because a query spanning a day and many customers opens thousands of such files. (The remedy for file size itself — compaction toward a target size — is a separate lever; here the point is that the *layout* guarantees the problem returns after every compaction, because the partition boundaries force the split.) **Skew.** Customer volume follows a power law. Some partitions hold gigabytes and others a few rows, so parallelism is uneven: most tasks finish instantly and a few run long, leaving the cluster idle at the tail. **Operational drag.** Metadata grows, listing and maintenance operations take longer, and anything that must walk the partition list — cleanup jobs, catalog syncs, statistics collection — slows in proportion. ## The opposite failure, for contrast Under-partitioning is the mirror image: too few partitions, each enormous, so a filter that should touch a day instead reads a month. The symptom is the reverse — planning is instant, the scan is huge, and pruning shows most partitions surviving. Knowing both signatures is what lets you say which one you have from a plan rather than from intuition. Between them is a defensible target: partitions large enough to hold at least one properly sized data file, and few enough that the planner's work is negligible next to the scan. ## The fix, in order 1. **Coarsen the time grain.** Hour to day divides the count by 24 immediately. Hour is justified only when a large fraction of queries genuinely bound themselves to a few hours *and* an hour holds enough data to be worth isolating. 2. **Bound the second axis.** Replace `customer_id` identity with a hash bucket of fixed count. Point lookups on one customer still prune to one bucket, but the partition count per day is a constant you chose rather than the size of the customer table. 3. **Or drop the second axis entirely.** If most queries filter time and only some filter customer, partition by day and let finer pruning come from ordering data within the partition by customer, so file-level statistics narrow the scan. That keeps the partition count minimal. 4. **Apply the new layout going forward.** In a format that supports changing the layout in place, this is a metadata commit affecting new writes only; history keeps its old, bad layout until you decide whether it is worth rewriting or simply let retention expire it. ## Isolating the truly large tenant One more option is worth naming for a lead-level answer: if a handful of customers dominate volume, uniform partitioning serves neither them nor the long tail. Splitting those tenants into their own tables — or accepting deliberate skew and sizing the maintenance job around it — is often better engineering than a single layout that is a compromise for everyone. ## What to say in the interview Name the mechanism (planning cost paid per query before any I/O), name the two contributing cardinalities, propose coarsening plus bucketing, and be explicit that the layout change helps new data first. Candidates who answer only "run compaction" have addressed the symptom while leaving the layout that regenerates it in place.