--- title: 'Data Retention Policy' source: 'https://academia.sh/en/courses/data-storage-architectures/data-retention-policy' course: 'Data Storage Architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Calculates the number of files, the number of rows and the query cost left in the hot tier as the retention window narrows.' - 'Compares the price of reading from the archive with the price of reading from the hot tier and finds the break-even value of the archive constant.' - 'Measures in buckets which breakdown the archive closes and shows that an unguarded query silently gives an incomplete answer.' - 'Collects the disordered and the ordered numbers of the twelve lessons of the course in a single table.' --- # Data Retention Policy Counting what moving to the archive gains and what it closes: in a six-period hot window the hot tier falls from 2200 rows to 1200 and from 55 files to 30, a query with a filter outside the key pays 6000 instead of 11000, and because the 1000 archived rows come down to 25 summary entries the twelve-period region total costs 6675 instead of 11000; the more is archived, the cheaper the fully dated query becomes. The price is in resolution: the region breakdown is answered in all eleven periods and the hot tier plus the archive total comes out exactly equal to the full data, whereas the type, subscriber and region plus type breakdowns are answered in only six periods and 10, 1000 and 50 buckets are closed respectively. An unguarded type query does not raise an error, it returns the commercial total as 16818 instead of 29932 and 13114 cubic meters stay invisible. The lesson is the wrap-up of the course. 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. ```python # 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. ```python # 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**. | Lesson | Disordered baseline | Ordered setup | Cost | |---|---|---|---| | Data Warehouse — cost of the period query | unpartitioned and row-oriented, 15400 cells, **15520** | `period`-partitioned and columnar, 400 cells, **520** (**−15000**) | 5720 on a filter outside the key (**+1200**); the schema gate drops 400 rows, its measurements are sound | | Data Lake — cost of the period query | raw lake, 14400 cells, **14520** | warehouse, 400 cells, **520** (**−14000**) | the lake gains 1000 cells on write, repays 14000 on one query; two consumers find 48004 and 58454 m3 | | Lakehouse Approach — corruption class caught and cells stored | mechanism without enforcement **0/6**, detection lag undefined, 14400 cells | mechanism enforcing on read **6/6**, mean 1.33 stages (**+6**) | cells stored **29800**; on a field name change 2200 rows instead of 1800, `unit_changed` is caught by no gate | | Data Mart — cost of the three-question set | unpartitioned warehouse **13,560** | two marts **2,580** (**−10,980**) | 3,322 extra rows, 2.51 copies per row; under diverging refresh 126 pairs, 4,707 and 5,340 for the same question | | Data Fabric and Data Mesh — deciding party / layout touched, six-question mix | centralized **1/1**, **3,120** | data fabric **1/5**, **2,000** (**−1,120**) | data mesh 5/5 and **6,280** (**+3,160**); unmeasured key alone 2,450 | | Metadata Catalog — entries looked at in five searches | no catalog **295** | with catalog **29** (**−266**) | with a stale catalog 251 against 406; two searches end costlier than without a catalog (129 against 122, 104 against 100) | | Dimensional Modeling — query cost and cells stored | flat table, 15400 cells, **4520** | fact table and two dimensions, 7236 cells, **5040** (**+520**) | joins from 0 to **2200**, same result; a region name correction touches 46 rows instead of 506 | | Star and Snowflake Schema — query cost and joins | star: 2200 joins, 212 dimension rows, **5040** | snowflake: 4400, 217, **5170** (**+130**) | joins double for five rows, same result; a loss of 10 cells on a single attribute, a gain of 965 on six | | Slowly Changing Dimensions — commercial total (m3) and cost | overwriting dimension **35893**, 200 rows, **7240** | history-keeping dimension **33145**, 2200 rows, **13440** (**+6200**) | **2748 m3 in the wrong bucket**, the bucket total is the same at 58454, four checks green | | Columnar File Formats — cells read by the two-column query | row-oriented, 1400 cells, **1520** | columnar, 400 cells, **520** (**−1000**) | difference **0** at the seventh column; with compression seven columns 851 against 1400; region ordering raises the `m3` run count 196 to 200 | | Partitioning and File Layout — cost of a filter outside the key | unpartitioned **4520** | `period`+`region` **11000** (**+6480**) | the whole difference is the per-file constant paid 54 times more; on a pruning query cells stay 400 while cost is 520, 1000, 1600 | | Data Retention Policy — twelve-period region total | no archive, 55 files, **11000** | six-period window, 30 files, **6675** (**−4325**) | three breakdowns close for five periods (10 / 1000 / 50 buckets); an unguarded query returns **16818** instead of 29932 | 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.