Data Modeling: Kimball vs. Inmon
Normalization, their approaches and motivations.
Reminder: I’m offering a limited-time 50% discount on the annual plan:
Once you claim it, the discount will be applied forever.
Now, with only $5/month, you will have access to:
200+ deep-dive data engineering articles
practice-spark: 65 LeetCode-style problems to practice Spark SQL/DataFrame
learn-spark/dbt/airflow: CLI tools to master Spark/dbt/Airflow
If you’re a Vietnamese user, please DM me for an upgrade due to payment issues
Intro
If you spend enough time in data engineering, you must already know the importance of data modeling; it might slow the team down a bit at first, but in the long term, it literally backs your entire data foundation.
The data model defines many things:
Which entities are there? Customer? Device? Subscription Package?
What is the relationship between them?
Which data will be collected? Source A? Source B? Extract all the fields or just five fields?
How to calculate a metric?
Which constraints and business rules does this table have? Thus, it defines what quality data looks like.
…
—
I used to link data modeling → dimensional modeling (Kimball), but in fact there are other data modeling approaches such as Inmon or Vault.
—
In this article, I want to deliver my understanding of the difference between the Kimball and Inmon approaches. The goal is to give you a mental model of what these approaches look like and their ultimate goals; from that, you can understand why a company chooses one or combines them.
Note: This article does not deep-dive into these two approaches; instead, I want to focus on the differences in how the two guide the data modeling process.
But first, we will learn about data normalization and denormalization. We will spend a long time on this; it’s a good opportunity to learn about them.
Normalization vs. denormalization
Let’s imagine we have a table with the schema:
employee_id
certifications, which is a list of nested fields:
{ cert_name: "AWS Certified", issuer: "Amazon", expiry: "2027-03" }
{ cert_name: "Scrum Master", issuer: "Scrum.org", expiry: "2026-11" }
manager_id
manager_name
department.
Denormalization
This table can be considered a form of denormalization, in which it can contain nested and repeated data.
When you want to answer “which certifications expire in the next 90 days”, you can’t just filter a column for it. The dates you need are nested within the “certifications” section.
You need to unpack every row’s nested object first.
Nested data like this prevents the basic database operations (which are supposed to be easy with normalization): querying and updating a single sub-value without touching the surrounding structure.
Let’s normalize it.
Database normalization is the method used to control the relationships of tables and columns (in a table). Its purpose is to avoid data redundancy, thus guaranteeing data integrity.
The concept was first introduced by Edgar Codd in the early 1970s. He also introduced normalization forms, which are sequential, with the higher one including the characteristics of the previous one and being stricter (solving more data integrity problems).
—
In my experience as a data engineer, understanding the first three forms is enough. In the next section, we will go through each form and apply it to normalize our example data to see which problem each form solves.
“NF” stands for normal form
1NF
Let’s flatten and unnest the data:
At first, we can use the employee_id as the unique identifier for a row; after flattening, this is no longer feasible, as you can see; id 42 appears twice because this employee has two certifications: AWS and Scrum.
—
To uniquely identify a row, let’s combine the employee_id + cert_id and use them as the primary key. Each row can now be addressed.
By flattening the data and defining the primary key (in this case, the combination of employee_id and cert_id), we normalize it to 1NF.
In a 1NF table, each column holds a single, atomic value. The table has a unique primary key. It ensures that each row in the table has a unique identifier and that a column doesn’t contain mixed types or multiple values.
However, it still has data integrity problems. Back to our example, as you can see, the “department” and “manager_name” are now repeated across every cert record for the same employee.
2NF
Reminder: I’m offering a limited-time 50% discount on the annual plan:
Once you claim it, the discount will be applied forever.
Now, with only $5/month, you will have access to:
200+ deep-dive data engineering articles
practice-spark: 65 LeetCode-style problems to practice Spark SQL/DataFrame
learn-spark/dbt/airflow: CLI tools to master Spark/dbt/Airflow
If you’re a Vietnamese user, please DM me for an upgrade due to payment issues