--- title: 'Slowly Changing Dimensions' source: 'https://academia.sh/en/courses/data-storage-architectures/slowly-changing-dimensions' course: 'Data Storage Architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Compares the result produced by overwriting and history-keeping dimensions when a dimension attribute changes within the period.' - 'Shows that the wrong answer does not slow the query and that none of the checks running at the row level sees it.' - 'Measures the effect on dimension rows and cost of keeping the history with a validity interval.' --- # Slowly Changing Dimensions Measuring how a dimension that keeps no history makes the answer wrong without slowing the query: when the type of twenty subscribers changes in one period, the overwriting dimension qualifies past periods with today's type, 100 fact rows fall into the wrong bucket, and the commercial total comes out 35893 instead of 33145, sending 2748 cubic meters to the wrong bucket; because the total of the two buckets stays the same at 58454, the fact rows never change and the join count stays fixed at 2200, all four of the schema, type, uniqueness and row count checks stay green, and the distribution check does not see it at a band of 0.25; the price of keeping history is the dimension going from 200 to 2200 rows and the cost going from 7240 to 13440, while the validity interval gives the same correct answer with 220 rows and a cost of 7500. 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. ```python # 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. ```python 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. ```python 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. ```python 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.