ETL and ELT

The first six lessons of this course ingested data into the pipeline: it was pulled from the source, queued, delivered and returned in processed form. Their shared assumption can be written in one sentence: data is taken from one place and put in another. None of them asked what state the data was brought to.

This lesson opens that question with the first decision: where the transformation will run. Next to the source, or at the target after the data has been moved there. The two answers are the names of two patterns, and the difference between them is a trade-off: transforming at the source lowers the transported rows, transforming at the target keeps the raw data and makes recomputation possible. In this lesson the two run side by side.

• TF1. The source, the pipeline and the split are fictional; no real store, query engine or transformation framework is called. The seed is 20260218, and the measurement does not depend on the run.
• TF2. ETL means extract, transform, load; ELT means extract, load, transform. Both are pattern names, not product names; the only difference between them is which side the transformation runs on.
• TF3. The five stages of the pipeline are split in two by a single boundary. The stages before the boundary run on the source side, the rest run at the target. The ETL boundary is 4, the ELT boundary is 0.
• TF4. A cell read is a model: each stage reads the stated number of fields of every row in its input. The number is not a hardware measurement but a measure of the stage’s workload.
• TF5. The baseline is the unsplit pipeline: there is no boundary decision, and recomputation has not been asked about.
• TF6. The source is an operational system and keeps only the last three periods; the target keeps all twelve periods.
• TF7. Storage layout, partitioning and cost were measured in the Data Storage Architectures course and are not repeated. The question here is not where the row sits but where the transformation runs.
• TF8. Resolution: the step of the row ratio is 0.0004. No ratio is printed over the serving output, the value itself is written.

Where the Boundary Sits

The five stages of the pipeline are fixed: screening, deduplication, consumption calculation, summary and serving. The only thing that changes is where this chain is cut. If the boundary is zero, no stage runs at the source, the raw rows are moved to the target as they are, and all five stages run there. If the boundary is four, four stages run next to the source and only the summary table goes to the target.

Choosing the boundary is not an architectural preference but a trade-off between two quantities. The code below counts those two quantities: the rows that pass through the boundary and the cells read by each side.

# FICTIONAL. Meter readings, the five-stage pipeline and the split are defined
# in the lesson; no real store, query engine or transformation framework 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


# ---- PIPELINE: five stages. Each stage counts the rows it drops.
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


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):
    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


STAGES = [("screen", s1_screen), ("dedupe", s2_dedupe), ("consumption", s3_consumption),
          ("summary", s4_summary), ("serve", s5_serve)]

# ---- Cells read: each stage reads this many fields of every row in its input.
FIELDS_READ = {"screen": 5, "dedupe": 2, "consumption": 4, "summary": 3, "serve": 2}


def cells(data):
    """A summary dict carries three values per cell , a record carries its own fields."""
    if isinstance(data, dict):
        return len(data) * 3
    return sum(len(k) for k in data)


def split_pipeline(records, boundary):
    """The first `boundary` stages run on the source side , the rest at the target.
    What crosses the boundary is the output of stage number `boundary`."""
    cells_read, data, transported = {"source": 0, "target": 0}, records, records
    for i, (name, f) in enumerate(STAGES):
        cells_read["source" if i < boundary else "target"] += len(data) * FIELDS_READ[name]
        data, _ = f(data)
        if i + 1 == boundary:
            transported = data
    return data, cells_read, transported

Transported Rows and Cells Read

The second block runs the same source with three boundaries. transported gives the rows that pass through the boundary, t.cells the number of fields those rows carry, and source and target the cells read by the two sides. The last column states whether the five served values stay the same as the baseline.

SOURCE_RECORDS = generate_source()
SETUPS = [("target (ELT)", 0), ("hybrid", 3), ("source (ETL)", 4)]
BASELINE, _, _ = split_pipeline(SOURCE_RECORDS, 0)

print("baseline serving:", BASELINE)
print(f"{'setup':<16}{'boundary':>10}{'transported':>13}{'t.cells':>11}"
      f"{'source':>8}{'target':>7}{'serving':>8}")
for name, n in SETUPS:
    serving, ok, moved = split_pipeline(SOURCE_RECORDS, n)
    print(f"{name:<16}{n:>10}{len(moved):>13}{cells(moved):>11}{ok['source']:>8}{ok['target']:>7}"
          f"{'same' if serving == BASELINE else 'changed':>8}")

baseline serving: {'central': 1321.45, 'north': 1212.55, 'east': 881.36, 'west': 833.09, 'south': 1065.55}
setup             boundary  transported    t.cells  source target serving
target (ELT)             0         2400      14400       0  33110    same
hybrid                   3         2200      15400   26400   6710    same
source (ETL)             4           55        165   33000    110    same

The last column is the same for all three setups: wherever the boundary is put, the five served values come out identical. The decision has no effect on the output; its whole effect is on how the output is produced.

The first two columns give the expected trade-off. The setup that transforms at the source transports 55 rows and 165 cells to the target; the setup that transforms at the target transports 2400 rows and 14400 cells. The ratio in transported rows is 43.6, and this is the only measurable quantity of data carried over the network.

The hybrid row shows that this trade-off is not a straight line. When the boundary is moved to three, the transported rows fall from 2400 to 2200, but the transported cells rise from 14400 to 15400. The reason is that the consumption stage adds the m3 field to every row: the row count shrinks while the number of fields per row grows. Lowering the transported rows does not mean lowering the transported data.

The last two columns carry the most important number of the lesson. The total of cells read by the three setups is, in order, 33110, 33110 which is the sum of 26400 and 6710, and 33110 which is the sum of 33000 and 110. All three are the same. The boundary decision does not remove the computation work, it only changes who does it. Transforming at the source drops a read of 33000 cells onto the operational system; transforming at the target moves the same load to the target. Choosing a setup because it is “cheap” means not seeing where the load lands.

The way the two sides absorb this load is not the same either. The source is an operational system and its main job is not running transformations but serving its own application; a read of 33000 cells runs there, sharing resources with that service. The target, by contrast, is set aside only for this job. So two numbers that look equal are not equal: the cells read at the source are stolen from another job, the cells read at the target are not. This difference grows every time the transformation chain grows, because every stage added raises the load of whichever side of the boundary it sits on. Writing the transformation in SQL does not change this decision: the language is one choice, the boundary is a separate one, and the same query can be written on either side.

The Cost of Recomputation

Up to this point there is no measurable advantage between the two setups. The difference appears when the pipeline runs a second time. A reporting rule changes: commercial consumption will from now on be reported with a factor of 1.05. Whether the rule change can be applied at the target depends on which stage’s output sits at the target.

RULES = {"commercial factor 1.05": 3, "dedupe key": 1, "unit correction": 0}
SOURCE_RETENTION = 3        # the operational source keeps only the last three periods

print(f"{'rule':<24}{'required boundary':>18}" + "".join(f"{a:>16}" for a, _ in SETUPS))
for rule, need in RULES.items():
    print(f"{rule:<24}{need:>18}" + "".join(f"{('yes' if n <= need else 'no'):>16}"
                                            for _, n in SETUPS))
print(f"{'recomputable at target':<24}{'':>18}"
      + "".join(f"{sum(1 for g in RULES.values() if n <= g):>16}" for _, n in SETUPS))
print()


def commercial_rule(boundary):
    """The target tries to recompute using only the data it already holds."""
    _, _, moved = split_pipeline(SOURCE_RECORDS, boundary)
    if boundary > RULES["commercial factor 1.05"]:
        return None, "yes", len(SOURCE_RECORDS), SOURCE_RETENTION
    v = moved
    if not any("m3" in k for k in v):
        v, _ = s1_screen(v)
        v, _ = s2_dedupe(v)
        v, _ = s3_consumption(v)
    new = [dict(k, m3=k["m3"] * (1.05 if k["type"] == "commercial" else 1.0)) for k in v]
    summ, _ = s4_summary(new)
    serving, _ = s5_serve(summ)
    return serving, "no", 0, len(PERIODS)


print(f"{'setup':<16}{'return to source':>18}{'re-read':>16}{'periods':>8}  serving")
for name, n in SETUPS:
    serving, back, reread, periods = commercial_rule(n)
    print(f"{name:<16}{back:>18}{reread:>16}{periods:>8}  {serving if serving else 'not at target'}")
print()
TRANSFERABLE = len(SOURCE_RECORDS) // len(PERIODS) * SOURCE_RETENTION
print("moving the boundary from 4 to 0: history to transport", len(SOURCE_RECORDS),
      " available from source", TRANSFERABLE,
      " permanently lost", len(SOURCE_RECORDS) - TRANSFERABLE)

rule                     required boundary    target (ELT)          hybrid    source (ETL)
commercial factor 1.05                   3             yes             yes              no
dedupe key                               1             yes              no              no
unit correction                          0             yes              no              no
recomputable at target                                   3               1               0

setup             return to source         re-read periods  serving
target (ELT)                    no               0      12  {'central': 1357.32, 'north': 1235.57, 'east': 902.13, 'west': 859.22, 'south': 1095.81}
hybrid                          no               0      12  {'central': 1357.32, 'north': 1235.57, 'east': 902.13, 'west': 859.22, 'south': 1095.81}
source (ETL)                   yes            2400       3  not at target

moving the boundary from 4 to 0: history to transport 2400  available from source 600  permanently lost 1800

Three rules ask for three different boundaries. The commercial factor touches the consumption row, changing the deduplication key touches the row before deduplication, and the unit correction touches the raw index itself. A target can apply a rule only if the output it holds sits before that boundary. The number is this: the rules that can be recomputed at the target are 3 in the ELT setup, 1 in the hybrid setup and 0 in the ETL setup.

The second table writes what that means. The first two setups recompute all twelve periods and produce the new serving output without ever going back to the source, transporting 0 rows again. The third setup holds only 55 summary cells at the target, so it cannot see the commercial and residential distinction; the only way to apply the rule is to go back to the source and re-read 2400 rows. The source is an operational system and keeps the last three periods: the periods that can be recomputed are 3, and the remaining nine periods cannot be brought back in any form.

This is the first concrete counterpart of the rule of the course: a pipeline’s number gives the same result when it is rerun, and a pipeline that cannot be rerun counts as unmeasured. The setup that transforms at the source works flawlessly in its first run, lowers the transported rows by a factor of 43.6 and produces the correct serving output. The moment it cannot recompute the nine periods, none of those numbers is defensible any longer.

The other side of the cost must also be written. Keeping the raw data at the target means holding 2400 rows in two places at once, and the cells read at the target rise from 110 to 33110. The ELT setup does not get the right to recompute for free; it buys it by reading 301 times more cells at the target.

Moving the Boundary Later

The assumption that the boundary is chosen once and left alone is also wrong. As the target’s capacity grows, or as the need for recomputation appears, the boundary is wanted not toward the source but toward the target. The last line at the end of the block writes the cost of this move.

Taking the boundary from four to zero requires bringing to the target the raw rows that have never sat there: 2400 rows. Of these, the ones that can come from the source are only the rows of the three retained periods, that is 600. The remaining 1800 rows sit nowhere and cannot be brought back in any form. After the boundary is moved, the pipeline can run forward with the new rules, but recomputing the nine periods of history is now permanently impossible.

The conclusion that follows is sharper than where the boundary should go: the boundary decision is the one decision that cannot be corrected retroactively. A wrong factor can be corrected, a dropped row can be taken again, a faulty check can be replaced. A raw row that was not stored does not come back, and with it goes the rerunnability of every number belonging to those periods.

Summary

• A transformation decision consists of where the chain of five stages is cut. The ETL boundary is 4, the ELT boundary is 0, and all three setups produce exactly the same serving output.
• Transforming at the source carries 55 rows and 165 cells to the target, transforming at the target carries 2400 rows and 14400 cells; the ratio in transported rows is 43.6.
• Lowering the transported rows does not lower the transported data: the hybrid boundary brings the rows down to 2200 while raising the transported cells to 15400, because the consumption stage adds a field to every row.
• The total of cells read by the three setups is fixed at 33110. The boundary decision does not remove the work, it only moves it between the source and the target.
• When a rule changes, the number of rules that can be recomputed at the target is 3, 1 and 0; the setup that transforms at the source has to go back to the source, and because the source keeps three periods, nine periods cannot be brought back.
• The cost of keeping the raw data at the target is the cells read at the target rising from 110 to 33110; the right to recompute is bought with that cost.

Next Step

All three setups in this lesson shared a single assumption: the transformation is a chain of five stages whose order is fixed and which can be written by hand. A real transformation layer is not a single chain but dozens of separate outputs that refer to one another, and which of them runs before which is written in a hand-kept list. That list breaks every time a new output is added. The next lesson writes the transformations as models that refer to one another, derives the dependency from the reference itself and puts two numbers side by side: the tasks that dependency resolution orders and the dependencies missed by the hand-kept order.