DE Data Warehouses

Every organization collects data from many different systems — sales software, marketing tools, customer support platforms, inventory systems. These systems each have their own database. Getting answers that span all of them is nearly impossible without a central place that brings everything together. That central place is the data warehouse.

What Is a Data Warehouse

A data warehouse is a large, centralized storage system designed specifically for analysis and reporting. It collects data from multiple source systems, organizes it into a consistent structure, and makes it easy to query across all of it at once. Unlike operational databases that power day-to-day applications, a data warehouse is built for reading large volumes of data quickly.

The Central Library Analogy

Imagine a city with dozens of bookshops — each selling books in its own format, with its own catalog, organized in its own way. Finding all books on a particular subject means visiting every shop individually. Now imagine a central library that collects a copy of every book from every shop, organizes them all by subject and author, and lets you search the entire collection instantly. That library is the data warehouse. The bookshops are the source systems.

How a Data Warehouse Differs from an Operational Database

Feature            | Operational Database          | Data Warehouse
-------------------|-------------------------------|-------------------------------
Purpose            | Run the application           | Analyze historical data
Optimized for      | Write speed (insert/update)   | Read speed (complex queries)
Data coverage      | Current, live data            | Historical data; months/years
Schema style       | Normalized (many tables)      | Denormalized (fewer, wider)
Users              | Application code              | Analysts, BI tools
Query type         | Lookup one record fast        | Aggregate millions of records
Example            | MySQL for an e-commerce app   | Snowflake for business reports

The ETL Process That Feeds a Warehouse

Data does not appear in a warehouse by magic. A process called ETL — Extract, Transform, Load — moves data from source systems into the warehouse regularly.

Extract

Data pipelines pull data from source systems. Orders come from the e-commerce database. Marketing events come from the email platform's API. Website sessions come from the analytics tool. Each source uses a different format and structure.

Transform

The extracted data gets cleaned and reshaped into a consistent format. Date formats standardize, currency values convert to one unit, duplicate records merge, and missing fields fill in with defaults or nulls. Column names align across sources so that "customer_id" means the same thing everywhere.

Load

The cleaned, transformed data loads into the warehouse tables. Analysts can now write SQL queries that join order data with marketing data and website session data in a single query — something impossible when those datasets lived in separate, disconnected systems.

[CRM System]    --extract-->  \
[Order System]  --extract-->   [Transform & Clean]  --load-->  [Data Warehouse]
[Ad Platform]   --extract-->  /

Columnar Storage: Why Warehouses Run Fast

Operational databases store data row by row. To read a column value, the system reads the entire row. Data warehouses store data column by column. A query that sums the "revenue" column across 100 million rows reads only that one column from disk, skipping all the others. This makes analytical queries dramatically faster.

Row Storage (Operational):
Row 1: [ORD001, C001, 2024-05-01, 2500, "completed"]
Row 2: [ORD002, C003, 2024-05-03, 800,  "pending"  ]

Column Storage (Warehouse):
order_id:  [ORD001, ORD002, ORD003 ...]
amount:    [2500,   800,    1200   ...]   <-- query reads only this
status:    [completed, pending, completed ...]

Schemas Inside a Warehouse

Data warehouses use purpose-built schemas designed for fast analytical queries. The two most common are the star schema and the snowflake schema — covered in depth in later topics. At a high level, both organize data around a central "fact" table surrounded by "dimension" tables that provide context.

Popular Data Warehouses

Warehouse     | Provider    | Key Strength
--------------|-------------|------------------------------------------
Snowflake     | Independent | Separates compute from storage; very scalable
BigQuery      | Google      | Serverless; pay per query
Redshift      | Amazon      | Tight integration with AWS services
Synapse       | Microsoft   | Integrates with Azure and Power BI
Databricks    | Databricks  | Combines warehouse and lake (Lakehouse)

Who Uses the Data Warehouse

Business intelligence tools like Tableau, Power BI, and Looker connect directly to the data warehouse. Analysts write SQL queries against warehouse tables. Executives view dashboards that pull from warehouse data. The warehouse becomes the single source of truth — one authoritative place where everyone in the organization finds consistent, trustworthy numbers.

Limitations of Traditional Warehouses

Traditional data warehouses work best with structured, tabular data. They handle semi-structured formats like JSON with difficulty and cannot store raw unstructured data like images or video files. They also tend to be expensive at very large scales. These limitations gave rise to data lakes and later to data lakehouses — hybrid systems that appear in the next two topics.

Summary

A data warehouse centralizes data from many source systems into one structured, analysis-ready storage layer. It uses columnar storage and purpose-built schemas to answer complex analytical queries quickly. Every data-driven organization with multiple data sources benefits from a data warehouse as a single source of truth for reporting and decision-making.