What is OLAP (Online Analytical Processing)?

How it works

An OLAP query touches millions or billions of rows but usually only a few columns: “revenue by region by month for three years”. The engine is built for that:

• Columnar storage, so only the referenced columns are read, and similar values in a column compress well.
• Pruning by partition and by min/max statistics on files or micro-partitions, instead of many secondary indexes.
• Dimensional or wide schemas, such as star schemas, which keep joins few and predictable.
• Massively parallel processing, which splits one scan across many nodes or threads.

Snowflake, BigQuery, Redshift, Databricks SQL, ClickHouse and DuckDB are OLAP engines. Latency targets are seconds rather than milliseconds, and concurrency is tens or hundreds of queries rather than thousands of tiny transactions. OLAP systems do support updates, but they are costlier than in OLTP because data lives in immutable, columnar files.

The term is older than cloud warehouses: “OLAP cubes” were pre-aggregated multidimensional structures that BI tools queried. Today OLAP usually means the workload, served by a warehouse or lakehouse, with SQL features such as GROUP BY ROLLUP or CUBE producing subtotals that cubes once precomputed.

Example

A typical OLAP query: aggregate a fact table by two dimensions with subtotals, using ROLLUP.

CREATE TABLE sales (region text, month text, amount numeric);
INSERT INTO sales VALUES
  ('North', '2026-03', 120), ('North', '2026-04', 80),
  ('South', '2026-03', 200), ('South', '2026-04', 50);

SELECT coalesce(region, 'All regions') AS region,
       coalesce(month, 'All months')   AS month,
       sum(amount)                      AS revenue
FROM sales
GROUP BY ROLLUP (region, month)
ORDER BY region, month;

   region    |   month    | revenue
-------------+------------+---------
 All regions | All months |     450
 North       | 2026-03    |     120
 North       | 2026-04    |      80
 North       | All months |     200
 South       | 2026-03    |     200
 South       | 2026-04    |      50
 South       | All months |     250

Common mistakes

• Trying to speed up warehouse scans with more indexes instead of partitioning, clustering and selecting fewer columns.
• SELECT * on a wide columnar table, which reads every column and removes the main advantage of the format.
• Confusing OLAP the workload with OLAP cubes the older technology.
• Sending high-frequency single-row updates to an OLAP engine as if it were an OLTP database.