Data Mart

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.

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

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