--- title: 'Dimensional Modeling' source: 'https://academia.sh/en/courses/data-storage-architectures/dimensional-modeling' course: 'Data Storage Architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Builds the same set of rows as a single flat table and as a fact table plus dimensions, and counts the cells stored.' - 'Shows that the two layouts produce the same result and measures the joins and the cost paid in return for the split.' - 'Compares how many rows the correction of a dimension attribute touches in the two layouts.' --- # 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.