Describe data architecture for fast multi-dimensional slicing and pre-computation trade-offs

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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?

ONE 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.

Source: Wikipedia: Data warehouse