Data Warehouse and Data Marts

The idea of a Data Warehouse emerged when organizations realized that transactional systems (OLTP), while excellent for recording day-to-day operations, were never designed for heavy reporting or analytical workloads. As business leaders demanded consolidated views of performance, finance, sales, and customer behaviour, data modelers introduced the concept of a centralized analytical datastore, the Data Warehouse, to relieve OLTP systems from the burden of reporting and to provide trusted, historical insight.

It is important to clarify a common source of confusion: a Data Warehouse is not the same as Dimensional Modelling. The warehouse refers to the central repository that consolidates data from multiple source systems, while dimensional modelling is only one possible technique for structuring that repository. A Data Warehouse can be designed in different ways, depending on organizational priorities such as performance, scalability, or agility.

Data Warehouse Techniques

Over time, several modelling approaches have been used to design Data Warehouses:

• Third Normal Form (3NF): This approach emphasizes data integration and normalization. Popularized by Bill Inmon, 3NF aims to minimize redundancy and create a subject-oriented, integrated structure. While highly consistent, it can be complex for end-user queries.
• Dimensional Modelling (Star or Snowflake): Introduced by Ralph Kimball, this technique organizes data around facts (measurable events) and dimensions (context for analysis). A Star Schema uses a central fact table connected to multiple dimensions, while a Snowflake Schema normalizes dimensions further into related tables. Dimensional modelling makes data highly consumable for business users and is the backbone of most BI systems.
  • • Facts: This table keeps the transaction’s lowest level of data which can be numeric or non-numeric like sales numbers, number of clicks on social media portal, number of searches in google etc.
    • Dimension: This table keeps data points by which Fact can be analyzed e.g., sale by time, sales by region, sales by the product etc.
    • Conformed Dimensions: A Dimension which can connect to multiple Facts.
• Ensemble Modelling (Data Vault, Anchor, Focal Point): These modern approaches address scalability and agility.
  • Data Vault structures data into hubs, links, and satellites to support historical tracking and auditability.
  • Anchor Modelling enables flexible schema evolution with minimal disruption.
  • Focal Point Modelling builds on ensemble ideas to handle semantic richness and multi-perspective analysis.
• Hook Model: A supporting pattern often used alongside ensemble methods to simplify integration and provide stable “connection points” for downstream systems.

Each technique has its own strengths, and many organizations adopt a hybrid approach depending on maturity and use cases.

Data Warehouse vs. Data Mart

A Data Warehouse consolidates data at the enterprise level, integrating sources across the entire organization. By contrast, a Data Mart is scoped to a department or function, such as Finance, Sales, or Marketing. Architecturally, both share the same design principles and may use any of the modelling techniques described above. The main difference is scale and focus: warehouses serve the entire organization, while marts deliver subject-specific solutions for faster adoption.

In both environments, data is optimized for analysis rather than transactions. Historical information is preserved, and deletion operations are avoided to ensure that past trends remain intact.

Historical Foundations

Two pioneers shaped the foundations of data warehousing:

• Bill Inmon, widely regarded as the Father of Data Warehousing, defined it in 1990 as “a subject-oriented, integrated, time-variant, and non-volatile collection of data in support of the management’s decision-making process.” His top-down method recommended building the enterprise warehouse first (typically in 3NF) and then populating dependent marts.
• Ralph Kimball, in contrast, argued in 1996 that “a Data Warehouse is a copy of transaction data specifically structured for query and analysis.” He championed dimensional modelling and promoted a bottom-up approach, starting with business-driven Data Marts that could later be integrated into an enterprise warehouse.

This debate, Inmon’s warehouse-first vs. Kimball’s mart-first, continues to influence data strategy.

A Practitioner’s Perspective

From my own experience, dimensional modelling offers the best balance of usability and performance for business consumption. However, I lean toward an enterprise warehouse-first approach rather than starting with marts. If marts are built first, any changes at the warehouse level require modifications in marts and upstream pipelines, introducing unnecessary maintenance overhead. By building the warehouse as a foundation and then designing marts as tailored subsets, organizations achieve consistency without redundant effort.

Over the years, warehouse design has also expanded to include Galaxy Schemas (multiple fact tables with shared dimensions) and OLAP Cubes (pre-aggregated multidimensional structures). These approaches provide additional flexibility for performance optimization and advanced analytical needs.

Real-World Example

Consider a global retailer operating hundreds of stores. Each store’s point-of-sale system captures daily transactions but cannot answer enterprise-wide questions such as “Which products are performing best across regions?” or “How do promotions in one city affect overall sales trends?” By consolidating data into a central Data Warehouse, the retailer gains visibility across all stores, enabling demand forecasting, inventory optimization, and targeted marketing. Department-specific Data Marts, such as one focused on Finance or Marketing, then allow specialized teams to drill deeper into their areas of interest without overwhelming the enterprise warehouse.

Summary

A Data Warehouse remains the cornerstone of structured enterprise analytics. Whether implemented using 3NF, Dimensional Modelling, Ensemble techniques, or hybrids, it provides a trusted foundation for reporting, trend analysis, and strategic decision-making. Data Marts complement this design by offering departmental focus, but both serve the same ultimate purpose: transforming raw transactional data into actionable business intelligence.

What is Star Schema, Snowflake Schema, Galaxy Schema and OLAP Cubes? (All are explained in separate topics)

‘On a side node, I have the honor to work with Bill Inmon on an Analytics project.’