Data Warehouse — Definition, Key Features & OLTP vs OLAP

Definition

A Data Warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data used to support management's decision-making process (Bill Inmon).

Key Features

1. Subject-oriented — organized around major business subjects (Sales, Customer, Product), not individual applications.
2. Integrated — data pulled from multiple heterogeneous sources is made consistent (naming conventions, units, encoding).
3. Time-variant — stores historical data with a time dimension; every record is time-stamped, enabling trend analysis.
4. Non-volatile — once loaded, data is not updated/deleted by users; it's read-only for analysis, refreshed only via scheduled batch loads.
5. Summarized/denormalized — data is often pre-aggregated and stored in star/snowflake schemas for fast querying.

OLTP vs OLAP

Feature	OLTP	OLAP
Purpose	Run day-to-day operations	Support analysis & decisions
Data	Current, detailed	Historical, summarized
Schema	Normalized (E-R)	Denormalized (star/snowflake)
Query type	Simple, short transactions	Complex, multidimensional queries
Operations	Insert/Update/Delete	Roll-up, drill-down, slice, dice
Response time	Milliseconds	Seconds to minutes (large scans)
Users	Large number of clerks/staff	Fewer analysts/executives
Design goal	Concurrency, integrity	Query throughput, aggregation speed

     OLTP                              OLAP
┌───────────┐  periodic ETL       ┌───────────────┐
│ Live       │ ───────────────▶  │  Data          │
│ Transaction│                     │  Warehouse     │──▶ Analysis/Reports
│ System     │                     │  (subject-     │
└───────────┘                     │  oriented)     │
                                    └───────────────┘

In short: OLTP powers the business (fast writes, current state), while OLAP powers understanding the business (fast reads, historical trends) — the data warehouse exists specifically to bridge them without letting analytical load slow down live transactions.