Backfill

The last block of the previous lesson wrote a rule that reports commercial consumption with a factor of 1.05, and none of the six expectations broke. The expectations were right, because there was no corruption: the rule had changed on purpose. The question left open there was this — if the rule is valid from today, what happens to the eleven periods computed with the old rule.

This lesson measures that question. Recomputing all the periods means run cost; recomputing only a few periods leaves one part of the report with the new rule and the rest with the old one. The second option is cheap, fast, and the report it produces looks internally consistent. This is the clearest example of the second claim of this course.

• TF49. The source and the five-stage pipeline are fictional; no real store, transformation framework or orchestrator is called. The seed is 20260218, and the report does not depend on the run.
• TF50. Backfill is applying a changed rule or corrected data to past periods and recomputing those periods. What is measured is which periods are recomputed.
• TF51. The rule change is not a corruption but a decision: commercial consumption will be reported with a factor of 1.05 instead of 1.00. The new rule is taken as correct; what is debated is its scope.
• TF52. The baseline is the run without a backfill: no past period is recomputed, the report stands entirely on the old rule.
• TF53. Reprocessing and idempotence were measured at the row level in the Introduction to Data Engineering course and are not repeated; what is measured here is at the period level.
• TF54. The cost is written as periods and rows, raw time is not written.
• TF55. Since consumption is computed from the period difference, recomputing a period requires reading also the source of the previous period; the window is one period wider at the source.
• TF56. Resolution: consumption rows 2200, summary 55 cells, serving 5 values. No ratio is printed over the serving output, the value itself is written.

The Scope Question of Backfill

When a rule changes there are three options and all three are defensible. Not backfilling at all leaves the report consistent but describes everything before today with the wrong rule. Backfilling everything carries the whole report to the new rule and has all twelve periods recomputed. Backfilling partially is the cheapest and usually has a reason too: only the audited periods, only the quarter still open, only the window for which the data is still at hand.

The problem with the partial option is not its cost but its invisibility. The resulting report is a combination of two rules, yet none of the rows carries which rule it was computed with. The block below sets up the source, the five stages and the backfill function that applies the rule to the chosen periods.

# FICTIONAL. The meter readings of the measurement network and the five-stage pipeline are
# defined in the lesson; no real store, framework or orchestrator 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):
    """A row that does not carry the required fields is dropped."""
    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):
    """Consumption from the period difference. If the previous period does not exist, the row cannot be computed."""
    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


def s4_summary(records):
    totals = {}
    for k in records:
        key = (k["region"], k["period"])
        totals[key] = totals.get(key, 0) + k["m3"]
    return totals, 0


def s5_serve(summary):
    """Serving: the period average per region."""
    region = {}
    for (b, d), v in summary.items():
        region.setdefault(b, []).append(v)
    return {b: round(sum(v) / len(v), 2) for b, v in region.items()}, 0


def backfill(records, periods, new_rule=True):
    """The rule changed: commercial consumption will be reported with a factor of 1.05 instead of 1.00.
    Only the chosen periods are recomputed."""
    v, _ = s1_screen(records)
    v, _ = s2_dedupe(v)
    v, _ = s3_consumption(v)
    out = []
    for k in v:
        factor = 1.05 if (new_rule and k["period"] in periods and k["type"] == "commercial") else 1.0
        out.append(dict(k, m3=k["m3"] * factor))
    sm, _ = s4_summary(out)
    served, _ = s5_serve(sm)
    return served

Three Reports and Two Costs

The second block produces three reports from the same source: the rule never applied, applied to all twelve periods, and applied only to the periods 2026-07 and 2026-08. Then the cost of the two backfill windows is counted.

WINDOW = ["2026-07", "2026-08"]
SOURCE = generate_source()


def diff(a, b):
    return {k: round(a[k] - b[k], 2) for k in a}


OLD = backfill(SOURCE, [], new_rule=False)
FULL = backfill(SOURCE, PERIODS)
MIXED = backfill(SOURCE, WINDOW)
print("old rule         :", OLD)
print("full application :", FULL)
print("two periods      :", MIXED)
print("mixed - full     :", diff(MIXED, FULL))
print("mixed - old      :", diff(MIXED, OLD))


def cost(periods):
    """Recomputed periods , consumption rows written , source periods read and rows."""
    i = min(PERIODS.index(d) for d in periods)
    read = sorted(set(periods) | {PERIODS[max(i - 1, 0)]})
    written = 200 * sum(1 for d in periods if d != PERIODS[0])
    return len(periods), written, len(read), 200 * len(read)


print()
print(f"{'window':<13}{'periods':>7}{'written':>9}{'periods read':>14}{'rows read':>14}")
for name, pl in (("all periods", PERIODS), ("two periods", WINDOW)):
    print(f"{name:<13}" + "".join(f"{x:>{w}}" for x, w in zip(cost(pl), (7, 9, 14, 14))))

old rule         : {'central': 1321.45, 'north': 1212.55, 'east': 881.36, 'west': 833.09, 'south': 1065.55}
full application : {'central': 1357.32, 'north': 1235.57, 'east': 902.13, 'west': 859.22, 'south': 1095.81}
two periods      : {'central': 1327.75, 'north': 1216.36, 'east': 885.15, 'west': 837.77, 'south': 1070.65}
mixed - full     : {'central': -29.57, 'north': -19.21, 'east': -16.98, 'west': -21.45, 'south': -25.16}
mixed - old      : {'central': 6.3, 'north': 3.81, 'east': 3.79, 'west': 4.68, 'south': 5.1}

window       periods  written  periods read     rows read
all periods       12     2200            12          2400
two periods        2      400             3           600

The cost table shows why the partial option is chosen: the two-period window recomputes 2 periods and 400 rows, while the full application computes 12 periods and 2200 rows. The source-read column is the cost of the dependency between periods: to recompute two periods the source of three periods is read, because the 2026-07 consumption rests on the 2026-06 index. The window is always one period wider at the source, and this width is paid at the start of the window.

The deviation line is the main number of the lesson. The mixed report deviates from the full application between −16.98 and −29.57; none of the five regions stands at the value the full application gives. In return, the mixed report also differs from the old rule — all five values are between 3.79 and 6.30 higher. So the resulting report is neither the report of the old rule nor of the new rule; it is a third thing, and no rule in the real world corresponds to that third thing.

The source of the deviation is not the data. All three reports were produced from the same 2400 source rows with the same five stages; no row was lost, no row was corrupted. The only thing that creates the difference is which periods were recomputed.

Which Check the Mixed Report Passes

The third block tests the mixed report with the six expectations of the previous lesson, checks whether the report is internally consistent, and compares the jump the rule creates in a cell with the region’s natural period volatility. This last comparison represents the most optimistic case: what an observer who inspects the report period by period would see if they looked for the jump.

import statistics


def recompute(records, periods):
    """The same rule , two differences: the rows are returned and every row is stamped with the rule version."""
    v, _ = s1_screen(records)
    v, _ = s2_dedupe(v)
    v, _ = s3_consumption(v)
    return [dict(k, rule="v2" if k["period"] in periods else "v1",
                 m3=k["m3"] * (1.05 if (k["period"] in periods and k["type"] == "commercial") else 1.0))
            for k in v]


def expectations(v):
    """The six expectations of the previous lesson , on the transformation's own output."""
    sm = s4_summary(v)[0]
    key = [(k["subscriber"], k["period"]) for k in v]
    d = {"key_unique": len(key) != len(set(key)),
         "no_negative": any(k["m3"] < 0 for k in v),
         "ceiling": any(k["m3"] > 200 for k in v),
         "row_ratio": abs(len(v) - 2200) / 2200 > 0.02,
         "cell_completeness": len(sm) != 55, "volatility": False}
    for b in REGION:
        vals = [x for (bb, _), x in sm.items() if bb == b]
        if any(abs(x - statistics.median(vals)) / statistics.median(vals) > 0.30 for x in vals):
            d["volatility"] = True
    return [a for a, s in d.items() if s], sm


BROKEN, SM_MIXED = expectations(recompute(SOURCE, WINDOW))
SM_OLD = s4_summary(recompute(SOURCE, []))[0]
print("broken expectations in the mixed report:", BROKEN or "none")
print("is the report internally consistent:",
      all(abs(MIXED[b] - round(sum(x for (bb, _), x in SM_MIXED.items() if bb == b) / 11, 2)) < 0.01
          for b in REGION))
for b in ("central", "east"):
    natural = [abs(SM_OLD[(b, PERIODS[i])] - SM_OLD[(b, PERIODS[i - 1])]) / SM_OLD[(b, PERIODS[i - 1])]
               for i in range(2, 12)]
    jump = [(SM_MIXED[(b, d)] - SM_OLD[(b, d)]) / SM_OLD[(b, d)] for d in WINDOW]
    print(f"{b:<7} natural change median={statistics.median(natural):.4f} "
          f"largest={max(natural):.4f}  rule jump={max(jump):.4f}")

broken expectations in the mixed report: none
is the report internally consistent: True
central natural change median=0.0883 largest=0.1874  rule jump=0.0275
east    natural change median=0.0909 largest=0.1541  rule jump=0.0234

Three lines say the same thing: no check reports this. None of the six expectations breaks, because there is nothing to break — the row count is 2200, the cell count 55, the keys are unique, no value is negative or above the ceiling. The report is internally consistent: each region’s serving value equals the average of its own eleven summary cells. The checks that look at the source also stay silent, because the source has not changed at all.

The last two lines eliminate even the most optimistic observer. The largest jump the rule creates in a cell is 2.75% in the central region and 2.34% in the east; yet the median of the natural period change of the same regions is 8.83% and 9.09%, and the largest 18.74% and 15.41%. The trace of the rule is about a third of even the median of the natural volatility. An observer who looks period by period cannot find the jump even if they look for it, because the jump is inside the noise.

That both runs look internally consistent is the core of this lesson. Neither the full application nor the partial application produces an error; both run to the end, both print a clean report. The only difference between them is a list — which periods were recomputed — and that list is written nowhere in the report.

The Rule Version Stamp

The fourth block adds what is missing. recompute writes into every row the rule it was computed with; looking at how many versions the report contains is enough. The same block also tests the rule of the course: what happens when the backfill is run twice.

V_FULL = recompute(SOURCE, PERIODS)
V_MIXED = recompute(SOURCE, WINDOW)
for name, v in (("full", V_FULL), ("mixed", V_MIXED)):
    s = sorted(set(k["rule"] for k in v))
    print(f"{name:<8} rule version={str(s):<14} single version in the report:", len(s) == 1)
print("run twice from the source:",
      backfill(SOURCE, WINDOW) == backfill(SOURCE, WINDOW))
patched = [dict(k, m3=k["m3"] * (1.05 if k["period"] in WINDOW and k["type"] == "commercial" else 1.0))
           for k in V_MIXED]
print("second time on the output:", diff(s5_serve(s4_summary(patched)[0])[0], MIXED))

full     rule version=['v2']         single version in the report: True
mixed    rule version=['v1', 'v2']   single version in the report: False
run twice from the source: True
second time on the output: {'central': 6.62, 'north': 4.0, 'east': 3.98, 'west': 4.92, 'south': 5.35}

The stamp is a single field and reduces the mixing to a single comparison: if the report has two versions, the report is mixed. Its cost is also a single field, a string written to each of the 2200 rows. The moment the stamp is written matters; after the calculation is done, which row was produced with which rule cannot be worked out retroactively, because the only thing left in the row is the result itself.

The last two lines show the rule of the course from two sides. When the backfill is computed from the source, running it twice does not change the result; the function is idempotent and the report is independent of the number of runs. When the same correction is applied as a patch over the output, the second run multiplies the factor once more, and the report shifts once more by between 3.98 and 6.62. Both paths aim at the same result, one can be rerun, the other is correct only once — and there is no way to tell from the report which of the two was run.

Summary

• Backfill is a scope decision: the full application has 12 periods and 2200 rows recomputed, while the two-period window makes do with 2 periods and 400 rows, but three periods are read at the source, because consumption rests on the index of the previous period.
• The report of the partial backfill deviates from the full application between −16.98 and −29.57 and also differs from the old rule by between 3.79 and 6.30; the resulting report belongs to neither of the two rules.
• The source of the deviation is not the data but which periods were recomputed; all three reports are produced from the same 2400 source rows without losing a single row.
• The mixed report breaks none of the six expectations, comes out internally consistent, and the largest jump the rule creates in the central cells (2.75%) stays far below the median of the natural period volatility (8.83%), so it is invisible even to an observer who looks period by period.
• When the row is stamped with the rule version, the mixing becomes visible with a single comparison; when the backfill is computed from the source it is idempotent, and when it is applied as a patch over the output the second run shifts the report once more by between 3.98 and 6.62.

Next Step

Eleven lessons built the pipeline: data was ingested through six separate paths, what loss and duplication do to the serving was counted, and where the transformation is done, in which order it runs, in which layer it stops, how it is tested and how it is applied to the past were written. In all of these eleven lessons there was one thing that ran the pipeline: a start decision a person gave by hand. Which task will run when, what the tasks behind it will do when a task falls, whether the same job was triggered twice, and who will write the rule version stamp of this lesson were never asked. The next topic turns the pipeline into a task graph and measures how far the shape of the graph carries a failure.