What are the challenges of grouping by a high-cardinality dimension?

WHAT THIS TESTS: This question probes whether you understand the internals of columnar databases and distributed query engines under extreme cardinality. Interviewers want to see that you can connect a business requirement, grouping by user_id, to concrete resource bottlenecks rather than hand-waving about big data.

A GOOD ANSWER COVERS: Four technical layers in order. First, query execution memory and CPU costs: a GROUP BY on a high-cardinality dimension forces the engine to maintain a hash table or sorted run with one entry per unique user_id, which can be billions of entries, spilling to disk and destroying latency. Second, storage compression collapse: columnar formats like Parquet or ORC rely on dictionary or run-length encoding, but near-unique values reduce compression ratios by an order of magnitude, inflating IO. Third, result set and network overhead: returning millions or billions of groups to a client or downstream service is often impractical and can OOM the coordinator. Fourth, indexing futility: bitmap or inverted indexes grow linearly with cardinality and can become slower than a full scan. You should also mention mitigations: approximation algorithms like HyperLogLog or TopK, pre-aggregating into rollups, enforcing mandatory filters before grouping, and partitioning plus partition pruning to shrink the working set.

COMMON WRONG ANSWERS: Saying you will just add a B-tree index without discussing how indexes degrade or explode at billions of unique values. Recommending vertical scaling, more RAM, or a bigger warehouse as the primary strategy without addressing the algorithmic linear blowup. Proposing to shard randomly without explaining how that does not reduce per-node cardinality for a global GROUP BY. Ignoring the client-side or network cost of shipping a massive result set.

LIKELY FOLLOW-UPS: How would you change the API or product requirements if the result set has ten million groups? When is it acceptable to use approximate aggregates and how do you communicate accuracy to users? How does partitioning by time help if the user still groups by user_id across the entire time range? How would you design a materialized view or rollup table that trades granularity for performance?

ONE CONCRETE EXAMPLE: Imagine a trillion-row events table where user_id has five hundred million unique values. A query grouping by user_id for a week of data might need a ten-gigabyte hash table per executor, spill to disk, and return a result set that is two gigabytes compressed. A strong design would require a mandatory time filter, use a daily partitioned rollup that stores per-user_id aggregates, and expose a COUNT DISTINCT user_id via HyperLogLog sketches for unfiltered explorations while keeping exact GROUP BY behind a strict sampling or top-K limit.

Source: Hydrolix Docs, A Detailed Guide to High Cardinality