📦 Data Warehousing Basics & Characteristics
📦 What is a Data Warehouse?
Imagine a company has 10 departments — Sales, HR, Finance, Logistics, etc. Each has its own spreadsheet or database. When the CEO asks "How did we perform last year?", nobody can answer clearly — data is scattered everywhere.
A Data Warehouse is the single place where all that data is brought together, cleaned, and stored — so anyone can query it and get one consistent answer.
A Data Warehouse (DW) is a centralized repository that stores large volumes of structured, historical data collected from multiple sources — designed specifically for analysis and reporting, not day-to-day operations.
Data Warehouse = Store everything → Clean it → Make it queryable → Analyse it
🔑 4 Characteristics of a Data Warehouse
These 4 characteristics were defined by Bill Inmon (father of Data Warehousing).
1️⃣ Subject-Oriented
A DW is organized around key business subjects — not around applications or processes.
| OLTP (Application-oriented) | DW (Subject-oriented) |
|---|---|
| Order management system | Sales subject area |
| HR payroll system | Employee subject area |
| Hospital billing app | Patient subject area |
Instead of storing "what the app does", the DW stores "what the business cares about" — Sales, Customers, Products, Finance.
2️⃣ Integrated
Data comes from many different sources — and they often use different formats, naming conventions, and units. The DW integrates all of it into a single consistent format.
Source A (CRM): gender = "M" / "F"
Source B (HR): gender = "Male" / "Female"
Source C (Legacy): gender = 1 / 0
After Integration in DW → gender = "Male" / "Female" (one standard)
Integration happens during ETL & ELT|ETL — this is where conflicts are resolved and data is standardized.
3️⃣ Time-Variant
Data in a DW is stamped with time and kept historically. Unlike OLTP (which only shows current state), DW shows how data changed over time.
OLTP → Employee salary = ₹80,000 (current value only)
DW → Jan 2022: ₹60,000
Jan 2023: ₹70,000
Jan 2024: ₹80,000 (full history preserved)
DW data typically spans 5–10 years of history. This is what enables trend analysis.
4️⃣ Non-Volatile
Once data is loaded into the DW, it is not updated or deleted — it's read-only.
OLTP → UPDATE Employees SET salary = 80000 WHERE id = 101; ✅ Happens constantly
DW → No updates. New records are added. Old records stay as-is. ✅
DW does NOT support real-time edits. Data is loaded in batches (daily, weekly) via ETL. The purpose is analysis — not transaction management.
❓ Why is a Data Warehouse Needed?
1. Centralization
Without a DW, data lives in silos — CRM, ERP, spreadsheets, legacy systems. A DW pulls everything into one place.
Before DW: Finance queries their DB. Sales queries theirs. Different answers. Confusion.
After DW: Everyone queries the same DW. One answer. One truth.
2. Analytics Optimization
OLTP databases are optimized for transactions — running analytics on them is slow and risky. A DW is purpose-built for complex, heavy queries.
SELECT region, SUM(revenue)
FROM Sales
WHERE year BETWEEN 2020 AND 2024
GROUP BY region
ORDER BY SUM(revenue) DESC;
-- This query on OLTP: slow, affects live users
-- Same query on DW: fast, isolated from operations
3. Data Consistency
Different departments often have conflicting numbers. DW enforces one version of the truth — same definitions, same calculations, same data.
4. Scalability
DW systems (like Data Architectures|Snowflake, Redshift, BigQuery) are built to scale to terabytes and petabytes of data without performance degradation.
🗺️ Full Picture
Multiple Sources (OLTP, Files, APIs, Logs)
↓
ETL Process
↓
Data Warehouse
(Subject-oriented, Integrated, Time-variant, Non-volatile)
↓
BI Tools / Reports / Dashboards
↓
Decision Making
🧪 Practice Drill
// Try answering these:
// Q1. What are the 4 characteristics of a Data Warehouse? Give a one-line definition for each.
// Q2. Your company's CRM stores `country = "IN"` but the ERP stores `country = "India"`. Which DW characteristic handles this problem? How?
// Q3. Why is data in a DW non-volatile? What would go wrong if updates were allowed?
// Q4. An analyst wants to compare monthly revenue for the past 5 years. Why is a Data Warehouse better suited for this than an OLTP system?
// Q5. Match the characteristic to the scenario:
// | Scenario | Characteristic |
// |---|---|
// | All sales data from 2019–2024 is stored | ? |
// | HR and Finance data use the same employee ID format | ? |
// | The DW is organized around Customers, Products, Sales | ? |
// | Once loaded, last year's data cannot be changed | ? |
💡 Click for Solutions
A1.
- Subject-oriented — organized around business subjects (Sales, HR, Finance), not applications
- Integrated — data from all sources is standardized into one consistent format
- Time-variant — historical data is preserved with timestamps to enable trend analysis
- Non-volatile — once loaded, data is not updated or deleted; it's read-only
A2. Integrated — during ETL, a transformation rule standardizes both to one format (e.g., "India")
A3. Non-volatile ensures historical accuracy. If past data could be updated, trend analysis would be corrupted — you'd lose the ability to see what happened at any point in time.
A4.
- OLTP is optimized for writes and current data only; heavy reads slow it down and impact live users
- DW is read-optimized, holds historical data spanning years, and is isolated from operations
A5.
| Scenario | Characteristic |
|---|---|
| All sales data from 2019–2024 is stored | Time-variant |
| HR and Finance data use the same employee ID format | Integrated |
| The DW is organized around Customers, Products, Sales | Subject-oriented |
| Once loaded, last year's data cannot be changed | Non-volatile |
← Previous Topic | Next Topic → Next Topic