How to Implement Dimensional Data Modeling with Slowly Changing Dimensions (SCD) in PostgreSQL

Dimensional data modeling with Slowly Changing Dimensions (SCD) in PostgreSQL requires choosing between Type 1 (overwrite), Type 2 (add row), or Type 3 (add column) strategies, with Type 2 being the preferred method for data warehouses because it preserves historical context using surrogate keys and validity timestamps.

Dimensional data modeling organizes analytical data into fact tables and dimension tables, but when descriptive attributes change over time, you need a robust strategy to track history without breaking existing reports. This guide demonstrates how to implement all three SCD types in PostgreSQL using the practical examples and Docker environment provided in the DataExpert-io/data-engineer-handbook repository.

Understanding SCD Types in Dimensional Modeling

Slowly Changing Dimensions describe how to handle updates to descriptive attributes in dimension tables. PostgreSQL supports three standard strategies, each with different trade-offs for historical tracking.

SCD Type 1: Overwrite Existing Data

Type 1 updates overwrite the existing value without preserving history. This approach is suitable for correcting data errors where tracking the old value provides no analytical value.

UPDATE dim_customer 
SET address = '200 Oak Ave' 
WHERE customer_id = 123;

SCD Type 2: Add a New Row (Recommended)

Type 2 is the industry standard for dimensional data modeling. It inserts a new row for each change, using a surrogate key and validity period columns (effective_from, effective_to, is_current) to maintain a complete audit trail. This enables point-in-time analysis and complies with star schema best practices.

SCD Type 3: Add a Previous Value Column

Type 3 preserves only the most recent previous value in a separate column (e.g., address_prev). This limited history approach is rarely used in modern data warehouses but suits scenarios needing only the immediate prior state.

ALTER TABLE dim_customer 
ADD COLUMN address_prev TEXT;

Why SCD Type 2 Is the Standard for Data Warehouses

SCD Type 2 dominates production data warehouses because it satisfies three critical requirements that other types cannot meet:

• Complete historical accuracy: Every state of a dimension is preserved, enabling reports that reflect the world as it was on any specific date.
• Surrogate key stability: Fact tables reference immutable surrogate keys rather than natural keys, ensuring that historical facts remain linked to the correct dimension version.
• Regulatory compliance: Audit requirements often mandate retaining all historical states of master data, which Type 1 destroys and Type 3 limits.

Setting Up the PostgreSQL Environment

The DataExpert-io/data-engineer-handbook repository provides a containerized PostgreSQL environment specifically designed for testing dimensional data modeling patterns. According to the intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md, you can initialize this environment using the provided Makefile:

make up

This command spins up a PostgreSQL container with the necessary schema and seed data. The Makefile also includes make restart and other utilities for resetting your sandbox. The intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md file contains specific assignments for building dimension tables, making it the ideal place to apply the SCD Type 2 patterns described below.

Implementing SCD Type 2 in PostgreSQL

Implementing a robust Type 2 dimension requires four architectural components: a surrogate key, validity timestamps, a current flag, and transactional logic to manage row expiration.

Step 1: Design the Dimension Table Schema

Create the dimension table with a BIGSERIAL surrogate key and validity tracking columns. The UNIQUE constraint on the natural key and effective_from timestamp prevents overlapping periods.

CREATE TABLE dim_customer (
    dim_customer_id   BIGSERIAL PRIMARY KEY,   -- surrogate key
    customer_id       INTEGER NOT NULL,        -- natural key from source system
    name              TEXT,
    address           TEXT,
    effective_from    TIMESTAMP NOT NULL DEFAULT now(),
    effective_to      TIMESTAMP,
    is_current        BOOLEAN NOT NULL DEFAULT true,
    UNIQUE (customer_id, effective_from)        -- prevents duplicate periods
);

Step 2: Insert Initial Dimension Records

When loading the first version of a customer, set effective_from to the current timestamp and leave effective_to as NULL with is_current set to true.

INSERT INTO dim_customer (customer_id, name, address)
VALUES (123, 'Acme Corp', '100 Main St');

Step 3: Handle Attribute Changes

When a source attribute changes, you must perform two operations atomically: expire the existing row and insert the new version. This pattern ensures that fact table foreign keys remain valid while capturing the new state.

-- 1. Close the current version
UPDATE dim_customer
SET effective_to = now(),
    is_current    = false
WHERE customer_id = 123
  AND is_current = true;

-- 2. Insert the new version
INSERT INTO dim_customer (customer_id, name, address, effective_from, is_current)
VALUES (123, 'Acme Corp', '200 Oak Ave', now(), true);

Step 4: Query Historical and Current Data

To retrieve the current version of a dimension, filter on the is_current flag:

SELECT *
FROM dim_customer
WHERE customer_id = 123
  AND is_current = true;

To retrieve the version that was valid on a specific historical date, use a range query on the validity columns:

SELECT *
FROM dim_customer
WHERE customer_id = 123
  AND effective_from <= '2024-04-15'::date
  AND (effective_to IS NULL OR effective_to > '2024-04-15'::date);

Summary

• Dimensional data modeling separates measurable events (facts) from descriptive contexts (dimensions), but attributes in dimension tables change over time.
• SCD Type 2 is the preferred implementation in PostgreSQL because it preserves complete history using surrogate keys and validity periods (effective_from, effective_to, is_current).
• The intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md in the DataExpert-io/data-engineer-handbook provides a Docker-based PostgreSQL environment via make up for testing these patterns.
• Implementing Type 2 requires closing the current row with an UPDATE before inserting the new version, ensuring referential integrity for existing fact table records.
• Query current dimensions using is_current = true and historical versions using date range filters on the validity columns.

Frequently Asked Questions

What is the difference between SCD Type 1 and Type 2?

SCD Type 1 overwrites the existing attribute value with no history retained, while Type 2 inserts a new row with a new surrogate key and validity timestamps to preserve the complete historical record. Type 1 is suitable for correcting errors; Type 2 is required for historical reporting and audit trails.

How do you handle multiple concurrent changes in SCD Type 2?

You should wrap the UPDATE (to expire the current row) and INSERT (to create the new row) in a single database transaction. This ensures atomicity and prevents orphaned current records or gaps in the validity timeline if the process fails mid-operation.

Can you implement SCD Type 2 without a surrogate key?

While technically possible using composite natural keys and validity dates, this is strongly discouraged. Surrogate keys (such as BIGSERIAL in PostgreSQL) ensure that fact table foreign keys remain stable even if the natural key changes, and they improve query performance in star schema joins.

Where can I practice SCD implementations in PostgreSQL?

The DataExpert-io/data-engineer-handbook repository provides a complete PostgreSQL sandbox environment. Navigate to intermediate-bootcamp/materials/1-dimensional-data-modeling/ and run make up to start the container, then complete the assignments in homework/homework.md to build Type 2 SCD logic hands-on.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}