Slowly Changing Dimensions

The previous two lessons built the dimension tables on a silent assumption: the subscriber’s region and type do not change. The dimension kept a single row per subscriber, that row gave the subscriber’s information, and which period of the fact was being looked at never affected it. Every number measured so far stands on this assumption.

Real dimensions do not behave this way. A residential subscriber turns into a commercial one, a subscriber moves to another region, a classification is redone. These changes are not as frequent as fact rows, which is why they are called slowly changing dimensions. Being slow does not make them unimportant: once a dimension row is overwritten, the facts of past periods are also qualified with the new information. This lesson measures that moment and gives the most unsettling result of the course. An overwriting dimension does not slow the query, it makes the answer wrong.

• MF17. The source, the pipeline and the store are fictional; no real data warehouse, data lake, file format or query engine is called. The seed is 20260218, and the measurement does not depend on the run.
• MF18. The change is fictional and is not an accident: starting from the period 2026-07, the residential subscribers in the range A001-A050 are counted as commercial. This is an ordinary classification update.
• MF19. The fact is 2200 rows and is not affected by the change; the only thing that changes is the dimension table.
• MF20. The overwriting dimension keeps a single row per subscriber and stores the last value. The history-keeping dimension keeps one row for each pair of subscriber and period.
• MF21. Cost reads as files x 120 + cells. The per-file constant of 120 is a choice.
• MF22. The definition of the checks was built in the Introduction to Data Engineering course and its procedure is not repeated; here the only question is which one fires.
• MF23. The baseline is the overwriting dimension: no history, dimension rows equal to the number of subscribers.
• MF24. Resolution: the step of a row share is 0.0005. Totals are written as the values themselves, not as shares; a share is printed only for the distribution band.

Building the Change

The first block sets up the shared definition and adds three pieces on top of it. type_change applies the change to the fact rows: in the rows of 2026-07 and after, the type of the relevant subscribers becomes commercial, and in earlier periods it stays residential. That is, the correct history lives inside the fact rows. scd1 produces the overwriting dimension from these rows; because the dictionary carries the subscriber key, the last seen type remains for each subscriber. scd2 produces the history-keeping dimension, and its key is the pair of subscriber and period. total_per_type differs between the two only in which key it looks up with.

# FICTIONAL. Meter readings, pipeline and dimension are defined inside the lesson; no real
# data warehouse, data lake, file format or query engine is called.
SEED, M32 = 20260218, 0xFFFFFFFF


def rng(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


REGION = ["east", "west", "north", "south", "central"]
TYPE = ["residential", "commercial"]
PERIODS = [f"2026-{a:02d}" for a in range(1, 13)]
SUBSCRIBERS = {}
for i in range(200):
    r = rng(SEED + 37 * i)
    SUBSCRIBERS[f"A{i + 1:03d}"] = {"region": REGION[int(r() * 5)], "type": TYPE[int(r() * 2)],
                                    "start": 10000 + int(r() * 60000)}


def generate_source():
    records = []
    for i, (sub, a) in enumerate(SUBSCRIBERS.items()):
        e = a["start"]
        for p in PERIODS:
            r = rng(SEED + 101 * i + 7 * PERIODS.index(p))
            e += 4 + int(r() * 46)
            records.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"],
                            "index": e, "unit": "m3"})
    return records


def s1_screen(records):
    REQUIRED = ("subscriber", "period", "region", "index", "unit")
    keep = [k for k in records if all(a in k for a in REQUIRED)]
    return keep, len(records) - len(keep)


def s2_dedupe(records):
    seen, keep = set(), []
    for k in records:
        key = (k["subscriber"], k["period"])
        if key in seen:
            continue
        seen.add(key)
        keep.append(k)
    return keep, len(records) - len(keep)


def s3_consumption(records):
    table = {(k["subscriber"], k["period"]): k for k in records}
    out, dropped = [], 0
    for k in records:
        i = PERIODS.index(k["period"])
        if i == 0:
            continue
        prev = table.get((k["subscriber"], PERIODS[i - 1]))
        if prev is None:
            dropped += 1
            continue
        try:
            f = int(k["index"]) - int(prev["index"])
        except (TypeError, ValueError):
            dropped += 1
            continue
        out.append(dict(k, m3=f))
    return out, dropped


COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3")
FILE_CONSTANT = 120          # the fixed cost of touching each file , in cells


def rows():
    v, _ = s1_screen(generate_source())
    v, _ = s2_dedupe(v)
    v, _ = s3_consumption(v)
    return v


def write(records, partitioning=()):
    layout = {}
    for k in records:
        key = tuple(k[a] for a in partitioning)
        layout.setdefault(key, []).append(k)
    return layout


def query(layout, partitioning, selected, filters=None, columnar=True):
    filters = {} if filters is None else filters
    pruning = {a: v for a, v in filters.items() if a in partitioning}
    remaining = {a: v for a, v in filters.items() if a not in partitioning}
    files, cells, matching = 0, 0, 0
    read_columns = len(selected) if columnar else len(COLUMNS)
    for key, records in layout.items():
        if pruning:
            pair = dict(zip(partitioning, key))
            if any(pair[a] != v for a, v in pruning.items()):
                continue
        files += 1
        cells += len(records) * read_columns
        matching += sum(1 for k in records if all(k[a] == v for a, v in remaining.items()))
    return {"files": files, "cells": cells, "matching_rows": matching,
            "cost": files * FILE_CONSTANT + cells}


def type_change(records, period="2026-07", last_subscriber="A050"):
    """Residential subscribers between A001..A050 become commercial in this period."""
    new = []
    for k in records:
        if k["subscriber"] <= last_subscriber and k["period"] >= period and k["type"] == "residential":
            new.append(dict(k, type="commercial"))
        else:
            new.append(dict(k))
    return new


def scd1(records):
    """Overwrite: the dimension keeps only the last value."""
    return {k["subscriber"]: k["type"] for k in records}


def scd2(records):
    """Keep history: (subscriber , period) -> type."""
    return {(k["subscriber"], k["period"]): k["type"] for k in records}


def total_per_type(records, dim, dated):
    total = {}
    for k in records:
        t = dim[(k["subscriber"], k["period"])] if dated else dim[k["subscriber"]]
        total[t] = total.get(t, 0) + k["m3"]
    return total

The Cubic Meters Sent to the Wrong Bucket

The second block builds the two dimensions, counts the dimension side and asks the same question of both: what is the total consumption per type.

ROWS = rows()
ACTUAL = type_change(ROWS)
D1, D2 = scd1(ACTUAL), scd2(ACTUAL)
changed = {k["subscriber"] for k in ACTUAL if SUBSCRIBERS[k["subscriber"]]["type"] != k["type"]}
wrong_rows = sum(1 for k in ACTUAL if D1[k["subscriber"]] != k["type"])
T1 = total_per_type(ACTUAL, D1, False)
T2 = total_per_type(ACTUAL, D2, True)
print("fact rows:", len(ACTUAL), " subscribers whose type changed:", len(changed))
print(f"{'dimension':<20}{'rows':>7}{'cells':>7}{'joins':>13}")
print(f"{'overwriting':<20}{len(D1):>7}{len(D1) * 2:>7}{len(ACTUAL):>13}")
print(f"{'history-keeping':<20}{len(D2):>7}{len(D2) * 3:>7}{len(ACTUAL):>13}")
print()
print("overwriting result:", T1, " total", sum(T1.values()))
print("history-keeping result:", T2, " total", sum(T2.values()))
print("moved to the wrong bucket:", T1["commercial"] - T2["commercial"], "m3")
print("wrongly qualified fact rows:", wrong_rows)

fact rows: 2200  subscribers whose type changed: 20
dimension              rows  cells        joins
overwriting             200    400         2200
history-keeping        2200   6600         2200

overwriting result: {'commercial': 35893, 'residential': 22561}  total 58454
history-keeping result: {'commercial': 33145, 'residential': 25309}  total 58454
moved to the wrong bucket: 2748 m3
wrongly qualified fact rows: 100

The result lines are the whole lesson. The correct answer is 33145 in the commercial bucket and 25309 in the residential bucket. The query that works with the overwriting dimension gives 35893 for commercial and 22561 for residential. The 2748 cubic meters between them are not lost; they sit in the wrong bucket. The total of the two buckets is 58454 in both setups, exactly the same.

The mechanism fits in one sentence. The 100 fact rows of the 20 subscribers whose type changed, between 2026-02 and 2026-06, were residential consumption in those periods; because the overwriting dimension keeps only the last value, the query counts these hundred rows as commercial too. The past was requalified with today’s information. The dimension row is not wrong: A007 is really commercial today. What is wrong is the application of today’s truth to the past.

The joins column gives the second half of the claim. 2200 joins are made in both setups. The query that works with the overwriting dimension does not do even one step of extra work; in fact, the dimension it reads is 400 cells against 6600 for the other. The setup that gives the wrong answer is faster and cheaper. This error cannot be found by looking at the query, at the plan or at the duration, because there is no slowdown anywhere.

What No Check Sees

The third block applies the checks defined in the previous course to this setup. All four run on the fact rows: field, type, uniqueness and row count. Then the distribution check compares the two buckets served against the correct answer.

def c_schema(data, fields=("subscriber", "period", "region", "index", "unit")):
    return any(any(a not in k for a in fields) for k in data)


def c_type(data):
    return any(not isinstance(k.get("index"), int) for k in data)


def c_uniqueness(data):
    key = [(k["subscriber"], k["period"]) for k in data]
    return len(key) != len(set(key))


def c_row_count(data, expected, tolerance=0.05):
    return abs(len(data) - expected) / expected > tolerance


def c_distribution(summary, baseline, tolerance=0.25):
    for key, v in summary.items():
        t = baseline.get(key)
        if t and abs(v - t) / max(abs(t), 1) > tolerance:
            return True
    return False


print("checks that run on the fact rows")
for name, result in (("schema", c_schema(ACTUAL)), ("type", c_type(ACTUAL)),
                     ("uniqueness", c_uniqueness(ACTUAL)),
                     ("row_count", c_row_count(ACTUAL, 2200))):
    print(f"  {name:<14}{'fired' if result else 'green'}")
print("fact rows read by the two setups:", len(ACTUAL), "and", len(ACTUAL))
print("total of the two buckets:", sum(T1.values()), "and", sum(T2.values()))
print()
print("distribution check , against the history-keeping result")
for tolerance in (0.25, 0.15, 0.10, 0.05):
    print(f"  tolerance {tolerance:.2f}   {'fired' if c_distribution(T1, T2, tolerance) else 'green'}")
print(f"{'bucket':<12}{'history':>10}{'overwriting':>13}{'deviation':>11}")
for bucket in sorted(T2):
    print(f"{bucket:<12}{T2[bucket]:>10}{T1[bucket]:>13}"
          f"{abs(T1[bucket] - T2[bucket]) / T2[bucket]:>11.4f}")

checks that run on the fact rows
  schema        green
  type          green
  uniqueness    green
  row_count     green
fact rows read by the two setups: 2200 and 2200
total of the two buckets: 58454 and 58454

distribution check , against the history-keeping result
  tolerance 0.25   green
  tolerance 0.15   green
  tolerance 0.10   fired
  tolerance 0.05   fired
bucket         history  overwriting  deviation
commercial       33145        35893     0.0829
residential      25309        22561     0.1086

All four of the four checks are green, and this is not a defect. The checks look at the fact rows, and the fact rows are flawless: 2200 rows, no missing field, no type drift, no duplicate key. The error is not inside the rows but inside the join between the rows and the dimension. No dimension of data quality defined at the row level can see this, because there is no corruption in the place they measure.

The distribution check is the only candidate, and it has two conditions. The first is that it requires a baseline; in this measurement the baseline is the answer of the history-keeping setup, that is, the history that was thrown away. In a setup that chooses the overwriting dimension that baseline does not exist to begin with, because the rows that would produce it were not stored. The second is the band: the deviation is 0.0829 in commercial and 0.1086 in residential. The customary band of 0.25 does not see it; it fires when the band is lowered to 0.10, but at that band the ordinary fluctuation of a healthy pipeline will fire it too.

This is the storage-side counterpart of the previous course’s claim that a served number can be silently wrong, and the difference between the two sides matters. There, the corruption happened on the producer side and a stage of the pipeline swallowed it. Here there is no corruption at all: the source is correct, the pipeline is correct, the rows are correct. The only thing that is wrong is the layout decision, and that decision is how many rows to write to a table.

The Cheap Form of History

The price of the correct answer is visible in the table: the history-keeping dimension carries 2200 rows, the overwriting one 200. Eleven times. This is not the necessary price of keeping history. The history-keeping dimension writes a row for every period, yet nothing changes except for twenty subscribers. The fourth block measures a third form: the dimension writes only the change points, and each row carries the period from which that value is valid.

def scd_interval(records):
    """Validity interval: only the change points are written per subscriber."""
    last, dim = {}, []
    for k in sorted(records, key=lambda k: (k["subscriber"], k["period"])):
        if last.get(k["subscriber"]) != k["type"]:
            dim.append({"subscriber": k["subscriber"], "type": k["type"], "valid_from": k["period"]})
            last[k["subscriber"]] = k["type"]
    return dim


def interval_total(records, dim):
    index = {}
    for s in dim:
        index.setdefault(s["subscriber"], []).append(s)
    total, joins = {}, 0
    for k in records:
        t = None
        for s in index[k["subscriber"]]:
            joins += 1
            if s["valid_from"] <= k["period"]:
                t = s["type"]
        total[t] = total.get(t, 0) + k["m3"]
    return total, joins


INTERVAL = scd_interval(ACTUAL)
T3, joins3 = interval_total(ACTUAL, INTERVAL)
fact = [{"subscriber": k["subscriber"], "period": k["period"], "m3": k["m3"]} for k in ACTUAL]
q_fact = query(write(fact, ()), (), ("subscriber", "period", "m3"))
print(f"{'dimension':<20}{'rows':>7}{'cells':>7}{'cost':>9}{'joins':>13}")
for name, record, selected, join in (
        ("overwriting", [{"subscriber": a, "type": t} for a, t in D1.items()],
         ("subscriber", "type"), len(ACTUAL)),
        ("history-keeping", [{"subscriber": a, "period": d, "type": t} for (a, d), t in D2.items()],
         ("subscriber", "period", "type"), len(ACTUAL)),
        ("validity interval", INTERVAL, ("subscriber", "type", "valid_from"), joins3)):
    q = query(write(record, ()), (), selected)
    print(f"{name:<20}{len(record):>7}{q['cells']:>7}"
          f"{q['cost'] + q_fact['cost']:>9}{join:>13}")
print("fact read the same in all three:", q_fact["cost"], " per-file constant:", FILE_CONSTANT)
print("validity interval result:", T3)
print("equal to the history-keeping result:", T3 == T2)

dimension              rows  cells     cost        joins
overwriting             200    400     7240         2200
history-keeping        2200   6600    13440         2200
validity interval       220    660     7500         2420
fact read the same in all three: 6720  per-file constant: 120
validity interval result: {'commercial': 33145, 'residential': 25309}
equal to the history-keeping result: True

The third row is the way out of the lesson. The validity interval carries 220 rows: the first value of 200 subscribers, plus the second row of the twenty subscribers whose type changed. The result it produces is exactly the same as that of the history-keeping dimension, that is, it is correct. The cost is 7500, only 260 above the 7240 of the overwriting dimension and far below the 13440 of the history-keeping one. The rest of the price shows up in the joins: 2420, because two looks are made for the two-row subscribers.

The rule of the course reads this table: a layout’s number is not the rows it stores but what a query pays to touch it. The three dimension forms share the same fact table, read the same 2200 rows, and each pays the fact read of 6720 cost exactly as it is. The place where they part is the dimension side, and the difference paid there is the price of the correct answer. That price is 260. In this measurement, choosing the overwriting dimension means sending 2748 cubic meters to the wrong bucket to save 260 units of cost, and when it is not written for which query it was chosen, this trade is never visible.

Summary

• When a slowly changing dimension is overwritten, the facts of past periods are qualified with today’s value; when the type of twenty subscribers changes, 100 fact rows fall into the wrong bucket.
• The result is silently made wrong: the commercial total comes out 35893 instead of 33145, 2748 cubic meters go to the wrong bucket, and the total of the two buckets stays exactly the same at 58454.
• The setup that gives the wrong answer is cheaper: joins are 2200 in both setups, and the dimension cells read are 400 against 6600. This error cannot be found by looking at the query, at the plan or at the duration.
• All four of the schema, type, uniqueness and row count checks, which run at the row level, are green, because there is no corruption in the fact rows; the error is inside the join.
• The distribution check is the only candidate, but it both asks for the discarded history as its baseline and does not see at a band of 0.25: the deviation is 0.0829 in commercial and 0.1086 in residential.
• The price of history is the dimension going from 200 to 2200 rows and the cost going from 7240 to 13440; the validity interval gives the same correct answer with 220 rows and a cost of 7500.

Next Step

What was discussed in three lessons was the shape of the table: which field sits in which table, how many tables there are, how many rows the dimension carries. In every measurement the stored cells and the read cells were counted, but how the cell lands on disk was never asked. When a query reads a table, does it take all of the rows or only the columns it selected, by how much does that choice divide the cells read, and do repeated values gain anything on the space side. These questions are independent of the schema: when the same star schema is written in two different formats, it gives the same answer at very different costs. The next lesson measures that format.