How to Build a Layered Data Warehouse Architecture

After three years of data‑warehouse development, the author reflects on the evolution from beginner to independently designing a complete warehouse architecture, emphasizing lessons learned and the need for a systematic approach.

Data warehouses differ from OLTP databases: databases handle real‑time transactional processing, while warehouses (OLAP) store historical data for analytical queries, such as aggregating daily deposits across all customers.

Key requirements for a robust warehouse include:

Data consistency – source values must remain unchanged (e.g., a customer name "aaa" should not become "aa" after loading).

Data universality – reconcile differing formats (e.g., YYYY‑MM‑DD vs yyyymmdd) and identifiers (customer_id vs customer_code) to simplify analysis.

Data storage – decide whether to load full daily snapshots or only incremental changes, balancing storage cost and freshness.

Usability – ensure downstream analysts can quickly obtain correct results.

Security – prevent data leakage, loss, and enforce fine‑grained access controls.

1. Source Layer (ODS)

The source layer mirrors raw business systems to guarantee traceability. Two extraction methods are common:

Full extraction for small tables, performed daily.

Incremental extraction for large tables, pulling only changed records for a given period.

Retention policies vary (7 days, 1 month, 1 year, or permanent); a minimum of seven days is recommended.

2. Historical Layer

This layer preserves immutable historical records, enabling queries such as “when did a customer achieve the highest balance?” Storage techniques include:

Incremental slicing – insert daily deltas into history tables.

Full slicing – reload entire tables daily (suitable for small, frequently changing tables).

Slowly changing dimension ("link table") – store only the change record with an effective‑date column, reducing storage for infrequently updated entities like customer balances.

Choosing a method requires balancing storage cost against query performance and data freshness.

3. Data‑Model Layer

Built on dimensional modeling, this layer aggregates and refines data for analytical consumption. Recommendations:

Follow dimensional modeling principles but remain flexible for specific business needs.

Design robust models: avoid tables tied to a single application, anticipate business rule changes, and maintain consistent statistical definitions.

Although real‑world warehouses are often more complex (including data marts and distribution mechanisms), the three‑layer structure forms the foundation.

Key takeaways for a functional warehouse are accurate, usable, historically traceable, and securely controlled data.