Designing Effective Data Warehouse Models: Architecture and Best Practices

Key Considerations for Building a Data Warehouse

Unlike single‑table ER designs, a data warehouse must integrate multiple input systems and serve many business lines, so designers must evaluate business requirements, system architecture, logical design, and physical design to ensure performance and scalability.

Purpose of a Data Warehouse Data Model

A well‑designed model provides a unified enterprise view, defines departmental data needs, forms the foundation of the atomic layer, supports future planning, and clarifies data ownership.

Common Data Models

Two primary models are used:

Relational model – builds a 3NF schema that abstracts the whole enterprise and emphasizes data integration and consistency.

Dimensional model – starts from analytical decision needs and typically adopts star or snowflake schemas; most warehouses use this approach.

Fact tables capture business events (e.g., an order) and consist of foreign keys to dimension tables; dimension tables store descriptive attributes such as user, product, time, etc.

Physical Design and Layered Storage

After conceptual and logical design, the physical model implements layered storage to simplify cleaning and maintenance:

ODS (Operational Data Store) – raw source data with minimal transformation.

DIM – stores shared reference data (e.g., country codes).

DWD (Data Warehouse Detail) – permanent storage that resolves data quality and completeness issues.

DWS (Data Warehouse Service) – provides fine‑grained wide tables and coarse‑grained summary tables, covering roughly 80% of business calculations.

ADS (Application Data Store) – optional higher‑level aggregates for reporting.

Case Study: Procurement Bidding Data Warehouse

The procurement process is broken into concept, logic, and physical layers. Conceptual design maps the Oracle‑based production ER model into a warehouse model with fact tables for bidding events and dimension tables for bidders, agencies, experts, etc. Logical design follows a snowflake schema and uses Kettle for ETL. Naming conventions are tb_层次_主题域_业务域_粒度 for tables and kt_层次_主题域_业务域_粒度 for Kettle jobs. Physical design builds the ODS→DWD→DWS→ADS pipeline, enabling summaries such as bid‑rate rankings and supplier statistics.

Product Selection: MPP vs. Hadoop

Data warehouses handle warm and cold data for offline analytics, so a Hadoop + MPP combination is common. Hadoop/Hive stores large historical datasets, while HBase offers fast semi‑structured queries. MPP engines (e.g., Vertica, Greenplum, GBase) provide columnar storage, high compression, shared‑nothing architecture, and distributed compute.

MPP characteristics include:

Columnar storage → high compression, high I/O, smart indexing.

Shared‑nothing → independent nodes with data replication.

Distributed compute → scalable performance and security.

Architectural variants: Greenplum uses a master/slave model, Vertica is master‑less, and GBase employs a dual‑center storage/management design.