Data Modeling Basics — ERD, Normalization, and Dimensional Modeling

Data Modeling Basics — ERD, Normalization, and Dimensional Modeling

Data engineers build and maintain the infrastructure that powers data pipelines, warehouses, and analytics systems. Data modeling is the foundation of every well-designed data system.

Overview

Entity-Relationship Diagrams (ERD)

ERD Symbols and Notation

ERD Entities and Relationships:

Relationships:

• CUSTOMER ||--o{ ORDER : places (one-to-many)
• ORDER ||--|{ ORDER_ITEM : contains (one-to-many)
• PRODUCT ||--o{ ORDER_ITEM : "ordered in" (one-to-many)

Relationship Types

ERD Design Process

Normalization Deep Dive

Normal Forms with Examples

1NF — Atomic Values (no repeating groups)

2NF — No Partial Dependencies

3NF — No Transitive Dependencies

Normalization vs Denormalization

Dimensional Modeling

Star Schema

Star Schema Design:

Star Schema Relationships:

• FACT_SALES ||--o{ DIM_DATE : "sold on"
• FACT_SALES ||--o{ DIM_PRODUCT : "contains"
• FACT_SALES ||--o{ DIM_STORE : "sold at"
• FACT_SALES ||--o{ DIM_CUSTOMER : "bought by"

Snowflake Schema

Snowflake Schema Design:

Snowflake Schema Relationships:

• FACT_SALES ||--o{ DIM_DATE : "sold on"
• FACT_SALES ||--o{ DIM_PRODUCT : "contains"
• DIM_PRODUCT ||--o{ DIM_CATEGORY : "belongs to"
• DIM_CATEGORY ||--o{ DIM_DEPARTMENT : "part of"
• DIM_STORE ||--o{ DIM_REGION : "located in"

Star vs Snowflake Comparison

Slowly Changing Dimensions (SCD)

SCD Types

SCD Type 2 Implementation

-- SCD Type 2 table design
CREATE TABLE dim_customer_scd2 (
    customer_sk SERIAL PRIMARY KEY,
    customer_id INTEGER,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    segment VARCHAR(50),
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP,
    is_current BOOLEAN DEFAULT TRUE
);

-- Insert initial record
INSERT INTO dim_customer_scd2 (customer_id, first_name, last_name, email, segment, valid_from, is_current)
VALUES (1001, 'John', 'Doe', 'john@example.com', 'Premium', '2024-01-01', TRUE);

-- When customer changes segment, expire old record and insert new
UPDATE dim_customer_scd2
SET valid_to = CURRENT_TIMESTAMP, is_current = FALSE
WHERE customer_id = 1001 AND is_current = TRUE;

INSERT INTO dim_customer_scd2 (customer_id, first_name, last_name, email, segment, valid_from, is_current)
VALUES (1001, 'John', 'Doe', 'john@example.com', 'Enterprise', CURRENT_TIMESTAMP, TRUE);

-- Query current state
SELECT * FROM dim_customer_scd2 WHERE is_current = TRUE;

-- Query history
SELECT * FROM dim_customer_scd2 WHERE customer_id = 1001 ORDER BY valid_from;

SCD Decision Flow

Data Vault Modeling

Best Practices for Data Modeling

MathSummary Takeaways

1. ERDs visualize data relationships — use them to communicate entity structures before writing any SQL.
2. Normalization eliminates redundancy — follow 1NF through 3NF for transactional (OLTP) systems.
3. Dimensional modeling optimizes analytics — star schemas with fact and dimension tables are the standard for data warehouses.
4. SCD Type 2 preserves history — track how dimension attributes change over time using validity windows.
5. Denormalize intentionally — sacrifice normalization for query performance in OLAP workloads.
6. Surrogate keys are preferred — they protect against source system key changes and enable SCD tracking.
7. Data Vault enables auditing — Hubs, Links, and Satellites provide full lineage and historization.
8. Model for the queries you run — the best model depends on access patterns, not theoretical purity.

See Also

• What is Data Engineering — Introduction to data engineering
• SQL Fundamentals — Essential SQL skills
• Advanced SQL — Advanced SQL techniques
• Databases Fundamentals — Relational vs NoSQL databases
• Data Formats — JSON, Parquet, Avro comparison

Practice Exercises

1. ERD design: Create an ERD for a library management system with books, authors, members, loans, and fines.

2. Normalization exercise: Take a denormalized spreadsheet and normalize it to 3NF. Document each step.

3. Star schema design: Design a star schema for a retail sales data warehouse with appropriate fact and dimension tables.

4. SCD implementation: Implement SCD Type 2 for a customer dimension table in PostgreSQL. Write the merge logic.

5. Data Vault: Convert a star schema to a Data Vault model. Compare the number of tables and query complexity.