Data Warehouse Architectures

While Kimball's DW/BI architecture is the primary focus of this book, it is essential to understand the other prominent architectures that exist in the data warehousing landscape. Comparing these approaches clarifies why specific architectural decisions are made.

Four prominent data warehouse architectures are:

1. Independent Data Mart Architecture
2. Hub-and-Spoke Corporate Information Factory (Inmon Architecture)
3. Kimball's DW/BI Architecture
4. Hybrid Hub-and-Spoke and Kimball Architecture

1. Independent Data Mart Architecture

In this architecture, data is extracted from operational source systems and loaded directly into standalone, isolated data marts designed for specific departments or business functions (e.g., a Sales Data Mart, a Finance Data Mart).

• Structure: There is no central, integrated Enterprise Data Warehouse (EDW), and each data mart is developed and populated independently.
• Pros: Fast to build initially and inexpensive for a single department.
• Cons: This approach is widely considered an anti-pattern. Because there is no integration or shared dimensions, different departments will calculate the same metrics differently, leading to "multiple versions of the truth". It results in duplicated ETL efforts and a fragmented, unmanageable analytical environment.

WARNING

Building independent data marts without an enterprise integration strategy creates technical debt that is notoriously difficult and expensive to fix later.

2. Hub-and-Spoke CIF (Inmon Architecture)

Championed by Bill Inmon (often called the "father of data warehousing"), the Corporate Information Factory (CIF) represents a top-down approach.

• Structure: Data from source systems is extracted, transformed, and loaded into a centralized, highly normalized (typically 3rd Normal Form) Enterprise Data Warehouse. This EDW acts as the atomic-level single source of truth (the "Hub").
• Data Marts: End users and BI applications do not query the EDW directly. Instead, data is extracted from the EDW to populate specialized departmental data marts (the "Spokes"). These data marts can be dimensional or relational.
• Pros: Provides a rigorous, integrated, and highly consistent enterprise-wide foundation. The normalized EDW is excellent for complex, cross-functional data integration.
• Cons: Requires massive upfront planning and effort before delivering any business value. Building and maintaining a highly normalized enterprise model is notoriously slow and complex.

3. Kimball's DW/BI Architecture

Developed by Ralph Kimball, this architecture takes a bottom-up approach focused entirely on dimensional modeling and delivering immediate business value.

The architecture consists of four distinct and logically separated components:

Operational Source Systems

These are the core systems capturing the day-to-day transactions of the business (e.g., ERP, CRM, legacy systems).

• Purpose: Designed to run operations, highly normalized, and optimized for fast inserts/updates (OLTP).

ETL System

The "kitchen" of the data warehouse. It is responsible for extracting source data, transforming it (cleansing, standardizing, resolving quality issues, generating surrogate keys), and loading it into the presentation area.

Data Presentation Area

Data is organized, stored, and made available for direct querying by users and BI applications. This is the core deliverable.

• Dimensional Modeling: Data is structured into Fact tables (metrics) and Dimension tables (context), forming star schemas.
• Enterprise Data Warehouse Bus Architecture: Kimball builds the warehouse incrementally through business-process-oriented dimensional data marts. These marts are integrated through conformed dimensions, forming an enterprise-wide architecture rather than isolated departmental silos.
• Characteristics: Denormalized to simplify analytical querying and provide efficient performance, contains deep history, and is strictly read-only for users.

Business Intelligence (BI) Applications

The tools (dashboards, ad-hoc query tools) that business users interact with. They primarily query the dimensional models in the Data Presentation Area.

4. Hybrid Hub-and-Spoke and Kimball Architecture

As the name suggests, this architecture attempts to combine the best aspects of both the Inmon (Hub-and-Spoke) and Kimball methodologies. It can be appropriate in large, complex enterprise environments where a centralized, integrated atomic data store is required in addition to a dimensional presentation layer.

• Structure: Data is extracted from source systems and loaded into a centralized, normalized (3NF) Enterprise Data Warehouse (Inmon style). This serves as the atomic, integrated foundation.
• Presentation: From this centralized EDW, data is transformed into a dimensional presentation layer (Kimball style star schemas) utilizing conformed dimensions.
• Pros: Offers the rigorous, atomic data integration of a normalized EDW while still providing the highly optimized, user-friendly dimensional querying layer advocated by Kimball.
• Cons: Extremely complex and expensive to build and maintain, as it effectively requires building the warehouse twice (once in 3NF, then again dimensionally).

TIP

Modern cloud data warehouses (like Snowflake or BigQuery) can host multiple logical layers - including staging, integration, and dimensional presentation - within the same physical platform. The logical separation between these layers can still remain important even when they share the same underlying technology.

Common Myths

There are several persistent misconceptions about dimensional modeling, often stemming from confusion between different data warehouse architectures. Reviewing these myths at the end of this chapter provides a good summary of the principles of dimensional modeling.

Myth 1: Dimensional Models are Only for Summary Data

Reality: Dimensional models should always be built on the lowest possible grain of data (atomic data). Summary tables (aggregates) are added later purely for performance tuning, but the foundation must be atomic to answer unpredictable, granular queries.

Myth 2: Dimensional Models are Departmental, Not Enterprise

Reality: While a dimensional model might be deployed incrementally (one business process or department at a time), it is absolutely an enterprise-wide architecture. Conformed dimensions (shared dimensions like Date or Customer) stitch these incremental models together into a cohesive enterprise data warehouse.

Myth 3: Dimensional Models are Not Scalable

Reality: Dimensional models scale exceptionally well. Fact tables can handle billions of rows. The star schema design is highly optimized for modern relational database engines, which easily partition and distribute these large tables.

Myth 4: Dimensional Models are Only for Predictable Usage

Reality: By providing data at the lowest atomic grain and standardizing the schema structure, dimensional models allow business users to ask highly unpredictable ad-hoc queries. They are not restricted to pre-canned reports.

Myth 5: Dimensional Models Can't Be Integrated

Reality: The entire architecture relies on integration. Because dimensions are shared (conformed) across different fact tables, data from completely different business processes can be seamlessly integrated and queried together.