Comparative Analysis of One Big Table, Kimball, and Relational Data Modelling Techniques

Data modelling is a crucial aspect of database design and data management. Different modelling techniques have been developed to optimize performance, maintainability, and analytical capabilities based on business requirements.

This article compares and analyses three widely used data modelling approaches: One Big Table (OBT), Kimball’s Dimensional Modelling, and Relational (3NF) Data Modelling. We explore their architectures, advantages, disadvantages, and use cases, supported by examples and references to industry best practices.

Introduction

Data modelling serves as the foundation for database design, determining how data is structured, stored, and queried. Choosing the right modelling technique is essential for ensuring data integrity, optimizing performance, and facilitating analytics. This article examines three prominent data modelling approaches:

1. One Big Table (OBT) — A denormalized approach that consolidates all data into a single table.

2. Kimball’s Dimensional Modelling — A star or snowflake schema-based model optimized for analytical queries.

3. Relational (3NF) Modelling — A normalized model that reduces redundancy and enforces data integrity.

Each approach has distinct trade-offs in terms of performance, maintainability, scalability, and analytical efficiency.

Lets get the comparison out of the way for those who are not interested in going through the entire article and want gist of it:

[embed]

1. One Big Table (OBT) Model

Overview

One Big Table (OBT) is a fully denormalized data model where all relevant data is stored in a single wide table. This approach is commonly used in big data environments and data warehouses, particularly with columnar storage databases like Google BigQuery and Amazon Redshift.

Architecture

• All data attributes are merged into a single, flat table.

• Joins are eliminated, improving query performance.

• Typically used in columnar storage systems, where queries can efficiently scan only the required columns.

Advantages

Optimized for Read Performance — Eliminates costly joins, making queries faster.

Simplicity — Easy to understand and implement.

Best for Columnar Databases — Works well with BigQuery, Redshift, and Snowflake.

Disadvantages

✘ Data Redundancy — Increased storage costs due to duplicated data.

✘ Difficult to Maintain — Schema changes require modifications to the entire table.

✘ Limited Flexibility — Not suitable for transactional applications where normalization is beneficial.

Use Cases

• Analytical workloads in BigQuery, Redshift, Snowflake.

• Reporting dashboards where speed is more critical than storage optimization.

• ETL pipelines where data is pre-aggregated for performance.

2. Kimball’s Dimensional Modeling (Star Schema)

Overview

Kimball’s approach focuses on organizing data into fact and dimension tables, optimizing it for analytical processing in a data warehouse. It follows a denormalized structure while maintaining a logical relationship between entities.

Architecture

• Fact Tables: Contain measurable business events (e.g., sales, transactions).

• Dimension Tables: Contain descriptive attributes (e.g., customer details, product information).

• Uses Star Schema (denormalized) or Snowflake Schema (partially normalized).

Advantages

Optimized for Analytics — Structured for BI tools and reporting.

Improves Query Performance — Denormalized structure speeds up joins.

Scalable for Large Datasets — Commonly used in data warehouses.

Disadvantages

✘ Requires ETL Processing — Data needs to be transformed before loading.

✘ Less Flexible for Operational Systems — Best suited for read-heavy analytical workloads.

Use Cases

• Business Intelligence (BI) and Reporting (Power BI, Tableau, Looker).

• Data Warehousing Solutions (Google BigQuery, Snowflake, Redshift).

• Historical Analysis where performance is critical.

3. Relational (3NF) Data Modeling

Overview

The Relational Model, also known as Third Normal Form (3NF), organizes data into normalized tables, minimizing redundancy and ensuring referential integrity. It is widely used in OLTP (Online Transaction Processing) systems.

Architecture

• Data is broken into multiple tables, linked by foreign keys.

• Ensures data consistency and integrity.

• Requires JOIN operations for data retrieval.

Advantages

Eliminates Data Redundancy — Saves storage and improves consistency.

Ideal for OLTP Systems — Ensures data integrity and supports frequent updates.

Disadvantages

✘ Slower Read Performance — Joins can impact query speed.

✘ Complex Queries — Requires multiple joins for analytical reports.

Use Cases

• Transactional Databases (PostgreSQL, MySQL, Oracle, SQL Server).

• Enterprise Applications (CRM, ERP).

• Systems with frequent updates and strong consistency requirements.

Conclusion

Each data modelling approach serves different purposes. OBT is best for big data analytics, Kimball’s model is ideal for BI and reporting, while Relational (3NF) is essential for transactional systems. Choosing the right approach depends on data volume, query patterns, and business needs.

References

1. Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling.

2. Codd, E. F. (1970). “A Relational Model of Data for Large Shared Data Banks.” Communications of the ACM.

3. Google BigQuery Documentation: https://cloud.google.com/bigquery