Data warehouses and analytics
Operational databases run the business; analytical systems help people understand it. Warehouses, lakes, and lakehouses organise data for large-scale analysis.
OLTP and OLAP
Online transaction processing (OLTP) systems handle many small, fast reads and writes, such as placing orders or updating accounts, with high concurrency. Online analytical processing (OLAP) runs fewer but much larger queries scanning millions or billions of rows, such as revenue by region and month. Running heavy analytics on the OLTP database slows customers, so organisations copy data into separate analytical systems.
Data warehouses
A data warehouse is a central store of cleaned, integrated historical data from many sources, organised for analysis. Bill Inmon and Ralph Kimball shaped the field in the 1990s. Modern cloud warehouses, such as Snowflake, Google BigQuery, Amazon Redshift, and Azure Synapse, use columnar storage, compression, and massively parallel processing, and separate storage from compute so each scales independently.
Dimensional modelling
Kimball's dimensional modelling organises warehouses into fact tables, holding measurable events such as sales with numeric measures like quantity and amount, and dimension tables, holding descriptive context such as date, product, customer, and store. A star schema places a fact table at the centre joined to denormalised dimensions; a snowflake schema normalises dimensions further.
Slowly changing dimensions
Dimension attributes change over time: a customer moves city, a product changes category. Type 1 overwrites the old value, losing history; type 2 adds a new row with validity dates, preserving history so past sales stay linked to the city the customer lived in then; type 3 keeps a limited history in extra columns. Choosing correctly matters for accurate historical reporting.
Data lakes and lakehouses
A data lake stores raw data of any kind, such as logs, JSON, images, and tables, cheaply in object storage like Amazon S3, often in open columnar formats such as Parquet. Lakes are flexible but can become disorganised 'data swamps'. Lakehouses add warehouse features on top of lakes, including ACID transactions, schemas, and time travel, using table formats such as Delta Lake, Apache Iceberg, and Apache Hudi.
OLAP operations
Analysts slice data (fix one dimension, such as 2026), dice it (select sub-ranges on several dimensions), roll up (aggregate from days to months to years), and drill down (go from yearly totals to monthly detail). SQL supports these with GROUP BY, ROLLUP, CUBE, and GROUPING SETS, which compute subtotals at several levels in one query.
Business intelligence
Business intelligence (BI) tools such as Tableau, Power BI, and Looker sit on top of warehouses, letting users build dashboards and reports without writing SQL. A semantic layer defines business metrics, such as active customers or gross margin, once and consistently, so different teams do not compute the same number in conflicting ways.
Worked example
A star schema for a retailer: fact_sales(date_key, product_key, store_key, customer_key, quantity, amount) with dimensions dim_date (day, month, quarter, year, holiday flag), dim_product (name, brand, category), dim_store (city, region), and dim_customer (age band, loyalty tier). 'Revenue by category and quarter for 2026' joins fact_sales to dim_product and dim_date, filters year = 2026, and groups by category and quarter, a query a columnar warehouse answers in seconds over billions of rows.
Key takeaways
OLTP handles many small transactions; OLAP runs large analytical scans, so analytics moves to separate systems. Data warehouses integrate cleaned historical data; cloud warehouses such as Snowflake, BigQuery, and Redshift use columnar, parallel processing with separate storage and compute. Dimensional modelling uses fact and dimension tables in star or snowflake schemas. Slowly changing dimensions preserve history, especially type 2. Data lakes store raw files in object storage; lakehouses add ACID tables with Delta Lake, Iceberg, and Hudi. OLAP operations slice, dice, roll up, and drill down. BI tools and semantic layers deliver consistent metrics.
Flashcard
Many small, fast transactions
Check your understanding
A customer moved from Lima to Quito in 2025. Which slowly changing dimension type keeps 2024 sales attributed to Lima?