Data Engineering

SCD Type 2 for Data Engineers: How a Customer Tier Change Broke Historical Reporting

Imagine reviewing a sales dashboard and discovering that revenue previously attributed to Silver customers is now showing under Gold.

The total revenue hasn’t changed. No transactions have disappeared. Yet the report no longer reflects what happened when those sales were made.

What went wrong?

A customer upgraded from Silver to Gold, and the customer table was updated to show their latest tier. When the reporting query joined historical sales to that table, it assigned every transaction to Gold, including purchases made before the upgrade.

This is a common historical reporting challenge in data warehousing and a classic use case for Slowly Changing Dimension Type 2 (SCD Type 2).

In this article, we’ll use a fictional customer-tier example to reproduce the problem, implement SCD Type 2 in PostgreSQL, fix the reporting query and test the results. The SQL examples and validation results were tested in PostgreSQL 17.

1. The Business Scenario

Imagine a subscription business that groups customers into Bronze, Silver and Gold tiers.

Customer 1001 starts the year in Silver and upgrades to Gold on 1 March 2026. The customer makes three purchases:

sale_id	customer_id	sale_date	amount
501	1001	2026-01-15	£100
502	1001	2026-02-20	£150
503	1001	2026-03-10	£200

The first two purchases happened while the customer was Silver. The third happened after the upgrade. So the correct historical breakdown is:

Customer tier	Revenue
Silver	£250
Gold	£200
Total	£450

Before the upgrade, the customer lookup showed Silver. Afterwards, it was overwritten with Gold. That latest record correctly answers “What tier is this customer in now?” but cannot answer “What tier were they in when each purchase happened?”

2. How the Customer Tier Change Broke Reporting

The business runs a revenue-by-tier report and gets Gold: £450, with no Silver row. All three sales have been assigned to the customer’s latest tier.

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer c
    ON s.customer_id = c.customer_id
GROUP BY

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer c
    ON s.customer_id = c.customer_id
GROUP BY

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer c
    ON s.customer_id = c.customer_id
GROUP BY

The query is valid SQL, but dim_customer only holds the current value, Gold. This is a current-state lookup. It is useful if the question is “How much revenue came from customers who are Gold today?” It is not suitable for “How much revenue was generated while customers were Silver or Gold?”

The important distinction is that correct grand totals do not guarantee correct historical attribution. The £450 total is accurate; the tier breakdown is not.

Set up the sample data

To reproduce the example, run the following statements in a practice PostgreSQL database where these tables do not already exist. They create the latest-state lookup and the sales facts. We’ll create the historical dimension separately.

CREATE TABLE dim_customer (
    customer_id INTEGER PRIMARY KEY,
    customer_tier VARCHAR(20) NOT NULL
);

CREATE TABLE fact_sales (
    sale_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO dim_customer (customer_id, customer_tier)
VALUES (1001, 'Gold');

INSERT INTO fact_sales (
    sale_id, customer_id, sale_date, amount
)
VALUES
    (501, 1001, DATE '2026-01-15', 100.00),
    (502, 1001, DATE '2026-02-20', 150.00),
    (503, 1001, DATE '2026-03-10', 200.00)

CREATE TABLE dim_customer (
    customer_id INTEGER PRIMARY KEY,
    customer_tier VARCHAR(20) NOT NULL
);

CREATE TABLE fact_sales (
    sale_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO dim_customer (customer_id, customer_tier)
VALUES (1001, 'Gold');

INSERT INTO fact_sales (
    sale_id, customer_id, sale_date, amount
)
VALUES
    (501, 1001, DATE '2026-01-15', 100.00),
    (502, 1001, DATE '2026-02-20', 150.00),
    (503, 1001, DATE '2026-03-10', 200.00)

CREATE TABLE dim_customer (
    customer_id INTEGER PRIMARY KEY,
    customer_tier VARCHAR(20) NOT NULL
);

CREATE TABLE fact_sales (
    sale_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO dim_customer (customer_id, customer_tier)
VALUES (1001, 'Gold');

INSERT INTO fact_sales (
    sale_id, customer_id, sale_date, amount
)
VALUES
    (501, 1001, DATE '2026-01-15', 100.00),
    (502, 1001, DATE '2026-02-20', 150.00),
    (503, 1001, DATE '2026-03-10', 200.00)

Run the current-state query above after loading this data. Tested result: Gold = £450.00.

3. Introducing SCD Type 2

SCD Type 2 preserves previous versions of important attributes rather than overwriting them. When the customer upgrades, we close the Silver version and insert a Gold version.

customer_key	customer_id	customer_tier	effective_from	effective_to	is_current
1	1001	Silver	2026-01-01	2026-03-01	false
2	1001	Gold	2026-03-01	NULL	true

customer_id is the stable business key; customer_key is a unique surrogate key for each version. effective_from and effective_to define when that version applied. is_current identifies the latest version.

We use an inclusive start and exclusive end: Silver applies from 1 January until before 1 March; Gold applies from 1 March onwards. NULL means the current record has no known end date.

This example uses whole dates, assuming a tier change takes effect at the beginning of the day. Systems with changes during a day should generally use timestamps and consistent timezone rules.

SCD Type 2 also requires a reliable source of changes. It cannot automatically recreate a previous value and effective date that were permanently overwritten without a log, snapshot or other historical source.

4. Implementing SCD Type 2 in PostgreSQL

Create the history table

CREATE TABLE dim_customer_history (
    customer_key BIGINT
        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    customer_tier VARCHAR(20) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE,
    is_current BOOLEAN NOT NULL,
    CHECK (
        effective_to IS NULL
        OR effective_to > effective_from
    )
)

CREATE TABLE dim_customer_history (
    customer_key BIGINT
        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    customer_tier VARCHAR(20) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE,
    is_current BOOLEAN NOT NULL,
    CHECK (
        effective_to IS NULL
        OR effective_to > effective_from
    )
)

CREATE TABLE dim_customer_history (
    customer_key BIGINT
        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    customer_tier VARCHAR(20) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE,
    is_current BOOLEAN NOT NULL,
    CHECK (
        effective_to IS NULL
        OR effective_to > effective_from
    )
)

The CHECK prevents a version from ending on or before its start date. It does not prevent different versions from overlapping, so we’ll validate that separately.

Insert the original Silver version

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Silver', DATE '2026-01-01', NULL, TRUE
)

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Silver', DATE '2026-01-01', NULL, TRUE
)

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Silver', DATE '2026-01-01', NULL, TRUE
)

Process the upgrade to Gold

Run this transaction once:

BEGIN;

UPDATE dim_customer_history
SET
    effective_to = DATE '2026-03-01',
    is_current = FALSE
WHERE customer_id = 1001
  AND is_current = TRUE
  AND customer_tier = 'Silver'
  AND effective_from < DATE '2026-03-01';

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Gold', DATE '2026-03-01', NULL, TRUE
);

COMMIT

BEGIN;

UPDATE dim_customer_history
SET
    effective_to = DATE '2026-03-01',
    is_current = FALSE
WHERE customer_id = 1001
  AND is_current = TRUE
  AND customer_tier = 'Silver'
  AND effective_from < DATE '2026-03-01';

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Gold', DATE '2026-03-01', NULL, TRUE
);

COMMIT

BEGIN;

UPDATE dim_customer_history
SET
    effective_to = DATE '2026-03-01',
    is_current = FALSE
WHERE customer_id = 1001
  AND is_current = TRUE
  AND customer_tier = 'Silver'
  AND effective_from < DATE '2026-03-01';

INSERT INTO dim_customer_history (
    customer_id, customer_tier, effective_from,
    effective_to, is_current
)
VALUES (
    1001, 'Gold', DATE '2026-03-01', NULL, TRUE
);

COMMIT

The transaction keeps the update and insert together: both commit, or neither does. This is an educational example, not a production-ready change-processing pipeline. In particular, rerunning it can create another Gold version because the insert is unconditional. Production code needs checks for duplicate events, unexpected current states, overlapping dates and concurrent writes.

Verify the versions:

SELECT
    customer_key, customer_id, customer_tier,
    effective_from, effective_to, is_current
FROM dim_customer_history
WHERE customer_id = 1001
ORDER BY

SELECT
    customer_key, customer_id, customer_tier,
    effective_from, effective_to, is_current
FROM dim_customer_history
WHERE customer_id = 1001
ORDER BY

SELECT
    customer_key, customer_id, customer_tier,
    effective_from, effective_to, is_current
FROM dim_customer_history
WHERE customer_id = 1001
ORDER BY

Tested result: two versions: Silver (is_current = false, ending 2026-03-01) and Gold (is_current = true, no end date). In a newly created table, the generated keys will normally be 1 and 2.

5. Fixing the Report with a Point-in-Time Join

Now match each sale to the customer version valid on the sale date:

SELECT
    s.sale_id,
    s.sale_date,
    s.amount,
    c.customer_tier
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
ORDER BY s.sale_date,

SELECT
    s.sale_id,
    s.sale_date,
    s.amount,
    c.customer_tier
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
ORDER BY s.sale_date,

SELECT
    s.sale_id,
    s.sale_date,
    s.amount,
    c.customer_tier
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
ORDER BY s.sale_date,

Tested result:

sale_id	sale_date	amount	customer_tier
501	2026-01-15	£100	Silver
502	2026-02-20	£150	Silver
503	2026-03-10	£200	Gold

The effective-date conditions are the crucial difference. They match each transaction to its historical version, rather than today’s customer record.

Now aggregate by historical tier:

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY c.customer_tier
ORDER BY c.customer_tier DESC

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY c.customer_tier
ORDER BY c.customer_tier DESC

SELECT
    c.customer_tier,
    SUM(s.amount) AS total_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY c.customer_tier
ORDER BY c.customer_tier DESC

Tested result: Silver = £250.00; Gold = £200.00. Total revenue is still £450; the historical attribution is now correct.

Our sales facts contain customer_id, so the query uses that business key and the effective dates. Another warehouse design resolves the relevant customer_key when loading the fact, allowing a direct surrogate-key join. Both approaches require reliable history.

6. Testing Before and After SCD Type 2

We executed the following checks in PostgreSQL 17. The results below are the actual outcomes from our fictional dataset, not hypothetical results.

Compare the before-and-after reports

Customer tier	Before	After
Silver	£0	£250
Gold	£450	£200
Total	£450	£450

The incorrect report didn’t lose revenue. It misclassified £250.

Reconcile the original facts and the join

SELECT
    COUNT(*) AS sales_count,
    SUM(amount) AS total_revenue
FROM

SELECT
    COUNT(*) AS sales_count,
    SUM(amount) AS total_revenue
FROM

SELECT
    COUNT(*) AS sales_count,
    SUM(amount) AS total_revenue
FROM

Tested result: 3 sales; £450.00.

SELECT
    COUNT(*) AS joined_sales_count,
    SUM(s.amount) AS joined_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )

SELECT
    COUNT(*) AS joined_sales_count,
    SUM(s.amount) AS joined_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )

SELECT
    COUNT(*) AS joined_sales_count,
    SUM(s.amount) AS joined_revenue
FROM fact_sales s
JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )

Tested result: 3 joined sales; £450.00. Matching totals and counts are useful, but they don’t prove each sale matched exactly once.

Check for missing or duplicate matches

SELECT
    s.sale_id,
    COUNT(c.customer_key) AS matching_versions
FROM fact_sales s
LEFT JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY s.sale_id
HAVING COUNT(c.customer_key) <> 1

SELECT
    s.sale_id,
    COUNT(c.customer_key) AS matching_versions
FROM fact_sales s
LEFT JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY s.sale_id
HAVING COUNT(c.customer_key) <> 1

SELECT
    s.sale_id,
    COUNT(c.customer_key) AS matching_versions
FROM fact_sales s
LEFT JOIN dim_customer_history c
    ON s.customer_id = c.customer_id
   AND s.sale_date >= c.effective_from
   AND (
       s.sale_date < c.effective_to
       OR c.effective_to IS NULL
   )
GROUP BY s.sale_id
HAVING COUNT(c.customer_key) <> 1

Tested result: 0 rows. Each sale matched exactly one historical version. A count of zero would mean missing history; more than one would mean duplicate matches and potentially inflated revenue.

Check for overlapping historical versions

SELECT
    a.customer_id,
    a.customer_key AS first_version,
    b.customer_key AS second_version
FROM dim_customer_history a
JOIN dim_customer_history b
    ON a.customer_id = b.customer_id
   AND a.customer_key < b.customer_key
   AND (
       b.effective_to IS NULL
       OR a.effective_from < b.effective_to
   )
   AND (
       a.effective_to IS NULL
       OR b.effective_from < a.effective_to
   )

SELECT
    a.customer_id,
    a.customer_key AS first_version,
    b.customer_key AS second_version
FROM dim_customer_history a
JOIN dim_customer_history b
    ON a.customer_id = b.customer_id
   AND a.customer_key < b.customer_key
   AND (
       b.effective_to IS NULL
       OR a.effective_from < b.effective_to
   )
   AND (
       a.effective_to IS NULL
       OR b.effective_from < a.effective_to
   )

SELECT
    a.customer_id,
    a.customer_key AS first_version,
    b.customer_key AS second_version
FROM dim_customer_history a
JOIN dim_customer_history b
    ON a.customer_id = b.customer_id
   AND a.customer_key < b.customer_key
   AND (
       b.effective_to IS NULL
       OR a.effective_from < b.effective_to
   )
   AND (
       a.effective_to IS NULL
       OR b.effective_from < a.effective_to
   )

Tested result: 0 rows. The Silver period ends precisely when the Gold period starts; the ranges don’t overlap.

Check for exactly one current version

SELECT
    customer_id,
    SUM(
        CASE WHEN is_current THEN 1 ELSE 0 END
    ) AS current_version_count
FROM dim_customer_history
GROUP BY customer_id
HAVING SUM(
    CASE WHEN is_current THEN 1 ELSE 0 END
) <> 1

SELECT
    customer_id,
    SUM(
        CASE WHEN is_current THEN 1 ELSE 0 END
    ) AS current_version_count
FROM dim_customer_history
GROUP BY customer_id
HAVING SUM(
    CASE WHEN is_current THEN 1 ELSE 0 END
) <> 1

SELECT
    customer_id,
    SUM(
        CASE WHEN is_current THEN 1 ELSE 0 END
    ) AS current_version_count
FROM dim_customer_history
GROUP BY customer_id
HAVING SUM(
    CASE WHEN is_current THEN 1 ELSE 0 END
) <> 1

Tested result: 0 rows. This catches customers with no current version as well as customers with multiple current versions.

Test the upgrade-date boundary

SELECT
    customer_id,
    customer_tier
FROM dim_customer_history
WHERE customer_id = 1001
  AND DATE '2026-03-01' >= effective_from
  AND (
      DATE '2026-03-01' < effective_to
      OR effective_to IS NULL
  )

SELECT
    customer_id,
    customer_tier
FROM dim_customer_history
WHERE customer_id = 1001
  AND DATE '2026-03-01' >= effective_from
  AND (
      DATE '2026-03-01' < effective_to
      OR effective_to IS NULL
  )

SELECT
    customer_id,
    customer_tier
FROM dim_customer_history
WHERE customer_id = 1001
  AND DATE '2026-03-01' >= effective_from
  AND (
      DATE '2026-03-01' < effective_to
      OR effective_to IS NULL
  )

Tested result: customer 1001, Gold. A sale on 1 March matches the new tier, not Silver.

These checks validate the example, not every possible production scenario. Real pipelines also need to handle late-arriving changes, gaps, corrections and repeated events.

7. Common SCD Type 2 Interview Questions

What’s the difference between SCD Type 1 and Type 2?

Type 1 overwrites an existing value. Type 2 keeps historical versions with effective dates. Type 1 can suit corrections where history isn’t needed; Type 2 suits attributes whose past values matter to reporting.

Why use a surrogate key?

The business key identifies the customer. The surrogate key identifies a specific version of that customer. Customer 1001 therefore has one business key but two historical version keys.

What if the same change arrives twice?

A reliable pipeline should detect a previously processed event and avoid inserting an unnecessary duplicate. This property is called idempotency. Our simple transaction deliberately doesn’t implement that protection.

How do you handle a late-arriving change?

Suppose the business later confirms the Gold upgrade was effective on 20 February, not 1 March. The version dates must be corrected, and affected sales may need to be reprocessed, especially if facts store surrogate keys. A sale loaded late can still match the correct version if its true transaction date and the historical dimension are accurate.

Can SCD Type 2 recover history that was overwritten?

Not on its own. You need a reliable historical source, such as an audit log, event stream or snapshot, to reconstruct the previous tier and when it changed.

Should every changing attribute use Type 2?

No. Preserve history when it supports a real reporting or audit requirement. If only the current value matters, a simpler current-state design may be sufficient.

Common mistakes include joining historical facts to the latest record, filtering on is_current for historical reporting, allowing overlapping date ranges, leaving gaps in history and validating only grand totals.

8. Key Takeaways

Correct totals can hide incorrect attribution. Our original report had £450 in revenue but assigned £250 to the wrong tier.
Current-state and historical reporting answer different questions. Be clear about which one the business needs.
SCD Type 2 preserves versions, but queries must use the right dates. The point-in-time join is what fixes the report.
Validate the history as well as the totals. Check for missing matches, duplicate matches, overlaps and invalid current-version counts.

A report can contain every transaction and still tell the wrong story if its historical context has been lost.

Further Practice

If you’re preparing for a data engineering or analytics interview, being able to explain the approach, write the SQL and validate the results matters as much as knowing the definition of SCD Type 2.

For another useful SQL interview topic, read ROW_NUMBER vs RANK vs DENSE_RANK: What’s the Difference?.

For hands-on exercises, explore our SQL Interview Preparation for Data Engineers & Analytics Professionals workbook.

It includes 50 real-world SQL challenges with worked solutions and explanations.

Back to all articles