Data Warehousing and Analytics

Two kinds of workload: OLTP and OLAP

The shop database on the other pages of this topic does one job well: it records what happens. A customer signs up, an order is placed, the stock goes down. This is online transaction processing (OLTP): many small reads and writes, each touching a few rows, each needing to be fast and correct right now.

The questions the business asks later are different: "How did revenue per category develop month by month?" "Which cities buy the most peripherals?" These are online analytical processing (OLAP) queries: few in number, read-only, and each one scanning and aggregating a large part of the data, often years of history.

The OLTP side is designed with normalization so every fact is stored once and updates stay consistent. The OLAP side deliberately gives some of that up, because its data is written by a controlled load process rather than by thousands of users, and fewer joins make analytical queries simpler and faster.

Why analytics doesn't run on the production database

You can run a report on the live shop database, and for a small shop that is fine. As things grow, it stops working well:

• Performance. A query that scans two years of orders competes for disk, memory and processor time with checkout. Customers notice before the analyst does.
• Locking and consistency. Long-running reads can hold locks or keep old row versions alive, which slows writes and maintenance (see Transactions and ACID).
• Many sources. Real questions combine the shop with the warehouse system, the marketing tool and the finance package. No single production database holds all of that.
• History. Production keeps the current state: a customer who moved has only the new city. Analysis often needs the history as well.
• Shape. A schema built for safe updates is awkward for analysis; one built for analysis would be unsafe for updates.

A read replica (see Backup, Recovery and Replication) solves the first point for modest reporting. The other points are why organizations build a separate analytical store.

Data warehouses, data lakes and lakehouses

This page is the overview. The Data & Analytics topic goes deeper: Warehouses, Lakes and Lakehouses (open table formats, medallion layers, dbt), Data Pipelines, Data Modeling for Analytics (grain, all SCD types), BI Tools and Data Governance and Privacy.

Data warehouse

A data warehouse is a central database built for analysis. It holds cleaned, integrated data from many source systems, keeps history, and is organized in a schema designed for querying, usually star schemas. You query it with Structured Query Language (SQL). Because data is checked and shaped on the way in, a warehouse is the place for official figures: the revenue number the finance team reports.

A data mart is a smaller, subject-specific part of a warehouse (sales, or human resources) for one department.

Data lake

A data lake is a large, inexpensive store, usually cloud object storage, that keeps data in its raw form as files: comma-separated values (CSV), JavaScript Object Notation (JSON), logs, images, and analytics file formats such as Parquet. Structure is applied when the data is read ("schema on read") instead of when it is written ("schema on write", as in a warehouse). A lake can take in anything quickly and cheaply, which suits data science and machine learning; without catalogs and quality rules it easily turns into a "data swamp" nobody trusts.

Lakehouse

A lakehouse is a newer architecture that tries to combine the two: data stays as open files in the lake, and a table layer on top (open table formats such as Delta Lake or Apache Iceberg) adds warehouse features like transactions, schema enforcement and fast SQL. The term was popularized by Databricks, and vendors use it in slightly different ways, so check what a product means by it.

Getting data in: ETL and ELT

Data reaches the warehouse through a pipeline. Extract, transform, load (ETL) is the classic pattern: extract data from the source systems, transform it in a separate processing engine (clean up formats, remove duplicates, look up keys, calculate revenue, filter out orders with status 'cancelled'), then load the result into the warehouse.

Extract, load, transform (ELT) swaps the last two steps: load the raw data into the warehouse or lake first, then transform it there with SQL. ELT became common with cloud warehouses, whose compute can be scaled up for the transformation, and it keeps the raw data, so a transformation can be fixed and rerun later.

Pipelines usually run in batches: every night, or every hour, the changes since the last run are processed. Stream processing handles events continuously as they arrive (a click, a sensor reading, a payment), for dashboards that must be current within seconds. Tools for building pipelines include Azure Data Factory, Amazon Web Services (AWS) Glue, Google Cloud Dataflow and open-source tools such as Apache Airflow and dbt.

Star schema: facts and dimensions

The standard way to organize a warehouse is the star schema, from Ralph Kimball's dimensional modeling: one table of measurements in the middle, surrounded by tables that describe them.

• A fact table records business events at a chosen level of detail (the grain), here one row per order line. It holds numeric measures you add up (quantity, revenue) and foreign keys to the dimensions. Fact tables are long and narrow, and they make up most of the warehouse's size.
• A dimension table holds the descriptive context you filter and group by: the date (day, month, year), the customer (name, city, country), the product (name, category). Dimension tables are short and wide, and they are deliberately denormalized: the category name sits right in the product row.

Building a small star from the shop

The example builds a tiny star schema from the shop tables with CREATE TABLE … AS SELECT, which creates a table from the result of a query. In a real warehouse this is the transform step of the pipeline, run on a schedule. Orders with status 'cancelled' are left out, as a business rule decided once in the pipeline instead of in every report. The date dimension here has only the days that have sales; a real one has a row for every calendar day, with columns such as weekday, quarter and holiday.

-- Dimension: one row per calendar day that has sales
CREATE TABLE dim_date AS
SELECT DISTINCT
  order_date                                AS date_key,
  CAST(substr(order_date, 1, 4) AS INTEGER) AS year,
  substr(order_date, 1, 7)                  AS year_month
FROM orders;

-- Dimension: who bought
CREATE TABLE dim_customer AS
SELECT customer_id AS customer_key, first_name, city, country
FROM customers;

-- Dimension: what was bought
CREATE TABLE dim_product AS
SELECT product_id AS product_key, name AS product_name, category
FROM products;

-- Fact: one row per order line, with keys and measures
CREATE TABLE fact_sales AS
SELECT
  o.order_date                AS date_key,
  o.customer_id               AS customer_key,
  oi.product_id               AS product_key,
  oi.quantity                 AS quantity,
  oi.quantity * oi.unit_price AS revenue
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS fact_rows, SUM(revenue) AS total_revenue FROM fact_sales;

SELECT d.year_month, p.category, SUM(f.revenue) AS revenue
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key    = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY d.year_month, p.category
ORDER BY d.year_month, revenue DESC;

fact_rows  total_revenue
---------  -------------
15         1263

year_month  category     revenue
----------  -----------  -------
2026-06     Displays     249
2026-06     Peripherals  114
2026-06     Cables       30
2026-07     Audio        129
2026-07     Cables       55
2026-07     Peripherals  50
2026-07     Accessories  45
2026-08     Displays     498
2026-08     Accessories  35
2026-09     Cables       33
2026-09     Peripherals  25

The four CREATE TABLE … AS SELECT statements print nothing. The fact table has 15 rows: the 16 order lines minus the one line of order 105, which has status 'cancelled'. The analytical query has the typical star shape: join the fact table to the dimensions you need, filter and group by dimension columns, and aggregate the measures with SUM. Every question ("revenue by country", "quantity by weekday") follows the same pattern with different dimension columns.

Snowflake schema

In a snowflake schema the dimensions are normalized further: dim_product would hold a category_key pointing to a separate dim_category table. That saves a little space and avoids repeating category attributes, at the cost of more joins. With today's cheap storage and columnar compression, most designs prefer the simpler star.

Slowly changing dimensions

Dimension data changes now and then: Sanne moves from Utrecht to Amsterdam. Should her past orders count for Utrecht or Amsterdam? A slowly changing dimension (SCD) strategy decides this. The two most common:

• Type 1: overwrite the old value. Simple, but history now says all her orders came from Amsterdam.
• Type 2: add a new dimension row with a new surrogate key and validity dates (valid_from, valid_to), and mark the old row as no longer current. Old facts keep pointing to the Utrecht row, new facts to the Amsterdam row, so history stays correct.

Columnar storage

An OLTP database stores a table row by row: all columns of order line 1, then all columns of order line 2. That is ideal for "fetch or update this one order". An analytical query is the opposite: it reads a few columns of millions of rows. With columnar storage the database stores each column separately, all values of revenue together, all values of product_key together.

Row store:     [2026-06-02, 1, 1, 2, 18] [2026-06-02, 1, 3, 1, 25] [2026-06-05, 2, 5, 1, 249]

Column store:  date_key:     2026-06-02, 2026-06-02, 2026-06-05
               customer_key: 1, 1, 2
               product_key:  1, 3, 5
               quantity:     2, 1, 1
               revenue:      18, 25, 249

Why this makes analytics fast:

• Less to read. SUM(revenue) grouped by product reads two columns out of fifty, not whole rows.
• Better compression. Values in one column are similar (the same date repeated thousands of times), so they compress very well, often several times smaller than row storage. Less data on disk also means less to read.
• Faster processing. The engine can process a column in large batches of values at once (vectorized execution).

The price: inserting or updating a single row touches many column files, so column stores are poor at OLTP work and are loaded in bulk. Analytical file formats such as Apache Parquet and ORC (Optimized Row Columnar) are columnar too, and SQL Server offers columnstore indexes inside an otherwise row-based database.

BI tools and dashboards

Most people never write a query against the warehouse. They use business intelligence (BI) tools: applications that connect to the warehouse, let users build charts and tables by dragging fields, and publish dashboards, pages of visualizations that refresh automatically. Well-known examples are Microsoft Power BI, Tableau (part of Salesforce) and Looker (part of Google Cloud).

Behind the drag-and-drop the tool generates SQL like the star query above. A star schema maps naturally to the way these tools think: dimensions become the fields you slice by, measures become the numbers you add up. Many BI tools add a semantic model on top that defines measures such as "net revenue" once, so every report calculates them the same way.

Cloud analytics services

The major clouds offer analytical databases as managed services that separate storage from compute, so you can scale (and pay for) query power independently of how much data you keep. General aspects of managed databases are covered in Managed Databases.

Names and packaging in this area change often. Microsoft in particular has been moving its analytics offering toward Fabric, which builds on and partly succeeds Synapse. Check the current product names before relying on them.

Data quality and governance

A warehouse is only useful if people trust its numbers. Two dashboards that show different revenue for the same month undo years of work. That trust comes from:

• Data quality checks in the pipeline: no missing keys, no duplicate rows, totals that match the source system, values in valid ranges. A failed check stops the load instead of publishing wrong figures.
• Clear definitions: what counts as revenue (with or without tax, canceled orders, returns?), written down once.
• Ownership and lineage: who owns each dataset, and where each figure comes from. A data catalog (Microsoft Purview, AWS Glue Data Catalog, Google Knowledge Catalog, formerly Dataplex) records this.
• Access control and privacy: a warehouse combines data from many systems, which makes it valuable to attackers too. Give analysts the least access they need, mask or pseudonymize personal data where the analysis does not need it, and follow the General Data Protection Regulation (GDPR) rules on purpose and retention.