Implementing SCD Type 2 Dimensions in PostgreSQL

Hello! In the previous lesson, you created a PostgreSQL customer dimension that permits multiple rows for one source customer. Its constraints ensure that each row has a sensible validity window, but they do not decide when to close a version or create the next one. This lesson supplies that load logic.

You will implement a Type 2 load for changed, unchanged, and new customers. The result should be repeatable, preserve the surrogate keys already referenced by facts, and leave exactly one current row per customer. We will use the dw.dim_customer table and exclusive valid_to boundary established previously.

What a Type 2 change must preserve

Suppose a customer moves from the West region to the East region on July 1. Updating region_name in place would make a sale from June appear to have occurred in the East. Instead, the old dimension row remains available to June’s facts; a new row, with a new surrogate key, describes the customer from July 1 onward.

Watch the following explanation before looking at the SQL. It makes clear why matching a fact to a customer’s source ID alone becomes unsafe once that ID has multiple dimension versions.

The visual below shows an old phone number ending when its replacement begins.

Our date convention is:

A null valid_to means the version remains open. Closing the West row with valid_to = DATE '2025-07-01' and opening the East row with valid_from = DATE '2025-07-01' leaves neither a gap nor an overlap. is_current identifies the open row efficiently; it does not replace the date range when resolving a historical sale.

Specify the batch contract before writing the load

The following load assumes a current-state batch: one staged row per (source_system, source_customer_id), with an effective_date stating when its attributes became valid. It compares customer_name and region_name, our two tracked attributes. A missing customer from this batch is not interpreted as a deletion; absence may simply mean the source sent a partial batch.

For each staged row, there are three outcomes:

The previous table already enforces that a real current row has a null valid_to, and that a closed row has a non-null valid_to. Add one more PostgreSQL safeguard: at most one current version for each source-qualified customer. The reserved unknown row is excluded.

CREATE UNIQUE INDEX customer_one_current_version
ON dw.dim_customer (source_system, source_customer_id)
WHERE is_current AND customer_key > 0;

This partial unique index does not prohibit historical versions; the existing UNIQUE (source_system, source_customer_id, valid_from) constraint also remains in force. Neither constraint alone prevents all possible overlaps between closed versions. We will check for those after the load.

An effective date should come from a defined source or batch policy, not be substituted silently with CURRENT_DATE. If the source only supplies the latest state and you discover a change today, you cannot infer when during the preceding days the change actually occurred. Likewise, a DATE-based design cannot distinguish two changes within one day; that requirement would call for a finer-grained temporal design.

Implement the PostgreSQL load

We will make the example reproducible with three customers: one changed, one unchanged, and one new. If you are using the database from the previous lesson, the reserved -1 customer is already present. Run the following once to establish ordinary starting versions:

INSERT INTO dw.dim_customer (
    source_system, source_customer_id,
    customer_name, region_name,
    valid_from, valid_to, is_current
)
VALUES
    ('sales_app', 42, 'Customer A', 'West',
     DATE '2025-01-01', NULL, TRUE),
    ('sales_app', 43, 'Customer B', 'Central',
     DATE '2025-01-01', NULL, TRUE);

The load itself is one transaction. Its temporary staging table has a primary key so that duplicate source identities cannot cause ambiguous updates. The temporary scd2_delta table records the differences before any current row is closed. That detail matters: if you first close a row and then search for differences against current rows, the row you need to compare has disappeared from the current set.

BEGIN;

CREATE TEMP TABLE stg_customer_batch (
    source_system TEXT NOT NULL,
    source_customer_id BIGINT NOT NULL,
    customer_name TEXT NOT NULL,
    region_name TEXT NOT NULL,
    effective_date DATE NOT NULL,
    PRIMARY KEY (source_system, source_customer_id)
) ON COMMIT DROP;

INSERT INTO stg_customer_batch (
    source_system, source_customer_id,
    customer_name, region_name, effective_date
)
VALUES
    ('sales_app', 42, 'Customer A', 'East',    DATE '2025-07-01'),
    ('sales_app', 43, 'Customer B', 'Central', DATE '2025-07-01'),
    ('sales_app', 44, 'Customer C', 'South',   DATE '2025-07-01');

CREATE TEMP TABLE scd2_delta ON COMMIT DROP AS
SELECT
    s.source_system,
    s.source_customer_id,
    s.customer_name,
    s.region_name,
    s.effective_date,
    d.customer_key AS old_customer_key
FROM stg_customer_batch AS s
LEFT JOIN dw.dim_customer AS d
    ON d.source_system = s.source_system
   AND d.source_customer_id = s.source_customer_id
   AND d.is_current
WHERE d.customer_key IS NULL
   OR s.customer_name IS DISTINCT FROM d.customer_name
   OR s.region_name IS DISTINCT FROM d.region_name;

IS DISTINCT FROM is PostgreSQL’s null-safe comparison: unlike <>, it returns a definite result when one value is null. Our two tracked attributes happen to be NOT NULL, but the operator makes the intended change-detection rule explicit if nullable attributes are added later. The delta now contains customers 42 and 44, but not unchanged customer 43.

Before modifying history, reject a changed or new version whose effective date conflicts with an existing version. In particular, a new version cannot begin on or before an existing version’s start, or inside a closed version’s interval.

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM scd2_delta AS x
        JOIN dw.dim_customer AS d
          ON d.source_system = x.source_system
         AND d.source_customer_id = x.source_customer_id
         AND d.customer_key > 0
        WHERE x.effective_date <= d.valid_from
           OR (
               d.valid_to IS NOT NULL
               AND x.effective_date < d.valid_to
           )
    ) THEN
        RAISE EXCEPTION
            'Customer batch contains a non-forward effective date';
    END IF;
END;
$$;

For this forward-moving load, close only the current rows identified in the delta. old_customer_key is null for a genuinely new customer, so customer 44 has nothing to close. Then insert one new version for every delta row, letting the identity column generate its surrogate key.

UPDATE dw.dim_customer AS d
SET valid_to = x.effective_date,
    is_current = FALSE
FROM scd2_delta AS x
WHERE d.customer_key = x.old_customer_key
  AND d.is_current;

INSERT INTO dw.dim_customer (
    source_system, source_customer_id,
    customer_name, region_name,
    valid_from, valid_to, is_current
)
SELECT
    x.source_system,
    x.source_customer_id,
    x.customer_name,
    x.region_name,
    x.effective_date,
    NULL,
    TRUE
FROM scd2_delta AS x;

COMMIT;

Keeping the update and insert in one transaction matters: a failure must not leave customer 42 with its old row closed but no replacement. Within this transaction, the partial unique index also prevents an accidental second current row. In an environment where multiple jobs can load the same customer concurrently, coordinate or serialize those jobs as well; the uniqueness constraint will reject a conflict, but it does not schedule competing batches in business-effective-date order.

There is a useful idempotency property here. If you rerun the entire batch transaction with the same values, customers 42, 43, and 44 all match their current rows. scd2_delta is empty, so no versions are closed or inserted. A replay containing a different value for an already-used effective date is rejected rather than silently manufacturing two versions starting that day.

Verify both the history and its use by facts

Inspect the loaded versions, excluding the intentional unknown member:

SELECT
    source_customer_id,
    customer_key,
    region_name,
    valid_from,
    valid_to,
    is_current
FROM dw.dim_customer
WHERE source_system = 'sales_app'
ORDER BY source_customer_id, valid_from;

You should see two rows for customer 42: West from January 1 up to but excluding July 1, and East from July 1 with a null end date. Customer 43 still has just its original Central row. Customer 44 has one South row. The unknown row remains unchanged.

The current-row indicator and validity dates should agree with one another:

SELECT customer_key, source_customer_id
FROM dw.dim_customer
WHERE customer_key > 0
  AND is_current IS DISTINCT FROM (valid_to IS NULL);

This query should return no rows; the previous lesson’s shape constraint also guards this rule. A separate self-join checks the less obvious rule that two historical intervals for one source customer never overlap:

SELECT
    a.source_customer_id,
    a.customer_key AS first_key,
    b.customer_key AS second_key
FROM dw.dim_customer AS a
JOIN dw.dim_customer AS b
  ON b.source_system = a.source_system
 AND b.source_customer_id = a.source_customer_id
 AND b.customer_key > a.customer_key
WHERE a.customer_key > 0
  AND daterange(a.valid_from, a.valid_to, '[)')
      && daterange(b.valid_from, b.valid_to, '[)');

For this batch, that query should also return no rows. PostgreSQL’s daterange treats a null upper bound as unbounded; '[)' matches our inclusive-start, exclusive-end rule. This is a validation query, not an overlap-prevention constraint for every possible future write path.

Finally, connect the load to the upcoming fact load. These two illustrative source lines belong to the same source customer but occurred on opposite sides of the change:

WITH incoming_lines(source_line_id, source_customer_id, sale_date) AS (
    VALUES
        (201, 42, DATE '2025-06-30'),
        (202, 42, DATE '2025-07-01')
)
SELECT
    l.source_line_id,
    l.sale_date,
    d.customer_key,
    d.region_name
FROM incoming_lines AS l
JOIN dw.dim_customer AS d
  ON d.source_system = 'sales_app'
 AND d.source_customer_id = l.source_customer_id
 AND l.sale_date >= d.valid_from
 AND (d.valid_to IS NULL OR l.sale_date < d.valid_to)
ORDER BY l.source_line_id;

Line 201 should resolve to the West version’s surrogate key; line 202 should resolve to the East version’s different surrogate key. Do not replace that date-sensitive condition with d.is_current: it would assign a late-arriving June sale to East. Once resolved, the fact stores the selected customer_key; later customer changes do not require rewriting correctly linked historical facts.

Key takeaways

A PostgreSQL Type 2 load compares incoming attributes with the current source-qualified row, closes changed rows at an explicit effective date, and inserts new rows with new surrogate keys. A single transaction keeps those operations together. The partial unique index protects current-row uniqueness; staging-key uniqueness, forward-date checks, and overlap validation protect different parts of the history.

Most importantly, is_current answers “which version is latest?”, while the validity interval answers “which version described the customer when this sale occurred?” In the next lesson, you will use that distinction to resolve dimension keys while loading atomic facts, including cases where a source reference arrives late.