--- title: 'Data Storage Architectures' source: 'https://academia.sh/en/courses/data-storage-architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' lessons: 12 outcomes: - 'Compares the data warehouse, data lake and lakehouse approaches on the same data by read cost and detection lag' - 'Measures what dimensional modeling decisions cost in joins and shows how a dimension that keeps no history makes the answer wrong' - 'Lowers query cost with partitioning, file layout and columnar read decisions and counts where the small file problem begins' --- # Data Storage Architectures Counting where the same rows sit and in what layout, and what that costs a query: the rows schema enforcement rejects and the cells read in return, writing in a raw layout being cheap and reading expensive, and which corruption cannot be caught at write time, the detection lag that changes when schema enforcement moves to read time, the rows and the inconsistency risk a domain-oriented subset multiplies, the party making the layout decision when ownership is distributed and the number of layouts a query touches, the entries looked at to find an asset with and without a catalog, and the catalog going stale, the cells and joins separating facts from dimensions saves, 2200 joins and 212 dimension rows in the star against 4400 and 217 in the snowflake with exactly the same result, the overwriting dimension making the answer wrong without slowing the query and sending 2748 cubic meters to the wrong bucket, 400 rows columnar against 1400 cells row-oriented when two columns are selected and the point where the gap closes, the cost rising from 4520 to 11000 on a filter outside the partition key, and fine partitioning raising it from 520 to 1600 even on a query that prunes, and what moving to the archive gains and the question it makes impossible to answer retroactively. ## Storage Approaches Counting what storing the same data in four different approaches costs on the write and read sides: the row enforcement rejects and the cells a query reads in schema-conforming storage, writing being cheap and reading expensive in raw storage and which class of corruption cannot be caught at write time at all, comparing the detection lag with the same unit when the raw layer is kept and schema enforcement moves to read time, the query cost and the number of places the same row stands when a subset belonging to a region is written separately, the number of parties making the layout decision when ownership is distributed and the number of layouts an end-to-end query touches, and which results are sensitive to the per-file constant when it is swept from 60 to 480, the number of entries looked at to find an asset with and without a catalog and how many entries of the catalog now point to a layout that no longer exists. ### Data Warehouse Source: https://academia.sh/en/courses/data-storage-architectures/data-warehouse Counting what a query pays to touch the layout that the 2200 rows leaving the pipeline are written into: on the unpartitioned, row-oriented baseline a period-filtered query costs 15400 cells and 15520 cost, 4400 cells and 4520 cost when only the selected columns are read, and 400 cells and 520 cost under period partitioning. The same layout gives 5720 instead of 4520 on a filter outside the partition key, so a layout gains for one query and loses for another. Schema enforcement on write catches two of the six corruption classes and rejects 400 rows, yet in all 400 of the rejected rows the measurement is still intact; the schema throws away the row, not the number. The per-file constant of 120 is a choice: the 4000-cell gain on the period query is independent of the constant, while the 1200 loss on the other query is entirely the constant's doing and equals ten files. The Introduction to Data Engineering course built a pipeline in ten lessons and counted it. The 2400 meter readings of the fictional measurement network passed through five stages, 2200 rows remained at the output of the consumption stage, the summary shrank to 55 cells and the serving to 5 values; then the number of stages each of the six corruption classes needed before it was caught was measured. All of this was about the rows the pipeline **carries**. None of it asked where those 2200 rows **sit**, in which layout they are written, and what that layout makes a query pay. The question is not idle: the same 2200 rows can be put on disk in very different shapes, and the shape changes a query's cost many times over. This lesson makes that cost measurable. It builds a **storage layout**, counts what a query pays to touch it, and writes down the first layout decision, **enforcing the schema on write**, from the side it adds and the side it costs. The course's rule comes from here and runs for twelve lessons: **the number of a storage layout is not the rows it stores but what a query pays to touch it; a layout whose query is not written down is counted as unmeasured.** - **SA1.** The code is a **fiction**; no real warehouse product, file format, catalog or query engine is called, and no product, vendor or format name is written. Approaches are named by **class**; SQL is a language and is free. The source and the pipeline are taken over from the Introduction to Data Engineering course, seed **20260218**. - **SA2.** The source is **200 subscribers x 12 periods = 2400 readings**; the pipeline outputs **2200** rows, because each subscriber's first period has no consumption difference by definition. - **SA3.** A **layout** is which file a row falls into. `write` distributes rows by the **partition key**; if the key is empty, a single file results. - **SA4.** The cost of a query is three numbers: **files touched**, **cells read** and **cost = files x 120 + cells**. If the filter is on the partition key, files are pruned; otherwise every file is touched. - **SA5. The per-file constant of 120 is a choice**; every result sensitive to it is written together with the value of the constant. - **SA6.** The baseline is the **unpartitioned, row-oriented, history-free** layout; only the first of these three axes is varied in this lesson. - **SA7.** The six corruption classes are taken from the Introduction to Data Engineering course as **input**, not derived again: `late_arrival`, `duplicated`, `schema_changed`, `unit_changed`, `missing_partition`, `type_drift`. None of them is an attack; all are producer-side accidents. - **SA8.** The procedure of partitioning, indexing and query planning is **not repeated**; it was measured in the Data Modeling and Relational Theory and Query Optimization courses. Only **cost at the file level** is added. - **SA9.** Resolution: on 2200 rows the step of the row share is **0.0005**, on at most 110 files the step of the file share is **0.0091**. **Cost is not a share**; the value itself is written. #### Layout, Files and Cost A **data warehouse** is the storage class for schema-conforming structured data. Two decisions define it. The first is **schema enforcement on write**: to enter the warehouse, a row must fit its fields and types to a schema declared in advance. The second is that **the layout is chosen in advance**: which file a row falls into is decided during the write, not during the read. The second decision is measurable, because the cost of a query follows directly from it. The code below builds the pipeline, lowers its output into a layout and measures two queries on three layouts. Both select the same two columns; query A filters on `period`, query B on `type`. ```python # FICTIONAL storage layout -- no real warehouse product, file format or query # engine is called. Source: FICTIONAL meter readings, seed 20260218. 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): """A row that lacks a required field is dropped.""" 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): """Consumption from the period difference. Without a previous period the row cannot be computed.""" 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 # ---- STORAGE LAYOUT: write , query , cost COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3") FILE_CONSTANT = 120 # fixed cost of touching one file , in cells def rows(): v, _ = s1_screen(generate_source()) v, _ = s2_dedupe(v) v, _ = s3_consumption(v) return v def write(records, partitioning=()): """Distributes the records into files by the partition key.""" 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, filter_=None, columnar=True): """Filter: {field: value}. Fields in the partition key prune files , other fields make the whole file be read.""" filter_ = {} if filter_ is None else filter_ pruning = {a: v for a, v in filter_.items() if a in partitioning} remaining = {a: v for a, v in filter_.items() if a not in partitioning} files, cells, matched = 0, 0, 0 columns_read = len(selected) if columnar else len(COLUMNS) for key, record in layout.items(): if pruning: match = dict(zip(partitioning, key)) if any(match[a] != v for a, v in pruning.items()): continue files += 1 cells += len(record) * columns_read matched += sum(1 for k in record if all(k[a] == v for a, v in remaining.items())) return {"files": files, "cells": cells, "matching_rows": matched, "cost": files * FILE_CONSTANT + cells} records = rows() baseline = write(records, ()) partitioned = write(records, ("period",)) SELECTED, MEASURED = ("region", "m3"), {} print("pipeline output rows", len(records), " selected columns", len(SELECTED), "/", len(COLUMNS)) print("layout files avg.rows A.files A.cells A.cost B.cost") for name, d, b, col in [("unpartitioned/row-wise", baseline, (), False), ("unpartitioned", baseline, (), True), ("period", partitioned, ("period",), True)]: a = query(d, b, SELECTED, {"period": "2026-07"}, columnar=col) v = query(d, b, SELECTED, {"type": "commercial"}, columnar=col) MEASURED[name] = (a, v) print(f"{name:<22}{len(d):>6}{len(records) / len(d):>11.1f}" f"{a['files']:>9}{a['cells']:>9}{a['cost']:>11}{v['cost']:>11}") ``` ``` pipeline output rows 2200 selected columns 2 / 7 layout files avg.rows A.files A.cells A.cost B.cost unpartitioned/row-wise 1 2200.0 1 15400 15520 15520 unpartitioned 1 2200.0 1 4400 4520 4520 period 11 200.0 1 400 520 5720 ``` The table sets up the course's **triple**: from here on the same three numbers stand side by side in every lesson. The **layout-free baseline** is the first row: one file, row-oriented read, 15400 cells for query A and **15520 cost**. The **layout setup** is the last row: eleven files by the `period` key, 400 cells for query A and **520 cost**. The **cost** in this lesson has two items: the cost of query B and the rejected rows that will be measured shortly. The middle row separates two axes. Reading only the two selected columns brings the cells from 15400 down to 4400; this is a gain of the **read format**, measured separately in a later lesson. Partitioning is a different axis: the whole drop from 4400 to 400 comes from **partition pruning**. Because the filter is on the `period` field and the layout is built on the `period` key, the query never opens ten of the eleven files. The last column is the first appearance of the course's **second claim**: **a layout also corrupts.** The same `period` layout gives **5720** for query B, whose filter is on `type`; the unpartitioned baseline finishes it at **4520**. Partitioning cannot prune any file here, because `type` is not in the key; the cells read are 4400 in both layouts, but each of the eleven files is touched separately. A layout gains for one query and loses for another, and both results stand in the same table. The course's rule says exactly this: **a layout whose query is not written down is counted as unmeasured.** #### The Row That Schema Enforcement Rejects When the schema is enforced on write, the warehouse gains a **gate**: a row that does not conform never enters the store. What it adds and what it costs becomes visible when the six corruption classes are driven at it. ```python # ---- FICTIONAL corruption classes. None of them is an attack; all are # producer-side accidents that happen in everyday operation. CORRUPTIONS = ["late_arrival", "duplicated", "schema_changed", "unit_changed", "missing_partition", "type_drift"] def corrupt(records, cls, period="2026-07"): """Corrupts a single period , the remaining periods stay intact.""" out, counter = [], 0 for k in records: if k["period"] != period: out.append(k) continue if cls == "late_arrival": if k["subscriber"] <= "A040": # 40 subscribers never arrived this period counter += 1 continue out.append(k) elif cls == "duplicated": out.append(k) if k["subscriber"] <= "A060": # 60 records arrived twice out.append(dict(k)) counter += 1 elif cls == "schema_changed": y = dict(k) y["index_value"] = y.pop("index") # field name changed counter += 1 out.append(y) elif cls == "unit_changed": y = dict(k) y["index"] = k["index"] * 1000 # liters instead of m3 y["unit"] = "m3" # LABEL DID NOT CHANGE , silent corruption counter += 1 out.append(y) elif cls == "missing_partition": if k["region"] == "north": # one region's partition was never written counter += 1 continue out.append(k) elif cls == "type_drift": y = dict(k) y["index"] = str(k["index"]) # number turned into text counter += 1 out.append(y) return out, counter # ---- SCHEMA IS ENFORCED ON WRITE: a row that does not conform is never written SCHEMA = {"subscriber": str, "period": str, "region": str, "type": str, "index": int, "unit": str} def schema_violation(k): for a, t in SCHEMA.items(): if a not in k: return "missing_field" if not isinstance(k[a], t): return "wrong_type" return None def write_with_schema(records): accepted, rejected = [], {} for k in records: n = schema_violation(k) if n: rejected[n] = rejected.get(n, 0) + 1 continue accepted.append(k) return accepted, rejected def measurement_recoverable(k): """Is the rejected row's measurement still there: renamed or turned into text.""" d = k.get("index", k.get("index_value")) try: int(d) return True except (TypeError, ValueError): return False source = generate_source() print(f"{'class':<19}{'arrived':>7}{'corrupted':>11}{'rejected':>12} {'reason':<15}{'written':>8}") total_rejected, total_intact, caught = 0, 0, 0 for s in ["clean"] + CORRUPTIONS: incoming, n = (source, 0) if s == "clean" else corrupt(source, s) accepted, rejected = write_with_schema(incoming) r = sum(rejected.values()) reason = "-" if not rejected else "/".join(sorted(rejected)) total_rejected += r total_intact += sum(1 for k in incoming if schema_violation(k) and measurement_recoverable(k)) caught += 1 if r else 0 print(f"{s:<19}{len(incoming):>7}{n:>11}{r:>12} {reason:<15}{len(accepted):>8}") print(f"rejected total {total_rejected} measurement intact {total_intact} " f"classes caught {caught}/{len(CORRUPTIONS)}") ``` ``` class arrived corrupted rejected reason written clean 2400 0 0 - 2400 late_arrival 2360 40 0 - 2360 duplicated 2460 60 0 - 2460 schema_changed 2400 200 200 missing_field 2200 unit_changed 2400 200 0 - 2400 missing_partition 2354 46 0 - 2354 type_drift 2400 200 200 wrong_type 2200 rejected total 400 measurement intact 400 classes caught 2/6 ``` What the gate **adds** is read off the first row: no row is rejected in the clean source, so schema enforcement does not obstruct sound data. Two classes get caught. `schema_changed` renamed a field, so 200 rows are turned back as a **missing field**; `type_drift` turned a number into text, so 200 rows are turned back as a **wrong type**. Because the fields and types of every stored row are known, the query in the previous section can select its two columns by name and finish with 400 cells: **what schema enforcement adds is the right to shrink the cells a query reads.** What it **costs** comes in two items. The first is coverage: **four** of the six classes pass the gate unaffected. `late_arrival` and `missing_partition` **reduce** the rows, but no nonconforming row exists; a schema cannot see what did not arrive. `duplicated` writes a conforming row a second time, because a schema asks about fields and types, not uniqueness. `unit_changed` is the most dangerous: it multiplies the value by a thousand and leaves the unit label as `m3`, so it **conforms to the schema perfectly**. The second item is the counterpart of the course's second claim: **a layout also corrupts.** In **all 400 of the 400 rejected rows the measurement stands intact**: under the name `index_value` in the `schema_changed` rows, as text in the `type_drift` rows, convertible to an integer in both. The gate throws away the **row**, not the **number**. Here a layout decision does not raise data quality; it **destroys** recoverable data, silently, because the rejected-row count appears in no query unless written down separately. #### The Per-File Constant Is a Choice The number 120 in the cost formula is not a measured quantity but **a choice of the model**: it stands for the cost, in cells, of touching each file. Being a choice, its effect on each derived result must be written down separately. ```python def cost(r, constant): return r["files"] * constant + r["cells"] (A_b, B_b), (A_p, B_p) = MEASURED["unpartitioned"], MEASURED["period"] print(" const A.base A.layout A.gain B.base B.layout B.gain") for s in (0, 120): print(f"{s:>6}{cost(A_b, s):>9}{cost(A_p, s):>11}{cost(A_b, s) - cost(A_p, s):>10}" f"{cost(B_b, s):>9}{cost(B_p, s):>11}{cost(B_b, s) - cost(B_p, s):>10}") print("touched files difference A", A_p["files"] - A_b["files"], " B", B_p["files"] - B_b["files"]) ``` ``` const A.base A.layout A.gain B.base B.layout B.gain 0 4400 400 4000 4400 4400 0 120 4520 520 4000 4520 5720 -1200 touched files difference A 0 B 10 ``` The two rows separate the results cleanly. The **4000 cells that partitioning gains** for query A are independent of the constant: the gain is the same at zero and at one hundred twenty, because both layouts touch a single file and the difference comes entirely from the cells read. The **loss of 1200 units** on query B is, by contrast, **entirely the constant's doing**: at zero there is no loss, and at one hundred twenty the loss is ten files times one hundred twenty. The "a layout also corrupts" finding depends on the constant being **positive**, and its size is proportional to it. Sweeping the constant over several values to see which results change order is measured in a later lesson of the course. No number in the table depends on a run. The pipeline is deterministic, `random` is not used, and every cell can be derived without running anything: the rows leaving the pipeline are 200 subscribers x 11 periods = **2200**; the `period` layout has one file per period with computable consumption, **11**, so 2200 / 11 = **200** rows per file; an unpartitioned read of two columns is 2200 x 2 = **4400** cells; a pruning query reads 200 x 2 = **400**. Derivability is what makes the measure sufficient: later differences between layouts come from the layout decision, not from run-to-run variation. #### Summary - A storage layout is which file a row falls into; its cost is three numbers: files touched, cells read and cost = files x 120 + cells. The per-file constant of 120 is a choice. - The same query gives 15400 cells and 15520 cost on the unpartitioned, row-oriented baseline, 4400 cells and 4520 cost when only the selected columns are read, and 400 cells and 520 cost under `period` partitioning. - The same `period` layout gives 5720 instead of 4520 for query B, whose filter is outside the key: a layout gains for one query and loses for another, and this loss comes entirely from the per-file constant. - Schema enforcement on write catches two of the six corruption classes and rejects 400 rows; `late_arrival`, `duplicated`, `missing_partition` and `unit_changed` pass unaffected. - In all 400 of the 400 rejected rows the measurement stays intact: the schema gate throws away the row, not the number. - The course's rule: the number of a layout is not the rows it stores but what a query pays to touch it; a layout whose query is not written down is counted as unmeasured. #### Next Step The warehouse in this lesson held the gate tight and in return gave the query a cheap read. The cost of the gate was measured as well: four corruption classes pass anyway, and the 400 rejected rows never enter the store even though their measurement is recoverable. A natural question follows: what if the gate had never been built, and every incoming row were written as it is, with no schema enforced. What would the write side gain, and what would the read side pay? The next lesson measures this approach, the **data lake**, by the contrast of two numbers: how many cells the same query reads on the raw layout, and how many on the schema-conforming one. The second half of the table will be more uncomfortable, because with the gate removed, the corruption class that is **never** caught at write time is counted as well. ### Data Lake Source: https://academia.sh/en/courses/data-storage-architectures/data-lake Counting what storing raw data as it is gives the write side and costs the read side: the lake writes 2400 rows into 14400 cells without any transformation, while the warehouse processes 7200 rows to write 2200 rows into 15400 cells; the same period query gives 14400 cells and 14520 cost on the lake and 400 cells and 520 cost on the warehouse, so the 1000 cells the lake gains on write are paid back as 14000 cells in a single query. Of the six corruption classes only two can be seen by looking at a single row; two need the whole set and two need the aggregate, so no row gate at write time can see four of them, and a unit change is invisible in both the row and the set scope. Two consumers reading the same lake layer find 1800 and 2200 rows, 45 and 55 aggregate cells, 48004 and 58454 cubic meters. The previous lesson defined the data warehouse by two decisions: the schema is enforced on write and the layout is chosen in advance. What the gate adds was measured, and the query finished with 400 cells. Its cost was measured as well: four of the six corruption classes pass the gate unaffected, and the 400 rejected rows never enter the store even though their measurement is recoverable. This lesson measures the opposite decision. A **data lake** is the storage class that keeps incoming data **as it is**, enforcing no schema and performing no transformation. To say there is no schema would be wrong; the schema is **deferred to read time**, and the reading side carries it. The deferral has two sides, and both are countable: **writing gets cheaper, reading gets more expensive.** The measure of the lesson is the contrast of these two numbers; its second half is more uncomfortable, because it counts **which corruption class is never seen at write time**. - **SA10.** The code is a **fiction**; no lake product, file format, catalog or query engine name is written. The source and the pipeline are taken over from the Introduction to Data Engineering course, seed **20260218**. - **SA11.** On the raw layer the **field set is unknown**. For this reason columns cannot be selected and partitions cannot be pruned while reading: **all fields** of every row are read, and a single file is touched. - **SA12.** The warehouse side is the setup of the previous lesson: the pipeline output partitioned by the `period` key, the query selecting two columns. **The per-file constant of 120 is a choice.** - **SA13.** **Rows processed** is a separate item: the number of rows a stage sees. The warehouse pays this cost once at write time, the lake pays it again at every read. Duration is not written as a raw figure. - **SA14.** The six corruption classes are taken from the Introduction to Data Engineering course as **input** and the detection lag table is **not repeated**; the new question here is **which scope** is needed to see a corruption. - **SA15.** Resolution: on 2200 rows the step of the row share is **0.0005**. Cost is not a share; the value itself is written. #### Writing and Reading on the Raw Layer The write side of the lake is defined in one sentence: the incoming row is written as it is. The write side of the warehouse does two jobs: it passes the rows through the pipeline and places the output rows into the chosen layout. The code below gives the same source to both sides and asks both a single question, the region total of one period. ```python # FICTIONAL storage -- no real lake product, file format or query engine is called. # The source is FICTIONAL meter readings of a measurement network, seed 20260218. 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): """A row that lacks a required field is dropped.""" 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): """Consumption from the period difference. Without a previous period the row cannot be computed.""" 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 # fixed cost of touching one file , in cells def write(records, partitioning=()): """Distributes the records into files by the partition key.""" 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, filter_=None, columnar=True): """Filter: {field: value}. Fields in the partition key prune files , other fields make the whole file be read.""" filter_ = {} if filter_ is None else filter_ pruning = {a: v for a, v in filter_.items() if a in partitioning} remaining = {a: v for a, v in filter_.items() if a not in partitioning} files, cells, matched = 0, 0, 0 columns_read = len(selected) if columnar else len(COLUMNS) for key, record in layout.items(): if pruning: match = dict(zip(partitioning, key)) if any(match[a] != v for a, v in pruning.items()): continue files += 1 cells += len(record) * columns_read matched += sum(1 for k in record if all(k[a] == v for a, v in remaining.items())) return {"files": files, "cells": cells, "matching_rows": matched, "cost": files * FILE_CONSTANT + cells} # ---- RAW LAYER: no schema , no partitioning , columns cannot be selected def read_raw(records): """The field set is unknown, so all fields of every row are read.""" cells = sum(len(k) for k in records) return {"files": 1, "cells": cells, "cost": 1 * FILE_CONSTANT + cells} def run_pipeline(records): """Three stages; the rows each stage sees are counted as processed.""" processed, v = 0, records for f in (s1_screen, s2_dedupe, s3_consumption): processed += len(v) v, _ = f(v) return v, processed source = generate_source() lake = read_raw(source) warehouse_rows, transform = run_pipeline(source) partitioned = write(warehouse_rows, ("period",)) wh = query(partitioned, ("period",), ("region", "m3"), {"period": "2026-07"}) ROWS = [("written rows", len(source), len(warehouse_rows)), ("written cells", lake["cells"], sum(len(k) for k in warehouse_rows)), ("rows processed at write time", 0, transform), ("query: files touched", lake["files"], wh["files"]), ("query: cells read", lake["cells"], wh["cells"]), ("query: cost", lake["cost"], wh["cost"]), ("rows processed at read time", transform, 0)] print(f"{'measure':<28}{'data lake':>11}{'data warehouse':>16}") for name, a, b in ROWS: print(f"{name:<28}{a:>11}{b:>16}") print("lake gain on write", ROWS[1][2] - ROWS[1][1], " warehouse gain on one query", lake["cost"] - wh["cost"]) ``` ``` measure data lake data warehouse written rows 2400 2200 written cells 14400 15400 rows processed at write time 0 7200 query: files touched 1 1 query: cells read 14400 400 query: cost 14520 520 rows processed at read time 7200 0 lake gain on write 1000 warehouse gain on one query 14000 ``` The upper half of the table confirms the claim of the lake. The lake writes 2400 rows into 14400 cells and **processes no row** while writing; the warehouse writes 2200 rows into 15400 cells and for this processes a total of **7200 rows** across the three stages of the pipeline. On the write side the lake's gain is two items: 1000 cells and 7200 rows of transformation. The lower half takes this gain back. The same question reads **14400 cells** on the lake and **400** on the warehouse; the cost is **14520** against **520**. The difference has three separate causes, and all three come from the absence of a schema. Because the field set is unknown, **columns cannot be selected** on the raw layer; because the layout does not say which row belongs to which period, **partitions cannot be pruned**; and because consumption has not been computed, the pipeline reruns **at read time**, so 7200 rows are processed again in every query. The last row compares the two gains in the same unit: the **1000 cells** the lake gains on write are paid back as **14000 cells** in a single query. In this model the warehouse amortizes itself **before the first query**, and the gap grows linearly with the number of queries. The conclusion is not that the lake is unnecessary, but that the lake's case **is not in the cost table**. The lake is chosen because it can accept data whose schema is not yet known and rejects no row. The count for these two properties is in the next table. #### What the Write Time Cannot See How much data must be looked at to see a corruption decides whether that corruption can be caught at write time. A gate at write time sees a **single row**: it accepts or rejects the row in hand. A check that looks at the whole set or at the aggregate cannot run at write time, because the set is not yet complete. The code below separates the six classes by these three scopes. ```python # ---- FICTIONAL corruption classes. None of them is an attack; all are producer-side accidents. CORRUPTIONS = ["late_arrival", "duplicated", "schema_changed", "unit_changed", "missing_partition", "type_drift"] def corrupt(records, cls, period="2026-07"): """Corrupts a single period , the remaining periods stay intact.""" out, counter = [], 0 for k in records: if k["period"] != period: out.append(k) continue if cls == "late_arrival": if k["subscriber"] <= "A040": # 40 subscribers never arrived this period counter += 1 continue out.append(k) elif cls == "duplicated": out.append(k) if k["subscriber"] <= "A060": # 60 records arrived twice out.append(dict(k)) counter += 1 elif cls == "schema_changed": y = dict(k) y["index_value"] = y.pop("index") # field name changed counter += 1 out.append(y) elif cls == "unit_changed": y = dict(k) y["index"] = k["index"] * 1000 # liters instead of m3 y["unit"] = "m3" # LABEL DID NOT CHANGE , silent corruption counter += 1 out.append(y) elif cls == "missing_partition": if k["region"] == "north": # one region's partition was never written counter += 1 continue out.append(k) elif cls == "type_drift": y = dict(k) y["index"] = str(k["index"]) # number turned into text counter += 1 out.append(y) return out, counter def s4_summary(records): totals = {} for k in records: key = (k["region"], k["period"]) totals[key] = totals.get(key, 0) + k["m3"] return totals, 0 # ---- THREE SCOPES: row , set , aggregate SCHEMA = {"subscriber": str, "period": str, "region": str, "type": str, "index": int, "unit": str} def row_scope(k): """What can be seen at write time by looking at a single row: field and type.""" return int(any(a not in k or not isinstance(k[a], t) for a, t in SCHEMA.items())) def set_scope(data): """What cannot be seen without the whole set: uniqueness , row count , partition completeness.""" key = [(k["subscriber"], k["period"]) for k in data] uniqueness = len(key) != len(set(key)) count = abs(len(data) - 2400) / 2400 > 0.05 seen = set((k["region"], k["period"]) for k in data) partition = seen != set((b, d) for b in REGION for d in PERIODS) return int(uniqueness or count or partition) def aggregate_scope(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 1 return 0 baseline_summary, _ = s4_summary(warehouse_rows) print(f"{'class':<19}{'arrived':>7}{'row':>7}{'set':>6}{'aggregate':>11} " f"{'scope':<11}{'lake':>7}{'warehouse':>11}") for s in ["clean"] + CORRUPTIONS: incoming, _ = (source, 0) if s == "clean" else corrupt(source, s) ro = sum(row_scope(k) for k in incoming) se = set_scope(incoming) ag = aggregate_scope(s4_summary(run_pipeline(incoming)[0])[0], baseline_summary) scope = "row" if ro else ("set" if se else ("aggregate" if ag else "-")) print(f"{s:<19}{len(incoming):>7}{ro:>7}{se:>6}{ag:>11} " f"{scope:<11}{len(incoming):>7}{len(incoming) - ro:>11}") # ---- Two readers interpret the same lake layer differently def repairing_reader(records): """A reader that carries a field-name mapping: the old name is met with the new one.""" y = [] for k in records: if "index" not in k and "index_value" in k: k = dict(k) k["index"] = k.pop("index_value") y.append(k) return y corrupted, _ = corrupt(source, "schema_changed") for name, data in [("unrepaired reader", corrupted), ("repaired reader", repairing_reader(corrupted))]: v, _ = run_pipeline(data) summary, _ = s4_summary(v) print(f"{name:<19}rows {len(v):>5} aggregate cells {len(summary):>3} total m3 {sum(summary.values()):>6}") ``` ``` class arrived row set aggregate scope lake warehouse clean 2400 0 0 0 - 2400 2400 late_arrival 2360 0 0 1 aggregate 2360 2360 duplicated 2460 0 1 0 set 2460 2460 schema_changed 2400 200 0 0 row 2400 2200 unit_changed 2400 0 0 1 aggregate 2400 2400 missing_partition 2354 0 1 0 set 2354 2354 type_drift 2400 200 0 0 row 2400 2200 unrepaired reader rows 1800 aggregate cells 45 total m3 48004 repaired reader rows 2200 aggregate cells 55 total m3 58454 ``` The scope column carries the main result of the lesson. Of the six classes only **two** can be seen by looking at a single row; two need the whole set and two need the aggregate. So **no row gate at write time can see four of them**, and this is not an implementation shortcoming but a limit that comes from the scope itself: a row that never arrives passes no gate, a row that arrives a second time is known to be a repeat only if the first is remembered, and a region's missing period is seen only after all periods have been written. The `unit_changed` row is the quietest one: 0 in the row scope and 0 in the set scope as well. The value has grown a thousandfold, the unit label has stayed `m3`, and the row fits the schema flawlessly; uniqueness is intact, the partitions are complete and the row count is right. The only scope that sees this corruption is the **aggregate**, that is, the time after the pipeline has run and the totals have come out. In storage terms the conclusion is this: against this class there is **no difference at all** between the warehouse's write gate and the lake's lack of one; both write 2400 rows. The lake and the warehouse part ways in the last two columns, and the difference is **only two rows**: `schema_changed` and `type_drift` stand as 2400 rows in the lake and 2200 in the warehouse. The detection difference the lake pays consists of these two classes alone; on the other hand, the 200 rows left in the lake **are not lost**. The last two rows show the consequence: two consumers reading the same lake layer produce different answers. The unrepaired reader does not recognize the field name, so it drops 200 rows, and then loses 200 more because the difference of the next period cannot be computed; it is left with **1800 rows, 45 aggregate cells and 48004 m3**. The reader carrying a field-name mapping finds **2200 rows, 55 cells and 58454 m3**. The gap of **10450 m3** between them is the difference not of the bytes on disk but of **the interpretation at read time**. The course's second claim holds here too: **a layout also corrupts.** The raw layout destroys no row, but the storage layer does **not say** what the correct answer is. Deferring schema enforcement to read time does not remove it, it only **multiplies** it: every consumer carries its own schema, and as many answers appear as there are consumers. #### Summary - A data lake stores incoming data as it is; the schema does not disappear, it is deferred to read time, and the reading side carries it. - The lake writes 2400 rows into 14400 cells without any transformation; the warehouse processes 7200 rows to write 2200 rows into 15400 cells. - The same period query gives 14400 cells and 14520 cost on the lake and 400 cells and 520 cost on the warehouse; the 1000 cells the lake gains on write are paid back as 14000 in a single query. - Of the six corruption classes two are seen in the row scope, two in the set scope and two in the aggregate scope; a row gate at write time cannot see four of them, and `unit_changed` is invisible in both the row and the set scope. - The write difference between the lake and the warehouse lies in only two classes; on the other hand, two consumers reading the same lake layer find 1800 and 2200 rows and 48004 and 58454 m3. #### Next Step The two lessons measured two extreme decisions. The warehouse enforces the schema at write time, gives the query a cheap read, and in return destroys recoverable rows. The lake destroys no row, and in return reruns the pipeline for every query and leaves the correct interpretation to the consumer. The desired sides of the two decisions seem not to exclude each other: the raw layer can be kept **and** schema enforcement can still be gathered in a single place, only not at write time but **at read time**. The next lesson measures this combination, that is, the **lakehouse approach**. Its question is tied to a single number: when enforcement is moved to read time, after how many stages is a corruption caught. The measure will be read in the unit of the Introduction to Data Engineering course, that is, as **stages**; so that the loss can be written next to the gain, the stored cells will also be counted separately. ### Lakehouse Approach Source: https://academia.sh/en/courses/data-storage-architectures/lakehouse-approach Counting what moving schema enforcement to read time gains and loses while the raw layer is preserved: the write gate catches two of the six corruption classes with zero stages of lag and never catches four, the setup with no enforcement catches none of the six, the setup that enforces at read time catches all six and its mean lag becomes 1.33 stages. The rise of the mean is not a deterioration but four undefined values becoming finite. Under a field-name change the setup that preserves the raw layer produces 2200 curated rows, while the setup that screens at the gate rejects 200 rows and stays at 1800; the cost is stored cells being 29800 instead of 12600. A unit change is written to the curated layer without catching on any gate and is seen only at the fourth stage. The previous two lessons measured two extreme decisions. The data warehouse enforces the schema on write, gives the query a read costing 520, and in return destroys 400 recoverable rows. The data lake destroys no row, makes the same query pay 14520 in return, and leaves the correct interpretation to the consumer; two consumers reading the same raw layer finding 48004 and 58454 m3 was the result of this. The **lakehouse** separates these two decisions and combines them again. The raw layer is preserved as in the lake: incoming rows are written as they are, and nothing is rejected. Schema enforcement does not disappear, but it is taken from write time, moved to **read time** and gathered in a single place; the result is, as in the warehouse, a schema-conforming and partitioned **curated layer**. What this combination gains and loses is read on a single axis: **detection lag**. The measure is written in the unit of the Introduction to Data Engineering course, that is, in **stages**, so that the three setups can be compared with the same ruler. - **SA16.** The code is a **fiction**; no lakehouse product, table format, catalog or query engine name is written. The source, the pipeline and the checks are taken over from the Introduction to Data Engineering course, seed **20260218**. - **SA17.** The lakehouse has two layers: the **raw layer** keeps the incoming row as it is, the **curated layer** keeps the output of the pipeline with enforcement applied. Enforcement runs while the curated layer is being built, that is, **at read time**. - **SA18.** Detection lag is written in **stages**: **0** with no stage run, **1** to **5** the order of the five stages. Time is not written as a raw figure. - **SA19.** The seven checks and the six corruption classes are taken **as they are** from the Introduction to Data Engineering course and their procedures are **not repeated**. The new question of this lesson is a single line: what happens to the lag when enforcement moves to read time. - **SA20.** The warehouse's write gate sees only the **row scope**, that is, `schema` and `type`; the checks in the set and aggregate scopes cannot run at write time. This distinction was measured in the previous lesson. - **SA21.** Since the raw layer is preserved, **repair** is possible: a field-name mapping and a type conversion. There is no repair at the warehouse gate; a row that does not conform is rejected. - **SA22.** Query cost is not measured again in this lesson, it is taken as **input** from the previous two lessons: 14520 on the raw layer, 520 on the curated layer partitioned by `period`. **The per-file constant of 120 is a choice.** #### Moving Enforcement to Read Time The three setups see the same corrupted source and differ only in **which check runs where**. In the warehouse the write gate runs only the two checks in the row scope. In the lake there is no check at all. In the lakehouse all seven checks run at read time, while the curated layer is being built, and each fires at its own stage. ```python # FICTIONAL pipeline and checks -- no real lakehouse product, table format or query # engine is called. The source is FICTIONAL meter readings of a measurement network, seed 20260218. 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 CORRUPTIONS = ["late_arrival", "duplicated", "schema_changed", "unit_changed", "missing_partition", "type_drift"] def corrupt(records, cls, period="2026-07"): """Corrupts a single period , the remaining periods stay intact.""" out, counter = [], 0 for k in records: if k["period"] != period: out.append(k) continue if cls == "late_arrival": if k["subscriber"] <= "A040": # 40 subscribers never arrived this period counter += 1 continue out.append(k) elif cls == "duplicated": out.append(k) if k["subscriber"] <= "A060": # 60 records arrived twice out.append(dict(k)) counter += 1 elif cls == "schema_changed": y = dict(k) y["index_value"] = y.pop("index") # field name changed counter += 1 out.append(y) elif cls == "unit_changed": y = dict(k) y["index"] = k["index"] * 1000 # liters instead of m3 y["unit"] = "m3" # LABEL DID NOT CHANGE , silent corruption counter += 1 out.append(y) elif cls == "missing_partition": if k["region"] == "north": # one region's partition was never written counter += 1 continue out.append(k) elif cls == "type_drift": y = dict(k) y["index"] = str(k["index"]) # number turned into text counter += 1 out.append(y) return out, counter 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): """Consumption from the period difference. Without a previous period the row cannot be computed.""" 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 def s4_summary(records): totals = {} for k in records: key = (k["region"], k["period"]) totals[key] = totals.get(key, 0) + k["m3"] return totals, 0 def s5_serve(summary): """Serving: per-region mean over the periods.""" region = {} for (b, d), v in summary.items(): region.setdefault(b, []).append(v) return {b: round(sum(v) / len(v), 2) for b, v in region.items()}, 0 STAGES = [("screen", s1_screen), ("dedupe", s2_dedupe), ("consumption", s3_consumption), ("summary", s4_summary), ("serve", s5_serve)] # ---- CHECKS: each one runs AFTER a specific stage 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_partition_completeness(data): """FLAWED CHECK: looks at the region set , does not see the period.""" return set(k["region"] for k in data) != set(REGION) def c_partition_completeness_period(data): """CORRECT CHECK: is every region present in every period.""" seen = set((k["region"], k["period"]) for k in data) return seen != set((b, d) for b in REGION for d in PERIODS) 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 CHECKS = [ ("schema", 0, lambda v, t: c_schema(v)), ("type", 0, lambda v, t: c_type(v)), ("uniqueness", 0, lambda v, t: c_uniqueness(v)), ("row_count", 0, lambda v, t: c_row_count(v, t["expected_rows"])), ("partition", 0, lambda v, t: c_partition_completeness(v)), ("partition_period", 0, lambda v, t: c_partition_completeness_period(v)), ("distribution", 4, lambda v, t: c_distribution(v, t["baseline_summary"])), ] def instrumented_pipeline(records, baseline, enabled=None): """Runs the pipeline and applies the enabled checks after every stage. Returns: serving , trace , first detecting check and detection lag (stage).""" enabled = [a for a, _, _ in CHECKS] if enabled is None else enabled data, trace, detector, lag = records, [], None, None for step in range(len(STAGES) + 1): if step > 0: name, f = STAGES[step - 1] data, dropped = f(data) trace.append({"stage": name, "remaining": len(data), "dropped": dropped}) for cname, cstage, cf in CHECKS: if cname not in enabled or cstage != step: continue if cf(data, baseline): if detector is None: detector, lag = cname, step serving = data if isinstance(data, dict) and data and isinstance( list(data.values())[0], float) else None return serving, trace, detector, lag def build_baseline(source): v1, _ = s1_screen(source) v2, _ = s2_dedupe(v1) v3, _ = s3_consumption(v2) summ, _ = s4_summary(v3) serve, _ = s5_serve(summ) return {"expected_rows": len(source), "baseline_summary": summ, "baseline_serving": serve} source = generate_source() baseline = build_baseline(source) SETUPS = [("data warehouse", ["schema", "type"]), # the gate looks at the row only ("data lake", []), # no enforcement at all ("lakehouse", None)] # all checks at read time def cell_text(detector, lag): return f"{detector}/{lag}" if detector else "-" lag_log = {name: [] for name, _ in SETUPS} print(f"{'class':<19}{'data warehouse':>16}{'data lake':>11}{'lakehouse':>20}") for s in CORRUPTIONS: incoming, _ = corrupt(source, s) cells = [] for name, enabled in SETUPS: _, _, d, lag = instrumented_pipeline(incoming, baseline, enabled=enabled) if d: lag_log[name].append(lag) cells.append(cell_text(d, lag)) print(f"{s:<19}{cells[0]:>16}{cells[1]:>11}{cells[2]:>20}") for name, _ in SETUPS: v = lag_log[name] mean = f"{sum(v) / len(v):.2f}" if v else "undefined" print(f"{name:<19}caught {len(v)}/{len(CORRUPTIONS)} mean lag {mean}") ``` ``` class data warehouse data lake lakehouse late_arrival - - distribution/4 duplicated - - uniqueness/0 schema_changed schema/0 - schema/0 unit_changed - - distribution/4 missing_partition - - partition_period/0 type_drift type/0 - type/0 data warehouse caught 2/6 mean lag 0.00 data lake caught 0/6 mean lag undefined lakehouse caught 6/6 mean lag 1.33 ``` The last column is **exactly the same** as the first-detecting-check column of the Introduction to Data Engineering course; the lakehouse setup gives the same result as that course's fully instrumented pipeline, because although **where** the enforcement stands changes, the set of checks does not. The comparison is therefore valid: the three columns are read on the same ruler, in the unit of stages. The gain is on the coverage side. The write gate catches **2/6**, the setup with no enforcement **0/6**, the setup that enforces at read time **6/6**. The four classes the gate fails to catch are not merely overlooked; the gate is **not in a position to see them**, because two need the set scope, two need the aggregate scope, and these scopes have not yet formed at write time. The mean lag row must be read carefully. The warehouse's **0.00** is smaller than the lakehouse's **1.33**, but this does not mean the warehouse catches faster. The two means are taken **over different numbers of classes**: the warehouse's over only the two classes it catches, the lakehouse's over all six. The two shared classes, `schema_changed` and `type_drift`, are caught at **0 stages** in the lakehouse as well; moving enforcement to read time **does not delay** these two at all. The mean rising from 0.00 to 1.33 has one cause: the four values that are **undefined** in the warehouse becoming finite: 0, 0, 4 and 4. This is the storage-side counterpart of the distinction in the Introduction to Data Engineering course: **an undefined lag is not better than zero, it is what was not measured.** The mean lag of a setup cannot be read without writing how many classes it never catches. #### What the Raw Layer Rescues and What It Costs The lag table shows that the two setups give the same result against `schema_changed`: both catch it at zero stages. Where they part is **what they do after catching**. The warehouse rejects the row; the lakehouse keeps it on the raw layer and repairs it at read time. ```python SCHEMA = {"subscriber": str, "period": str, "region": str, "type": str, "index": int, "unit": str} FIELD_MAP = {"index_value": "index"} def violates(k): return any(a not in k or not isinstance(k[a], t) for a, t in SCHEMA.items()) def screen_at_gate(records): """Warehouse: a row that does not conform is never written.""" accepted = [k for k in records if not violates(k)] return accepted, len(records) - len(accepted) def repair_on_read(records): """Lakehouse: the raw layer is kept , enforcement is applied at read time with repair.""" y = [] for k in records: if violates(k): k = dict(k) for old, new in FIELD_MAP.items(): if old in k and new not in k: k[new] = k.pop(old) if isinstance(k.get("index"), str) and k["index"].isdigit(): k["index"] = int(k["index"]) y.append(k) return y def run_pipeline(records): v = records for f in (s1_screen, s2_dedupe, s3_consumption): v, _ = f(v) return v def count_cells(records): return sum(len(k) for k in records) corrupted, _ = corrupt(source, "schema_changed") accepted, rejected = screen_at_gate(corrupted) repaired = repair_on_read(corrupted) print("three setups under schema_changed") print(f"{'setup':<14}{'rejected':>11}{'raw layer':>12}{'curated rows':>15}" f"{'stored cells':>16}") for name, raw, curated_source in [("data lake", corrupted, corrupted), ("data warehouse", [], accepted), ("lakehouse", corrupted, repaired)]: d = run_pipeline(curated_source) h = count_cells(raw) + (count_cells(d) if name != "data lake" else 0) print(f"{name:<14}{rejected if name == 'data warehouse' else 0:>11}{len(raw):>12}{len(d):>15}{h:>16}") clean = run_pipeline(source) print("stored cells on clean source lake", count_cells(source), " warehouse", count_cells(clean), " lakehouse", count_cells(source) + count_cells(clean)) ``` ``` three setups under schema_changed setup rejected raw layer curated rows stored cells data lake 0 2400 1800 14400 data warehouse 200 0 1800 12600 lakehouse 0 2400 2200 29800 stored cells on clean source lake 14400 warehouse 15400 lakehouse 29800 ``` The gain is in the third column: the lakehouse produces **2200** curated rows, the other two setups **1800**. Half of the 400-row gap is the directly rejected records, the other half is their indirect consequence; when the period whose field name changed drops out, the consumption difference of the next period cannot be computed either. The warehouse's loss is therefore **larger** than the number of rows it rejects, and the gate itself does not report this growth. In the lakehouse, since the raw layer stands, both periods come back the moment the mapping table is written. The cost is in the last column: the lakehouse stores **29800** cells, the warehouse **12600**, the lake **14400**. On the clean source the picture is clearer: 14400, 15400 and 29800. The lakehouse stores the sum of the two, because it keeps the same data both raw and curated. On query cost it is at the warehouse's level, that is 520; its raw layer can also be queried at 14520. So what the lakehouse buys is **an option bought with stored cells**: it can be regenerated when the curated layer is corrupted or the understanding of the schema changes. The course's second claim is written here too: **a layout also corrupts.** That the lakehouse rejects no row does not mean its curated layer is clean. The `unit_changed` row is the proof: it passes no gate, requires no repair, **all 2200 rows** are written to the curated layer, and the corruption is seen only **at the fourth stage**. Up to that point the curated layer is a table with a flawless-looking schema, carrying the full row count, with two periods grown a thousandfold. Conforming to the schema is not the same as being correct; enforcement moved to read time does not close this gap. #### Summary - The lakehouse preserves the raw layer and gathers schema enforcement in a single place by moving it from write time to read time; the result is a schema-conforming curated layer. - Corruption classes caught: 2/6 at the write gate, 0/6 in the setup with no enforcement, 6/6 in the setup that enforces at read time. - The mean detection lag is 0.00 stages in the warehouse and 1.33 in the lakehouse; since the two shared classes are caught at 0 stages in both, the rise of the mean is not a deterioration but four undefined values becoming finite. - Under a field-name change the lakehouse produces 2200 curated rows, the other two setups 1800; half of the loss is the rejected rows, half is the next period they cause to drop. - The cost is stored cells: on the clean source the lake is 14400, the warehouse 15400, the lakehouse 29800. - Enforcement at read time does not prevent a bad row from being written to the curated layer; `unit_changed` passes no gate, is written as 2200 rows and is seen at the fourth stage. #### Next Step The three lessons separated **what** storage keeps. The warehouse keeps the schema-conforming row, the lake keeps the incoming row itself, the lakehouse keeps both, and what each choice costs the query, the detection and the stored cells was written one by one. There is a question that was never asked in the three lessons: **for whom** are these rows stored. It was assumed that all three setups served a single consumer, yet the parties reading from the same warehouse ask different questions and look at different columns. A party interested in one region has to filter a layout carrying all regions from scratch on every query. The next lesson asks this question and measures the subsets focused on a domain, that is, **data marts**; its measure is two numbers side by side: what the query cost becomes when the subset is written separately, and **in how many places the same row stands**. ### Data Mart Source: https://academia.sh/en/courses/data-storage-architectures/data-mart Counting what a query costs and in how many places the same row stands when a subset dedicated to a single unit is written separately: on a set of three questions the unpartitioned warehouse pays 13560, the best central layout 3256, two marts 2580, and the mart's gain is zero on the region question and 676 on the type question. The cost is multiplication: six marts store 3322 more rows, 2.51 copies per row, and 1122 rows stand in three separate places. When a late-arriving record is processed into the warehouse, the six unrefreshed marts diverge on 126 (row, place) pairs, and on a half-refreshed set the five region marts total 4707 while the warehouse total is 5340. The previous three lessons separated **what** storage keeps: the row that conforms to the schema, the raw row, and the layout that keeps both in the same place. The question asked in all three was "how is this row stored", and the answer was a write decision. One question was never asked: **for whom** is this row stored. The question the region manager asks and the question the tariff manager asks touch the same rows, but they do not want the same layout. What this lesson measures is the cost of that divergence. When a subset dedicated to the questions of a single unit is taken out of the warehouse and written separately, the query cost falls; in return the same row stands in more than one place and those places are refreshed separately. Two numbers are put side by side: the cost the subset saves and **how many copies of the same row there are**. - **SA41.** The source, the pipeline and the storage layout are a **fiction** and are defined within the lesson. No warehouse, lake, file format, catalog product or query engine is called. Seed 20260218. - **SA42.** A **data mart** is a **separately written** subset of the warehouse rows, taken out on the value of a single field. The derivation rule is equality on one field; there is no aggregation, and the rows are the same rows as in the warehouse. - **SA43.** The cost measure is the same throughout the course: `files x 120 + cells read`. **The per-file constant of 120 is a choice**; it is kept fixed in this lesson and its sweep is left to the next lesson. - **SA44.** A mart carries **the rows of the moment it was written**. Refreshing is a separate job and does not have to coincide with the refresh of the warehouse. - **SA45.** The procedure of partitioning, sharding and replication was measured in the Relational Database Administration course of the Databases curriculum and in the Scaling the Data Layer course of the System Design and Distributed Systems curriculum; **it is not repeated here**. This lesson counts only **what the decision to take out a subset makes a query pay**. - **SA46.** If a share of rows is printed, the resolution is written: the set is 2,200 rows, the step of the share is 0.0005. Cost is not a share but an **integer**, and the value itself is written. #### What the Subset Adds The measurement is made with three questions. Two are a region's questions: one period of that region, and all periods of that region. The third is a tariff question: the commercial subscribers in one period. All three questions are over the same 2,200 rows and all three must give the same answer; the only place they differ is the files touched and the cells read to reach that answer. ```python # FICTIONAL. The meter readings of the measurement network, the pipeline and the storage layout # are defined within the lesson; no warehouse, 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 out.append(dict(k, m3=int(k["index"]) - int(prev["index"]))) return out, dropped COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3") FILE_CONSTANT = 120 # fixed cost of touching one file , in cells def run_pipeline(source): v, _ = s1_screen(source) v, _ = s2_dedupe(v) v, _ = s3_consumption(v) return v def write(records, partitioning=()): """Distributes the records into files by the partition key.""" 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, filter_=None, columnar=True): """A field in the partition key prunes files , any other field makes the whole file be read.""" filter_ = {} if filter_ is None else filter_ pruning = {a: v for a, v in filter_.items() if a in partitioning} remaining = {a: v for a, v in filter_.items() if a not in partitioning} files, cells, matched = 0, 0, 0 columns_read = len(selected) if columnar else len(COLUMNS) for key, record in layout.items(): if pruning: match = dict(zip(partitioning, key)) if any(match[a] != v for a, v in pruning.items()): continue files += 1 cells += len(record) * columns_read matched += sum(1 for k in record if all(k[a] == v for a, v in remaining.items())) return {"files": files, "cells": cells, "matching_rows": matched, "cost": files * FILE_CONSTANT + cells} def mart(records, field, value): """Unit-focused subset: the rows that belong to a single value are written separately from the warehouse.""" return [k for k in records if k[field] == value] ALL_ROWS = run_pipeline(generate_source()) SELECTED = ("subscriber", "m3") QUESTIONS = [("east/07", {"region": "east", "period": "2026-07"}), ("east/all", {"region": "east"}), ("commercial/07", {"type": "commercial", "period": "2026-07"})] LAYOUTS = [("unpartitioned", ()), ("period", ("period",)), ("period+region", ("period", "region")), ("period+region+type", ("period", "region", "type"))] print(f"rows from the pipeline {len(ALL_ROWS)} , file constant {FILE_CONSTANT} cells") print("\ncentral warehouse files " + " ".join(f"{a:>13}" for a, _ in QUESTIONS) + " total") for name, part in LAYOUTS: layout = write(ALL_ROWS, part) costs = [query(layout, part, SELECTED, f)["cost"] for _, f in QUESTIONS] print(f"{name:<19} {len(layout):>5} " + " ".join(f"{x:>13}" for x in costs) + f" {sum(costs):>6}") m_region = write(mart(ALL_ROWS, "region", "east"), ("period",)) m_type = write(mart(ALL_ROWS, "type", "commercial"), ("period",)) costs = [query(m_region, ("period",), SELECTED, {"period": "2026-07"})["cost"], query(m_region, ("period",), SELECTED, {})["cost"], query(m_type, ("period",), SELECTED, {"period": "2026-07"})["cost"]] print(f"{'two marts':<19} {len(m_region) + len(m_type):>5} " + " ".join(f"{x:>13}" for x in costs) + f" {sum(costs):>6}") ``` ``` rows from the pipeline 2200 , file constant 120 cells central warehouse files east/07 east/all commercial/07 total unpartitioned 1 4520 4520 4520 13560 period 11 520 5720 520 6760 period+region 55 188 2068 1000 3256 period+region+type 110 308 3388 804 4500 two marts 22 188 2068 324 2580 ``` The first row of the table is the baseline: in the unpartitioned, row-oriented, history-free layout all three questions read the same single file from start to finish, and the total paid is 13,560. The mart setup is the cheapest at 2,580 and does it with **twenty-two files**; the best central layout, `period`+`region`, pays 3,256 with fifty-five files. But the place to read is not the total, it is the columns. On the region questions the mart's gain is **zero**. `east/07` is 188 both in the `period`+`region` layout and in the east mart, and `east/all` is 2,068 in both. The reason is plain: a region mart makes **the same pruning** as adding the region field to the partition key. Of two roads that give the same result, one copies the rows and the other only changes the arrangement. If a decision to take out a subset is claimed to gain something different from a partitioning decision, the number of that difference must be shown. The difference shows in the third column. On the `commercial/07` question the `period`+`region` layout pays 1,000 and the commercial mart 324; the gain is 676. A central layout cannot make this question cheaper, because the partition key is **a single ordering** and that ordering is spent on the region. Adding the third field to the key does not fix it, it makes it worse: the `period`+`region`+`type` layout rises to one hundred and ten files and moves the total from 3,256 to 4,500, and gets more expensive on the first two questions as well. While the cells read fall, paying the file constant one hundred and ten times swallows the gain. **What the mart really buys is this: every subset can choose its own partition key.** In a single layout that is not possible. #### In How Many Places the Same Row Stands The gain was bought with a write decision, and every write decision produced a copy. What will now be counted is the copy itself. The setup is extended: five region marts and one tariff mart, next to the warehouse. Then an accident happens on the producer side, in one period the readings of forty subscribers arrive late, and the record is processed into the warehouse afterwards. The refresh of the marts is a separate job; as long as they are not refreshed, what they say is counted. ```python # The definitions of the previous block carry on: run_pipeline, generate_source, mart, ALL_ROWS, REGION. def late_arrival(records, period="2026-07", upper="A040"): """FICTIONAL producer accident: in this period the readings of 40 subscribers never arrived.""" return [k for k in records if not (k["period"] == period and k["subscriber"] <= upper)] def row_keys(records): return {(k["subscriber"], k["period"]) for k in records} def period_total(records, period="2026-07"): return sum(k["m3"] for k in records if k["period"] == period) MARTS = [(r, "region", r) for r in REGION] + [("commercial", "type", "commercial")] STALE = run_pipeline(late_arrival(generate_source())) # marts written with the late-arrival record copies = {} for k in ALL_ROWS: n = 1 + sum(1 for _, a, d in MARTS if k[a] == d) copies[n] = copies.get(n, 0) + 1 stored = len(ALL_ROWS) + sum(len(mart(ALL_ROWS, a, d)) for _, a, d in MARTS) print(f"warehouse {len(ALL_ROWS)} rows , six marts {stored - len(ALL_ROWS)} rows , " f"total stored {stored}") print(f"rows in two places {copies[2]} , in three places {copies[3]} , " f"mean per row {stored / len(ALL_ROWS):.2f} copies") print("\nmart rows unrefreshed missing rows 07 warehouse 07 mart diff") missing_total = 0 for name, a, d in MARTS: fresh, stale = mart(ALL_ROWS, a, d), mart(STALE, a, d) missing = len(row_keys(fresh) - row_keys(stale)) missing_total += missing print(f"{name:<10} {len(fresh):>5} {len(stale):>12} {missing:>12} {period_total(fresh):>12}" f" {period_total(stale):>8} {period_total(fresh) - period_total(stale):>5}") print(f"inconsistent (row , place) pairs: {missing_total}") # Partial refresh: the warehouse and two region marts were refreshed , three marts were not. REFRESHED = {"east", "west"} mart_total = sum(period_total(mart(ALL_ROWS if r in REFRESHED else STALE, "region", r)) for r in REGION) print(f"\nwarehouse 2026-07 total {period_total(ALL_ROWS)} , total of the five region marts " f"{mart_total} , diff {period_total(ALL_ROWS) - mart_total}") ``` ``` warehouse 2200 rows , six marts 3322 rows , total stored 5522 rows in two places 1078 , in three places 1122 , mean per row 2.51 copies mart rows unrefreshed missing rows 07 warehouse 07 mart diff east 374 356 18 958 665 293 west 341 333 8 812 674 138 north 506 492 14 1251 1041 210 south 440 418 22 1039 780 259 central 539 521 18 1280 1116 164 commercial 1122 1076 46 2738 2113 625 inconsistent (row , place) pairs: 126 warehouse 2026-07 total 5340 , total of the five region marts 4707 , diff 633 ``` The first number is on the storing side: a data set of 2,200 rows stands as 5,522 rows together with the six marts, 2.51 copies per row. Since the five region marts partition the rows, each of them copies every row exactly once; the tariff mart rides on top of that and writes 1,122 rows to a **third** place. The number of copies grows not with the number of layouts but with their **intersection**; the five marts that do not intersect brought the second copy, the one that intersects brought the third. The second number is on the consistency side and it is heavier than the storage number. When the late-arriving record is processed into the warehouse, the warehouse goes up to 2,200 rows, and the unrefreshed marts stay on the rows of the moment they were written. The divergence is in 126 (row, place) pairs: eighty rows are missing in the five region marts, and forty-six of them are missing in the tariff mart as well. The reflection of the divergence on the served number is in the columns: the east mart says 665 for that period, the warehouse says 958. Two addresses give two answers to the same question and **no error is seen anywhere**; each side is consistent within itself, complete and flawless as to type. The third number is the most insidious form of multiplication. Refreshing is not done for all at the same moment; at a moment when the warehouse and two region marts have been refreshed and three have not, the total of the five region marts is 4,707 and the warehouse total for the same period is 5,340. The difference is 633 m3 and both numbers are the correct reading of a source within itself. Although the warehouse is the **union** of the region marts, the total of the union does not give the warehouse's; what gives it is the order of the refresh. The third number to be measured when a subset is taken out is how long two addresses give different answers. #### Summary - A data mart is a subset of the warehouse rows taken out on the value of a single field and written separately; it is not a summary, it is a second copy of the same rows. - On the three-question set the unpartitioned baseline pays 13,560, the best central layout 3,256 and two marts 2,580; but the mart's gain on the region questions is **zero**, because the same pruning is obtained there by adding one field to the partition key. - The real gain of the mart is that every subset can choose **its own partition key**: on the tariff question the central layout pays 1,000 and the tariff mart 324; adding the third field to the central key raises the total from 3,256 to 4,500. - The cost is multiplication: six marts store 3,322 more rows, 2.51 copies per row, and 1,122 rows stand in three separate places. The number of copies is determined not by the number of layouts but by their intersection. - When refreshes diverge, 126 (row, place) pairs diverge and the same question gets two answers at two addresses (665 and 958), with no error visible. - On a half-refreshed set the total of the five region marts is 4,707 and the warehouse total for the same period is 5,340; both numbers are correct in their own source. #### Next Step In this lesson who took out the subset was never asked. The partition key of the six marts, the order of refresh and the derivation rule were determined by a single party; the region manager only read the subset written for them. What would happen if the decision did not stay with that party, if the region itself made the layout decision, if it chose the refresh schedule itself. Two numbers change with this question: **the number of parties that make the layout decision** and **the number of layouts an end-to-end query touches**. The next lesson measures the two models in which ownership is distributed with these two numbers and, in the same place, sweeps the per-file constant on which every cost of this course rests, to show which results depend on that constant. ### Data Fabric and Data Mesh Source: https://academia.sh/en/courses/data-storage-architectures/data-fabric-and-data-mesh Measuring the two numbers that change when ownership is distributed: the number of parties that make the layout decision and the number of layouts an end-to-end query touches. The central setup gives 1/1, the data fabric 1/5, the data mesh 5/5; on a mix of five local questions plus one countrywide question the cost comes out 3120, 2000 and 6280. The mesh is expensive not because of distribution but because of unmeasured key choices: the decision of two units alone costs 2450. The lesson sweeps the per-file constant over 60, 120, 240 and 480: the two numbers are independent of the constant, the ranking is not — the constant at which the fabric and the central setup are equal is 400, and when a single unpruned question is added the distributed setups lose at all four values. In the previous lesson six subsets were taken out, and the partition key, the refresh order and the derivation rule of each were determined by **a single party**. The region unit only read the set written for it. What would happen if the decision did not stay there: if the region itself chose the layout key, set up the write schedule itself, and published its own set like a product. This question looks like an architectural preference, yet it changes two numbers, and those two numbers can be measured. The first is **the number of parties that make the layout decision**: in how many separate places the question "by which key will these rows be filed" is answered. The second is **the number of layouts an end-to-end query touches**: how many separate partitioning decisions a single question that covers all regions passes over. The lesson measures these two numbers on three arrangements, then sweeps the per-file constant on which every cost of this course rests, to show which result depends on that constant. - **SA51.** The source, the pipeline and the storage layout are a **fiction** and are defined within the lesson. No warehouse, lake, catalog product, query engine or cloud storage service is called. Seed 20260218. - **SA52.** A **data fabric** is an arrangement in which the rows are written in more than one place but **the key decision stays in a single standard**. A **data mesh** is an arrangement in which the unit that produces the rows owns both the writing and **the key choice**; each unit publishes its own **data product**. - **SA53.** The **number of addresses** the query knows is written separately as a third number. The fabric has a single access point, the mesh five. This number does not change the cost. - **SA54.** There are two question mixes. **Mix A** is five local questions (each unit one period of its own region) plus one countrywide question (the same period, all regions). **Mix B** adds an **unpruned** question on top of A: all periods of a single subscriber. - **SA55.** The procedure of ownership and governance was measured in the Governance, Risk and Compliance course of the Cybersecurity curriculum, and the number of the ownership gap in the Data Governance lesson of the Introduction to Data Engineering course; **it is not repeated here**. This lesson counts ownership only as **which layout is in whose hands**. - **SA56.** The per-file constant is **a choice** and is swept in this lesson: 60, 120, 240, 480. Cost is not a share but an integer; if a file share is printed, the resolution step is 0.0091. #### How Many Hands the Decision Is In The three arrangements are built on the same 2,200 rows. The central setup has a single store, and its key is `period`. In the fabric the rows are written to five separate places, but a single party decides what the key will be, and all five use `period`. In the mesh each unit chooses its own key: two units choose `period`, one `period`+`type`, one does no partitioning at all, and one chooses only `type`. These choices are not made in bad faith; none of them wrote down which question it was made for. ```python # FICTIONAL. The meter readings of the measurement network, the pipeline and the storage layout # are defined within the lesson; no warehouse, lake, catalog product 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 out.append(dict(k, m3=int(k["index"]) - int(prev["index"]))) return out, dropped COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3") FILE_CONSTANT = 120 # fixed cost of touching one file , in cells def run_pipeline(source): v, _ = s1_screen(source) v, _ = s2_dedupe(v) v, _ = s3_consumption(v) return v def write(records, partitioning=()): """Distributes the records into files by the partition key.""" 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, filter_=None, columnar=True): """A field in the partition key prunes files , any other field makes the whole file be read.""" filter_ = {} if filter_ is None else filter_ pruning = {a: v for a, v in filter_.items() if a in partitioning} remaining = {a: v for a, v in filter_.items() if a not in partitioning} files, cells, matched = 0, 0, 0 columns_read = len(selected) if columnar else len(COLUMNS) for key, record in layout.items(): if pruning: match = dict(zip(partitioning, key)) if any(match[a] != v for a, v in pruning.items()): continue files += 1 cells += len(record) * columns_read matched += sum(1 for k in record if all(k[a] == v for a, v in remaining.items())) return {"files": files, "cells": cells, "matching_rows": matched, "cost": files * FILE_CONSTANT + cells} ALL_ROWS = run_pipeline(generate_source()) SELECTED = ("subscriber", "m3") P07 = {"period": "2026-07"} SUB = {"subscriber": "A007"} # Three arrangements. Who owns the key choice: the center , a single standard , the unit. CENTRAL = {"": ("period",)} FABRIC = {r: ("period",) for r in REGION} # distributed write , single key decision MESH = {"east": ("period",), "west": ("period", "type"), "north": (), "south": ("period",), "central": ("type",)} # each unit chose its own key COUNTS = {"central": (1, 1, 1), "fabric": (1, 5, 1), "mesh": (5, 5, 5)} def build(keys): if list(keys) == [""]: return {"": write(ALL_ROWS, keys[""])} return {r: write([k for k in ALL_ROWS if k["region"] == r], keys[r]) for r in keys} def measure(keys, layouts): """Five local questions (each unit the period of its own region) , one countrywide question , one subscriber question. In the central setup all three go to the single layout.""" if list(keys) == [""]: d, a = layouts[""], keys[""] local = sum(query(d, a, SELECTED, dict(P07, region=r))["cost"] for r in REGION) return local, query(d, a, SELECTED, P07)["cost"], query(d, a, SELECTED, SUB)["cost"] local = sum(query(layouts[r], keys[r], SELECTED, P07)["cost"] for r in REGION) sub = sum(query(layouts[r], keys[r], SELECTED, SUB)["cost"] for r in REGION) return local, local, sub SETUPS = [("central", CENTRAL), ("fabric", FABRIC), ("mesh", MESH)] print(f"rows {len(ALL_ROWS)} , file constant {FILE_CONSTANT} cells\n") print("setup deciding parties layouts touched addresses files local x5 countrywide subscriber") for name, keys in SETUPS: lay = build(keys) y, u, a = measure(keys, lay) t, dd, ad_r = COUNTS[name] print(f"{name:<10} {t:>16} {dd:>15} {ad_r:>9} {sum(len(x) for x in lay.values()):>5}" f" {y:>8} {u:>11} {a:>10}") print("\nin the mesh, local question per unit: key / files / cost") lay = build(MESH) for r in REGION: res = query(lay[r], MESH[r], SELECTED, P07) print(f" {r:<7} {str(MESH[r]):<18} {len(lay[r]):>3} files , {res['files']:>2} touched" f" , cost {res['cost']:>5}") ``` ``` rows 2200 , file constant 120 cells setup deciding parties layouts touched addresses files local x5 countrywide subscriber central 1 1 1 11 2600 520 5720 fabric 1 5 1 55 1000 1000 11000 mesh 5 5 5 47 3140 3140 10040 in the mesh, local question per unit: key / files / cost east ('period',) 11 files , 1 touched , cost 188 west ('period', 'type') 22 files , 2 touched , cost 302 north () 1 files , 1 touched , cost 1132 south ('period',) 11 files , 1 touched , cost 200 central ('type',) 2 files , 2 touched , cost 1318 ``` The first three columns carry the lesson's two numbers. In the central setup the decision is in one hand and the end-to-end query touches a single layout: 1 and 1. In the fabric the rows are spread over five places but the key decision is still in one hand: 1 and 5. In the mesh both are five. The address column shows the third distinction; the fabric puts five stores behind a single access point, the mesh does not. **The number of addresses has no contribution at all to the cost columns**: the fabric pays 1,000 because that is where the rows are; that the address is single does not change the cells read. An access layer reduces not the cost but **who has to know**. Two things are read together in the cost columns. On the local questions the fabric is three times cheaper than the central setup (1,000 against 2,600), because in the central layout asking for one period of one region makes it read **all** the rows of that period, and five regions read the same file five times. In the distributed write the five local questions land on disjoint sets, and their total comes out exactly equal to the cost of the countrywide question. On the countrywide question, on the other hand, the central setup pays 520 and the fabric 1,000: the same 400 cells are read, and the difference is that the file constant is paid five times instead of once. On the subscriber question a gap opens, 5,720 against 11,000, because there is no key there that prunes and all fifty-five files are touched. The 3,140 of the mesh, on the other hand, is the number of **unmeasured key choices**, not of distribution. The sub-table separates it unit by unit: east 188, south 200 and west 302 pay little, while north 1,132 and central 1,318 pay a lot. The decision of two units alone costs 2,450, more than two thirds of the mesh's local total. North does no partitioning, so it reads the whole of its single file; central chose `type`, so the period filter prunes nothing. The rule of the course shows up here for the second time: **a layout whose query is not written down is counted as unmeasured**, and five unmeasured decisions cost more than one measured decision. Distributing ownership does not create this gap; distributing ownership without a measure does. #### When the File Constant Is Swept Every cost of this course rests on one choice: the price of touching a file is 120 cells. The number is a fiction and could have been otherwise. The only way to learn how much all the comparisons above owe to that choice is to change the constant and repeat the same measurement. ```python # The definitions of the previous block carry on: build, measure, SETUPS, COUNTS, ALL_ROWS, FILE_CONSTANT. ARRANGEMENTS = {name: (keys, build(keys)) for name, keys in SETUPS} NAMES = ("central", "fabric", "mesh") def mixes(): """A: five local questions + one countrywide question. B: A , plus one subscriber question.""" c = {} for name, (keys, layouts) in ARRANGEMENTS.items(): local, country, sub = measure(keys, layouts) c[name] = (local + country, local + country + sub) return c print("const central-A fabric-A mesh-A central-B fabric-B mesh-B") for constant in (60, 120, 240, 480): FILE_CONSTANT = constant c = mixes() print(f"{constant:>5} " + " ".join(f"{c[a][0]:>9}" for a in NAMES) + " " + " ".join(f"{c[a][1]:>9}" for a in NAMES)) # The cost is linear in the constant: cost(constant) = cells + constant x touched files. FILE_CONSTANT = 0 base = mixes() FILE_CONSTANT = 1 slope = {a: tuple(mixes()[a][i] - base[a][i] for i in (0, 1)) for a in NAMES} FILE_CONSTANT = 120 print("\nsetup mix A mix B") for a in NAMES: print(f"{a:<9} {base[a][0]:>7} + const x {slope[a][0]:>3}" f" {base[a][1]:>7} + const x {slope[a][1]:>3}") threshold = (base["central"][0] - base["fabric"][0]) // (slope["fabric"][0] - slope["central"][0]) print(f"\nmix A, the constant at which the fabric and the central setup are equal: {threshold}") print("independent of the constant: parties deciding the layout / layouts touched , " + " , ".join(f"{a} {COUNTS[a][0]}/{COUNTS[a][1]}" for a in NAMES)) ``` ``` const central-A fabric-A mesh-A central-B fabric-B mesh-B 60 2760 1400 5440 7820 9100 12660 120 3120 2000 6280 8840 13000 16320 240 3840 3200 7960 10880 20800 23640 480 5280 5600 11320 14960 36400 38280 setup mix A mix B central 2400 + const x 6 6800 + const x 17 fabric 800 + const x 10 5200 + const x 65 mesh 4600 + const x 14 9000 + const x 61 mix A, the constant at which the fabric and the central setup are equal: 400 independent of the constant: parties deciding the layout / layouts touched , central 1/1 , fabric 1/5 , mesh 5/5 ``` The sweep gives results of three different kinds. The first are those **independent of the constant**: the number of parties deciding the layout and the number of layouts touched stay 1/1, 1/5 and 5/5 at all four values. The two main numbers of this lesson are the numbers of an organizational decision, not of a cost model, and the file constant does not touch them. In the same way the mesh being more expensive than the fabric holds at all four values; the price of unmeasured key choices is independent of the constant. The second is **the ranking, which depends on the constant**, and it is the most important warning of this lesson. In mix A the fabric is cheaper than the central setup at 60, 120 and 240, and not at 480. The linear form gives the reason: the fabric pays 10 per file against 800 cells, the central setup 6 per file against 2,400 cells. The cell advantage of the fabric is fixed, its file disadvantage is multiplied by the constant; the two are equal at exactly 400. That 120 was chosen makes the sentence "distributed writing makes local questions cheaper" true; had 480 been chosen, the same sentence would be false. **A comparison of layouts cannot be passed on without writing the constant it rests on.** The third is the result **that depends on the question mix**, and sweeping the constant does not remove it. Mix B adds a single unpruned question on top of A, and the ranking is reversed at all four values: it starts with central 7,820 and fabric 9,100, and at 480 the difference is 14,960 against 36,400. The slope column explains it: in B the file coefficient of the fabric is 65, that of the central setup 17. A single question for which there is no pruning key turns the file count of the distributed write directly into cost. Distributing breaks things too; where it breaks them is the questions that no key helps. #### Summary - Distributing ownership changes two numbers: the number of parties that make the layout decision and the number of layouts an end-to-end query touches. The central setup gives 1/1, the data fabric 1/5, the data mesh 5/5. - What separates the fabric from the mesh is not the distribution of the writing but the distribution of **the key decision**; the number of addresses the query knows is a third number and does not change the cost at all. - The local total of the mesh is 3,140, that of the fabric 1,000; the source of the difference is not distribution but unmeasured key choices, and the decision of two units alone costs 2,450. - When the file constant is swept, the two main numbers and the mesh being more expensive than the fabric do not change at any of the four values; what changes is the ranking of the fabric and the central setup, and the point of equality is 400. - The cost is linear in the constant (`cells + constant x files`), so a comparison cannot be passed on without writing the constant it rests on. - When a single unpruned question is added, the distributed setups lose at all four constant values: in mix B the central setup pays 8,840, the fabric 13,000, the mesh 16,320. #### Next Step In this lesson five stores, forty-seven files and five separate key decisions were counted. Every cost measured rested on the assumption that **it was already known which store the question would go to**. In the mesh arrangement there were five addresses and the query knew all of them; yet the name of a region unit's product, the columns it carries, its partition key and the periods it covers cannot be found unless they are written down somewhere. The next lesson turns this into a single number: how many entries are looked at to find a data asset, with and without a catalog. In the same lesson the catalog's own flaw is measured, how many entries now point to a layout that no longer exists, and this topic closes after six lessons. ### Metadata Catalog Source: https://academia.sh/en/courses/data-storage-architectures/metadata-catalog Counting the entries looked at to find a data asset with and without a catalog: on a set of twelve assets and 119 files five searches look at 295 entries without a catalog and 29 with one, and the most expensive search drops from 117 to 11. When the catalog goes stale the number comes back: after three assets change, three of the twelve entries point to a layout that no longer exists, the same five searches cost 406 without a catalog and 251 with the stale one, but two searches end up more expensive than without a catalog (122 against 129 and 100 against 104), because the dead entry is scanned and the full scan is still done afterwards. The previous two lessons accumulated a set of assets: a central warehouse, unit-focused subsets, and unit products that each chose their own partition key. In every measurement it was already known **which store the question would go to**; the cost started after the address was known. In reality, if that address is not written down somewhere, it cannot be found. If the name of a unit's product, its owner, the columns it carries, its partition key and the periods it covers are not written, finding the asset that will answer a question is itself a search job. What this lesson measures is that search. A **metadata catalog** keeps a single entry per asset and the search runs over those entries; if there is no catalog, the search runs over the files of the assets. In both cases the **number of entries looked at** is counted. Then the catalog's own flaw is measured: when the storage side changes, how many entries now point to a layout that no longer exists, and what a search that trusts those entries costs. - **SA61.** The source, the pipeline, the storage layout and the catalog are a **fiction** and are defined within the lesson. No warehouse, lake, catalog product or query engine is called. Seed 20260218. - **SA62.** An **entry looked at** is of two kinds and both are counted one by one. In a search without a catalog, **the files of an asset must be opened** to learn what it covers; the entry looked at is **the file opened**. In a search with a catalog, the entry looked at is **the catalog entry scanned**. - **SA63.** The assets are scanned **sorted by name**. The search stops at the **first** asset that suffices for the task; the decision to pick the cheapest asset is outside the measure of this lesson. - **SA64.** A catalog entry carries the layout **at the moment it was written**. A change on the storage side is not reflected in the entry by itself; reflecting it is a separate job. - **SA65.** A search with a catalog trusts the entry but **verifies**: if the asset of the matching entry does not really suffice for the task, the search falls back to the search without a catalog and the two costs are **added**. - **SA66.** The lineage measure was measured in the Data Lineage lesson of the Introduction to Data Engineering course; **it is not repeated here**. The object of lineage is **the rows a row rests on**; the object of the catalog is **where an asset stands**. One explains the value, the other gives the address. #### With and Without a Catalog The set consists of twelve assets. The central warehouse carries all the regions and the tariff column, but holds only the last six periods because of the retention window. The five region marts carry all the periods and do not carry the tariff column. A tariff mart collects the commercial rows from all the regions. The five unit products store all the rows of their own region, with the key each chose. Five search tasks run over this set. ```python # FICTIONAL. The meter readings of the measurement network, the pipeline and the storage layout # are defined within the lesson; no warehouse, lake, catalog product 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 values stay Turkish: asset names are built from them and scanned sorted by name # dogu/bati/kuzey/guney/merkez = east/west/north/south/central REGION = ["dogu", "bati", "kuzey", "guney", "merkez"] 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 out.append(dict(k, m3=int(k["index"]) - int(prev["index"]))) return out, dropped def run_pipeline(source): v, _ = s1_screen(source) v, _ = s2_dedupe(v) v, _ = s3_consumption(v) return v def write(records, partitioning=()): """Distributes the records into files by the partition key.""" layout = {} for k in records: key = tuple(k[a] for a in partitioning) layout.setdefault(key, []).append(k) return layout ALL_ROWS = run_pipeline(generate_source()) LAST6 = sorted({k["period"] for k in ALL_ROWS})[-6:] FULL = ("subscriber", "period", "region", "type", "m3") REGIONAL = ("subscriber", "period", "region", "m3") # the tariff column is not carried MESH_KEYS = {"dogu": ("period",), "bati": ("period", "type"), "kuzey": (), "guney": ("period",), "merkez": ("type",)} def asset(name, owner, records, key, columns): """A data asset: its rows , partition key and the columns it carries.""" layout = write(records, key) return {"name": name, "owner": owner, "key": key, "columns": columns, "files": len(layout), "rows": {(k["subscriber"], k["period"]) for k in records}, "region": {k["region"] for k in records}, "type": {k["type"] for k in records}, "period": {k["period"] for k in records}} def setup_assets(): """The assets accumulated by the previous two lessons , sorted by name.""" v = [asset("central_warehouse", "central team", [k for k in ALL_ROWS if k["period"] in LAST6], ("period",), FULL)] for r in REGION: v.append(asset(f"mart_{r}", f"{r} reporting", [k for k in ALL_ROWS if k["region"] == r], ("period",), REGIONAL)) v.append(asset("mart_tariff", "tariff team", [k for k in ALL_ROWS if k["type"] == "commercial"], ("period",), FULL)) for r in REGION: v.append(asset(f"product_{r}", f"{r} unit", [k for k in ALL_ROWS if k["region"] == r], MESH_KEYS[r], FULL)) return sorted(v, key=lambda x: x["name"]) def needed(task): return {(k["subscriber"], k["period"]) for k in ALL_ROWS if all(k[a] == d for a, d in task.items())} def covers_in_fact(v, task): """By looking at the asset itself: are all the requested rows here.""" return set(task) <= set(v["columns"]) and needed(task) <= v["rows"] def covers_by_entry(g, task): """By looking only at the catalog entry , without opening a file.""" if not set(task) <= set(g["columns"]): return False if task["period"] not in g["period"] or task["region"] not in g["region"]: return False return {task["type"]} <= g["type"] if "type" in task else g["type"] == set(TYPE) def write_catalog(assets): """A single entry per asset: name , owner , key , files , columns , coverage.""" return [{a: v[a] for a in ("name", "owner", "key", "files", "columns", "region", "type", "period")} for v in assets] def search_without_catalog(assets, task): """The files of each asset are opened in order; entries looked at = files opened.""" looked = 0 for v in assets: looked += v["files"] if covers_in_fact(v, task): return looked, v["name"] return looked, None def search_with_catalog(assets, catalog, task): """The entries are scanned; the asset of the matching entry is verified. If it is not found, the search falls back to the search without a catalog and the two costs are added.""" by_name = {v["name"]: v for v in assets} for i, g in enumerate(catalog, 1): if not covers_by_entry(g, task): continue v = by_name.get(g["name"]) if v is not None and covers_in_fact(v, task): return i, g["name"], False looked, name = search_without_catalog(assets, task) return i + looked, name, True return len(catalog), None, False TASKS = [("kuzey / 2026-03 / commercial", {"region": "kuzey", "period": "2026-03", "type": "commercial"}), ("guney / 2026-02", {"region": "guney", "period": "2026-02"}), ("bati / 2026-11 / residential", {"region": "bati", "period": "2026-11", "type": "residential"}), ("merkez / 2026-02", {"region": "merkez", "period": "2026-02"}), ("kuzey / 2026-05 / residential", {"region": "kuzey", "period": "2026-05", "type": "residential"})] ASSETS = setup_assets() CATALOG = write_catalog(ASSETS) print(f"assets {len(ASSETS)} , files {sum(v['files'] for v in ASSETS)} , " f"catalog entries {len(CATALOG)}\n") print("asset owner key files period columns") for v in ASSETS: print(f"{v['name']:<17} {v['owner']:<17} {str(v['key']):<19} {v['files']:>4}" f" {len(v['period']):>4} {len(v['columns']):>4}") print("\ntask no catalog catalog asset found") t1 = t2 = 0 for name, task in TASKS: b1, v1 = search_without_catalog(ASSETS, task) b2, v2, _ = search_with_catalog(ASSETS, CATALOG, task) t1, t2 = t1 + b1, t2 + b2 print(f"{name:<28} {b1:>10} {b2:>9} {v1}") print(f"{'total':<28} {t1:>10} {t2:>9}") ``` ``` assets 12 , files 119 , catalog entries 12 asset owner key files period columns central_warehouse central team ('period',) 6 6 5 mart_bati bati reporting ('period',) 11 11 4 mart_dogu dogu reporting ('period',) 11 11 4 mart_guney guney reporting ('period',) 11 11 4 mart_kuzey kuzey reporting ('period',) 11 11 4 mart_merkez merkez reporting ('period',) 11 11 4 mart_tariff tariff team ('period',) 11 11 5 product_bati bati unit ('period', 'type') 22 11 5 product_dogu dogu unit ('period',) 11 11 5 product_guney guney unit ('period',) 11 11 5 product_kuzey kuzey unit () 1 11 5 product_merkez merkez unit ('type',) 2 11 5 task no catalog catalog asset found kuzey / 2026-03 / commercial 72 7 mart_tariff guney / 2026-02 39 4 mart_guney bati / 2026-11 / residential 6 1 central_warehouse merkez / 2026-02 61 6 mart_merkez kuzey / 2026-05 / residential 117 11 product_kuzey total 295 29 ``` The ratio between them is tenfold: five searches look at 295 entries without a catalog and 29 with one. The source of the difference is not scale but **the unit**. A search without a catalog opens **all the files** of each asset, because there is no other way of knowing which periods or which tariffs an asset covers; a search with a catalog reads **a single entry** per asset. Twelve assets hold 119 files, and the catalog describes the same set in 12 entries. A catalog is not an index and does not speed up the query; what it gains for the search is **the ratio between the number of assets and the number of files**. The distribution among the tasks should also be read. The cheapest search is the one for `bati` (west): the central warehouse carries the tariff column, covers that period, and is the first asset in the order. The most expensive is `kuzey` (north) in `2026-05`: the warehouse drops out because of the retention window, the five region marts drop out because they do not carry the tariff column, the tariff mart drops out because it holds only commercial rows, and the search goes on until the eleventh asset — 117 files without a catalog, 11 entries with one. **What determines the cost of finding an asset is not the size of the set but the place of the sought thing in the order**; what the catalog does is convert that order from the unit of files to the unit of entries. #### The Staleness of the Catalog The catalog carries the layout of the moment it was written. The storage side does not stand still: a mart is removed, a unit product is repartitioned, a retention window is applied to a mart. These three changes are not reflected in the entries by themselves. The run below first counts how many entries have become wrong, then repeats the same five searches **with the old catalog on the new set**. ```python # The definitions of the previous block carry on: ASSETS, CATALOG, TASKS, asset, ALL_ROWS, LAST6, FULL, # REGIONAL, search_without_catalog, search_with_catalog. def layout_changed(assets): """Three changes on the storage side after the catalog was written.""" new = [] for v in assets: if v["name"] == "mart_tariff": # the asset was removed continue if v["name"] == "product_kuzey": # repartitioned new.append(asset(v["name"], v["owner"], [k for k in ALL_ROWS if k["region"] == "kuzey"], ("period", "type"), FULL)) elif v["name"] == "mart_guney": # the retention window narrowed new.append(asset(v["name"], v["owner"], [k for k in ALL_ROWS if k["region"] == "guney" and k["period"] in LAST6], ("period",), REGIONAL)) else: new.append(v) return new CHANGED = layout_changed(ASSETS) by_name = {v["name"]: v for v in CHANGED} print("stale entry registered key / files / period actual key / files / period") stale = 0 for g in CATALOG: v = by_name.get(g["name"]) if v is not None and all(g[a] == v[a] for a in ("key", "files", "period")): continue stale += 1 actual = ("no such asset" if v is None else f"{str(v['key']):<18} {v['files']:>3} {len(v['period']):>3}") print(f"{g['name']:<14} {str(g['key']):<18} {g['files']:>3} {len(g['period']):>3}" f" {actual}") print(f"\nof {len(CATALOG)} entries , {stale} now point to a layout that no longer exists.") print("\ntask no catalog catalog fallback asset found") t1 = t2 = 0 for name, task in TASKS: b1, v1 = search_without_catalog(CHANGED, task) b2, v2, fell = search_with_catalog(CHANGED, CATALOG, task) t1, t2 = t1 + b1, t2 + b2 print(f"{name:<28} {b1:>10} {b2:>9} {'yes' if fell else 'no':>8} {v2}") print(f"{'total':<28} {t1:>10} {t2:>9}") ``` ``` stale entry registered key / files / period actual key / files / period mart_guney ('period',) 11 11 ('period',) 6 6 mart_tariff ('period',) 11 11 no such asset product_kuzey () 1 11 ('period', 'type') 22 11 of 12 entries , 3 now point to a layout that no longer exists. task no catalog catalog fallback asset found kuzey / 2026-03 / commercial 122 129 yes product_kuzey guney / 2026-02 100 104 yes product_guney bati / 2026-11 / residential 6 1 no central_warehouse merkez / 2026-02 56 6 no mart_merkez kuzey / 2026-05 / residential 122 11 no product_kuzey total 406 251 ``` The three changes made three of the twelve entries wrong, and the three are not of the same kind. The `product_kuzey` entry points to an asset that still exists; only the registered key and file count are wrong, 1 instead of 22. This entry does not break the search, it breaks **the cost estimate**: a party that knows it will go to that asset thinks it will touch one file. The `mart_guney` entry promises eleven periods and the asset carries six. The `mart_tariff` entry points to an asset that does not exist at all. **That a catalog entry is green does not mean that the layout was measured**, and there are three separate degrees of wrongness: a missed estimate, a missed coverage, a complete absence. The last table shows what this does to the search. The catalog still gains in total, 251 against 406. But in two rows the gain turns negative. The search for `kuzey` in `2026-03` scans seven entries, goes to the dead entry, cannot find the asset, and falls back to a full scan from the beginning: 129, though without a catalog it would have been 122. The search for `guney` (south) in `2026-02` scans four entries, cannot find in the asset the period the entry promised, and takes the same road: 104, against 100 without a catalog. The difference is small but its sign matters; **a stale entry puts a wrong address in front of the search**, and that address cannot be eliminated without trying it. The cost of the catalog is not writing the entries but **refreshing the entries at the same speed as the layout**. #### Summary - A metadata catalog keeps a single entry per asset: name, owner, partition key, file count, columns, and the periods and regions covered. It is not an index; it makes the **search** cheaper, not the query. - On a set of twelve assets and 119 files five searches look at 295 entries without a catalog and 29 with one; the gain comes from the ratio between the number of assets and the number of files. - What determines the cost of a search is not the size of the set but the place of the sought asset in the order: the cheapest search looks at 6 against 1, the most expensive at 117 against 11 entries. - Three changes make three of the twelve entries wrong, in three separate degrees: a missed estimate (a file count of 22 instead of 1), a missed coverage (six periods instead of eleven) and a complete absence. - With the stale catalog the same five searches look at 251 entries against 406, but two searches end up more expensive than without a catalog: 129 against 122 and 104 against 100. - The cost of the catalog is not writing the entries but refreshing the entries at the same speed as the layout; an entry that is not refreshed puts a wrong address in front of the search. #### Next Step In six lessons **where the data stands** was separated: the place that conforms to the schema and the raw place, the layout that keeps the two together, what taking out a subset gains and multiplies, the two numbers of distributing ownership, and the cost of finding an asset in entries. All these measurements had a common silent assumption: the row always stood in the same form — subscriber, period, region, tariff and consumption side by side, in a single flat sequence. That form was never questioned. Yet what is written into a file is not a list of rows but **a table shape**; which fields are measurements and which are qualifiers, how many times the same qualifier is repeated, and whether the old value of a qualifier is kept when it changes over time are separate decisions. The next topic measures these decisions: the same data, the same place, a different **shape**. ## Modeling and Format Counting what the shape of a table and the format it lands on disk cost a query: the cells stored, the joins and the identical result when facts are separated from dimensions against a single flat table, 2200 joins and 212 dimension rows in the star schema against 4400 joins and 217 rows in the snowflake schema, with the join count doubling to gain five rows, a dimension that keeps no history making the answer wrong without slowing the query and sending 2748 cubic meters to the wrong bucket, with the commercial total coming out 35893 instead of 33145, against history keeping raising the dimension from 200 rows to 2200, the columnar read reading 400 rows against 1400 cells for the row-oriented read when two columns are selected and the point where the gap closes as the column count grows, the cost rising from 4520 to 11000 on a filter outside the partition key, and fine partitioning moving the cost from 520 to 1600 even on a pruning query without raising the cells read at all, and what moving to the archive gains in the hot tier against the question it makes impossible to answer retroactively. ### Dimensional Modeling Source: https://academia.sh/en/courses/data-storage-architectures/dimensional-modeling Measuring the cells stored and the joins charged to a query when facts are separated from dimensions: the same 2200 rows hold 15400 cells in a single flat table and 7236 cells in a fact table plus two dimensions, 8164 cells fewer, and the total per type from the two layouts stays exactly the same; in return the query touches one file and pays a cost of 4520, while fact plus dimension touches two files and reads 4800 cells for a cost of 5040, and zero joins become 2200; when a region name is corrected, 506 rows are updated in the flat table and 46 rows in the dimension. The previous six lessons separated **where** the data sits: the layer that enforces a schema, the raw layer, the setup that combines the two, domain-oriented subsets, the arrangements that distribute ownership, and the catalog that makes finding an asset possible. All of them shared one assumption: the rows sit somewhere, and someone looks them up. No measurement asked **in what layout** the rows sit. This lesson asks that question and starts from the oldest decision: should the measured value and the information that qualifies the measurement live in the same table. The consumption that comes out of a meter reading is a number; the subscriber's region and type, and the year and month of the period, qualify that number. A single table carries both side by side. Dimensional modeling separates them. What the split does to the stored cells, to the cost of the query and to **the result that comes out** is something that can be counted. - **MF1.** 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. - **MF2.** The pipeline outputs **2200** rows: 200 subscribers times twelve periods makes 2400, and 200 rows drop because the consumption of the first period cannot be computed. - **MF3.** The **fact table** carries the measured value; the **dimension table** carries the information that qualifies that value. The **grain** of the fact is the pair of subscriber and period. - **MF4.** A stored cell is the product of rows and columns. A field is one cell; the width of a field is not measured. - **MF5.** Cost reads as `files x 120 + cells`. **The per-file constant of 120 is a choice**; every result that is sensitive to the number of files is written together with the value of the constant. - **MF6.** The number of **joins** is how many times the query looks at the dimension table. Raw time is not written. - **MF7.** The baseline is **the single flat table**: unpartitioned, one file, join count **0**. - **MF8.** Resolution: the step of a row share is **0.0005**. **Totals are written as the values themselves, not as shares.** #### What the Single Table Carries The row before the fifth stage of the pipeline carries seven fields: subscriber, period, region, type, index, unit and the computed consumption. Writing this row to disk as it is is the layout that demands the fewest decisions; the query looks at a single place and never has to match one table against another. In return, the subscriber's region is rewritten in every row of that subscriber. If a subscriber has eleven rows, the region name is stored eleven times. The first block sets up the shared definition: the fictional source, the three stages of the pipeline that produce rows, writing a layout and a query that measures cost. The dimensional split lives in the `star` function: only the subscriber, the period and the consumption remain in the fact, and the qualifying fields move out to two dimension tables. ```python # FICTIONAL. Meter readings, pipeline and store 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 star(records): """Fact table plus two dimensions. The qualifying fields are taken out of the fact.""" fact = [{"subscriber": k["subscriber"], "period": k["period"], "m3": k["m3"]} for k in records] subscriber_dim = {k["subscriber"]: {"region": k["region"], "type": k["type"]} for k in records} period_dim = {d: {"year": d[:4], "month": d[5:]} for d in PERIODS} return fact, {"subscriber": subscriber_dim, "period": period_dim} ``` #### The Cells the Split Saves The second block builds the two layouts side by side and counts the stored cells. For the measurement to be honest, one intermediate row is needed. While building the fact, the `star` function does not carry the index and unit fields at all, because once the consumption is computed neither is needed. This is a gain of **dropping fields**, not of the dimensional split, and if it is not counted separately it gets credited to the split. For this reason the table lists the flat table twice: with seven fields and with the five fields the fact carries. Then the same question is asked of both layouts: what is the total consumption per type. ```python ROWS = rows() FACT, DIMENSION = star(ROWS) CARRIED = ("subscriber", "period", "region", "type", "m3") print(f"{'table':<22}{'rows':>7}{'columns':>9}{'stored cells':>16}") for name, n, s in (("flat, seven fields", len(ROWS), len(COLUMNS)), ("flat, five fields", len(ROWS), len(CARRIED)), ("fact", len(FACT), 3), ("dimension subscriber", len(DIMENSION["subscriber"]), 3), ("dimension period", len(DIMENSION["period"]), 3)): print(f"{name:<22}{n:>7}{s:>9}{n * s:>16}") flat7 = len(ROWS) * len(COLUMNS) flat5 = len(ROWS) * len(CARRIED) star_total = len(FACT) * 3 + len(DIMENSION["subscriber"]) * 3 + len(DIMENSION["period"]) * 3 print(f"{'star total':<22}{'':>7}{'':>9}{star_total:>16}") print("gain from dropping fields:", flat7 - flat5) print("gain from the split:", flat5 - star_total, " dimension rows:", len(DIMENSION["subscriber"]) + len(DIMENSION["period"])) def flat_type_total(records): total = {} for k in records: total[k["type"]] = total.get(k["type"], 0) + k["m3"] return total, 0 def star_type_total(fact, dimension): total, joins = {}, 0 for o in fact: t = dimension["subscriber"][o["subscriber"]]["type"] joins += 1 total[t] = total.get(t, 0) + o["m3"] return total, joins totals_flat, joins_flat = flat_type_total(ROWS) totals_star, joins_star = star_type_total(FACT, DIMENSION) print() print("flat result:", totals_flat, " joins:", joins_flat) print("star result:", totals_star, " joins:", joins_star) print("result identical:", totals_flat == totals_star) ``` ``` table rows columns stored cells flat, seven fields 2200 7 15400 flat, five fields 2200 5 11000 fact 2200 3 6600 dimension subscriber 200 3 600 dimension period 12 3 36 star total 7236 gain from dropping fields: 4400 gain from the split: 3764 dimension rows: 212 flat result: {'commercial': 29932, 'residential': 28522} joins: 0 star result: {'commercial': 29932, 'residential': 28522} joins: 2200 result identical: True ``` The total drops from **15400** to **7236**, that is, by **8164 cells**. **4400** of that number does not belong to the split: no longer carrying the index and unit fields removes two columns, which is 4400 cells over 2200 rows. The same operation could have been done in the flat table, and had it been done, the flat table would have come down to **11000** cells. The split's own gain is the difference between that line and the star total: **3764 cells**. Those 3764 cells are the repetition itself. The region and type fields sit in 200 rows, not in 2200, and the year and month of the period sit in twelve rows, not in 2200. All the dimension tables together hold **212 rows**. The gain comes from the ratio of fact rows to dimension rows: because every subscriber has eleven periods, each qualifying field is written once rather than eleven times. If this ratio approached one to one, that is, if every subscriber had a single period, the split would have nothing left to gain on the storage side. The result line is the first test of the split, and it passes: the two layouts produce the same dictionary. Dimensional modeling does not summarize, compress or sample the data; it changes only **where it is written**. The fact that it leaves no debt on the result side reduces every later measurement to a single axis: the same answer, a different cost. #### The Joins the Query Pays The cost is not in the result but in the way to the result. In the flat table, the type and the consumption sit in the same row, so the query reads a single file and does no matching. The fact table has no type; the query has to look at the dimension table for every fact row. The third block counts the files touched, the cells read and the cost paid by the two ways. ```python dim_subscriber_rows = [{"subscriber": a, **v} for a, v in DIMENSION["subscriber"].items()] q_flat = query(write(ROWS, ()), (), ("type", "m3")) q_fact = query(write(FACT, ()), (), ("subscriber", "m3")) q_dimension = query(write(dim_subscriber_rows, ()), (), ("subscriber", "type")) print(f"{'read':<22}{'files':>7}{'cells':>7}{'cost':>9}{'joins':>13}") print(f"{'flat table':<22}{q_flat['files']:>7}{q_flat['cells']:>7}" f"{q_flat['cost']:>9}{0:>13}") print(f"{'fact':<22}{q_fact['files']:>7}{q_fact['cells']:>7}{q_fact['cost']:>9}{'':>13}") print(f"{'dimension subscriber':<22}{q_dimension['files']:>7}{q_dimension['cells']:>7}" f"{q_dimension['cost']:>9}{'':>13}") print(f"{'fact + dimension':<22}{q_fact['files'] + q_dimension['files']:>7}" f"{q_fact['cells'] + q_dimension['cells']:>7}" f"{q_fact['cost'] + q_dimension['cost']:>9}{len(FACT):>13}") print("per-file constant:", FILE_CONSTANT, "cells") print() print(f"{'change':<26}{'flat table':>11}{'dimension':>11}") print(f"{'A007 type corrected':<26}" f"{sum(1 for k in ROWS if k['subscriber'] == 'A007'):>11}{1:>11}") print(f"{'north renamed':<26}" f"{sum(1 for k in ROWS if k['region'] == 'north'):>11}" f"{sum(1 for v in DIMENSION['subscriber'].values() if v['region'] == 'north'):>11}") print() DIMENSION_FIXED = len(DIMENSION["subscriber"]) * 3 + len(DIMENSION["period"]) * 3 print("dimension fixed cells:", DIMENSION_FIXED) print(f"{'fact rows':>12}{'five-field flat':>17}{'star':>9}{'gain':>9}") for n in (550, 1100, 2200, 4400): print(f"{n:>12}{5 * n:>17}{3 * n + DIMENSION_FIXED:>9}{2 * n - DIMENSION_FIXED:>9}") ``` ``` read files cells cost joins flat table 1 4400 4520 0 fact 1 4400 4520 dimension subscriber 1 400 520 fact + dimension 2 4800 5040 2200 per-file constant: 120 cells change flat table dimension A007 type corrected 11 1 north renamed 506 46 dimension fixed cells: 636 fact rows five-field flat star gain 550 2750 2286 464 1100 5500 3936 1564 2200 11000 7236 3764 4400 22000 13836 8164 ``` The first half of the table brings the rule of the course down to this lesson. The **8164 cells** gained in storage are not paid back on the read, they are stacked on top of it: answering the same question demands a cost of **4520** in the flat table and **5040** in fact plus dimension. The difference is **520** and has two parts: the 400 cells of the dimension table and the fixed **120**-cell cost of touching a second file. Because the per-file constant was chosen as 120, the second file costs that much; had the constant been chosen larger, the cost of the split on the read side would have grown as well. The joins column says where the difference comes from. It is **0** in the flat table and **2200** in fact plus dimension: the dimension is looked at once for every fact row. This is exactly the same number as the number of rows coming out of the pipeline, and it is no coincidence; the cost of the dimensional layout on the read side depends on the row count of the fact table. A layout's number is not the rows it stores but **what a query pays to touch it**; the number paid for this query is 5040, and without writing which query it is, this number means nothing. #### The Rows a Change Touches The second half of the table gives the counterpart of the split outside reading. If the type of a subscriber was recorded wrongly, **11** rows of that subscriber are corrected in the flat table and **1** row in the dimension. If the name of a region changes, **506** rows are updated in the flat table and **46** in the dimension. In both cases the information that changes is single; the number of rows that change equals the number of times that information is written. This is the most concrete gain of the split, and it is a direct consequence of the repetition. If the same information sits in 506 places, the update has to reach all 506 of them; if some place is left unreached, the same region shows up under two names. The dimension table shrinks the surface the update touches, that is, it shrinks the place where inconsistency can arise. The price is 2200 joins per query, and this trade is the whole of dimensional modeling. The last table gives how the two costs relate to scale. The cells of the dimension tables are **636** and do not grow as the fact grows: as long as the number of subscribers stays the same, 200 rows stay 200 rows. A flat table with five fields holds `5n` cells at `n` rows, while the star holds `3n + 636`, so the gain of the split is `2n - 636`. At the measured 2200 rows this comes to **3764**, and the block confirms it. The number falls to **464** at 550 rows and rises to **8164** at 4400 rows. The cost on the read side, however, is independent of scale: the dimension is read once in whichever query, and it costs **520**. This is why the split looks debatable on a small table and unavoidable on a large one; in both cases the thing measured is the same two numbers. #### Summary - The fact table carries the measured value and the dimension table carries the information that qualifies it; the 2200 rows, whose grain is the pair of subscriber and period, describe the same data in both layouts. - The stored cells fall from **15400** to **7236**, but **4400** of the drop belongs to the two fields that are no longer carried; the split's own gain is **3764 cells**, and all the dimension tables together hold **212 rows**. - The total per type that the two layouts produce is **exactly the same**; dimensional modeling changes not the result but the way to the result. - The cost is paid on the read: the same query demands a cost of **4520** in the flat table and **5040** in fact plus dimension, and the joins go from **0** to **2200**; 120 of the difference is the per-file constant. - The counterpart of the split outside reading is updating: a region name touches **506** rows in the flat table and **46** in the dimension, so the surface where inconsistency can arise shrinks elevenfold. #### Next Step In this lesson the dimension tables were built as flattened dimensions: the subscriber's region was written as the region name itself, in each of the 200 rows. The set of regions has five elements, so the same five texts repeat forty times in the dimension table. The operation applied to remove the repetition in the flat table can be applied here as well: moving the region to a separate table and leaving only a reference in the dimension. This builds a chain of two steps, not one, between the fact and the dimension. The next lesson counts that chain: as the degree of normalization rises, how many rows are gained, how many joins are paid in return, and whether the result moves. ### Star and Snowflake Schema Source: https://academia.sh/en/courses/data-storage-architectures/star-and-snowflake-schema Measuring what the degree of normalization gains on the dimension side in rows and what it makes the query pay in joins: when the region is moved to a separate table, dimension rows go from 212 to 217, dimension cells go from 636 to 646, and joins double from 2200 to 4400, while the region total the two schemas produce stays exactly the same; the cost rises from 5040 to 5170, and 120 of the difference is the per-file constant; when the region table carries a single attribute, normalization loses 10 cells, at the second attribute it starts to gain 185 cells, and correcting a region name touches 1 row instead of 46. The previous lesson built the dimension tables flattened: the subscriber's region was written as the region name itself in each of the 200 rows. The set of regions has five elements, so the same five texts repeat forty times in the dimension table. The repetition taken out of the fact reappeared inside the dimension, only at a scale eleven times smaller. It is possible to apply the same operation once more: moving the region to a separate table and leaving only a reference in the subscriber dimension. This puts a second link between the fact and the region name and changes the shape of the schema. The arrangement in which single-layer dimensions stand around the fact is called a **star schema**, and the arrangement in which the dimensions branch into their own subtables is called a **snowflake schema**. This lesson builds the two schemas from the same data and counts how many rows the degree of normalization gains on the dimension side and how many joins it costs on the query side. - **MF9.** 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. - **MF10.** The theory of normalization, functional dependency and the normal form procedure were measured in the **Data Modeling and Relational Theory** course and are **not repeated**. The question here is only **what the degree costs in dimension rows and in the number of joins**. - **MF11.** The two schemas share the same fact table; the only thing that changes is the arrangement of the dimensions. The grain of the fact is the pair of subscriber and period, and it is **2200 rows**. - **MF12.** The key of the region table is the region index. A key is a cell; its width is not measured. - **MF13.** The number of joins is how many times in total the query looks at the dimension tables. In the snowflake, for every fact row the subscriber dimension is looked at first, then the region table. - **MF14.** Cost reads as `files x 120 + cells`. **The per-file constant of 120 is a choice**, and a separate dimension table means at least one file. - **MF15.** The baseline is **the star schema**: two dimension tables, a single layer, no branching. - **MF16.** Resolution: the step of a row share is **0.0005**. **Totals are written as the values themselves, not as shares.** #### Building the Two Schemas The first block sets up the shared definition. The `star` function gives the layout of the previous lesson: the fact carries three fields, and the subscriber dimension carries the region name and the type. `snowflake` starts from the same data, turns the region name in the subscriber dimension into the region index and opens a region table of five rows. `region_total_query` answers the same question in both layouts and counts how many times it looked at a dimension along the way; in the snowflake one extra look is made for every row, because the index that comes out of the subscriber dimension is not a name. ```python # FICTIONAL. Meter readings, pipeline and store 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 star(records): """Flattened dimensions: the region name sits inside the subscriber dimension.""" fact = [{"subscriber": k["subscriber"], "period": k["period"], "m3": k["m3"]} for k in records] subscriber_dim = {k["subscriber"]: {"region": k["region"], "type": k["type"]} for k in records} period_dim = {d: {"year": d[:4], "month": d[5:]} for d in PERIODS} return fact, {"subscriber": subscriber_dim, "period": period_dim} def snowflake(records): """The same data , the region moved out to a separate table.""" fact, dim = star(records) subscriber_dim = {a: {"region_no": REGION.index(v["region"]), "type": v["type"]} for a, v in dim["subscriber"].items()} region_dim = {i: {"name": b} for i, b in enumerate(REGION)} return fact, {"subscriber": subscriber_dim, "region": region_dim, "period": dim["period"]} def region_total_query(fact, dim, snow=False): """Total per region. One more join in the snowflake.""" joins, total = 0, {} for o in fact: a = dim["subscriber"][o["subscriber"]] joins += 1 if snow: b = dim["region"][a["region_no"]]["name"] joins += 1 else: b = a["region"] total[b] = total.get(b, 0) + o["m3"] return total, joins ``` #### The Row Gained, the Join Paid The second block builds the two schemas, counts the dimension side and asks the same query of both. ```python ROWS = rows() STAR_FACT, STAR_DIM = star(ROWS) SNOW_FACT, SNOW_DIM = snowflake(ROWS) star_total, star_joins = region_total_query(STAR_FACT, STAR_DIM) snow_total, snow_joins = region_total_query(SNOW_FACT, SNOW_DIM, snow=True) star_rows = len(STAR_DIM["subscriber"]) + len(STAR_DIM["period"]) snow_rows = len(SNOW_DIM["subscriber"]) + len(SNOW_DIM["region"]) + len(SNOW_DIM["period"]) star_cells = len(STAR_DIM["subscriber"]) * 3 + len(STAR_DIM["period"]) * 3 snow_cells = len(SNOW_DIM["subscriber"]) * 3 + len(SNOW_DIM["region"]) * 2 + len(SNOW_DIM["period"]) * 3 print(f"{'schema':<12}{'dimension tables':>18}{'dimension rows':>16}{'dimension cells':>17}" f"{'joins':>13}") print(f"{'star':<12}{2:>18}{star_rows:>16}{star_cells:>17}{star_joins:>13}") print(f"{'snowflake':<12}{3:>18}{snow_rows:>16}{snow_cells:>17}{snow_joins:>13}") print("result exactly identical:", star_total == snow_total) print("result:", star_total) ``` ``` schema dimension tables dimension rows dimension cells joins star 2 212 636 2200 snowflake 3 217 646 4400 result exactly identical: True result: {'central': 14536, 'north': 13338, 'east': 9695, 'west': 9164, 'south': 11721} ``` The table carries the whole lesson. Dimension rows go from **212** to **217**: five rows, the region table itself. Dimension cells go from **636** to **646**. Joins, however, go from **2200** to **4400**, that is, they **double**. The result line says that both produce the same dictionary. The real finding here is the opposite of what was expected. Normalization removes repetition, so it should have saved space; in this measurement it **does not**. In the subscriber dimension the region index takes the place of the region name, but the number of columns does not change: 200 rows still carry three cells. The only thing gained is that the name is an index instead of text, and in this measurement a cell is a cell. In return, the five-row region table and its ten cells are added from the start. Five rows and ten cells on the negative side, and on the positive side joins that double. That the result stays exactly the same shows that this trade lies **only on the cost side**. The snowflake schema does not give an answer the star schema cannot give; it gives the same answer with twice the joins. The rule of the course reads this line: a layout's number is not the rows it stores but **what a query pays to touch it**; what is paid for this query is 4400 joins, and without writing which query it is, this number means nothing. This measurement is also the form that the second claim of the course takes in this lesson: **a layout decision can make the work worse.** A snowflake built with a region table that carries a single attribute loses ten cells, doubles the joins and gives no new answer in return. The reason the worsening goes unnoticed sits in the same line: the result does not move. #### Cost, Update and the Point Where Payment Begins The third block measures three things in a row: the files the same query touches in the two schemas and the cost it pays, the number of rows updated when a region name is corrected, and where the cell balance goes as the number of attributes the region table carries rises. ```python star_sub = [{"subscriber": a, **v} for a, v in STAR_DIM["subscriber"].items()] snow_sub = [{"subscriber": a, **v} for a, v in SNOW_DIM["subscriber"].items()] snow_region = [{"region_no": i, **v} for i, v in SNOW_DIM["region"].items()] q_fact = query(write(STAR_FACT, ()), (), ("subscriber", "m3")) q_star_sub = query(write(star_sub, ()), (), ("subscriber", "region")) q_snow_sub = query(write(snow_sub, ()), (), ("subscriber", "region_no")) q_snow_region = query(write(snow_region, ()), (), ("region_no", "name")) star_reads = [q_fact, q_star_sub] snow_reads = [q_fact, q_snow_sub, q_snow_region] print(f"{'schema':<12}{'files':>7}{'cells':>7}{'cost':>9}") for name, q in (("star", star_reads), ("snowflake", snow_reads)): print(f"{name:<12}{sum(x['files'] for x in q):>7}{sum(x['cells'] for x in q):>7}" f"{sum(x['cost'] for x in q):>9}") print("difference:", sum(x["cost"] for x in snow_reads) - sum(x["cost"] for x in star_reads), " of which", FILE_CONSTANT, "is the per-file constant") print() n_sub = sum(1 for v in STAR_DIM["subscriber"].values() if v["region"] == "north") print("a region name changes: star", n_sub, "rows , snowflake 1 row") print() print(f"{'region attributes':>19}{'star cells':>12}{'snowflake cells':>17}{'difference':>13}") for n in range(1, 7): star_h = len(STAR_DIM["subscriber"]) * (2 + n) snow_h = len(SNOW_DIM["subscriber"]) * 3 + len(SNOW_DIM["region"]) * (1 + n) print(f"{n:>19}{star_h:>12}{snow_h:>17}{star_h - snow_h:>13}") ``` ``` schema files cells cost star 2 4800 5040 snowflake 3 4810 5170 difference: 130 of which 120 is the per-file constant a region name changes: star 46 rows , snowflake 1 row region attributes star cells snowflake cells difference 1 600 610 -10 2 800 615 185 3 1000 620 380 4 1200 625 575 5 1400 630 770 6 1600 635 965 ``` The cost line gives a smaller difference than expected: **5040** against **5170**, a gap of **130**. **120** of it is the fixed cost of touching the third file, and the remaining **10** is the ten cells of the region table. The cells read hardly rise, because the region table has five rows. The cost of the additional table is this much because the per-file constant was chosen as 120; had the constant been chosen larger, the cost of the snowflake on the read side would have come almost entirely from the constant. The doubling of the joins is not visible in this measurement, because a join does not touch a file; the two things counted are separate costs, and a schema is not measured unless both are written. The update line is what normalization actually gets in return. When a region name is corrected, **46** subscriber rows are updated in the star schema and **1** row in the snowflake. Because in the star the name sits in forty-six places, the update has to reach forty-six places; in the snowflake the name has a single copy and cannot be found anywhere else. The gain appears not in storage but in **the surface where inconsistency can arise**. The relationship of the two sides to scale is opposite, and the schema decision comes out of this opposition. The extra join the snowflake charges is one per fact row: 2200 extra on 2200 rows, 4400 extra had the fact doubled. The update surface it gains is independent of the fact: the region name sits in **46** subscriber rows and, however much the fact grows, stays at 46 as long as the number of subscribers does not change. So as the data grows, the cost of branching grows and its gain stays where it is. For this reason the decision is made by looking not at how many times the dimension is repeated but at **the ratio of fact rows to dimension rows**; in this measurement the ratio is eleven, and every join is paid multiplied by that ratio. The last table says where the balance goes. While the region table carries a single attribute, normalization **loses 10 cells**. At the second attribute the table turns: the star has 800 and the snowflake 615, a difference of **185 cells** in favor of the snowflake. At the third attribute the difference rises to **380**, and at the sixth to **965**. Where the threshold lies depends on the repetition counts: 200 subscriber rows, 5 region rows and the attribute being written once per subscriber. If the information the region carries amounts to a single name, normalization does not pay; the moment the region starts to carry a few attributes, it starts to pay, and the join count stays doubled independently of this. #### Summary - The star schema keeps the dimensions flattened and the snowflake schema branches a dimension into its own subtable; the two schemas share the same fact table and the same 2200 rows. - When the region is moved to a separate table, dimension rows go from **212** to **217**, dimension cells go from **636** to **646**, and joins go from **2200** to **4400**, that is, they double. - The region total the two schemas produce is **exactly the same**; the snowflake does not give a new answer, it gives the same answer with twice the joins. - The cost rises from **5040** to **5170** and **120** of the difference is the per-file constant; the doubling of the joins is not visible in the cost, it is a separate cost and is written separately. - What normalization gets in return is the update surface: correcting a region name touches **46** rows in the star and **1** row in the snowflake. - The cell balance turns at the second attribute of the region table: with a single attribute the snowflake loses **10** cells, with two it gains **185**, with six it gains **965** cells. #### Next Step Both schemas worked in this lesson with a silent assumption: the subscriber's region and type **do not change**. The dimension table keeps a single row per subscriber, that row gives the subscriber's information, and which period of the fact is being looked at does not affect it. 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. Once a dimension row is overwritten, the facts of past periods are also qualified with the new information, and the query reports this nowhere. The next lesson measures this change: how much a dimension that keeps no history slows the query, how much it changes the answer, and what keeping history costs in dimension rows. ### Slowly Changing Dimensions Source: https://academia.sh/en/courses/data-storage-architectures/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. ### Columnar File Formats Source: https://academia.sh/en/courses/data-storage-architectures/columnar-file-formats Counting selective reading and compression in terms of cells read: when two columns are selected, the columnar read gives 400 cells and a cost of 520 while the row-oriented read gives 1400 cells and a cost of 1520; as the number of selected columns grows, the gap closes by 200 cells at a time and reaches exactly zero at the seventh column, that is, the gain of the columnar format is as large as the number of columns the query does not read. When compression is modeled in terms of cells read, this closing point comes out of the table: all seven columns come to 851 cells in the columnar layout and 1400 in the row-oriented layout. The gain is the work of the number of runs, not the number of distinct values; the region column carries five distinct values yet gives 158 runs. Ordering within the file is chosen for a single query: ordering by region and type brings that query from 253 cells down to 15 but takes the cubic meter column from 196 back up to 200, that is, to its uncompressed value. The previous three lessons discussed the shape of the table: the separation of fact and dimension, what the degree of normalization makes joins pay, a dimension that keeps no history silently making the answer wrong. In all three the table stood as a logical object: a set with rows, columns and a result. One thing was never asked: when this table lands on disk, what sits side by side, the rows or the columns. What this lesson measures is that decision. The measure does not change: files touched, cells read and cost. The only thing that changes is how many cells the same query has to read from the same file. Two questions are answered in turn. What does selective reading gain and where does that gain end; where does compression carry that limit. - **MF41.** The source, the consumption rows coming out of the pipeline and the file layout are **fictional** and are produced inside the lesson. No warehouse, lake, file format or query engine is called. The seed is 20260218. - **MF42.** **Columnar file format** and **row-oriented file format** are each a **class name**. The distinction is in a single behavior: the columnar read takes only the selected columns, the row-oriented read takes the whole record. - **MF43.** The record carries seven columns. The cells read are the product of the rows touched and the columns read. - **MF44.** Cost is the number of files times the **per-file constant of 120**, plus the cells read. The constant is a choice; in this lesson both layouts touch the same single file, so the constant **stays outside the difference**. - **MF45.** Compression is modeled **in terms of cells read**, and no algorithm is written. The compressed cost of a column is the number of **runs** in that column: identical values that follow one another in file order come down to a single cell. The compression procedure was measured in the Introduction to Linux course in the Linux and System Administration curriculum; it is **not repeated**. - **MF46.** In the row-oriented layout the unit of a run is the whole record. Because the pairs of subscriber and period are unique, two consecutive records are never equal and no shrinking takes place. - **MF47.** Ordering within the file is **a write decision** and can be **only one** per file. - **MF48.** The partition key is fixed in this lesson and is `period`. The key itself is the subject of the next lesson. #### What Selective Reading Makes You Pay The query wants the consumption of a single period by region; it needs two columns, `region` and `m3`. The block below sets up the pipeline, the layout and the cost measure, then sweeps the same query as the number of selected columns rises from one to seven. ```python # FICTIONAL. The meter readings of the measurement network , the consumption rows and the file # layout are produced inside the lesson; no 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 # values stay Turkish: sorting by region and type depends on their order # dogu/bati/kuzey/guney/merkez = east/west/north/south/central ; mesken/isyeri = residential/commercial REGION = ["dogu", "bati", "kuzey", "guney", "merkez"] TYPE = ["mesken", "isyeri"] PERIODS = [f"2026-{a:02d}" for a in range(1, 13)] COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3") FILE_CONSTANT = 120 # the fixed cost of touching a file , in cells 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 rows(): """Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe stages drop no rows , so consumption is computed directly from the source , by period difference.""" raw = [] 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) raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"], "index": e, "unit": "m3"}) table, out = {(k["subscriber"], k["period"]): k for k in raw}, [] for k in raw: i = PERIODS.index(k["period"]) prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None if prev is not None: out.append(dict(k, m3=k["index"] - prev["index"])) return out def write(records, partitioning=()): """Distributes the rows to files by the partition key.""" layout = {} for k in records: layout.setdefault(tuple(k[a] for a in partitioning), []).append(k) return layout def query(layout, partitioning, selected, filters=None, columnar=True): """Files touched , cells read and cost = files x FILE_CONSTANT + cells. The columnar read takes only the selected columns , the row-oriented read takes the whole record.""" filters = {} if filters is None else filters pruning = {a: v for a, v in filters.items() if a in partitioning} files, cells = 0, 0 width = 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) * width return {"files": files, "cells": cells, "cost": files * FILE_CONSTANT + cells} ROWS = rows() LAYOUT = write(ROWS, ("period",)) ADD_ORDER = ["region", "m3", "subscriber", "type", "unit", "index", "period"] print(f"pipeline rows {len(ROWS)} , files in the period layout {len(LAYOUT)} ," f" rows per file {len(ROWS) // len(LAYOUT)}") print("\nselected columns columnar cells cost row-oriented cells cost difference") for n in range(1, len(COLUMNS) + 1): sel = tuple(ADD_ORDER[:n]) a = query(LAYOUT, ("period",), sel, {"period": "2026-07"}, True) b = query(LAYOUT, ("period",), sel, {"period": "2026-07"}, False) print(f"{n:>16} {a['cells']:>14} {a['cost']:>7} {b['cells']:>18}" f" {b['cost']:>7} {b['cost'] - a['cost']:>10}") ``` ``` pipeline rows 2200 , files in the period layout 11 , rows per file 200 selected columns columnar cells cost row-oriented cells cost difference 1 200 320 1400 1520 1200 2 400 520 1400 1520 1000 3 600 720 1400 1520 800 4 800 920 1400 1520 600 5 1000 1120 1400 1520 400 6 1200 1320 1400 1520 200 7 1400 1520 1400 1520 0 ``` The second row is the starting number of the lesson. Of two layouts touching the same file, the same 200 rows and the same filter, one reads **400 cells** and pays **520**, the other reads **1400 cells** and pays **1520**. Neither is smarter; one has to take five columns the query did not ask for, the other does not. The real finding of the table is the constancy of the two columns on the right. The row-oriented read stays at **1400 cells** and does not react at all to the number of selected columns, because it already took the whole record on the first query. The columnar read grows by exactly **200 cells** with every added column. The difference therefore falls steadily: 1200, 1000, 800, 600, 400, 200 and at the seventh column **0**. The closing point is not a threshold but a definition. What the columnar format gains is the number of columns the query **does not read**, no more and no less. At the seventh column the gain is zero, because there the query already asks for the whole record. To call a layout "fast because it is columnar" means this table was not read: it is true for a query that reads two columns and false for a query that reads seven. The file constant has no share in this table. Because both layouts touch a single file, the fixed part of the cost is **120** in both, and the cost difference equals the cell difference exactly. Had the constant been 60 or 480, the right-hand column would not have changed. This is a result insensitive to the constant, and the opposite is seen in the next lesson. #### Compression Is Measured in Cells Read The model above counts every cell as one unit. The second gain of the columnar layout lies here: because all the values of a column sit **adjacent**, consecutive repetitions can come down to a single cell. The block below measures this shrinking in runs and counts the seven columns of the file one by one. ```python # This block continues the COLUMNS , FILE_CONSTANT , rows() and write() definitions of the first block. def run_count(values): """The number of blocks formed by consecutive identical values.""" n, prev = 0, object() for d in values: if d != prev: n += 1 prev = d return n def compressed_cells(records, selected, columnar=True): """Compressed read , in cells. In the columnar layout each column is adjacent within itself and consecutive identical values come down to a single cell; in the row-oriented layout the record stands whole , the keys of two records are never the same , so no shrinking takes place.""" if not columnar: return len(records) * len(COLUMNS) return sum(run_count([k[s] for k in records]) for s in selected) FILE = write(rows(), ("period",))[("2026-07",)] print(f"single file , period 2026-07 , rows {len(FILE)}") print("\ncolumn distinct values runs uncompressed compressed") for s in COLUMNS: print(f"{s:<11}{len(set(k[s] for k in FILE)):>15} {run_count([k[s] for k in FILE]):>4}" f" {len(FILE):>12} {run_count([k[s] for k in FILE]):>10}") raw, comp = len(FILE) * len(COLUMNS), compressed_cells(FILE, COLUMNS) rowwise = compressed_cells(FILE, COLUMNS, False) print(f"\nseven columns , columnar uncompressed: cells {raw} , cost {raw + FILE_CONSTANT}") print(f"seven columns , columnar compressed : cells {comp} , cost {comp + FILE_CONSTANT}") print(f"seven columns , row-oriented : cells {rowwise} , cost {rowwise + FILE_CONSTANT}") ``` ``` single file , period 2026-07 , rows 200 column distinct values runs uncompressed compressed subscriber 200 200 200 200 period 1 1 200 1 region 5 158 200 158 type 2 95 200 95 index 200 200 200 200 unit 1 1 200 1 m3 46 196 200 196 seven columns , columnar uncompressed: cells 1400 , cost 1520 seven columns , columnar compressed : cells 851 , cost 971 seven columns , row-oriented : cells 1400 , cost 1520 ``` The closing point of the previous section comes out of the table with this block. The query that reads all seven columns reads **851 cells** in the columnar layout and **1400 cells** in the row-oriented layout; the cost is **971** against **1520**. Where the gain of selective reading ends, the gain of compression continues, and the difference stops at **549 cells**. The parting of the run column and the distinct value column is the most misleading place of the lesson. `region` carries **five** distinct values and yet gives **158 runs**: because the file is written in subscriber order, the five values are scattered over two hundred rows and there are hardly any consecutive repetitions. `period` and `unit` carry a single value and come down to **1 cell**; neither changes across the file. What compresses is not **a small number of different values** but **identical values standing side by side**. The `subscriber` and `index` columns are the opposite end of this. Both are unique per row, so they give **200 runs** and stay exactly the same as the uncompressed value. A query that reads these two columns sees no compression gain at all in the columnar layout. #### Ordering Is Chosen for a Single Query The number of runs depends on the order in which the file is written, so it can be changed by a write decision. The block below writes the same file in three different orders and compares the cells read by three queries. ```python # This block continues the definitions of the previous two blocks. ORDERINGS = {"subscriber (natural)": lambda k: (k["subscriber"],), "region , type": lambda k: (k["region"], k["type"]), "m3": lambda k: (k["m3"],)} QUERIES = {"region+type": ("region", "type"), "m3": ("m3",), "subscriber+index": ("subscriber", "index")} FILE = write(rows(), ("period",))[("2026-07",)] print("single file , cells read ; in parentheses cost = 1 x 120 + cells") print("\nordering " + "".join(f"{a:>18}" for a in QUERIES) + f"{'seven columns':>18}") for name, key in ORDERINGS.items(): s = sorted(FILE, key=key) h = [compressed_cells(s, sel) for sel in QUERIES.values()] + [compressed_cells(s, COLUMNS)] print(f"{name:<21} " + "".join(f"{f'{x} ({x + FILE_CONSTANT})':>18}" for x in h)) h = [len(FILE) * len(sel) for sel in QUERIES.values()] + [len(FILE) * len(COLUMNS)] print(f"{'uncompressed':<21} " + "".join(f"{f'{x} ({x + FILE_CONSTANT})':>18}" for x in h)) ``` ``` single file , cells read ; in parentheses cost = 1 x 120 + cells ordering region+type m3 subscriber+index seven columns subscriber (natural) 253 (373) 196 (316) 400 (520) 851 (971) region , type 15 (135) 200 (320) 400 (520) 617 (737) m3 252 (372) 46 (166) 400 (520) 700 (820) uncompressed 400 (520) 200 (320) 400 (520) 1400 (1520) ``` The second row is the largest gain. When the file is ordered by region and type, the query reading these two columns goes **from 253 cells to 15 cells**, and the cost falls from **373** to **135**. The five regions and the two types now stand as adjacent blocks, and fifteen runs are enough to describe the whole file. The second column of the same row carries the second claim of this course: **a layout can also make things worse.** Ordering by region raises the `m3` column **from 196 runs to 200 runs**. The number is small but the direction is clear: for that column the ordering is not merely useless, it is **worse than the natural order** and lands in exactly the same place as the uncompressed value (**200**). The query that reads the cubic meter column loses all of its compression because of a decision taken for the region query. The third row shows the same trade from the opposite direction. When the file is ordered by cubic meters, that column comes down to **46 cells**, while region and type stay at **252**, that is, indistinguishable from the 253 of the natural order. A file can have **only one** ordering; the two cannot be chosen at the same time. The last column gives the total price of the decision: all seven columns come to **851** in the natural order, **617** in the region order and **700** in the cubic meter order. All three numbers are below the **1400** of the row-oriented layout, but which one gets chosen depends only on **which query's name is written**. A file whose ordering was chosen without a query name has, by the rule of the course, not been measured. #### Summary - The difference between the columnar and the row-oriented format is a single behavior: the columnar read takes the selected columns, the row-oriented read takes the whole record. On a two-column query the columnar layout pays **400 cells / a cost of 520** and the row-oriented layout pays **1400 cells / a cost of 1520**. - The gain of selective reading is as large as the number of columns the query does not read, and it melts by 200 cells with every added column; **it is exactly zero at the seventh column**. Because both layouts touch a single file, the per-file constant of 120 stays outside this difference. - When compression is modeled in terms of cells read, the closing point comes out of the table: all seven columns are **851** cells in the columnar layout and **1400** in the row-oriented layout. - What compresses is not a small number of distinct values but identical values standing side by side: `region` gives **158 runs** with five distinct values while `period` comes down to **1 run** with a single value; `subscriber` and `index`, which are unique per row, do not compress at all. - Ordering within the file is chosen for one query and takes away the gain of another: ordering by region and type brings that query **from 253 to 15 cells** while taking the `m3` column **from 196 to 200**, that is, back to its uncompressed value. #### Next Step All the numbers of this lesson were measured **inside a single file**. The partition key was held fixed as `period`, the filter always fell on that key and every query touched a single file; for this reason the per-file constant changed no difference. The key itself was never questioned. The next lesson removes this constant and asks two questions: what happens when the filter falls on a field **outside** the partition key, and where does splitting the same data into more files move the cost, even if the cells read do not rise at all. ### Partitioning and File Layout Source: https://academia.sh/en/courses/data-storage-architectures/partitioning-and-file-layout Counting what the choice of partition key and the fineness of the partition make a query pay: four layouts give exactly the same answer to the same three questions (200, 1122 and 46 rows) but their costs part ways. When the filter falls outside the partition key no file can be pruned, and the cost of 4520 in the unpartitioned layout rises to 11000 in the period plus region layout; the whole of the difference is the per-file constant paid fifty-four times more. Even on a pruning query the fineness carries the cost from 520 to 1000 and to 1600, while the cells read stay at 400 in all three layouts: this is the small file problem, and while the fixed share of the cost rises from 120 to 600 and to 1200, the cell share does not change at all. In three different mixes of the same ten queries the cheapest layout turns out to be three different layouts, so the key is chosen by looking at the query mix and not at the data. All the numbers of the previous lesson were measured **inside a single file**. The partition key was held fixed as `period`, the filter always fell on that key and every query touched exactly one file. For this reason the per-file constant entered no difference at all and the gain of the columnar read looked clean. The key itself was never questioned. This lesson removes the constant. Two questions are answered in turn: what happens when the filter falls on a field **outside** the partition key, and where does splitting the same data into more files move the cost, even if the cells read do not rise at all. The answer to the second question is the clearest example of the second claim of this course. - **MF49.** The source, the consumption rows and the file layout are **fictional** and are produced inside the lesson. No warehouse, lake, file format or query engine is called. The seed is 20260218. - **MF50.** **Partitioning** is distributing rows to separate **partitions** by the value of a key; in this model each partition is **a single file** and the two words are used as synonyms. The partitioning procedure was measured in the Relational Database Administration course in the Databases curriculum; it is **not repeated**. - **MF51.** **Sharding** is a separate concept and is **not confused** with it: sharding distributes rows to independent nodes that have their own storage. It was measured in the Scaling the Data Layer course in the System Design and Distributed Systems curriculum. In this lesson there is a single storage; what is measured is **the file layout**, not the distribution over nodes. - **MF52.** **Partition pruning**: if the field in the filter is in the partition key, the files that do not match are **never touched**. A field outside the key prunes no file and all the files are read from end to end. - **MF53.** Cost is the number of files touched times the **per-file constant of 120**, plus the cells read. The constant is a choice; most of the differences measured in this lesson are **of the same kind as the constant itself**, and every result is written together with the value of the constant. - **MF54.** The query reads two columns (`region` and `m3`); the read is columnar. The effect of column selection was measured in the previous lesson and is held fixed here. - **MF55.** The three questions are fixed: **A** asks for a single period, **B** for a single type, **C** for a single region of a single period. The correct answer to all three is independent of the layout. #### The Partition Key and Pruning The block below writes the 2200 rows coming out of the pipeline in four different layouts and asks the three questions in each layout. Next to the number of files, the row counts of the smallest and the largest file are printed as well. ```python # FICTIONAL. The meter readings of the measurement network , the consumption rows and the file # layout are produced inside the lesson; no 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)] FILE_CONSTANT = 120 # the fixed cost of touching a file , in cells 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 rows(): """Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe stages drop no rows , so consumption is computed directly from the source , by period difference.""" raw = [] 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) raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"], "index": e, "unit": "m3"}) table, out = {(k["subscriber"], k["period"]): k for k in raw}, [] for k in raw: i = PERIODS.index(k["period"]) prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None if prev is not None: out.append(dict(k, m3=k["index"] - prev["index"])) return out def write(records, partitioning=()): """Distributes the rows to files by the partition key; each partition is a file.""" layout = {} for k in records: layout.setdefault(tuple(k[a] for a in partitioning), []).append(k) return layout def query(layout, partitioning, selected, filters=None): """A filter field in the partition key prunes files; a field outside the key prunes no file and has the whole file read.""" 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 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) * len(selected) 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} SELECTED = ("region", "m3") LAYOUTS = [("unpartitioned", ()), ("period", ("period",)), ("period+region", ("period", "region")), ("period+region+type", ("period", "region", "type"))] QUESTIONS = [("A period", {"period": "2026-07"}), ("B type", {"type": "commercial"}), ("C period+region", {"period": "2026-07", "region": "north"})] ALL_ROWS = rows() print(f"pipeline rows {len(ALL_ROWS)} , selected columns {len(SELECTED)} ," f" file constant {FILE_CONSTANT} cells") print("A: period filter , B: type filter , C: period+region filter") print("\nlayout files small large" + "".join(f"{a[0] + ' files/cells/cost':>21}" for a, _ in QUESTIONS)) answer = {} for name, part in LAYOUTS: d = write(ALL_ROWS, part) size = [len(x) for x in d.values()] measured = [] for qname, f in QUESTIONS: s = query(d, part, SELECTED, f) answer.setdefault(qname, set()).add(s["matching_rows"]) measured.append(f"{s['files']}/{s['cells']}/{s['cost']}") print(f"{name:<19} {len(d):>5} {min(size):>5} {max(size):>5}" + "".join(f"{x:>21}" for x in measured)) print("\nmatching rows , in all four layouts: " + " , ".join(f"{a[0]} {sorted(answer[a])[0]}" for a, _ in QUESTIONS)) ``` ``` pipeline rows 2200 , selected columns 2 , file constant 120 cells A: period filter , B: type filter , C: period+region filter layout files small large A files/cells/cost B files/cells/cost C files/cells/cost unpartitioned 1 2200 2200 1/4400/4520 1/4400/4520 1/4400/4520 period 11 200 200 1/400/520 11/4400/5720 1/400/520 period+region 55 31 49 5/400/1000 55/4400/11000 1/92/212 period+region+type 110 12 28 10/400/1600 55/2244/8844 2/92/332 matching rows , in all four layouts: A 200 , B 1122 , C 46 ``` The last line is the precondition of the whole table: all four layouts give **exactly the same** answer to all three questions, 200, 1122 and 46 rows. What is measured is not correctness but what the same answer costs. The first row gives the floor. The unpartitioned layout has a single file, every question reads it from end to end and the cost is **4520** on all three questions. This layout is **indifferent** to the question; it neither rewards nor punishes. Question A is the promise of partitioning. In the `period` layout the filter falls on the key, ten files are never opened and the cells read fall **from 4400 to 400**; the cost drops **from 4520 to 520**. Up to here partitioning pays off at every scale. Question B is the limit of the promise and the clearest example of the **second claim** of this course. The filter falls on the `type` field, `type` is not among the first two keys of any layout, so **there is no pruning** and all the files are read. The cells read are **4400** in three layouts; the only thing that changes is the number of files: 1, 11 and 55. The cost becomes **4520**, **5720** and **11000**. The **6480** difference between layouts that read the same cells and return the same 1122 rows is nothing other than the per-file constant being paid **54 times more**. Partitioning has made the work worse here. The break-even point of this row can also be calculated and it is surprisingly low. Without pruning the cells read are 4400 regardless of the number of files, so the cost grows linearly with the number of files and equals the 4520 of the unpartitioned layout only at **a single file**. For a filter outside the key, **more than one partition is always a loss**; not even the size of the loss, but its existence, is open to debate. The fourth row is the first fix that comes to mind: `type` is put in the key as well. Question B now prunes, the files fall to 55 and the cells drop to **2244**. The cost, however, is **8844** and is still nearly twice that of the unpartitioned layout. The 2156 units gained in cells do not cover the 6600 units that are the fixed share of fifty-five files. The price of adding a column to the key for one query is **110 files**, and that price is reflected on all the other queries as well. #### The Small File Problem The second example of the second claim is seen where pruning **works**. Question A prunes in all three partitioned layouts; the block below splits the cost into two parts. ```python # This block continues the rows() , write() , query() , SELECTED , ALL_ROWS , LAYOUTS and # QUESTIONS definitions of the first block. print("Question A (pruning filter): fixed share and cell share of the cost") print("\nlayout touched files fixed share cell share cost") for name, part in LAYOUTS: s = query(write(ALL_ROWS, part), part, SELECTED, {"period": "2026-07"}) print(f"{name:<19} {s['files']:>15} {s['files'] * FILE_CONSTANT:>11}" f" {s['cells']:>11} {s['cost']:>7}") MIXES = [("A-heavy", (8, 0, 2)), ("C-heavy", (2, 0, 8)), ("B-heavy", (1, 8, 1))] print("\ntotal cost of the ten-query mix") print("\nmix (A,B,C) " + "".join(f"{a:>20}" for a, _ in LAYOUTS) + " cheapest") for mname, weight in MIXES: total = [] for name, part in LAYOUTS: d = write(ALL_ROWS, part) total.append(sum(n * query(d, part, SELECTED, f)["cost"] for n, (_, f) in zip(weight, QUESTIONS))) print(f"{mname:<19} " + "".join(f"{t:>20}" for t in total) + f" {LAYOUTS[total.index(min(total))][0]}") ``` ``` Question A (pruning filter): fixed share and cell share of the cost layout touched files fixed share cell share cost unpartitioned 1 120 4400 4520 period 1 120 400 520 period+region 5 600 400 1000 period+region+type 10 1200 400 1600 total cost of the ten-query mix mix (A,B,C) unpartitioned period period+region period+region+type cheapest A-heavy 45200 5200 8424 13464 period C-heavy 45200 5200 3696 5856 period+region B-heavy 45200 46800 89212 72684 unpartitioned ``` The cell share column is the unread finding of the first table: **the cell share is 400 in all three partitioned layouts.** The 200 rows the query needs neither rise nor fall; finer partitioning merely distributes them to more files. With no growth at all in the data read, the cost rises **from 520 to 1600**, that is, triples, and the whole of the increase is in the fixed share: **120, 600, 1200**. **The small file problem is this**: while the data a query reads does not change, the number of files opened to reach that data grows. The dependence of the result on the constant is written openly here. The difference is entirely of the kind of the **per-file constant of 120**; had the constant been 60, the costs of the three layouts would have been 460, 700 and 1000, and had it been 480, they would have been 880, 2800 and 5200. The order does not change, because as long as the constant is positive, more files cost more; what changes is the **magnitude**. The sentence "fine partitioning is expensive" is not measured unless the value of the constant is written. What the constant stands for has to be said here. Touching a file has a cost independent of its row count: finding the file, opening it, reading its header and working out which column sits where. This work is the same whether the file carries twelve rows or two thousand two hundred. All the model does is write that cost **in cells**; the number 120 is not a measurement but a stand-in given to reduce the measure to a single unit. As the number of partitions grows, the files also become **unequal**. The small and large columns of the first table give this: in the 55-file layout the files are between 31 and 49 rows, in the 110-file layout between 12 and 28. Because subscribers are not evenly distributed over regions, the averages of 40 and 20 rows describe no file; the fixed cost, however, is the same for the smallest file and for the largest. The two files at the extremes make this plain: in the 110-file layout the smallest file has two columns read for **24 cells**, and **more than four fifths** of the cost of touching that file is the constant; the single file of the unpartitioned layout has 4400 cells read and the share of the constant stays under three percent. What partitioning makes you pay is dividing the constant over fewer and fewer rows. #### The Key Is Chosen for a Query Mix The second table gives the total cost of three different mixes of ten queries and carries the decision of the lesson here. In the A-heavy mix the cheapest layout is `period`, in the C-heavy mix `period`+`region`, and in the B-heavy mix the **unpartitioned** layout. Three mixes, three winners. The middle row says the opposite of the one before it. Because query C applies a filter to both the period and the region, it goes down to a single file in the `period`+`region` layout and gives **92 cells / a cost of 212**; the same query reads 400 cells in the `period` layout. The small file problem is absent in this query, because the fineness **overlaps** with the filter of the query. The same 55-file layout is a penalty in query A and a reward in query C. The last row is the harshest: in a workload that uses only filters outside the key, not partitioning at all is the cheapest decision, 45200 against 46800, 89212 and 72684. Partitioning is a price that is paid when the query has not been written but for which nothing is received in return. These three rows are the definition of the rule of the course. A layout's number is not the rows it stores but what a query pays to touch it; **a layout whose query is not written down as the one it was chosen for has not been measured.** A partition key cannot be chosen by looking at the data, because all four layouts store the same data and give the same answer. The choice is made only by looking at the query mix, and when the mix changes the decision is measured again. #### Summary - If the field in the filter is in the partition key, the files that do not match are never touched: query A reads 4400 cells in the unpartitioned layout and 400 in the `period` layout, and the cost falls from 4520 to 520. - When the filter falls outside the key there is no pruning, and partitioning makes the work worse: in query B, while the cells read are 4400 in all three layouts, the cost is **4520, 5720 and 11000**; the whole of the 6480 difference is the per-file constant paid 54 times more. Adding `type` to the key pulls the cost to 8844, but at the price of 110 files. - Even on a pruning query, fineness writes a penalty: in query A the cells read are **400** in all three partitioned layouts, the cost becomes **520, 1000, 1600** and the whole of the increase is in the fixed share (120, 600, 1200). That is the small file problem. - The result is of the kind of the per-file constant of 120: at a constant of 60 the three values become 460/700/1000, and at 480 they become 880/2800/5200. The order depends on the sign of the constant, the magnitude on its value. - The four layouts give the same answer to all three questions (200, 1122, 46 rows); in the three mixes of ten queries the cheapest layout comes out as `period`, `period`+`region` and unpartitioned respectively. The key is chosen by looking at the query mix and not at the data. #### Next Step In all the layouts of this lesson, all twelve periods stood in the same place. No row was deleted, no period was moved to another tier; the oldest period could be read at the same price as the newest. Yet the per-file constant is the same for every file, and the number of files grows with the period. The last lesson turns this accumulation into a decision: when the old periods are taken out of the hot tier, how many files and how much cost remain in the hot tier, what does reading from the archive cost, and **which question can no longer be answered** retroactively. ### Data Retention Policy Source: https://academia.sh/en/courses/data-storage-architectures/data-retention-policy Counting what moving to the archive gains and what it closes: in a six-period hot window the hot tier falls from 2200 rows to 1200 and from 55 files to 30, a query with a filter outside the key pays 6000 instead of 11000, and because the 1000 archived rows come down to 25 summary entries the twelve-period region total costs 6675 instead of 11000; the more is archived, the cheaper the fully dated query becomes. The price is in resolution: the region breakdown is answered in all eleven periods and the hot tier plus the archive total comes out exactly equal to the full data, whereas the type, subscriber and region plus type breakdowns are answered in only six periods and 10, 1000 and 50 buckets are closed respectively. An unguarded type query does not raise an error, it returns the commercial total as 16818 instead of 29932 and 13114 cubic meters stay invisible. The lesson is the wrap-up of the course. In every layout of the previous lesson all twelve periods stood together; the oldest period was read at the same price as the newest. Yet the per-file constant is the same for every file, and in a partitioned layout the number of files grows with the period: a year later the same query touches twice the files. This lesson turns that accumulation into a decision. A retention policy moves old periods to the archive, and three things are measured: the files and cost left in the hot tier, the price of reading from the archive, and retroactively **which question can no longer be answered**. The third is the real finding; the first two are gains and alone always turn the decision the same way. - **MF56.** The source, the consumption rows, the hot tier and the archive are **fictional** and are produced inside the lesson. No warehouse, lake, cloud service or query engine is called. The seed is 20260218. - **MF57.** The **retention window** keeps the last N periods in the hot tier. The hot tier is written with `period`+`region` partitioning and the query reads two columns; both were measured in the previous two lessons and are held fixed here. - **MF58.** **Archiving** is not deleting: the rows stay but stay **at another resolution**. The archive reduces them to the `region` by `period` total in a single file; the subscriber and type breakdowns are not carried over, and all the loss numbers of the lesson depend on this choice. - **MF59.** An archive entry is three cells: region, period, total. The **archive constant is 600**, five times the file constant of the hot tier; both are choices. - **MF60.** The retention and lifecycle procedure was measured in the Storage and Data in the Cloud course in the Cloud Computing curriculum; it is **not repeated**. The effect of deletion on retroactive checking takes **input** from the Data Governance lesson of the Introduction to Data Engineering course: the six-period window was deleting six periods and raising the period that cannot be checked **from 1 to 7**. #### What Moving to the Archive Gains The block below narrows the hot window from twelve periods to one; each step prints the files left, the cost of the query with a filter outside the key, the archive size and the end-to-end cost of the twelve-period region total. ```python # FICTIONAL. The meter readings of the measurement network , the consumption rows , the hot tier # and the archive are produced inside the lesson; no data warehouse , data lake , cloud service # 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)] HOT_CONSTANT = 120 # the fixed cost of touching a file in the hot tier ARCHIVE_CONSTANT = 600 # the fixed cost of touching a file in the archive , a choice 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 rows(): # Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe # drop no rows , so consumption is computed directly from the source , by period difference. raw = [] 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) raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"], "index": e, "unit": "m3"}) table, out = {(k["subscriber"], k["period"]): k for k in raw}, [] for k in raw: i = PERIODS.index(k["period"]) prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None if prev is not None: out.append(dict(k, m3=k["index"] - prev["index"])) return out def write(records, partitioning=()): layout = {} for k in records: layout.setdefault(tuple(k[a] for a in partitioning), []).append(k) return layout def query(layout, partitioning, selected, filters=None): # A field in the key prunes files , a field outside the key does not. filters = {} if filters is None else filters pruning = {a: v for a, v in filters.items() if a in partitioning} files, cells = 0, 0 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) * len(selected) return {"files": files, "cells": cells, "cost": files * HOT_CONSTANT + cells} def archive(records): # The rows come down to the region x period total in a single file; subscriber and type are not carried. summary = {} for k in records: summary[(k["region"], k["period"])] = summary.get((k["region"], k["period"]), 0) + k["m3"] return summary PARTS = ("period", "region") SELECTED = ("region", "m3") ALL_ROWS = rows() print(f"pipeline rows {len(ALL_ROWS)} , periods carrying consumption" f" {len(set(k['period'] for k in ALL_ROWS))} , hot constant {HOT_CONSTANT} ," f" archive constant {ARCHIVE_CONSTANT}") print("\n window hot rows hot files B hot archive rows summary entries archive cost 12-period total") for p in (12, 9, 6, 3, 1): keep = set(PERIODS[len(PERIODS) - p:]) hot = [k for k in ALL_ROWS if k["period"] in keep] cold = [k for k in ALL_ROWS if k["period"] not in keep] d = write(hot, PARTS) b = query(d, PARTS, SELECTED, {"type": "commercial"}) summary = archive(cold) # The archive is a single file , three cells per entry: region , period , total. archive_cost = ARCHIVE_CONSTANT + 3 * len(summary) if cold else 0 print(f"{p:>7} {len(hot):>11} {len(d):>11} {b['cost']:>8} {len(cold):>12}" f" {len(summary):>15} {archive_cost:>12} {query(d, PARTS, SELECTED)['cost'] + archive_cost:>18}") ``` ``` pipeline rows 2200 , periods carrying consumption 11 , hot constant 120 , archive constant 600 window hot rows hot files B hot archive rows summary entries archive cost 12-period total 12 2200 55 11000 0 0 0 11000 9 1800 45 9000 400 10 630 9630 6 1200 30 6000 1000 25 675 6675 3 600 15 3000 1600 40 720 3720 1 200 5 1000 2000 50 750 1750 ``` The first row is where the previous lesson left off: the hot tier carries 55 files and a query with a filter outside the key pays **11000**. Every row down removes one period and drops the cost by exactly **1000** units; one period is five files and 400 cells. The sixth column is the surprise: as the archive grows its cost barely moves, **630, 675, 720, 750**, because every period going to the archive takes 200 rows and leaves only **5 entries**, that is, 15 cells. The last column is the sum of the two and reverses the expectation: **the more is archived, the cheaper the fully dated query becomes**; the region total is **11000** without the archive, **6675** with six periods archived and **1750** with everything but one period archived. The archive is not slow but **pre-aggregated**. The decision is sensitive to both constants, but differently. The break-even archive constant is **985**: reading one period from the archive costs the constant plus fifteen cells, from the hot tier **1000** units. The hot tier file constant **does not change the direction**: taking out one period removes five files and 400 cells from the query and adds fifteen cells to the archive; the gain is 985 at a constant of 120 and 385 even at 0. The existence of the reduction sets the direction, the choice of 120 the magnitude. #### The Question the Archive Closes These columns show only gain and alone suggest narrowing the window to one. The block below counts the price. ```python # This block continues the rows() , write() , query() , archive() , ALL_ROWS , PARTS and SELECTED # definitions of the first block. The window is six periods. WINDOW = 6 KEEP = set(PERIODS[len(PERIODS) - WINDOW:]) HOT = [k for k in ALL_ROWS if k["period"] in KEEP] SUMMARY = archive([k for k in ALL_ROWS if k["period"] not in KEEP]) HOT_PERIODS = sorted(set(k["period"] for k in HOT)) ARCHIVE_PERIODS = sorted(set(d for _, d in SUMMARY)) BREAKDOWN = {"region": ("region",), "type": ("type",), "subscriber": ("subscriber",), "region+type": ("region", "type")} def total(records, fields): t = {} for k in records: name = tuple(k[a] for a in fields) t[name] = t.get(name, 0) + k["m3"] return t print(f"window {WINDOW} periods , hot periods {len(HOT_PERIODS)} ," f" archive periods {len(ARCHIVE_PERIODS)}") print("\nbreakdown buckets from hot from archive periods answered closed periods closed buckets") for name, fields in BREAKDOWN.items(): buckets = len(total(ALL_ROWS, fields)) from_archive = len(ARCHIVE_PERIODS) if set(fields) <= {"region"} else 0 answered = len(HOT_PERIODS) + from_archive closed = len(HOT_PERIODS) + len(ARCHIVE_PERIODS) - answered print(f"{name:<11} {buckets:>7} {len(HOT_PERIODS):>8} {from_archive:>12} {answered:>16}" f" {closed:>14} {closed * buckets:>14}") merged = {b: v for (b,), v in total(HOT, ("region",)).items()} for (b, _), v in SUMMARY.items(): merged[b] = merged.get(b, 0) + v print(f"\nregion total , is the full data identical to hot+archive:" f" {total(ALL_ROWS, ('region',)) == {(b,): v for b, v in merged.items()}}") full, hot_only = total(ALL_ROWS, ("type",))[("commercial",)], total(HOT, ("type",))[("commercial",)] print(f"commercial total , eleven periods {full} ; from the hot tier alone {hot_only} ;" f" invisible {full - hot_only} m3") ``` ``` window 6 periods , hot periods 6 , archive periods 5 breakdown buckets from hot from archive periods answered closed periods closed buckets region 5 6 5 11 0 0 type 2 6 0 6 5 10 subscriber 200 6 0 6 5 1000 region+type 10 6 0 6 5 50 region total , is the full data identical to hot+archive: True commercial total , eleven periods 29932 ; from the hot tier alone 16818 ; invisible 13114 m3 ``` The first row is what the archive does right: the region breakdown is answered in **all eleven periods**, and the hot tier plus archive total is **exactly identical** to the full data. The archive is not an approximation; it is exact at its own resolution. The other three rows give the loss. `type`, `subscriber` and `region`+`type` are answered in only **six periods** and for five periods **not at all**; the closed buckets are **10, 1000 and 50** respectively. Cost is undefined in these rows because the answer cannot be read at any price: a layout makes some questions not expensive but **impossible**. The two forms of retention part here. Deletion costs **periods**: the previous course measured the six-period window raising the period that cannot be checked from 1 to 7. Archiving costs **columns**: no period disappears, and for five periods two of the three breakdowns are dropped. One cuts the time axis, the other the breakdown axis. The last row is the most dangerous number of the lesson. The type breakdown is absent from the archive but present in the hot tier; an unguarded query raises no error, it totals what is at hand and returns the commercial total as **16818 instead of 29932**. **13114 cubic meters** stay invisible without a warning: the query was not written wrongly, the data not corrupted, no check fired. The answer belongs to **a smaller question**; the two questions use the same words. #### Summary - Each time the window narrows by one period, the query with a filter outside the key becomes exactly **1000** units cheaper: from 11000 to **6000** in the six-period window. - The archive is cheap because it is pre-aggregated: each period takes 200 rows and leaves **5 entries**, the twelve-period region total falls **from 11000 to 6675**, the break-even archive constant is **985**. - The loss is in resolution: the region breakdown stays exactly equal to the full data, but `type`, `subscriber` and `region`+`type` cannot be answered at all for five periods (the closed buckets are **10, 1000, 50**); deletion costs periods, archiving costs columns. - An unguarded type query gives an incomplete answer without error: the commercial total comes out **16818 instead of 29932**. #### Course Wrap-Up The rows carry no common unit; **by which measure** each row is read stands next to the lesson name. The difference in parentheses is ordered setup minus disordered baseline: **minus is a saving, plus is a rise in cost**. | Lesson | Disordered baseline | Ordered setup | Cost | |---|---|---|---| | Data Warehouse — cost of the period query | unpartitioned and row-oriented, 15400 cells, **15520** | `period`-partitioned and columnar, 400 cells, **520** (**−15000**) | 5720 on a filter outside the key (**+1200**); the schema gate drops 400 rows, its measurements are sound | | Data Lake — cost of the period query | raw lake, 14400 cells, **14520** | warehouse, 400 cells, **520** (**−14000**) | the lake gains 1000 cells on write, repays 14000 on one query; two consumers find 48004 and 58454 m3 | | Lakehouse Approach — corruption class caught and cells stored | mechanism without enforcement **0/6**, detection lag undefined, 14400 cells | mechanism enforcing on read **6/6**, mean 1.33 stages (**+6**) | cells stored **29800**; on a field name change 2200 rows instead of 1800, `unit_changed` is caught by no gate | | Data Mart — cost of the three-question set | unpartitioned warehouse **13,560** | two marts **2,580** (**−10,980**) | 3,322 extra rows, 2.51 copies per row; under diverging refresh 126 pairs, 4,707 and 5,340 for the same question | | Data Fabric and Data Mesh — deciding party / layout touched, six-question mix | centralized **1/1**, **3,120** | data fabric **1/5**, **2,000** (**−1,120**) | data mesh 5/5 and **6,280** (**+3,160**); unmeasured key alone 2,450 | | Metadata Catalog — entries looked at in five searches | no catalog **295** | with catalog **29** (**−266**) | with a stale catalog 251 against 406; two searches end costlier than without a catalog (129 against 122, 104 against 100) | | Dimensional Modeling — query cost and cells stored | flat table, 15400 cells, **4520** | fact table and two dimensions, 7236 cells, **5040** (**+520**) | joins from 0 to **2200**, same result; a region name correction touches 46 rows instead of 506 | | Star and Snowflake Schema — query cost and joins | star: 2200 joins, 212 dimension rows, **5040** | snowflake: 4400, 217, **5170** (**+130**) | joins double for five rows, same result; a loss of 10 cells on a single attribute, a gain of 965 on six | | Slowly Changing Dimensions — commercial total (m3) and cost | overwriting dimension **35893**, 200 rows, **7240** | history-keeping dimension **33145**, 2200 rows, **13440** (**+6200**) | **2748 m3 in the wrong bucket**, the bucket total is the same at 58454, four checks green | | Columnar File Formats — cells read by the two-column query | row-oriented, 1400 cells, **1520** | columnar, 400 cells, **520** (**−1000**) | difference **0** at the seventh column; with compression seven columns 851 against 1400; region ordering raises the `m3` run count 196 to 200 | | Partitioning and File Layout — cost of a filter outside the key | unpartitioned **4520** | `period`+`region` **11000** (**+6480**) | the whole difference is the per-file constant paid 54 times more; on a pruning query cells stay 400 while cost is 520, 1000, 1600 | | Data Retention Policy — twelve-period region total | no archive, 55 files, **11000** | six-period window, 30 files, **6675** (**−4325**) | three breakdowns close for five periods (10 / 1000 / 50 buckets); an unguarded query returns **16818** instead of 29932 | The table is one rule written twelve times. **The number of a storage layout is not the rows it stores but what a query pays to touch it; a layout whose query is not written down as the one it was chosen for has not been measured.** The measure name in the first column is the rule itself: the same 2200 rows were stored twelve times and gave twelve different numbers, none from the size of the data. **The second claim is read from the same table: a layout can also make things worse.** The rows with a plus sign are these. Partitioning and File Layout is the clearest: **+6480** on a filter outside the key, all of it file constant; even on a pruning query the cost triples while the cells stay at 400. Data Warehouse writes the same at small scale (**+1200**), Data Fabric and Data Mesh in a distributed setup (**+3,160**), and in Lakehouse Approach catching six classes raises the cells stored from 14400 to 29800. Dimensional Modeling and Star and Snowflake Schema carry the **+520** and **+130** that separation and normalization write onto reading; in Columnar File Formats the ordering chosen for one query pushes another column back to its uncompressed value. In Data Mart the gain of the mart is zero on the region question, and in Metadata Catalog a stale catalog ends two searches costlier than no catalog. **The third claim stands in one line under its own name: a layout silently makes the answer wrong.** In Slowly Changing Dimensions the dimension that keeps no history does not slow the query, it makes the commercial total **35893 instead of 33145**; **2748 cubic meters** go to the wrong bucket, the bucket total stays at 58454, four checks are green. It has two echoes: here the unguarded query returns 16818 instead of 29932, and in Data Mart the same question gives 4,707 and 5,340 at two addresses. In all three there is no error, only an answer. In twelve lessons, where the data stands and in which layout was measured. Who fills this layout, **when and in which order**, was never asked; the files were always written, the dimensions always ready, the archive always moved. What the rest does when one step fails, whether running a step a second time duplicates rows, and the price of a pipeline being able to **rerun itself** were not counted. The next course turns not the layout but the order that fills it into the object of measurement.