Data Modeling Guide: Dimensional Design for Analytics

Data modeling is the process of designing how data is structured, stored, and accessed. In analytics, dimensional modeling — popularized by Ralph Kimball in The Data Warehouse Toolkit — is the standard approach. According to TDWI (The Data Warehousing Institute), organizations that follow formal data modeling practices report 50% faster query performance and 40% lower maintenance costs compared to ad-hoc schema designs. Good data modeling makes analytics intuitive and performant; bad modeling makes every query a struggle.

Dimensional Modeling Fundamentals

Fact Tables

Fact tables store quantitative measures of business events. Each row represents a measurement at a specific grain. A sales fact table, for example, contains one row per line item with measures like quantity, unit price, and discount amount. Facts are typically numeric, additive, and immutable. Once recorded, transactional facts should never be updated — corrections are handled through adjustment entries or slowly changing dimension techniques applied to the fact itself.

CREATE TABLE fact_sales (
    sale_id        INT PRIMARY KEY,
    date_key       INT REFERENCES dim_date(date_key),
    customer_key   INT REFERENCES dim_customer(customer_key),
    product_key    INT REFERENCES dim_product(product_key),
    store_key      INT REFERENCES dim_store(store_key),
    quantity       INT NOT NULL,
    unit_price     DECIMAL(10,2) NOT NULL,
    discount       DECIMAL(10,2) DEFAULT 0,
    total_amount   DECIMAL(10,2) NOT NULL
);

Fact tables grow quickly — a transactional fact table for an e-commerce retailer can reach billions of rows within months. Partitioning fact tables by date is essential for performance and data management. Most warehouses recommend partitioning by month or quarter and using table clustering or indexing on frequently filtered dimension keys.

Dimension Tables

Dimension tables provide context for facts. They contain descriptive attributes — names, categories, dates, locations — that answer who, what, when, where, and why. Dimensions are smaller than fact tables, typically ranging from hundreds to millions of rows. A well-designed dimension table is denormalized with all descriptive attributes in a single table, avoiding the need for additional joins during query time.

CREATE TABLE dim_product (
    product_key    INT PRIMARY KEY,
    product_id     VARCHAR(50),
    product_name   VARCHAR(200),
    category       VARCHAR(100),
    brand          VARCHAR(100),
    unit_cost      DECIMAL(10,2)
);

Dimensions are enriched with hierarchies that enable drill-down and roll-up analysis. A product dimension might include category and subcategory columns. A date dimension includes attributes like day of week, month, quarter, year, holiday flags, and fiscal period indicators. These hierarchies power the OLAP operations that business users depend on for exploratory analysis.

Star Schema

The star schema places a single fact table at the center, surrounded by dimension tables. This denormalized structure minimizes the number of joins needed for analytical queries. A sales analysis query joins fact_sales with dim_date, dim_product, and dim_store — three joins for a complete picture, regardless of how many attributes are included in the query.

Star schemas are optimized for query performance. Dimensions are denormalized — category and brand are stored directly in the product dimension rather than in separate reference tables — which avoids additional joins. The trade-off is some data redundancy, but in a warehouse context where storage is relatively cheap and query time is expensive, this redundancy is acceptable. Kimball’s methodology emphasizes that star schemas are designed for ease of use by business analysts, not for storage efficiency.

Snowflake Schema

The snowflake schema normalizes dimensions into sub-dimensions. The product dimension might reference a separate category dimension and brand dimension, reducing data redundancy at the cost of additional join complexity. While snowflake schemas were common in the early days of data warehousing when storage was expensive, most modern warehouses prefer star schemas. The additional storage cost of denormalization is negligible with modern columnar compression, and the query performance benefits of fewer joins are substantial.

Slowly Changing Dimensions

Dimensions change over time. A customer may move to a new address, or a product may be reassigned to a different category. How you handle these changes depends on your historical reporting requirements. Kimball’s The Data Warehouse Toolkit defines six types of slowly changing dimensions, with three being widely used in practice.

Type 0: Retain Original

The dimension attribute never changes. This is appropriate for immutable facts like a person’s date of birth or a product’s original SKU. Type 0 is the simplest strategy and applies to attributes that are genuinely immutable.

Type 1: Overwrite

The old value is replaced with the new value, losing all historical information about previous values. Type 1 is appropriate for error corrections where the original value was simply wrong and has no analytical value. It is also used for attributes where historical values are never needed for analysis.

UPDATE dim_customer
SET address = '123 New St'
WHERE customer_key = 42;

Type 2: Add New Row

A new dimension row is created with effective dates and a current flag. The old row remains for historical reporting, enabling accurate point-in-time analysis. This is the most common approach for tracking changes over time in data warehouses.

INSERT INTO dim_customer (customer_key, customer_id, address, effective_date, expiry_date, is_current)
VALUES (101, 'CUST42', '123 New St', '2024-06-01', '9999-12-31', true);

UPDATE dim_customer
SET expiry_date = '2024-05-31', is_current = false
WHERE customer_key = 100 AND customer_id = 'CUST42';

Type 2 preserves full history and supports point-in-time reporting: you can join the fact table to the dimension using the effective date, and each fact will be associated with the dimension attributes that were valid at the time of the event. The trade-off is increased dimension size and more complex queries that must filter by effective date.

Type 3: Add New Attribute

A new column stores the previous value, providing limited history (only the most recent change is tracked). Type 3 is rarely used in practice because it only supports tracking one previous value, which is insufficient for most analytical requirements.

Choosing an SCD Strategy

Fact Table Types

Transaction Fact Tables

One row per event. Transaction fact tables are the most common type and are fully additive — you can sum quantity, revenue, or count across any dimension. These tables grow rapidly and need careful partitioning, indexing, and data retention policies. They are the foundation of most operational reporting and analytics.

Periodic Snapshot Fact Tables

One row per period (typically day or month) per entity. A daily account balance table is a periodic snapshot, recording the state at the end of each business day. These tables are semi-additive — summing the balance across dates gives a meaningless total, but averaging across a dimension provides useful insights. Periodic snapshots are commonly used for inventory levels, account balances, and subscription counts.

Accumulating Snapshot Fact Tables

One row per process instance, updated as the process progresses. An order fulfillment fact table tracks order date, ship date, and delivery date in a single row, with multiple foreign keys to the date dimension. Accumulating snapshots enable cycle-time analysis and bottleneck identification. They are updated throughout the lifecycle of the process, with each update adding new dates and potentially new measures.

Data Vault Modeling

Data Vault is an alternative modeling approach designed for enterprise data warehouses with many source systems. Developed by Dan Linstedt, Data Vault separates business keys (hubs), relationships between keys (links), and descriptive attributes (satellites). This architecture provides resilience against source system changes and enables full auditability through dated satellite records.

hub (business key) → link (relationship) → satellite (attributes with effective dates)

Data Vault excels at handling source system changes: adding a new source system requires adding new satellites without modifying existing structures. Every satellite row includes effective dates and load timestamps, providing a complete audit trail of all data changes. The trade-off is query complexity — a simple report may require joining 15-20 tables across hubs, links, and satellites. Data Vault is best suited for large enterprise warehouses with frequent source system changes and strict audit requirements.

Naming Conventions

Consistent naming conventions make schemas self-documenting and reduce the cognitive load on data consumers. A common convention prefixes table names with their functional type:

Schema Design Process

1. Identify the business process: What event or activity generates the data? Examples include sales transactions, inventory movements, and user registrations
2. Define the grain: What does one row represent? The grain must be clear, unambiguous, and agreed upon by stakeholders
3. Identify dimensions: The who, what, when, where, and why surrounding the event — each dimension answers a specific analytical question
4. Identify facts: The numeric measures that quantify the event — revenue, quantity, duration, count
5. Handle history: Determine which dimension attributes change over time and how to track those changes using appropriate SCD strategies

Good data modeling makes analytics intuitive and performant. Investing time in proper dimensional design pays dividends across every downstream use case, from dashboard performance to self-service analytics adoption.

Frequently Asked Questions

What is the difference between a fact and a dimension? A fact is a numeric measure of a business event — revenue, quantity, count, duration. A dimension is a descriptive attribute that provides context — product name, customer region, date. Facts are aggregated using functions like SUM, AVG, and COUNT; dimensions are used for grouping and filtering in SQL queries.

When should I use a snowflake schema over a star schema? Use a snowflake schema when dimension tables are very large and normalization provides significant storage savings. In most modern warehouses with columnar compression, the storage savings from snowflaking are negligible compared to the query performance cost of additional joins, making star schemas the default choice for analytical workloads.

How do I handle many-to-many relationships in dimensional models? Many-to-many relationships require bridge tables. For example, a customer may belong to multiple marketing segments. The bridge table maps each customer-segment pair, and the fact table remains at the original grain. Bridge tables add join complexity but preserve analytical accuracy for many-to-many relationships.

What is the difference between Kimball and Inmon approaches? Kimball advocates bottom-up design: build dimensional data marts for individual business processes first, then integrate them through conformed dimensions shared across marts. Inmon advocates top-down: build a normalized enterprise data warehouse first, then derive dimensional data marts from it. Most modern teams use a Kimball-inspired approach for faster initial delivery and iterative expansion.

Can I use dimensional modeling in a data lake? Yes. Dimensional models work well on top of data lake storage using open table formats like Apache Iceberg and Delta Lake. You can define fact and dimension tables as managed tables and query them with Spark SQL, Trino, or any compatible engine. The medallion architecture (bronze-silver-gold) is often used to organize dimensional models within a data lake.

dbt Guide — Data Warehousing Guide — Analytical SQL Guide