Skip to content

🇮🇳 Independence Day Special Offer – Learn More, Pay Less!

Data Warehousing35 min

SCD Type Simulator

Change a customer's city and watch all three SCD types react at once: Type 1 overwrites and forgets, Type 2 expires the old row and inserts a new version, Type 3 keeps one previous value. Then drag the timeline to query the dimension as of any date.

  • 01

    The change runs as real SQL: an UPDATE that closes the current row, then an INSERT that opens the next version.

  • 02

    end_date 9999-12-31 with is_current = 1 marks the live version. Both exist so you can query either way.

  • 03

    Drag the timeline and the 'as of' query re-runs — that is what history actually buys you.

Make a change

One change, applied to all three SCD types at once, as real SQL.

— → Bengaluru from 2025-10-01

Type 2 — full history

A new row per change; the old one is closed, never touched again.

Run a query to see rows here.

-- Type 2, step 1: close the version that is current
UPDATE dim_customer_scd
SET end_date = date('2025-10-01', '-1 day'),
    is_current = 0
WHERE customer_id = 1
  AND is_current = 1;
-- Type 2, step 2: open a new version with a fresh surrogate key
INSERT INTO dim_customer_scd
  (customer_id, customer_name, city, segment, start_date, end_date, is_current, version)
SELECT customer_id,
       customer_name,
       'Bengaluru',
       segment,
       '2025-10-01',
       '9999-12-31',
       1,
       version + 1
FROM dim_customer_scd
WHERE customer_id = 1
ORDER BY version DESC
LIMIT 1;

Type 1 — overwrite

One row, always current. History is unrecoverable.

Run a query to see rows here.

-- Type 1: overwrite in place. The previous value is gone.
UPDATE dim_customer_type1
SET city = 'Bengaluru',
    updated_on = '2025-10-01'
WHERE customer_id = 1;

Type 3 — one previous value

Enough for current-vs-previous, and no more.

Run a query to see rows here.

-- Type 3: shift the old value into its parallel column
UPDATE dim_customer_type3
SET previous_city = city,
    city = 'Bengaluru',
    effective_date = '2025-10-01'
WHERE customer_id = 1;

Timeline — query the dimension as of any date

This is what history actually buys you: a correct answer to a question about the past.

202420252026
SELECT customer_id, customer_name, city, segment, start_date, end_date, version
FROM dim_customer_scd
WHERE '2025-10-01' BETWEEN start_date AND end_date
ORDER BY customer_id;

Run a query to see rows here.

Rows highlighted in green are on their second version or later — the dimension is returning what was true on 2025-10-01, not what is true today. Type 1 and Type 3 cannot answer this query at all.

Run something and the rows land here.

Booting SQLite