Knowledge Hub

This topic is very close to many experienced data modelers because it is surprisingly misunderstood. The confusion usually begins with one assumption: if both systems use Third Normal Form (3NF), then they must be conceptually the same. They are not.

In data architecture, the Data Warehouse concept popularized by Bill Inmon embraces the use of 3NF. Since 3NF is also widely used in OLTP systems, many architects assume the modeling philosophy and operational behavior are identical. The structure may look similar, but the intent, behavior, and lifecycle of data are fundamentally different.

OLTP 3NF: Designed for Transactional Efficiency

In an OLTP (Online Transaction Processing) system, the objective is operational efficiency. The system must support high-volume, real-time transactions such as order placement, payments, inventory updates, and customer modifications.

The primary goals are:

• Fast inserts, updates, and deletes
• Concurrency control
• Storage optimization
• Data consistency for current state

As organizations matured in their use of Data Warehouses, a new challenge began to surface. Warehouses were excellent for structured, relational data such as sales records, financial transactions, and inventory counts. However, the digital economy was producing entirely new kinds of information, clickstreams, social media posts, sensor readings, videos, documents, and logs. These data types, which fall into the categories of semi-structured and unstructured data, made up the vast majority of enterprise information, yet traditional warehouses were not designed to ingest, store, or process them at scale.

The solution that emerged was the Data Lake. A Data Lake is a centralized repository that stores structured, semi-structured, and unstructured data in its raw form. Unlike a Data Warehouse, which requires data to be modeled and cleansed before it is loaded, a Data Lake follows a schema-on-read philosophy. Data is ingested quickly, without requiring upfront modeling, and structure is applied later when the data is analyzed. This makes the Data Lake highly flexible for exploratory analysis, advanced analytics, and machine learning.

Over the past decade, the terms Big Data and Data Lake have often been used interchangeably, leading to significant confusion in both technical and business conversations. While they are closely related, they are not the same. Understanding the distinction helps organizations make better architectural decisions and avoid unrealistic expectations when planning data initiatives.

Big Data: The Broader Ecosystem

Big Data is not a single technology or storage system. It is the movement and ecosystem that arose to address the challenges posed by the three Vs, volume, velocity, and variety, of modern data. Big Data includes the distributed storage and processing technologies (HDFS, MapReduce, Spark, Kafka), the new types of databases (NoSQL, graph, document, columnar), and the analytical methods that enable organizations to handle massive, fast-moving, and diverse datasets.

In short, Big Data represents the toolbox and paradigm that allowed enterprises to move beyond the limitations of traditional RDBMS and warehouses.

The concept of the Data Lakehouse emerged around 2020 as an architectural response to the limitations of both Data Warehouses and Data Lakes. In simple terms:

Data Warehouse + Data Lake = Data Lakehouse.

Rather than being an entirely new invention, the Lakehouse combines the strengths of existing paradigms, the structured, governed nature of the Data Warehouse and the flexible, large-scale storage of the Data Lake, enhanced by modern innovations in table formats and metadata management.

At its core, the Data Lakehouse addresses two long-standing challenges. Traditional Data Lakes allowed organizations to store vast amounts of raw data but lacked strong transactional guarantees, often leading to inconsistent or unreliable datasets. Data Warehouses, by contrast, provided consistency and governance but were limited in their ability to handle semi-structured and unstructured data at scale. The Lakehouse resolves this by offering:

The Data Hub is a relatively new term in the evolution of enterprise data architecture, appearing after the rise of the Data Lake. It is important to clarify from the outset that a Data Hub is not another type of storage system, nor is it a new analytical framework. Instead, it plays a distinct role as the central integration and distribution point that connects analytical platforms with operational systems.

To understand why Data Hubs matter, it helps to recall the current division of enterprise systems:

• Operational systems (OLTP): These are core enterprise applications such as ERP, CRM, and core banking systems.
• Data Warehouses (DWH): These serve business intelligence needs such as dashboards, reports, and data mining.
• Data Lakes: These enable advanced analytics such as data science, machine learning, and deep learning.

This division creates a practical problem: how can the results of business intelligence and analytics, KPIs, predictions, classifications, and insights, be shared back with the very applications that drive day-to-day business?

The concept of the Data Fabric is often confused with the Data Hub, but while the two share the goal of breaking down silos, they operate at different levels of abstraction. The Data Hub serves as the distribution and operationalization layer, enabling insights from analytical platforms to flow back into enterprise applications. The Data Fabric, by contrast, is a broader architectural paradigm that unifies data governance, integration, and orchestration across the entire enterprise ecosystem.

In the earlier discussion of the Data Hub, we established that enterprise systems are typically divided into three categories:

• Operational systems (OLTP): core business applications such as ERP, CRM, and core banking.
• Data Warehouses (DWH): supporting business intelligence through dashboards, reports, and data mining.
• Data Lakes: enabling advanced analytics, machine learning, and data science.

The Hub emerged to connect these silos and to push the results of BI and analytics back into operational applications.

One of the biggest criticisms of early Data Lakes was their lack of reliability. While they excelled at storing vast volumes of structured, semi-structured, and unstructured data, they struggled to provide the transactional integrity and data consistency that relational databases and warehouses delivered as a matter of course. Operations such as inserts, updates, and deletes, known collectively as DML (Data Manipulation Language), were difficult to perform reliably on file-based storage systems like Hadoop Distributed File System (HDFS) or cloud object stores such as Amazon S3.

To achieve performance and flexibility, many organizations turned to Apache Spark, an in-memory processing engine capable of handling both batch and real-time streaming workloads. However, Spark introduced a different problem: while it enabled fast queries and analytics, it was not ACID-compliant. Without atomicity, consistency, isolation, and durability, organizations faced the risk of incomplete writes, corrupted data, or inconsistencies when jobs failed midway. Workarounds existed, but they were complex and fragile.

As discussed in the topic i.e., Data Warehouse or Data Mart, Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Star Schema, the model has a Fact table in the middle and all the Dimensions are joined with the Fact. For example, there is a Fact table called Sales and there can be many dimensions like Time, Location, Product, Categories etc. The Primary Key of the Fact table consists of all the foreign key columns coming from Dimensions. This way when a report or a query is created, there is always one direct join with any Dimension.

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Snowflake Schema, the model also has a Fact table in the middle and Dimensions around it but here Dimensions can be normalized further in multiple Sub-dimensions which changes the query to create a report where there will be multiple joins to reach a conclusive result. For example, there is a Fact table called Sales and a Dimension i.e., Location, this location Dimension can be normalized further in State, City, and Postal Code etc.

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach. But what about Galaxy Schema?

You must be wondering that we have been hearing about Facts and Dimensions in both Star and Snowflake schema(s) but where are Conformed Dimensions, so here it is.

Galaxy Schema is also known as Fact Constellation Schema and is more complex than Star and Snowflake schema. It’s further divide Star Schema into small Star Schema(s) where there are more than one Fact Table and have Reusable Dimension to connect multiple Fact Tables.

These dimensions which can connect with multiple Fact tables across multiple Star Schema(s) are known as Conformed Dimensions.