🧱 The 7 Core Components of a Data Warehouse

🧱 Data Warehouse Components

Analogy

Think of a Data Warehouse like a modern airport.

Planes bring passengers from different cities (Data Sources)
Arrivals hall processes and checks them (Staging + ETL)
The terminal is the central hub (Data Warehouse)
Different gates serve different destinations (Data Marts)
Flight information screens show real-time info (Reporting Layer)
The airport manual guides operations (Metadata Layer)

1️⃣ Data Sources

The starting point — where all raw data comes from before entering the warehouse.

Source Type	Examples
Relational databases	MySQL, Oracle, SQL Server (OLTP systems)
Flat files	CSV, Excel, JSON, XML
APIs	REST APIs from third-party services
Streaming data	Kafka, IoT sensors, clickstream
Legacy systems	Mainframes, old ERP systems
SaaS applications	Salesforce, SAP, HubSpot

Tip

Data sources are not cleaned or structured — they're raw. That's what the next layers handle.

2️⃣ Staging Area

Analogy

Like the arrivals lounge in an airport — passengers land here first before being processed. No one goes directly to the city.

The Staging Area is a temporary storage zone where raw data from sources is dumped before transformation. It acts as a buffer between sources and the warehouse.

What happens here:

Raw data is extracted and stored as-is
Basic validation checks run (file format, row counts)
Data is held temporarily — cleared after ETL completes
No transformation yet — just a landing pad

Source DB → [Extract] → Staging Area (raw tables) → [Transform] → DW

Warning

Staging area data is not used for reporting. It's intermediate. Never expose it to business users.

3️⃣ ETL Process

Extract → Transform → Load — the engine that moves data from staging into the warehouse.

Step	What Happens
Extract	Pull data from source systems into staging
Transform	Clean, standardize, deduplicate, apply business rules
Load	Push the transformed data into the DW

Example:
Extract  → Pull sales records from 5 regional databases
Transform → Standardize currency to INR, remove duplicates, fill nulls
Load     → Insert into the central Sales fact table in DW

Tip

ETL is covered in depth in upcoming topic. Think of it here as the pipeline that feeds the warehouse.

4️⃣ Data Warehouse (Core)

The central repository — the heart of the system. This is where clean, integrated, historical data lives.

Characteristics (recap):

Subject-oriented, Integrated, Time-variant, Non-volatile
Optimized for read-heavy analytical queries
Uses Star or Snowflake schema (covered in Schemas & Relationships)
Contains Fact tables (measures) and Dimension tables (context)

DW Structure:
├── Sales Fact Table         (revenue, quantity, discount)
├── Customer Dimension       (name, city, segment)
├── Product Dimension        (name, category, price)
└── Date Dimension           (day, month, quarter, year)

Info

This is what analysts actually query — everything upstream exists to feed this layer.

5️⃣ Data Marts

Analogy

If the DW is a supermarket, a Data Mart is a specialty store — only Electronics, or only Groceries. Smaller, faster, department-specific.

A Data Mart is a subset of the Data Warehouse scoped to a specific business department or function.

Data Mart	Who Uses It	What It Contains
Sales Mart	Sales team	Revenue, deals, targets
Finance Mart	Finance team	P&L, budgets, costs
HR Mart	HR team	Headcount, attrition, payroll
Marketing Mart	Marketing team	Campaigns, leads, conversions

Types:

Dependent — built from the central DW (recommended)
Independent — built directly from source systems (creates silos — avoid)

Warning

Independent data marts recreate the same problem DW was built to solve — inconsistent data per department.

6️⃣ OLAP / Reporting Layer

The presentation layer — where business users interact with the data.

What lives here:

OLAP cubes — pre-aggregated, multi-dimensional data structures for fast slicing and dicing
BI Tools — Power BI, Tableau, Qlik, Looker
Reports & Dashboards — scheduled reports, self-service analytics

Analyst query flow:
BI Tool → OLAP Layer → DW / Data Mart → Result in seconds

Tip

OLAP cubes pre-compute aggregations (SUM, AVG, COUNT) so dashboards load instantly instead of recalculating each time.

7️⃣ Metadata Layer

Analogy

Like the airport operations manual — the actual passengers don't read it, but it governs how everything runs. Without it, nothing works correctly.

Metadata is data about data. The metadata layer describes everything inside the warehouse — structure, lineage, definitions, and rules.

Types of Metadata:

Type	Description	Example
Technical	Schema, data types, table structures	`sales_fact` has columns `amount DECIMAL(10,2)`
Business	Definitions business users understand	`"revenue"` = net sales after discounts
Operational	ETL logs, load timestamps, job status	Last loaded: 2024-06-20 02:00 AM

Tip

Metadata answers: "Where did this data come from? What does it mean? When was it last updated?" — critical for trust and governance.

🗺️ Full Architecture Flow

┌─────────────────────────────────────────────────────┐
│                   DATA SOURCES                      │
│  (OLTP DBs, Files, APIs, SaaS, Streaming)           │
└────────────────────┬────────────────────────────────┘
                     │ Extract
┌────────────────────▼────────────────────────────────┐
│                 STAGING AREA                        │
│         (Temporary raw data landing zone)           │
└────────────────────┬────────────────────────────────┘
                     │ Transform + Load (ETL)
┌────────────────────▼────────────────────────────────┐
│               DATA WAREHOUSE (Core)                 │
│      (Fact tables + Dimension tables, historical)   │
└──────────┬──────────────────────────┬───────────────┘
           │ Subset                   │ Metadata
┌──────────▼──────────┐  ┌───────────▼───────────────┐
│     DATA MARTS      │  │      METADATA LAYER        │
│ (Dept-specific DW)  │  │  (Definitions, Lineage)    │
└──────────┬──────────┘  └───────────────────────────┘
           │ Query
┌──────────▼──────────────────────────────────────────┐
│          OLAP / REPORTING LAYER                     │
│      (Power BI, Tableau, OLAP Cubes, Reports)       │
└─────────────────────────────────────────────────────┘

🧪 Practice Drill

text

// Try answering these:
// Q1. Name all 7 components of a Data Warehouse architecture in order from source to reporting.
// Q2. What is the purpose of the Staging Area? Why is it separate from the DW?
// Q3. A company has a DW. The Sales team wants a faster, smaller database with only sales data. What should they use and what type should it be?
// Q4. What's the difference between a Dependent and an Independent Data Mart? Which is preferred and why?
// Q5. Match each metadata type to its example:
// | Example | Metadata Type |
// |---|---|
// | `employee_id` is an `INT NOT NULL` column | ? |
// | ETL job ran at 3:00 AM and loaded 50,000 rows | ? |
// | "Revenue" means total sales minus returns | ? |

💡 Click for Solutions

A1. Data Sources → Staging Area → ETL Process → Data Warehouse → Data Marts → OLAP/Reporting Layer → Metadata Layer

A2.

Staging is a temporary buffer where raw data lands before being transformed
It's separate from the DW to isolate dirty/raw data from clean analytical data
It also protects source systems — data is extracted once into staging, not queried repeatedly

A3.

Use a Data Mart (Sales Mart)
It should be Dependent — sourced from the central DW to ensure consistent, trusted data

A4.

Dependent — built from the central DW; inherits cleaned, standardized data ✅
Independent — built directly from source systems; creates data silos and inconsistency ❌
Dependent is preferred — it maintains a single source of truth

A5.

Example	Metadata Type
`employee_id` is an `INT NOT NULL` column	Technical
ETL job ran at 3:00 AM and loaded 50,000 rows	Operational
"Revenue" means total sales minus returns	Business

← Previous Topic | Next Topic → Next Topic