--- title: 'Star and Snowflake Schema' source: 'https://academia.sh/en/courses/data-storage-architectures/star-and-snowflake-schema' course: 'Data Storage Architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Builds the same fact table with flattened and normalized dimensions and counts the dimension rows and the number of joins.' - 'Shows that the two schemas produce the same result and measures separately the effect of normalization on cost and on the update surface.' - 'Determines after how many attributes in the dimension table normalization starts to gain cells.' --- # 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.