Describe data architecture for fast multi-dimensional slicing and pre-computation trade-offs
Tests analytical architecture trade-offs between latency and cost. Strong answers propose a hybrid pipeline: columnar warehouse for flexible exploration plus pre-aggregated OLAP cubes for sub-second dashboards.
What's really being asked
Your ability to architect analytical systems that separate exploratory workloads from dashboard serving workloads while reasoning about the trade-offs between pre-computation and flexible querying. Interviewers want to see that you understand dimensionality, cardinality, data freshness, and cost.
The full answer
First, a hybrid ingestion and storage layer, such as Kafka or Kinesis feeding a data lake or warehouse. Second, a columnar database like ClickHouse, Druid, or Snowflake for raw event storage and ad-hoc SQL exploration, which excels at filtering and aggregating across moderate cardinality but can struggle with sub-second latency on high-cardinality group-by queries over billions of rows. Third, a pre-computation layer such as Apache Druid rollups, Apache Pinot, ClickHouse materialized views, or a dedicated OLAP cube for dimensions that power the main dashboard, enabling sub-second response by trading storage for speed. Fourth, explicit trade-offs: pre-computation increases storage by three to ten times, introduces data freshness delays of minutes to hours, and limits flexibility to predefined dimensions, while pure columnar querying offers freshness and flexibility but risks ten to thirty second queries on complex slices. Fifth, a serving layer that routes dashboard queries to aggregates and exploratory queries to the raw columnar store.
The mistakes people make
Proposing only Elasticsearch or a single OLTP database for analytics without addressing scan performance. Suggesting a pure OLAP cube without acknowledging the rigidity of predefined dimensions or the cost of rebuilding cubes. Ignoring data freshness entirely, or claiming columnar stores are always fast regardless of cardinality. Failing to mention that high-cardinality dimensions like user_id make pre-aggregation explode in size.
What usually comes next
How would you handle a sudden requirement to slice by a new high-cardinality dimension that is not in the cube? How do you keep pre-aggregated data synchronized with late-arriving events? What is your backfill strategy if the metric definition changes? How do you control costs if query patterns are unpredictable?
A concrete example
Imagine a SaaS product with ten billion events per day. A columnar store can answer what is the weekly activation rate by country in two seconds, but slicing by individual user_id and twenty custom attributes times out. You create a daily OLAP rollup for country, plan, and device dimensions with one-minute granularity, reducing scan volume by one thousand times and achieving two hundred millisecond query times. Analysts keep full SQL access to raw data for unplanned investigations.
Interview question
In a hybrid analytics architecture, why would a query slicing by user_id likely be served from the raw columnar store rather than the OLAP cube?
- a.Pre-aggregated cubes are optimized only for time-series dimensions and cannot group by user attributes
- b.Pre-aggregating a high-cardinality dimension like user_id causes storage to explode with limited latency benefitCorrect
- c.User-level queries require transactional isolation that columnar stores provide but OLAP cubes lack
- d.OLAP cubes do not support filtering on string columns such as user identifiers
Why? this is the answer
The card highlights that high-cardinality dimensions like user_id cause pre-aggregated storage to explode, making the raw columnar store the right choice for such slices. Option A is tempting because cubes do rely on predefined dimensions, but they are not limited to time-series; the decisive factor is cardinality-driven cost, not dimension type.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #analytics
- #data-warehouse
- #olap
- #columnar-storage
- #system-design
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