Data Modeling

Data modeling defines how data is structured, stored, and accessed. A well-designed model makes queries intuitive, performs well at scale, and adapts to changing business requirements.

Dimensional Modeling (Kimball)

Ralph Kimball's dimensional modeling is the standard for data warehouses. It organizes data into facts (measurable events) and dimensions (descriptive attributes).

Star Schema

A central fact table connected to surrounding dimension tables, like a star. This design is optimized for analytical queries with minimal joins.

-- Star Schema Example: E-commerce Sales -- Fact table CREATE TABLE fct_sales ( sale_id          BIGINT PRIMARY KEY, date_key         INT NOT NULL,       -- FK to dim_date product_key      INT NOT NULL,       -- FK to dim_product customer_key     INT NOT NULL,       -- FK to dim_customer store_key        INT NOT NULL,       -- FK to dim_store quantity         INT NOT NULL, unit_price       DECIMAL(10,2), discount         DECIMAL(5,2), total_amount     DECIMAL(12,2) ); -- Dimension tables CREATE TABLE dim_date ( date_key         INT PRIMARY KEY, full_date        DATE NOT NULL, year             SMALLINT, quarter          TINYINT, month            TINYINT, day              TINYINT, day_of_week      VARCHAR(10), is_weekend       BOOLEAN ); 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) ); CREATE TABLE dim_customer ( customer_key     INT PRIMARY KEY, customer_id      VARCHAR(50), full_name        VARCHAR(200), email            VARCHAR(200), city             VARCHAR(100), state            VARCHAR(50), segment          VARCHAR(50) );

Snowflake Schema

An extension of the star schema where dimensions are normalized into sub-dimensions. Reduces redundancy at the cost of more joins.

Data Vault

Data Vault modeling is designed for auditability and flexibility in enterprise data warehouses. It separates data into three constructs:

• Hub - A unique list of business keys (e.g., customer_id, product_code)
• Link - Relationships between hubs (e.g., a customer purchased a product)
• Satellite - Descriptive attributes about hubs or links, with time-based versioning

Normalization (3NF)

Third Normal Form (3NF) eliminates data redundancy by ensuring every non-key column depends only on the primary key. Used in operational databases (OLTP) to maintain consistency.

Modeling for OLAP vs OLTP

Slowly Changing Dimensions (SCDs)

Dimension attributes evolve over time (e.g., a customer changes their address). SCD strategies handle this:

• Type 0 - Never change (retain original values)
• Type 1 - Overwrite - no history preserved
• Type 2 - Add a new row with valid_from/valid_to dates to track full history
• Type 3 - Add a previous_value column (limited history)

Type 2 Example

CREATE TABLE dim_customer_scd2 ( customer_key     INT PRIMARY KEY, customer_id      VARCHAR(50), email            VARCHAR(200), city             VARCHAR(100), valid_from       DATE NOT NULL, valid_to         DATE,           -- NULL = current is_current       BOOLEAN DEFAULT TRUE ); -- Insert a new version when a customer's city changes INSERT INTO dim_customer_scd2 SELECT nextval('customer_seq'), customer_id, email, 'San Francisco', CURRENT_DATE, NULL, TRUE FROM dim_customer_scd2 WHERE customer_id = 'C1001' AND is_current = TRUE; -- Expire the previous version UPDATE dim_customer_scd2 SET valid_to = CURRENT_DATE - 1, is_current = FALSE WHERE customer_id = 'C1001' AND is_current = TRUE;

Resources

• Kimball Group: Dimensional Modeling
• Data Vault Alliance
• dbt: Dimensional Modeling