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

> Master dimensional data modeling with Slowly Changing Dimensions (SCD) in PostgreSQL. Learn Type 1, Type 2, and Type 3 strategies to preserve historical data effectively.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-09

---

**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.

```sql
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.

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md), you can initialize this environment using the provided **Makefile**:

```bash
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.

```sql
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`.

```sql
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.

```sql
-- 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:

```sql
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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/homework/homework.md) to build Type 2 SCD logic hands-on.