Challenges of Grouping by High-Cardinality Dimensions

What's really being asked

This question assesses your ability to connect a logical data property (high cardinality) to its physical, system-level consequences (memory, CPU, I/O, cost). It separates candidates who have only used analytics databases from those who understand how they operate and fail at scale. The interviewer is looking for a mechanistic explanation of performance degradation, not just a high-level observation that queries will be slow.

The full answer

A strong answer will identify three core technical challenges in order of severity:

First, the aggregation state explosion. A GROUP BY user_id query requires the database to maintain a state (e.g., a hash map) for every unique user ID encountered. With millions of unique IDs, this state can consume gigabytes of RAM on aggregator nodes, leading to memory exhaustion (OOM errors) or query spilling to disk, which is orders of magnitude slower.

Second, ineffective compression. Columnar databases rely heavily on compression techniques like Run-Length Encoding (RLE), which are highly effective on low-cardinality data (e.g., a 'country' column). A high-cardinality column like user_id has few repeating values, so compression is minimal. This results in a larger storage footprint and increased I/O, as more data must be read from disk or network.

Third, indexing challenges. Traditional indexes like B-Trees become massive and unwieldy for high-cardinality columns in large datasets. The index itself may not fit in memory, leading to slow lookups that require multiple disk reads. Maintaining such an index during data ingestion also becomes a significant overhead.

The mistakes people make

Confusing high cardinality (many unique values) with high volume (many rows). A billion-row table with two unique values is low cardinality. Giving a vague answer like "it makes queries slow" or "it's bad for performance" without explaining the underlying resource contention (memory for aggregation, I/O from poor compression, etc.). Focusing only on storage costs while ignoring the more critical and immediate problem of in-memory aggregation failure, which is the most common reason these queries break. Suggesting solutions like HyperLogLog before clearly defining the problems it solves.

What usually comes next

"What are three strategies you could use to mitigate these issues?" (Probabilistic counting like HLL, pre-aggregation in an ETL pipeline, partitioning the data by time/customer, or simply disallowing this operation in the UI). "How would you estimate the memory required for such a query before running it?" "Under what circumstances might it be acceptable to allow this feature?" (e.g., for high-value customers on provisioned hardware, or if queries are always filtered to a small time range).

A concrete example

Imagine a table with 1 billion rows and 100 million unique user_ids. A query like SELECT user_id, COUNT() FROM events GROUP BY user_id forces the query engine to build a hash table with 100 million keys. If each entry for the key and its counter requires just 32 bytes, this aggregation alone consumes 100M 32 bytes = 3.2 GB of RAM. This can easily exhaust the memory of a single query worker, causing it to fail or spill to disk, increasing query time from seconds to many minutes or hours.