Dimensional Modeling

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.

# 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.

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.

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.