OLAP
OLAP (Online Analytical Processing) refers to database systems and workloads optimized for complex, read-heavy analytical queries — aggregations, trends, and multidimensional analysis — across large volumes of historical data.
Definition
OLAP (Online Analytical Processing) refers to database systems and workloads optimized for complex, read-heavy analytical queries — aggregations, trends, and multidimensional analysis — across large volumes of historical data.
Overview
Where OLTP systems handle many small, fast transactions, OLAP systems are built for the opposite pattern: relatively few queries, but each one scanning, aggregating, or joining across potentially billions of rows to answer questions like "what were total sales by region and month over the last three years?" This workload shape favors different storage and schema design — OLAP systems commonly use columnar storage (storing each column's values contiguously) rather than the row-oriented storage typical of OLTP databases, since analytical queries usually touch only a handful of columns across many rows, and columnar formats let the engine skip reading irrelevant columns entirely. OLAP schemas are also often deliberately denormalized, using patterns like the star schema (a central fact table surrounded by denormalized dimension tables) to minimize the number of joins a typical analytical query needs, trading some redundancy for query speed and simplicity — the opposite trade-off made in normalized OLTP schemas. Purpose-built OLAP engines and cloud data warehouses like Snowflake, BigQuery, and Amazon Redshift are optimized specifically for this columnar, aggregation-heavy access pattern, often achieving orders-of-magnitude better performance on analytical queries than a general-purpose OLTP database would. Data typically arrives in an OLAP system via ETL or ELT pipelines that extract it from OLTP source systems, transform it into the analytical schema, and load it into a data warehouse or data lakehouse. Tools such as Apache Spark and dbt are commonly used to build and maintain these transformation pipelines feeding OLAP systems. Understanding when a workload needs OLAP-style infrastructure versus a straightforward OLTP database is a foundational data architecture decision, explored further in courses like dbt & Analytics Engineering.
Key Concepts
- Optimized for complex, read-heavy analytical and aggregation queries
- Commonly uses columnar storage to speed up scans over few columns, many rows
- Schemas often deliberately denormalized (e.g., star schema) for query simplicity
- Powers business intelligence dashboards, reporting, and trend analysis
- Purpose-built engines like Snowflake, BigQuery, and Redshift specialize in this pattern
- Data typically loaded via ETL/ELT pipelines from OLTP source systems
- Contrasted with OLTP, which optimizes for many small transactional writes