Mastering Data Warehouse Architecture: Concepts, Modeling Techniques, and Real‑Time Strategies

1. Data Warehouse Basics

A data warehouse (DW/DWH) is an integrated, subject‑oriented, non‑volatile, and time‑variant data store designed to support decision‑making. It ingests data from operational systems via ETL (Extract, Transform, Load) and stores historical snapshots for analysis.

2. Core Characteristics

Subject‑oriented : Data is organized around business subjects rather than applications.

Integrated : Heterogeneous source data are cleaned, transformed, and consolidated.

Non‑volatile : Once loaded, data is rarely updated; only periodic loads occur.

Time‑variant : Stores historical data with timestamps, enabling trend analysis.

3. Why Use a Data Warehouse?

Directly querying operational systems leads to performance bottlenecks, inconsistent data, security issues, and difficulty handling schema changes. A warehouse isolates analytical workloads, provides a unified view, and improves query performance through pre‑aggregation and denormalization.

4. Data Warehouse vs. Database (OLTP vs. OLAP)

Operational databases (OLTP) handle transactional workloads, store current data, and prioritize consistency and concurrency. Data warehouses (OLAP) store historical data, are optimized for complex analytical queries, and often use denormalized schemas such as star or snowflake.

5. Terminology Overview

Entity : Real‑world object (e.g., product, user).

Dimension : Descriptive attribute used for analysis (time, region, product category).

Fact : Measurable event stored in fact tables (sales amount, transaction count).

Metric/Indicator : Calculated value derived from facts (e.g., average order value).

Granularity : Level of detail stored in a fact table.

Degenerate Dimension : Dimension key stored directly in the fact table when no separate dimension table exists.

6. Modeling Approaches

Three major methods are used:

Third Normal Form (3NF) Modeling : Emphasizes relational normalization, suitable for detailed, transaction‑level data.

Dimensional Modeling (Kimball) : Builds star or snowflake schemas with fact and dimension tables, optimized for query performance.

Entity Modeling : Abstracts business processes into entities, events, and descriptions.

6.1 Dimensional Modeling Details

Key concepts include:

Fact Table : Stores numeric measures; each row represents a single event at a consistent granularity.

Dimension Table : Stores descriptive attributes; each has a primary key used as a foreign key in fact tables.

Star Schema : Fact table at the center with directly linked dimension tables.

Snowflake Schema : Normalized dimensions that reference other dimension tables.

Galaxy (Fact Constellation) Schema : Multiple fact tables sharing common dimensions.

6.2 Fact Table Types

Transactional Fact

Periodic Snapshot Fact

Cumulative Snapshot Fact

Non‑Fact (no numeric measures)

Aggregate Fact

Hybrid Fact (merged from multiple processes)

7. Data Warehouse Layered Architecture

A typical offline warehouse is divided into the following layers:

ODS (Operational Data Store) : Raw copy of source data, minimal cleaning.

DWD (Data Warehouse Detail) : Detailed, cleaned data; may include degenerated dimensions.

DWM (Data Warehouse Middle) : Lightly aggregated tables for reuse.

DWS (Data Warehouse Service) : Wide tables or data marts covering ~80% of use cases.

APP (Application Layer) : Final tables served to BI tools, reporting, or downstream services.

DIM (Dimension Layer) : Optional layer dedicated to high‑cardinality and low‑cardinality dimension tables.

8. Metadata Management

Metadata stores definitions of source‑to‑target mappings, transformation rules, data lineage, and operational status of ETL jobs. It is divided into technical metadata (used by developers) and business metadata (used by analysts). Proper metadata ensures consistency, traceability, and easier maintenance.

9. Naming and Script Conventions

Tables, fields, and scripts follow a strict naming pattern to convey layer, subject, and purpose, e.g., dm_xxsh_user for a dimension‑model table or dw_xxsh_fact_users for a fact table. Scripts are named as hive.hive.dm.dm_xxsh_users and include a header defining owner, source, and target tables.

# Variable definition follows Python syntax
owner = "[email protected]"
source = {"table_name": {"db": "db_name", "table": "table_name"}}
target = {"db_table": {"host": "hive", "db": "db_name", "table": "table_name"}}
# SQL task body
task = '''
SELECT ...
'''

10. Real‑Time Processing

Modern warehouses extend to streaming data with low latency, handling infinite data streams, unbounded processing, and sub‑second response. Common frameworks include Flink, Spark Streaming, and Storm. Real‑time use cases cover recommendation, fraud detection, sentiment analysis, complex event processing, and online machine learning.

10.1 Architecture Styles

Lambda Architecture : Combines batch layer (high accuracy) with speed layer (low latency) to provide both comprehensive and near‑real‑time views.

Kappa Architecture : Simplifies Lambda by using a single streaming layer (e.g., Kafka + Flink) that can reprocess historic data, reducing operational complexity.

11. Practical Recommendations

Avoid cross‑layer table dependencies; follow ODS → DW → DM → APP flow.

Use single‑column surrogate keys (proxy keys) for dimension tables; avoid natural keys that may change.

Maintain clear naming conventions for tables, fields, and scripts to ensure governance.

Implement robust metadata and data quality monitoring to track lineage and detect anomalies.

When moving to real‑time, evaluate latency requirements, data volume, and tolerance for late arrivals to choose between Lambda and Kappa.