Slowly Changing Dimension Type 2 History Rebuilder

What you will learn

• Reconstruct continuous temporal validity ranges from discrete mutation audit logs
• Employ LEAD() partitioned by entity ID to capture subsequent change timestamps
• Handle unbounded current rows using COALESCE with high-water sentinel date 9999-12-31
• Generate boolean is_current indicator flags for fast dimensional filtering and point-in-time joins

What you need first

SELECT log_id, updated_at, LEAD(updated_at) OVER (ORDER BY updated_at) AS next_time FROM customer_audit_log LIMIT 2;

log_id | updated_at          | next_time
-------+---------------------+--------------------
1      | 2023-01-01 00:00:00 | 2023-03-01 00:00:00
4      | 2023-03-01 00:00:00 | 2023-06-01 00:00:00

SELECT 101 AS cust_id, TIMESTAMP '2023-01-01' AS valid_from, TIMESTAMP '2023-06-01' AS valid_to, 0 AS is_current;

cust_id | valid_from          | valid_to            | is_current
--------+---------------------+---------------------+-----------
101     | 2023-01-01 00:00:00 | 2023-06-01 00:00:00 | 0

Architecture and data flow

The data

Customer address audit mutation log with historical change timestamps.

CREATE OR REPLACE TABLE customer_audit_log (
  log_id INT,
  customer_id INT,
  address VARCHAR,
  state VARCHAR,
  updated_at TIMESTAMP
);
INSERT INTO customer_audit_log VALUES
  (1, 101, '12 MG Road', 'Karnataka', TIMESTAMP '2023-01-01 00:00:00'),
  (2, 101, '45 BKC Avenue', 'Maharashtra', TIMESTAMP '2023-06-01 00:00:00'),
  (3, 101, '88 Marine Drive', 'Maharashtra', TIMESTAMP '2023-11-01 00:00:00'),
  (4, 102, '99 Park Street', 'West Bengal', TIMESTAMP '2023-03-01 00:00:00');

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Query discrete mutation audit log

   We start by examining the raw customer_audit_log records sorted by customer_id and updated_at. We observe that customer 101 has three recorded updates across 2023: January 1 in Karnataka, June 1 in Maharashtra, and November 1 in Maharashtra. Customer 102 has a single registration event on March 1. In enterprise data systems, understanding the raw event cadence confirms that change records contain valid timestamps and non-null dimensional attributes before interval reconstruction begins. We verify that each customer has at least one recorded event and that change timestamps advance chronologically. Establishing this baseline data hygiene ensures that downstream window functions calculate non-overlapping validity ranges without encountering null timestamp errors.

   SQLStep 1Copy

   SELECT log_id, customer_id, address, state, updated_at FROM customer_audit_log ORDER BY customer_id, updated_at;

   Show outputGuess what it prints first

   log_id | customer_id | address         | state       | updated_at
   -------+-------------+-----------------+-------------+--------------------
   1      | 101         | 12 MG Road      | Karnataka   | 2023-01-01 00:00:00
   2      | 101         | 45 BKC Avenue   | Maharashtra | 2023-06-01 00:00:00
   3      | 101         | 88 Marine Drive | Maharashtra | 2023-11-01 00:00:00
   4      | 102         | 99 Park Street  | West Bengal | 2023-03-01 00:00:00

   Common mistake

   Sorting by log_id rather than updated_at, which can jumble chronological progression if logs are imported out of order.

   How you will know

   You will see 4 audit log entries ordered cleanly by customer ID and change timestamp.

2. 02

   Attempt naive latest-record-only query

   Wrong turn first: this step shows the mistake before the fix

   A common naive mistake in dimensional reporting is querying only the latest record using MAX(updated_at) or in-place table updates. When an analyst runs a query that selects only the most recent row per customer, historical states are completely wiped out. For customer 101, this naive approach returns only their November address, hiding the fact that they lived in Karnataka during early 2023 and causing historical tax calculations to be completely inaccurate. Overwriting historical attributes or selecting only current rows destroys the temporal audit trail, making point-in-time tax compliance impossible and exposing financial institutions to severe regulatory penalties.

   SQLStep 2Copy

   SELECT customer_id, address, state, MAX(updated_at) AS last_update FROM customer_audit_log GROUP BY customer_id, address, state ORDER BY customer_id;

   Show outputGuess what it prints first

   customer_id | address         | state       | last_update
   ------------+-----------------+-------------+--------------------
   101         | 12 MG Road      | Karnataka   | 2023-01-01 00:00:00
   101         | 45 BKC Avenue   | Maharashtra | 2023-06-01 00:00:00
   101         | 88 Marine Drive | Maharashtra | 2023-11-01 00:00:00
   102         | 99 Park Street  | West Bengal | 2023-03-01 00:00:00

   Common mistake

   Aggregating by customer and address directly, which fails to produce non-overlapping temporal intervals for point-in-time lookups.

   How you will know

   Notice that grouping without window functions fails to preserve the sequential timeline of residential transitions.

3. 03

   Derive next mutation timestamp using LEAD

   To convert discrete mutation timestamps into continuous intervals, we employ the LEAD() window function partitioned by customer_id and ordered by updated_at. LEAD inspects the timestamp of the next chronological change for that specific customer. For row 1, raw_next_time evaluates to 2023-06-01. For the final row of each customer, no subsequent update exists, so LEAD returns NULL, indicating an open active record. By ordering rows chronologically within each customer partition, LEAD establishes the exact moment an address became obsolete.

   SQLStep 3Copy

   SELECT
     customer_id,
     address,
     state,
     updated_at AS valid_from,
     LEAD(updated_at) OVER (
       PARTITION BY customer_id
       ORDER BY updated_at
     ) AS raw_next_time
   FROM customer_audit_log
   ORDER BY customer_id, updated_at;

   Show outputGuess what it prints first

   customer_id | address         | state       | valid_from          | raw_next_time
   ------------+-----------------+-------------+---------------------+--------------------
   101         | 12 MG Road      | Karnataka   | 2023-01-01 00:00:00 | 2023-06-01 00:00:00
   101         | 45 BKC Avenue   | Maharashtra | 2023-06-01 00:00:00 | 2023-11-01 00:00:00
   101         | 88 Marine Drive | Maharashtra | 2023-11-01 00:00:00 | NULL
   102         | 99 Park Street  | West Bengal | 2023-03-01 00:00:00 | NULL

   Common mistake

   Forgetting the PARTITION BY customer_id clause, which causes customer 101 final row to look ahead into customer 102 first timestamp.

   How you will know

   Output shows raw_next_time populated for historical rows and NULL for the active current rows of each customer.

4. 04

   Construct SCD2 validity intervals and current flags

   Next, we replace NULL expiration timestamps with the standard high-water sentinel date '9999-12-31 23:59:59' using COALESCE. A sentinel date ensures that point-in-time date range filters (such as BETWEEN valid_from AND valid_to) evaluate cleanly without requiring awkward OR valid_to IS NULL clauses. We also compute a boolean is_current indicator flag that equals 1 when raw_next_time IS NULL and 0 otherwise. Bounding open-ended records with sentinel dates simplifies range filtering in dimensional joins, while the is_current flag allows analytical queries to filter active records quickly without scanning historical rows.

   SQLStep 4Copy

   WITH ranked AS (
     SELECT
       customer_id,
       address,
       state,
       updated_at AS valid_from,
       LEAD(updated_at) OVER (
         PARTITION BY customer_id
         ORDER BY updated_at
       ) AS raw_next_time
     FROM customer_audit_log
   )
   SELECT
     customer_id,
     address,
     state,
     valid_from,
     COALESCE(raw_next_time, TIMESTAMP '9999-12-31 23:59:59') AS valid_to,
     CASE WHEN raw_next_time IS NULL THEN 1 ELSE 0 END AS is_current
   FROM ranked
   ORDER BY customer_id, valid_from;

   Show outputGuess what it prints first

   customer_id | address         | state       | valid_from          | valid_to            | is_current
   ------------+-----------------+-------------+---------------------+---------------------+-----------
   101         | 12 MG Road      | Karnataka   | 2023-01-01 00:00:00 | 2023-06-01 00:00:00 | 0
   101         | 45 BKC Avenue   | Maharashtra | 2023-06-01 00:00:00 | 2023-11-01 00:00:00 | 0
   101         | 88 Marine Drive | Maharashtra | 2023-11-01 00:00:00 | 9999-12-31 23:59:59 | 1
   102         | 99 Park Street  | West Bengal | 2023-03-01 00:00:00 | 9999-12-31 23:59:59 | 1

   Common mistake

   Setting valid_to to CURRENT_TIMESTAMP instead of a high-water sentinel date, which causes the interval to expire immediately tomorrow.

   How you will know

   The output provides valid_from, valid_to, and is_current across all 4 customer dimensional states.

5. 05

   Execute point-in-time dividend tax audit join

   In the final step, we demonstrate the power of our SCD2 model by running a point-in-time join against a simulated dividend distribution on 2023-07-15. Filtering the reconstructed dimension where the target dividend date falls between valid_from and valid_to returns exactly customer 101 residential state on that day: Maharashtra. This proves that point-in-time tax audits can resolve accurate historical state liabilities without relying on destructive operational tables. Executing temporal joins against reconstructed SCD2 tables gives auditors complete confidence that historical financial transactions are evaluated against the exact attributes that were active when the event occurred.

   SQLStep 5Copy

   WITH scd2 AS (
     WITH ranked AS (
       SELECT
         customer_id,
         address,
         state,
         updated_at AS valid_from,
         LEAD(updated_at) OVER (
           PARTITION BY customer_id
           ORDER BY updated_at
         ) AS raw_next_time
       FROM customer_audit_log
     )
     SELECT
       customer_id,
       address,
       state,
       valid_from,
       COALESCE(raw_next_time, TIMESTAMP '9999-12-31 23:59:59') AS valid_to,
       CASE WHEN raw_next_time IS NULL THEN 1 ELSE 0 END AS is_current
     FROM ranked
   )
   SELECT
     customer_id,
     state,
     address,
     valid_from,
     valid_to
   FROM scd2
   WHERE customer_id = 101
     AND TIMESTAMP '2023-07-15 00:00:00' >= valid_from
     AND TIMESTAMP '2023-07-15 00:00:00' < valid_to;

   Show outputGuess what it prints first

   customer_id | state       | address       | valid_from          | valid_to
   ------------+-------------+---------------+---------------------+--------------------
   101         | Maharashtra | 45 BKC Avenue | 2023-06-01 00:00:00 | 2023-11-01 00:00:00

   Common mistake

   Using <= valid_to instead of < valid_to on boundary timestamps, which can produce duplicate matches when a transaction coincides with a relocation.

   How you will know

   The query isolates exactly one historical record showing customer 101 in Maharashtra on 2023-07-15.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The reconstructed SCD2 table establishes an audit-proof timeline of residential changes for ZenithFin clients. Customer 101 resided in Karnataka from January 1 to June 1, relocated to Maharashtra at BKC Avenue until November 1, and subsequently moved to Marine Drive. Bounding intervals with explicit timestamps enables tax auditors to perform exact point-in-time joins.

The message you would send

Hi Compliance Team, the SCD Type 2 history reconstruction query is complete. The model successfully restores full temporal residency timelines for all clients using immutable audit logs. Tax auditors can now verify historical state liabilities for any dividend distribution date with complete mathematical precision.

Put it on your CV

Resume or LinkedIn bullet

Engineered an immutable Slowly Changing Dimension Type 2 history pipeline in SQL using LEAD() window functions and sentinel expiration dates in DuckDB.

README for GitHub

# Slowly Changing Dimension Type 2 History Rebuilder

A robust SQL pipeline reconstructing immutable temporal audit intervals from discrete mutation logs for regulatory compliance and tax reporting.

## Features
- Chronological validity range derivation using LEAD() partitioned by entity
- High-water sentinel date handling ('9999-12-31 23:59:59') via COALESCE
- Boolean is_current status indicators for fast dimensional queries
- Point-in-time join capabilities resilient to operational table mutations

Keep going

• LessonSQL slowly changing dimensions
• Case studyGrowfolio Portfolio Holding SCD2
• Case studyUpdate Old Rows Add New Rows