What are the challenges of grouping by a high-cardinality dimension?
Tests columnar storage internals and query engine scalability. A strong answer covers memory pressure from giant hash tables, destroyed compression ratios, and massive result-set overhead.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
In a columnar database, why does a GROUP BY on a high-cardinality user_id column most directly inflate storage read IO?
- a.Returning billions of groups compresses poorly over the network, increasing payload size.
- b.Each executor must build a multi-gigabyte hash table, leaving no memory for the OS page cache.
- c.Near-unique user_ids collapse dictionary and run-length encoding, causing column files to expand by an order of magnitude.Correct
- d.The query optimizer abandons indexes and performs a full table scan across all column files.
Why? this is the answer
High-cardinality dimensions defeat columnar compression schemes like dictionary or run-length encoding, directly inflating disk IO. While giant hash tables do spill to disk, that is a query execution memory bottleneck, not the root cause of storage read bloat.
Just read this? Test yourself on what you have been reading.
Read the original → hydrolix.io
- #analytics
- #databases
- #high-cardinality
- #query-optimization
- #columnar-storage
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 analytics — each one lists the topics its interview covers.
See open roles