Data Retention Policy

In every layout of the previous lesson all twelve periods stood together; the oldest period was read at the same price as the newest. Yet the per-file constant is the same for every file, and in a partitioned layout the number of files grows with the period: a year later the same query touches twice the files.

This lesson turns that accumulation into a decision. A retention policy moves old periods to the archive, and three things are measured: the files and cost left in the hot tier, the price of reading from the archive, and retroactively which question can no longer be answered. The third is the real finding; the first two are gains and alone always turn the decision the same way.

• MF56. The source, the consumption rows, the hot tier and the archive are fictional and are produced inside the lesson. No warehouse, lake, cloud service or query engine is called. The seed is 20260218.
• MF57. The retention window keeps the last N periods in the hot tier. The hot tier is written with period+region partitioning and the query reads two columns; both were measured in the previous two lessons and are held fixed here.
• MF58. Archiving is not deleting: the rows stay but stay at another resolution. The archive reduces them to the region by period total in a single file; the subscriber and type breakdowns are not carried over, and all the loss numbers of the lesson depend on this choice.
• MF59. An archive entry is three cells: region, period, total. The archive constant is 600, five times the file constant of the hot tier; both are choices.
• MF60. The retention and lifecycle procedure was measured in the Storage and Data in the Cloud course in the Cloud Computing curriculum; it is not repeated. The effect of deletion on retroactive checking takes input from the Data Governance lesson of the Introduction to Data Engineering course: the six-period window was deleting six periods and raising the period that cannot be checked from 1 to 7.

What Moving to the Archive Gains

The block below narrows the hot window from twelve periods to one; each step prints the files left, the cost of the query with a filter outside the key, the archive size and the end-to-end cost of the twelve-period region total.

# FICTIONAL. The meter readings of the measurement network , the consumption rows , the hot tier
# and the archive are produced inside the lesson; no data warehouse , data lake , cloud service
# 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)]
HOT_CONSTANT = 120          # the fixed cost of touching a file in the hot tier
ARCHIVE_CONSTANT = 600      # the fixed cost of touching a file in the archive , a choice
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 rows():
    # Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe
    # drop no rows , so consumption is computed directly from the source , by period difference.
    raw = []
    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)
            raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"],
                        "index": e, "unit": "m3"})
    table, out = {(k["subscriber"], k["period"]): k for k in raw}, []
    for k in raw:
        i = PERIODS.index(k["period"])
        prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None
        if prev is not None:
            out.append(dict(k, m3=k["index"] - prev["index"]))
    return out


def write(records, partitioning=()):
    layout = {}
    for k in records:
        layout.setdefault(tuple(k[a] for a in partitioning), []).append(k)
    return layout


def query(layout, partitioning, selected, filters=None):
    # A field in the key prunes files , a field outside the key does not.
    filters = {} if filters is None else filters
    pruning = {a: v for a, v in filters.items() if a in partitioning}
    files, cells = 0, 0
    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) * len(selected)
    return {"files": files, "cells": cells, "cost": files * HOT_CONSTANT + cells}


def archive(records):
    # The rows come down to the region x period total in a single file; subscriber and type are not carried.
    summary = {}
    for k in records:
        summary[(k["region"], k["period"])] = summary.get((k["region"], k["period"]), 0) + k["m3"]
    return summary


PARTS = ("period", "region")
SELECTED = ("region", "m3")
ALL_ROWS = rows()
print(f"pipeline rows {len(ALL_ROWS)} , periods carrying consumption"
      f" {len(set(k['period'] for k in ALL_ROWS))} , hot constant {HOT_CONSTANT} ,"
      f" archive constant {ARCHIVE_CONSTANT}")
print("\n window     hot rows    hot files     B hot  archive rows  summary entries  archive cost     12-period total")
for p in (12, 9, 6, 3, 1):
    keep = set(PERIODS[len(PERIODS) - p:])
    hot = [k for k in ALL_ROWS if k["period"] in keep]
    cold = [k for k in ALL_ROWS if k["period"] not in keep]
    d = write(hot, PARTS)
    b = query(d, PARTS, SELECTED, {"type": "commercial"})
    summary = archive(cold)
    # The archive is a single file , three cells per entry: region , period , total.
    archive_cost = ARCHIVE_CONSTANT + 3 * len(summary) if cold else 0
    print(f"{p:>7}  {len(hot):>11}  {len(d):>11}  {b['cost']:>8}  {len(cold):>12}"
          f"  {len(summary):>15}  {archive_cost:>12}  {query(d, PARTS, SELECTED)['cost'] + archive_cost:>18}")

pipeline rows 2200 , periods carrying consumption 11 , hot constant 120 , archive constant 600

 window     hot rows    hot files     B hot  archive rows  summary entries  archive cost     12-period total
     12         2200           55     11000             0                0             0               11000
      9         1800           45      9000           400               10           630                9630
      6         1200           30      6000          1000               25           675                6675
      3          600           15      3000          1600               40           720                3720
      1          200            5      1000          2000               50           750                1750

The first row is where the previous lesson left off: the hot tier carries 55 files and a query with a filter outside the key pays 11000. Every row down removes one period and drops the cost by exactly 1000 units; one period is five files and 400 cells.

The sixth column is the surprise: as the archive grows its cost barely moves, 630, 675, 720, 750, because every period going to the archive takes 200 rows and leaves only 5 entries, that is, 15 cells. The last column is the sum of the two and reverses the expectation: the more is archived, the cheaper the fully dated query becomes; the region total is 11000 without the archive, 6675 with six periods archived and 1750 with everything but one period archived. The archive is not slow but pre-aggregated.

The decision is sensitive to both constants, but differently. The break-even archive constant is 985: reading one period from the archive costs the constant plus fifteen cells, from the hot tier 1000 units. The hot tier file constant does not change the direction: taking out one period removes five files and 400 cells from the query and adds fifteen cells to the archive; the gain is 985 at a constant of 120 and 385 even at 0. The existence of the reduction sets the direction, the choice of 120 the magnitude.

The Question the Archive Closes

These columns show only gain and alone suggest narrowing the window to one. The block below counts the price.

# This block continues the rows() , write() , query() , archive() , ALL_ROWS , PARTS and SELECTED
# definitions of the first block. The window is six periods.
WINDOW = 6
KEEP = set(PERIODS[len(PERIODS) - WINDOW:])
HOT = [k for k in ALL_ROWS if k["period"] in KEEP]
SUMMARY = archive([k for k in ALL_ROWS if k["period"] not in KEEP])
HOT_PERIODS = sorted(set(k["period"] for k in HOT))
ARCHIVE_PERIODS = sorted(set(d for _, d in SUMMARY))
BREAKDOWN = {"region": ("region",), "type": ("type",), "subscriber": ("subscriber",),
             "region+type": ("region", "type")}


def total(records, fields):
    t = {}
    for k in records:
        name = tuple(k[a] for a in fields)
        t[name] = t.get(name, 0) + k["m3"]
    return t


print(f"window {WINDOW} periods , hot periods {len(HOT_PERIODS)} ,"
      f" archive periods {len(ARCHIVE_PERIODS)}")
print("\nbreakdown   buckets  from hot  from archive  periods answered  closed periods  closed buckets")
for name, fields in BREAKDOWN.items():
    buckets = len(total(ALL_ROWS, fields))
    from_archive = len(ARCHIVE_PERIODS) if set(fields) <= {"region"} else 0
    answered = len(HOT_PERIODS) + from_archive
    closed = len(HOT_PERIODS) + len(ARCHIVE_PERIODS) - answered
    print(f"{name:<11} {buckets:>7}  {len(HOT_PERIODS):>8}  {from_archive:>12}  {answered:>16}"
          f"  {closed:>14}  {closed * buckets:>14}")

merged = {b: v for (b,), v in total(HOT, ("region",)).items()}
for (b, _), v in SUMMARY.items():
    merged[b] = merged.get(b, 0) + v
print(f"\nregion total , is the full data identical to hot+archive:"
      f" {total(ALL_ROWS, ('region',)) == {(b,): v for b, v in merged.items()}}")
full, hot_only = total(ALL_ROWS, ("type",))[("commercial",)], total(HOT, ("type",))[("commercial",)]
print(f"commercial total , eleven periods {full} ; from the hot tier alone {hot_only} ;"
      f" invisible {full - hot_only} m3")

window 6 periods , hot periods 6 , archive periods 5

breakdown   buckets  from hot  from archive  periods answered  closed periods  closed buckets
region            5         6             5                11               0               0
type              2         6             0                 6               5              10
subscriber      200         6             0                 6               5            1000
region+type      10         6             0                 6               5              50

region total , is the full data identical to hot+archive: True
commercial total , eleven periods 29932 ; from the hot tier alone 16818 ; invisible 13114 m3

The first row is what the archive does right: the region breakdown is answered in all eleven periods, and the hot tier plus archive total is exactly identical to the full data. The archive is not an approximation; it is exact at its own resolution.

The other three rows give the loss. type, subscriber and region+type are answered in only six periods and for five periods not at all; the closed buckets are 10, 1000 and 50 respectively. Cost is undefined in these rows because the answer cannot be read at any price: a layout makes some questions not expensive but impossible.

The two forms of retention part here. Deletion costs periods: the previous course measured the six-period window raising the period that cannot be checked from 1 to 7. Archiving costs columns: no period disappears, and for five periods two of the three breakdowns are dropped. One cuts the time axis, the other the breakdown axis.

The last row is the most dangerous number of the lesson. The type breakdown is absent from the archive but present in the hot tier; an unguarded query raises no error, it totals what is at hand and returns the commercial total as 16818 instead of 29932. 13114 cubic meters stay invisible without a warning: the query was not written wrongly, the data not corrupted, no check fired. The answer belongs to a smaller question; the two questions use the same words.

Summary

• Each time the window narrows by one period, the query with a filter outside the key becomes exactly 1000 units cheaper: from 11000 to 6000 in the six-period window.
• The archive is cheap because it is pre-aggregated: each period takes 200 rows and leaves 5 entries, the twelve-period region total falls from 11000 to 6675, the break-even archive constant is 985.
• The loss is in resolution: the region breakdown stays exactly equal to the full data, but type, subscriber and region+type cannot be answered at all for five periods (the closed buckets are 10, 1000, 50); deletion costs periods, archiving costs columns.
• An unguarded type query gives an incomplete answer without error: the commercial total comes out 16818 instead of 29932.

Course Wrap-Up

The rows carry no common unit; by which measure each row is read stands next to the lesson name. The difference in parentheses is ordered setup minus disordered baseline: minus is a saving, plus is a rise in cost.

The table is one rule written twelve times. The number of a storage layout is not the rows it stores but what a query pays to touch it; a layout whose query is not written down as the one it was chosen for has not been measured. The measure name in the first column is the rule itself: the same 2200 rows were stored twelve times and gave twelve different numbers, none from the size of the data.

The second claim is read from the same table: a layout can also make things worse. The rows with a plus sign are these. Partitioning and File Layout is the clearest: +6480 on a filter outside the key, all of it file constant; even on a pruning query the cost triples while the cells stay at 400. Data Warehouse writes the same at small scale (+1200), Data Fabric and Data Mesh in a distributed setup (+3,160), and in Lakehouse Approach catching six classes raises the cells stored from 14400 to 29800. Dimensional Modeling and Star and Snowflake Schema carry the +520 and +130 that separation and normalization write onto reading; in Columnar File Formats the ordering chosen for one query pushes another column back to its uncompressed value. In Data Mart the gain of the mart is zero on the region question, and in Metadata Catalog a stale catalog ends two searches costlier than no catalog.

The third claim stands in one line under its own name: a layout silently makes the answer wrong. In Slowly Changing Dimensions the dimension that keeps no history does not slow the query, it makes the commercial total 35893 instead of 33145; 2748 cubic meters go to the wrong bucket, the bucket total stays at 58454, four checks are green. It has two echoes: here the unguarded query returns 16818 instead of 29932, and in Data Mart the same question gives 4,707 and 5,340 at two addresses. In all three there is no error, only an answer.

In twelve lessons, where the data stands and in which layout was measured. Who fills this layout, when and in which order, was never asked; the files were always written, the dimensions always ready, the archive always moved. What the rest does when one step fails, whether running a step a second time duplicates rows, and the price of a pipeline being able to rerun itself were not counted. The next course turns not the layout but the order that fills it into the object of measurement.