Data Warehouse vs Database

Quick verdict

A database, in this comparison, means an operational or transactional database (often called OLTP, online transaction processing): the database behind an application, such as PostgreSQL, MySQL or SQL Server holding customers, orders and stock. Microsoft's Azure Architecture Center describes OLTP databases as optimised for individual record entries, holding valuable information but not designed for analysis.

A data warehouse is, in AWS's definition, a central repository of information that can be analysed to make more informed decisions. It is an analytical (OLAP, online analytical processing) store: Microsoft describes OLAP stores as optimised for heavy-read, low-write work, modelled and cleansed for analysis, and often keeping history for time-series analysis. A warehouse can be built on a general purpose database server (for example SQL Server with columnstore indexes) or on a dedicated platform such as Snowflake, Amazon Redshift, Google BigQuery or Microsoft Fabric.

Both are usually queried with SQL and both can be relational, so the difference is not the language. It is the workload, the shape of the data, how data arrives, and how the system stores it. This page explains those differences and when a separate warehouse is justified.

Side by side

Key differences

OLTP vs OLAP: two different workloads

An OLTP database handles a high rate of short transactions: insert an order, update a balance, read one customer. It needs low latency, concurrency control, and enforced constraints so that every write leaves the data correct. An OLAP workload asks questions such as "revenue by month, region and product category for the last five years", which read a large share of the data and return a small summary.

Running both on the same server is possible, and common at small scale, but large scans use CPU, memory and I/O that the application also needs. Microsoft's guidance lists the reasons to use an OLAP store: running complex analytical queries without affecting OLTP systems, giving business users a simple way to report, and providing consistent aggregations. It also names the cost: OLAP stores refresh more slowly than OLTP data, and you must plan data cleansing and orchestration to keep them up to date.

Row storage vs columnar storage

Most operational databases store each row together. That suits fetching or updating one whole record. Analytical stores usually store each column together: a query that sums one column across a billion rows reads only that column, and values from the same column compress well because they are similar. Microsoft's columnstore documentation gives exactly these reasons, plus batch mode execution that processes many rows at a time, and recommends rowstore indexes for transactional seeks and columnstore for scans of large fact tables.

The line is not strict. Snowflake stores tables in compressed columnar micro-partitions; SQL Server can store a table as a clustered columnstore index or add a nonclustered columnstore index to an OLTP table for real-time operational analytics; PostgreSQL is row-oriented but gains columnar engines through extensions. In-process engines such as DuckDB and servers such as ClickHouse are columnar from the start. See DuckDB vs PostgreSQL and ClickHouse vs PostgreSQL.

Normalised schemas vs star and snowflake schemas

Operational databases are normalised so that each fact is stored once and updates cannot create contradictions; see database normalization. Warehouses usually use a star schema. Microsoft's Power BI guidance describes it as a mature approach widely adopted by relational data warehouses, in which fact tables store events or observations (sales, stock balances) with numeric measures and keys to dimensions, and dimension tables describe the business entities used for filtering and grouping (date, product, customer). Fact tables grow large; dimension tables stay comparatively small. A snowflake schema normalises a dimension into several related tables, for example product, subcategory and category.

Dimension tables usually have a surrogate key, a warehouse-generated identifier, so that a Type 2 slowly changing dimension can keep several versions of the same customer with valid-from and valid-to dates, and old sales stay linked to the region the customer was in at the time. A minimal star schema, written for PostgreSQL:

-- PostgreSQL syntax: dimensions
CREATE TABLE dim_date (
  date_key   int PRIMARY KEY,          -- e.g. 20261007
  full_date  date NOT NULL,
  year       smallint NOT NULL,
  month      smallint NOT NULL
);

CREATE TABLE dim_product (
  product_key  int PRIMARY KEY,         -- surrogate key
  product_code varchar(20)  NOT NULL,   -- business key from the source system
  product_name varchar(100) NOT NULL,
  category     varchar(50)  NOT NULL
);

CREATE TABLE dim_customer (
  customer_key int PRIMARY KEY,         -- one row per version (Type 2)
  customer_id  varchar(20) NOT NULL,
  country      varchar(50) NOT NULL,
  valid_from   date NOT NULL,
  valid_to     date,                    -- NULL for the current version
  is_current   boolean NOT NULL
);

-- PostgreSQL syntax: fact table, one row per order line
CREATE TABLE fact_sales (
  date_key     int NOT NULL REFERENCES dim_date (date_key),
  product_key  int NOT NULL REFERENCES dim_product (product_key),
  customer_key int NOT NULL REFERENCES dim_customer (customer_key),
  order_number varchar(20) NOT NULL,    -- degenerate dimension
  quantity     int NOT NULL,
  sales_amount numeric(12,2) NOT NULL
);

A typical analytical query joins the fact table to the dimensions it needs, filters on dimension attributes and aggregates the measures:

-- Revenue and units by month and category for 2026
SELECT d.year, d.month, p.category,
       SUM(f.sales_amount) AS revenue,
       SUM(f.quantity)     AS units
FROM   fact_sales f
JOIN   dim_date    d ON d.date_key    = f.date_key
JOIN   dim_product p ON p.product_key = f.product_key
WHERE  d.year = 2026
GROUP  BY d.year, d.month, p.category
ORDER  BY d.month, revenue DESC;

The same design works on any SQL warehouse with small type changes. On SQL Server you would typically store fact_sales as a clustered columnstore index; on Snowflake the REFERENCES and PRIMARY KEY clauses are accepted but, apart from NOT NULL, not enforced on standard tables, so the load process must keep keys valid. For interview-style questions on this topic, see database design interview questions.

Getting data in: ETL, ELT and change data capture

A warehouse is filled by pipelines, not by the application. Microsoft's architecture guide defines ETL (extract, transform, load) as consolidating data from several sources, transforming it with a separate engine according to business rules, often through staging tables, and then loading it. ELT (extract, load, transform) differs only in where the transformation happens: raw data is loaded first and transformed inside the target store using its own compute. Microsoft suggests ETL when the target is constrained or rules need a specialised engine, and ELT when the target is a modern warehouse or lakehouse with elastic compute and you want to keep raw data.

Many pipelines now use change data capture to copy each insert, update and delete from the operational database soon after it happens, instead of nightly full extracts. Examples include SQL Server's Change Data Capture feature, PostgreSQL logical replication, and vendor connectors that read them. Moving curated warehouse data back into operational tools is called reverse ETL.

Data lakes and lakehouses

AWS defines a data lake as a centralised repository for structured and unstructured data at any scale, stored as it is without first structuring it. The distinction AWS draws is schema on write for a warehouse (structure defined and data cleaned before loading) against schema on read for a lake (structure applied when the data is analysed). Lakes are typically files in object storage, often in columnar formats such as Parquet.

A lakehouse adds warehouse behaviour on top of lake storage, using an open table format that gives files table semantics. Apache Iceberg describes itself as a format for huge analytic tables that lets engines such as Spark, Trino, Flink, Presto, Hive and Impala work with the same tables at the same time; Delta Lake plays the same role in Databricks and Microsoft Fabric, where Microsoft describes a lakehouse as combining the scale of a data lake with the querying of a warehouse. Warehouses and lakehouses are converging: Snowflake, BigQuery, Redshift, Fabric and Databricks can all read open table formats to some degree. None of this replaces the operational database; it changes where analytical copies live. See Snowflake vs Databricks.

When a separate warehouse is justified

In our view a separate warehouse earns its cost when one or more of these is true: reports need to join data from several systems; analysts need history that the application does not keep (for example, what a customer's region was last year); heavy reporting measurably slows the application even after moving it to a replica; many people and BI tools query at once; or the business needs one agreed set of definitions (revenue, active customer) instead of each report computing its own.

Before that, cheaper steps inside the database often go a long way: a read replica for reporting; partitioning and summary tables or materialized views; columnar indexes or extensions (SQL Server columnstore, PostgreSQL extensions such as Citus or pg_duckdb); or an in-process engine such as DuckDB for one-off analysis of exports. When you do add a warehouse, the product choice is a separate decision; see Snowflake vs PostgreSQL, Snowflake vs SQL Server, Redshift vs PostgreSQL and BigQuery vs PostgreSQL.

Pricing and licensing

This is a conceptual comparison, so there is no single price. What changes is the cost model. An operational database costs a server or managed instance (and, for commercial engines such as SQL Server, a licence), sized for peak transaction load and paid whether busy or idle. Open source engines such as PostgreSQL and MySQL Community have no licence fee.

A data warehouse adds a second system and a pipeline. Cloud warehouses mostly bill by usage: Snowflake, for example, bills compute in credits per second of virtual warehouse time plus storage per TB per month, with the credit price set by edition, cloud and region. Other services bill by data scanned, node hours or capacity units; see the individual comparisons for each vendor's billing unit and a dated example. A warehouse on your own SQL Server or PostgreSQL servers costs hardware (and any licence) instead. In all cases, budget for building and running the ETL or ELT pipelines, which is often a larger cost than the platform itself.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• One analyst explores exports or files: an in-process engine is simpler than either; see DuckDB vs PostgreSQL and DuckDB vs pandas.
• You need real-time analytics on high-volume events, such as logs or clickstreams: a columnar database built for fast ingestion may fit; see ClickHouse vs PostgreSQL.
• Your data is mostly documents or key-value records: the operational choice is a different one; see SQL vs NoSQL.

Final recommendation

Frequently asked questions

Sources

• Microsoft Learn: Online analytical processing (Azure Architecture Center)
• Microsoft Learn: Extract, transform, load (ETL) and ELT (Azure Architecture Center)
• Microsoft Learn: Understand star schema (Power BI guidance)
• Microsoft Learn: Columnstore indexes overview
• Microsoft Learn: What is a lakehouse in Microsoft Fabric?
• AWS: What is a data warehouse?
• AWS: What is a data lake?
• Apache Iceberg
• Snowflake documentation: Key concepts and architecture
• Snowflake documentation: Constraints overview
• PostgreSQL documentation: Logical replication
• PostgreSQL documentation: Materialized views

Checked October 2026.

How we research comparisons: our editorial method.