Knowledge Hub

Dimensional Data Modelling is an analytical modelling approach that structures data into facts (quantitative measures) and dimensions (descriptive attributes) to support reporting, dashboards, and business intelligence. It provides a simplified, performance-oriented representation of organizational data, making analytics accessible to both technical and business users.

The primary goal of dimensional modelling is to deliver a business-friendly and performance-optimized structure that enables fast and intuitive access to information. Rather than focusing on eliminating redundancy at all costs, dimensional modelling focuses on making data easy to query, aggregate, and understand.

Dimensional models are organized around business processes. A fact table captures measurable events, such as loan approvals, repayments, or transactions. Surrounding the fact table are dimension tables, which provide context, such as customer attributes, officer details, product types, and dates. Together, they answer two fundamental questions:

The Star Schema is a dimensional modelling design in which a central fact table connects directly to multiple surrounding dimension tables, forming a star-like structure. The fact table stores quantitative measures of a business process, while the dimension tables provide descriptive context for analysis.

The primary goal of the star schema is to deliver a simple, high-performance, and business-friendly structure for analytics. It is designed specifically for reporting, dashboards, and self-service business intelligence, where clarity and speed are critical.

In a star schema, the fact table captures measurable events such as LoanAmount, InterestRate, ApprovalCount, or RepaymentAmount. Each row represents a clearly defined grain, such as one loan approval or one repayment transaction. The fact table contains foreign keys that link to dimension tables.

The dimension tables contain descriptive attributes used for filtering, grouping, and slicing data. Examples include:

The Snowflake Schema is a dimensional modelling design in which one or more dimension tables are normalized into related sub-dimensions, forming a snowflake-like structure around a central fact table. While the fact table remains the analytical core, dimension tables are decomposed to reduce redundancy and improve governance.

The primary goal of the snowflake schema is to improve data quality, storage efficiency, and reusability, especially for large, shared dimensions, while still preserving dimensional modelling semantics for analytics.

A snowflake schema typically begins as a classic star schema (Fact + Dimensions). Over time, wide dimension tables may be normalized into smaller, related sub-dimensions. For example:

• Dim_Customer may be split into:
  • Customer (core identity attributes)
  • Demographics (age band, income band)
  • Employment (industry, employer category)
  • Geography (city, region, country)

Medallion Architecture is a new term but again just like many other jargons used by Salespeople, this one is no more than a Buzz word. In other words, we already know this, we are already use it if we have a lakehouse implemented.

People who are working with Databricks and have implemented their Lakehouse probably know what this Medallion Architecture is. Let me try to explain in a few lines for the audience reading this book.

Medallion Architecture is sitting in Delta Lake tables, and it is about processing data in multiple layers i.e., Bronze ⇒ Silver ⇒ Gold layer tables.

• Raw Layer: This layer is normally not under Medallion Architecture as it totally Raw whereas Bronze, Silver and Gold all are in Delta Lake tables. So Raw Layer hosts source data which can’t be pushed to Bronze Layer directly. Most of the organizations prefer to land all source systems data in Raw layer (I also prefer this).
• Bronze: Those source systems which have connectors with Databricks/ Delta Lake can land data in Bronze else Bronze source is most Raw layer. Some data standardization transformation happens here like.

I am sure most of us know this term but not sure how accurate you are in terms of its importance.

‘The granularity of data means, the levels of data breakdown.’

Generally, people think having all relevant details about an entity is Granularity, but the question is what level of details?

For example, details can be name, email, and contact number, date of birth, color, blood group, home address, company, role, salary, father name, mother name and so. Is this the lower level of granularity? NO, when we say the lowest level of granularity, we can break down.

• Name into First Name, Middle Name and Last Name
• Address into Apartment Number, Building Name, Street, Area, City, District, Region, Country, Postal Code etc.

In OLTP systems, granularity must go as low as possible and in OLAP systems, it can depend on the business use case.

Now, when we have established what the Granularity is, let's discuss when it's needed. Data Wrangling, Data Mining, Data Science etc., all use raw data for analysis and analytics. And when we say RAW, the lower the level of granularity can be, the better the model result will be for DSS.

Another term, if data modelers are not aware and if data model doesn’t take care via proper Data Cardinality check and balance, data analysts are hit by Chasm and Fan Traps.

We know what Data Modelling is. We know what Data Cardinality is. Now, it’s time to understand Cartesian data which give nightmares to Finance department when they manually know the total is USD100k but when they run query, they see USD200 k 😊.

‘Cartesian Data is about writing a query on tables with ‘Many to Many (M:M)’ relationship.’

If there are two tables with ‘Many to Many’ relationship, it’s time for data modeler to introduce an intermediary table which can have unique records and then can have ‘One to Many’ relationship with previously used two tables.

If the Sales table is joined with Target tables directly based on Employee ID then surely there will Cartesian data as both Sales and Target tables can have more than 1 employee ID. But hang-on, even having intermediary table, if query is written wrongly then still data analyst can hit with Cartesian data know as Chasm Trap.

Business Analytics has been the most demanding cycle in the current era. Business Analytics supports DSS (Decision Support Systems) and the technologies to support started from OLTP systems to OLAP systems i.e., source systems to Data Warehouses or Data Marts or Data Lakes or Data Lake Houses or Data Vault or Business Vault or Enterprise Data Hubs or Enterprise Data Fabric etc., but that’s not the complete cycle. The cycle completes once Reverse ETL is performed.

Let’s understand with an example. For example, the product sales data is loaded in a system (OLTP) then it is pushed or pulled in a Data Lake or a Data/ Business Vault or in a Data Warehouse or Data Mart or Data Lake House etc. Once data has move in these datastores then analytics are performed using Data Science practice like by running Machine Learning, Deep Learning, and many other means to analyze data for prediction or forecasting and last but not the least the data is present to Top Management for decision marking. Management decides to initiate campaigns based on the data element extracted from the whole cycle from OLTP to OLAP etc.

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.