--- title: 'ETL and ELT' source: 'https://academia.sh/en/courses/data-pipelines/etl-and-elt' course: 'Data Pipelines and Orchestration' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Writes a working routine that splits a data pipeline between a source side and a target side with a single boundary.' - 'Measures the transported rows, the transported cells and the cells read by the two sides separately, according to where the boundary sits.' - 'Compares the number of rules that can be recomputed at the target when a rule changes across three setups and writes the cost of going back to the source in periods.' --- # ETL and ELT Counting whether the transformation runs at the source or at the target: when the boundary is moved to the source, the rows transported to the target fall from 2400 to 55 and the transported cells from 14400 to 165, but the total number of cells read by all three setups stays fixed at 33110 and only the identity of the reader changes; the hybrid boundary lowers the transported rows to 2200 while raising the transported cells to 15400; when a reporting rule changes, the number of rules that can be recomputed at the target is 3 for the setup that keeps the raw data, 1 for the hybrid setup and 0 for the setup that transforms at the source, and the last setup has to go back to the source and can recompute only three periods. 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. ```python # 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. ```python 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. ```python 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.