Design a Distributed OLAP Cube

Problem statement

Design a distributed OLAP cube platform that pre-computes aggregates (sums, counts, averages, min/max, and distinct counts) from a large star-schema fact table across dimensions such as time, region, product, store, channel, and campaign, so that analysts and dashboards can slice, dice, drill down, and roll up with sub-second latency. The platform must build cubes from raw batch or streaming data, expose dimension hierarchies (year → quarter → month → day), answer aggregation queries in milliseconds, and refresh incrementally as new data arrives without taking query traffic offline.

The defining tension of this problem is combinatorial explosion versus latency. A fact table with 12 dimensions has 2^12 = 4,096 possible cuboids (one per subset of dimensions), and the cell count of a single cuboid is the product of its dimension cardinalities. Fully materializing everything is impossible: for our reference workload the theoretical cell space exceeds 10^17 entries. Scanning raw rows instead is also impossible within a 300 ms budget at trillions of rows. The design therefore lives in the middle: materialize a pruned subset of the cuboid lattice chosen to cover the actual query workload, store it in compressed columnar segments, route each query to the smallest cuboid that can answer it, and refresh via incremental builds with atomic version swaps.

MOLAP, ROLAP, HOLAP

Classical OLAP splits into three strategies. MOLAP pre-computes and stores aggregates in a specialized multi-dimensional store: fastest queries, highest storage amplification, slowest refresh. ROLAP computes aggregates at query time over relational or columnar facts: zero precomputation cost, unbounded freshness, but latency proportional to scan size. HOLAP mixes both, keeping hot aggregates materialized and falling back to fact scans. A distributed OLAP cube platform is essentially a managed, workload-aware HOLAP engine: the cube layer is the fast path, the lake/warehouse is the fallback and the source of truth.

Public operating baseline versus design assumptions

The category is operationally real and publicly documented. Apache Kylin was created at eBay in 2014 to bring MOLAP-style precomputation to Hadoop-scale data; eBay and the Kylin community publicly reported production fleets of 10,000+ cubes serving 100,000+ queries per day with sub-second latency over petabyte-scale tables. LinkedIn created Apache Pinot around 2013–2014 and open-sourced it in 2015; Pinot's star-tree index is effectively a set of embedded mini-cubes inside immutable segments, and LinkedIn publicly reports trillions of rows under tens of thousands of queries per second across use cases. Cloudflare publicly described its ClickHouse-based HTTP Analytics pipeline ingesting on the order of 25 million+ events per second and using ClickHouse projections — engine-maintained re-aggregated copies of data that behave like automatic lightweight cubes.

For capacity planning, this answer explicitly assumes a mature internal platform: 2,000 active cubes, 50,000 analyst and service users, 5 million queries per day, 20 billion ingested fact rows per day, with all uncited numbers stated as design assumptions rather than company facts.

The four architectural planes

1. Build plane: batch and streaming ingest, dictionary construction, cuboid aggregation jobs, segment writers, incremental merge.
2. Storage plane: immutable, versioned, compressed columnar segments organized by cube and time partition.
3. Metadata plane: cube definitions, dimension hierarchies, cuboid lattice registry, version pointers, build lineage, access policy.
4. Query plane: SQL/metric API, query planner and cuboid router, distributed executors, result cache, admission control.

A strong answer keeps these planes separate: builds must never block queries, queries must never read a half-written segment, and metadata must swap versions atomically so the serving fleet moves from version N to version N+1 as one visible step.