APPLYDJINN
← ETL and Data Engineering: reliable pipelinesProgress & review ↗

Typorithm · medium

SCD Type 2 and half-open validity intervals

pythonetldata-engineeringindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

SCD Type 2 and half-open validity intervals

0%

Concept and contract

Retain dimension history with unambiguous as-of boundaries and an explicit ordered-change assumption.

A current-state table answers what an entity looks like now. A slowly changing dimension of type two retains earlier attribute values with validity intervals so an as-of query can recover historical state. This fixture uses valid_from inclusive and valid_to exclusive, with NULL marking the current open interval.

The source changes for each identity must arrive in chronological order under this teaching contract. A retroactive change is rejected rather than silently rewriting previously published history. That assumption keeps the mechanism small and honest: handling arbitrary late historical corrections requires a separate interval-reconstruction and reconciliation design.

Mechanism and worked example

The program finds an identity's open row for each incoming change.

The program finds an identity's open row for each incoming change. A changed country closes the prior interval at the new tick and inserts a new open version. An unchanged country creates no redundant interval. Equal-tick contradictions and changes older than the current interval are explicitly rejected.

The as-of query selects valid_from less than or equal to the requested tick and either no valid_to or a requested tick strictly below valid_to. At a transition, the old row stops exactly where the new row starts. The report returns ordered history and the actual as-of rows from SQLite.

Complete worked example

python

import sqlite3

def inspect_example(events, as_of):
    connection = sqlite3.connect('history.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE dimension(id TEXT, country TEXT, valid_from INTEGER, valid_to INTEGER, PRIMARY KEY(id,valid_from))')
        connection.commit()
        with connection:
            for event in events:
                current = connection.execute('SELECT country,valid_from FROM dimension WHERE id=? AND valid_to IS NULL', [event['id']]).fetchone()
                if current and event['tick'] < current[1]:
                    raise ValueError('historical correction needs explicit rebuilding')
                if current and event['country'] == current[0]:
                    continue
                if current and event['tick'] == current[1]:
                    raise ValueError('conflicting change at one instant')
                connection.execute('UPDATE dimension SET valid_to=? WHERE id=? AND valid_to IS NULL', [event['tick'],event['id']])
                connection.execute('INSERT INTO dimension VALUES(?,?,?,NULL)', [event['id'],event['country'],event['tick']])
        history = [list(r) for r in connection.execute('SELECT id,country,valid_from,valid_to FROM dimension ORDER BY id,valid_from')]
        visible = [list(r) for r in connection.execute('SELECT id,country FROM dimension WHERE valid_from <= ? AND (valid_to IS NULL OR ? < valid_to) ORDER BY id',[as_of,as_of])]
        return {'history': history, 'asOf': visible}
    finally:
        connection.close()
Reasoning
The program finds an identity's open row for each incoming change. A changed country closes the prior interval at the new tick and inserts a new open version. An unchanged country creates no redundant interval. Equal-tick contradictions and changes older than the current interval are explicitly rejected.
Download the ETL and Data Engineering computer lab ↗

Focused decision: use the complete example for context

python

SELECT id,country FROM dimension
WHERE valid_from <= ? AND (valid_to IS NULL OR ? < valid_to)
Decision
Count open history versions while preserving half-open validity intervals and rejecting unplanned historical rewrites.
Download the ETL and Data Engineering computer lab ↗

Counterexamples and common mistakes

The controlled bug makes the upper interval boundary inclusive.

The controlled bug makes the upper interval boundary inclusive. At the transition tick, both old and new rows now match, so one entity has ambiguous historical state. This defect can stay hidden when tests query only a time comfortably inside each interval rather than exactly at the change.

Repeated identical attributes should not automatically create another historical version. Conversely, skipping every repeated identity would lose genuine changes. Keep identity, attribute comparison and effective time separate. The example rejects out-of-order input; it does not promise that replaying the entire old source history into an already advanced dimension is a supported operation.

Controlled bug: observe the rejected data contract

python

import sqlite3

def inspect_example(events, as_of):
    connection = sqlite3.connect('history.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE dimension(id TEXT, country TEXT, valid_from INTEGER, valid_to INTEGER, PRIMARY KEY(id,valid_from))')
        connection.commit()
        with connection:
            for event in events:
                current = connection.execute('SELECT country,valid_from FROM dimension WHERE id=? AND valid_to IS NULL', [event['id']]).fetchone()
                if current and event['tick'] < current[1]:
                    raise ValueError('historical correction needs explicit rebuilding')
                if current and event['country'] == current[0]:
                    continue
                if current and event['tick'] == current[1]:
                    raise ValueError('conflicting change at one instant')
                connection.execute('UPDATE dimension SET valid_to=? WHERE id=? AND valid_to IS NULL', [event['tick'],event['id']])
                connection.execute('INSERT INTO dimension VALUES(?,?,?,NULL)', [event['id'],event['country'],event['tick']])
        history = [list(r) for r in connection.execute('SELECT id,country,valid_from,valid_to FROM dimension ORDER BY id,valid_from')]
        visible = [list(r) for r in connection.execute('SELECT id,country FROM dimension WHERE valid_from <= ? AND (valid_to IS NULL OR ? <= valid_to) ORDER BY id',[as_of,as_of])]
        return {'history': history, 'asOf': visible}
    finally:
        connection.close()
Reasoning
The controlled bug makes the upper interval boundary inclusive. At the transition tick, both old and new rows now match, so one entity has ambiguous historical state. This defect can stay hidden when tests query only a time comfortably inside each interval rather than exactly at the change.
Download the ETL and Data Engineering computer lab ↗

Independent practice

Add openVersions, the database count of rows whose valid_to remains NULL.

Add openVersions, the database count of rows whose valid_to remains NULL. Keep the half-open query, retained history and chronological rejection behavior unchanged. The new count should represent current open dimension rows after all accepted changes, not the number of events or countries in the input.

Locate the final readback query and add a separate aggregate over the dimension table. Use IS NULL and preserve integer output. Run the transition-boundary case, repeated-attribute case, empty input and rejected historical change. An independent count cannot compensate for an ambiguous as-of query, so both observations must continue passing together.

Step 1: Count open history versions while preserving half-open validity intervals and rejecting unplanned historical rewrites. Step 2: Edit the owned exercise file and run verify.py --practice etl-scd. Preserve caller inputs and the independently declared failure cases.

Practice starter: solve before reading the solution

python

import sqlite3

def inspect_example(events, as_of):
    connection = sqlite3.connect('history.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE dimension(id TEXT, country TEXT, valid_from INTEGER, valid_to INTEGER, PRIMARY KEY(id,valid_from))')
        connection.commit()
        with connection:
            for event in events:
                current = connection.execute('SELECT country,valid_from FROM dimension WHERE id=? AND valid_to IS NULL', [event['id']]).fetchone()
                if current and event['tick'] < current[1]:
                    raise ValueError('historical correction needs explicit rebuilding')
                if current and event['country'] == current[0]:
                    continue
                if current and event['tick'] == current[1]:
                    raise ValueError('conflicting change at one instant')
                connection.execute('UPDATE dimension SET valid_to=? WHERE id=? AND valid_to IS NULL', [event['tick'],event['id']])
                connection.execute('INSERT INTO dimension VALUES(?,?,?,NULL)', [event['id'],event['country'],event['tick']])
        history = [list(r) for r in connection.execute('SELECT id,country,valid_from,valid_to FROM dimension ORDER BY id,valid_from')]
        visible = [list(r) for r in connection.execute('SELECT id,country FROM dimension WHERE valid_from <= ? AND (valid_to IS NULL OR ? < valid_to) ORDER BY id',[as_of,as_of])]
        return {'history': history, 'asOf': visible}
    finally:
        connection.close()

# Independent task: Count open history versions while preserving half-open validity intervals and rejecting unplanned historical rewrites.
Reasoning
Count open history versions while preserving half-open validity intervals and rejecting unplanned historical rewrites.
Download the ETL and Data Engineering computer lab ↗
  • Hint 1: Add openVersions, the database count of rows whose valid_to remains NULL. Keep the half-open query, retained history and chronological rejection behavior unchanged. The new count should represent current open dimension rows after all accepted changes, not the number of events or countries in the input.
  • Hint 2: Locate the final readback query and add a separate aggregate over the dimension table. Use IS NULL and preserve integer output. Run the transition-boundary case, repeated-attribute case, empty input and rejected historical change. An independent count cannot compensate for an ambiguous as-of query, so both observations must continue passing together.

Solution, tests and trade-offs

The solution observes open intervals without changing history construction.

The solution observes open intervals without changing history construction. Under the declared contract, each known identity has one current row while earlier rows have closed upper bounds. Exact history assertions and boundary queries establish more than a final row count would establish on its own.

Production dimensions often need surrogate keys, source identifiers, delete handling and rules for corrections that arrive after reports have been published. A fact table's relationship to historical dimension versions also needs a declared time field. Do not choose the latest dimension row for every historical fact simply because that join is easier to write.

Practice solution and acceptance checks

python

import sqlite3

def inspect_example(events, as_of):
    connection = sqlite3.connect('history.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE dimension(id TEXT, country TEXT, valid_from INTEGER, valid_to INTEGER, PRIMARY KEY(id,valid_from))')
        connection.commit()
        with connection:
            for event in events:
                current = connection.execute('SELECT country,valid_from FROM dimension WHERE id=? AND valid_to IS NULL', [event['id']]).fetchone()
                if current and event['tick'] < current[1]:
                    raise ValueError('historical correction needs explicit rebuilding')
                if current and event['country'] == current[0]:
                    continue
                if current and event['tick'] == current[1]:
                    raise ValueError('conflicting change at one instant')
                connection.execute('UPDATE dimension SET valid_to=? WHERE id=? AND valid_to IS NULL', [event['tick'],event['id']])
                connection.execute('INSERT INTO dimension VALUES(?,?,?,NULL)', [event['id'],event['country'],event['tick']])
        history = [list(r) for r in connection.execute('SELECT id,country,valid_from,valid_to FROM dimension ORDER BY id,valid_from')]
        visible = [list(r) for r in connection.execute('SELECT id,country FROM dimension WHERE valid_from <= ? AND (valid_to IS NULL OR ? < valid_to) ORDER BY id',[as_of,as_of])]
        return {'history': history, 'asOf': visible, 'openVersions': sum(r[3] is None for r in history)}
    finally:
        connection.close()
Reasoning
The solution observes open intervals without changing history construction. Under the declared contract, each known identity has one current row while earlier rows have closed upper bounds. Exact history assertions and boundary queries establish more than a final row count would establish on its own.
Download the ETL and Data Engineering computer lab ↗

Interview reasoning and next step

Explain why the interval is half-open and what happens at the exact transition tick.

Explain why the interval is half-open and what happens at the exact transition tick. Then state the ordered-change assumption before proposing a backfill. A source event that corrects last month's country needs a different workflow from an ordinary next change arriving in chronological order.

For a stretch, design an owned interval validator that reports overlap, gaps and multiple open rows per identity. Add a separate retroactive-rebuild experiment only after defining how affected facts and published reports will be reconciled. Keep that extension distinct from the baseline's deliberately rejected out-of-order case.

Optional stretch — after the core task works: Validate interval overlap and multiple open versions before designing a separate retroactive rebuild.

Computer practice: https://applydjinn.com/typorithm/etl-data-engineering-lab-20261107.zip. Use an isolated Python 3.13 environment, install requirements.txt and run verify.py. Edit exercises/etl-scd.py and run --practice etl-scd. Each case owns its files and database; Parquet uses the pinned DuckDB engine. The recovery probe terminates a separate interpreter and reopens the SQLite file. The app retains static examples, prepared results and questions without an editor or code execution. Use the course Download button once to save every lesson and illustration; opening an unsaved course does not automatically download it.

Retain dimension history with unambiguous as-of boundaries and an explicit ordered-change assumption.