Star and Snowflake Schema

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.

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

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.

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.