Challenges of Grouping by a High-Cardinality Dimension
This tests your grasp of how data shape impacts system resources. A great answer explains that high cardinality explodes memory usage for aggregation state, increases CPU load, and hurts storage compression.
What's really being asked
This question tests your first-principles understanding of how database and analytics engines work. The interviewer isn't looking for a specific tool name. They want to see if you can connect an abstract data property (cardinality) to concrete resource contention (memory, CPU, I/O) and cost. It separates candidates who know buzzwords from those who understand the underlying mechanics of data processing at scale.
The full answer
An excellent answer addresses three main impact areas in order. First, Memory Pressure: A GROUP BY operation needs to hold the state for every group in memory, typically in a hash map. With millions of unique user_ids, this map can grow to gigabytes, exceeding worker RAM and causing Out-Of-Memory errors or expensive spills to disk. Second, Compute and Latency: Managing this enormous in-memory state, handling hash collisions, and potentially sorting millions of final group keys consumes significant CPU cycles, leading to slow queries. Third, Storage and I/O Inefficiency: High-cardinality data compresses poorly because there are few repeating values. This increases storage costs and the amount of data that must be read from disk (I/O) for a query. Indexes also become bloated and less effective.
The mistakes people make
Vague answers like "it will be slow" or "it will impact performance" without explaining why in terms of memory, CPU, or I/O.
Confusing high cardinality (many unique values) with a large number of rows. A column can have billions of rows but low cardinality (e.g., country_code).
Suggesting a standard B-tree index on the user_id column as a complete solution. While it can help with point lookups (WHERE user_id = X), it does not solve the core aggregation problem for GROUP BY and the index itself becomes a massive storage and memory burden.
Jumping immediately to a tool ("Just use Druid!") without explaining the principles that make that tool effective for this problem.
What usually comes next
How would you estimate the memory required for this query before running it? What is HyperLogLog and how could it help here? When is it not appropriate? How would you design the API or UI for this reporting system to protect the backend from these expensive queries? What are the trade-offs between pre-aggregating the data versus allowing these queries on raw data?
A concrete example
Imagine a table with 10 billion events and 100 million unique user_ids. A query doing SELECT user_id, COUNT() FROM events GROUP BY user_id requires the database to maintain a state for 100 million distinct groups. The memory for the hash map holding this state would be roughly 100M keys (8 bytes for user_id + 8 bytes for the count) = 1.6 GB. This is just for one query on one worker node. If multiple such queries run concurrently, or if the state needs to be larger, memory is quickly exhausted, forcing the operation to spill to disk, which can be 1000x slower than RAM access.
Interview question
What is the primary resource challenge when a database performs a GROUP BY operation on a column with millions of unique values?
- a.Slower individual row lookups caused by bloated indexes on the grouped column.
- b.Increased network latency from distributing large result sets.
- c.Exhaustion of available RAM due to the need to store a large, unique state for each group.Correct
- d.High CPU utilization from managing hash collisions and sorting group keys.
Why? this is the answer
Option C correctly identifies memory exhaustion as the primary challenge, as the database must hold a unique state for every group in RAM. While high CPU utilization (Option D) is also a consequence, memory pressure from the aggregation state is often the more immediate and fundamental bottleneck for high-cardinality GROUP BY operations.
Just read this? Test yourself on what you have been reading.
Read the original → hydrolix.io
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on system design — each one lists the topics its interview covers.
See open roles