An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.

Data Foundationsintermediate

ETL and ELT

Q: Which change most reliably stops a failed nightly load from corrupting the warehouse when it is rerun?

Loading through a merge on the business key, or replacing the whole day partition, so a rerun produces the same result as one run. The failure mode is duplication from a partial run followed by a rerun, and idempotency removes it. Faster hardware only makes failure less frequent, running twice guarantees the problem, and ELT changes where the transform runs rather than whether a load can be safely repeated.

Q: A pipeline advances its watermark to the current time as soon as extraction begins. What goes wrong?

Whatever a failed run had not yet loaded from the window since the previous watermark is skipped permanently, because the next run only reads changes made after this run started. The watermark is a promise that everything before it has already loaded. Advancing it when extraction begins breaks that promise for the window it was meant to cover, from the previous watermark up to this run's start time. If the run then fails partway, nothing ever re-reads that window, so whatever it had not yet written is lost silently. Changes made after the run started still fall after the new watermark and are collected next time, which is why the loss is confined to the earlier window and is far harder to notice than a duplicate.

ETL moves data from source systems into an analytical store in three steps. Extract reads from the source, usually incrementally so only rows changed since the last run are pulled. Transform reconciles the sources by standardising codes and units, applying business rules, resolving duplicates and assigning warehouse keys. Load writes the result, appending new rows or updating existing ones. ELT swaps the last two steps, landing raw data first and transforming inside the warehouse, which is practical when storage is cheap and compute is elastic. Either way the pipeline has to be rerunnable without corrupting what it has already loaded.

Why it matters

Think of a kitchen taking deliveries from several suppliers. Extract is bringing the crates in. Transform is the prep bench, where everything gets unwrapped, weighed in the same units, labelled in the same language, and anything spoiled gets pulled out. Load is putting it on the right shelf. The prep bench is where the value is added, and it is also where one mistake quietly ruins every dish that follows it.

Before you read on — recall

Which change most reliably stops a failed nightly load from corrupting the warehouse when it is rerun?

Formulas

Throughput a batch window demands

r = \dfrac{N}{w}

With

N

rows to load inside a window of

w

seconds, a nightly load of 2.4 million rows into a four-hour window needs about 167 rows a second sustained. If the pipeline cannot hold that rate the load spills into business hours, and the choice is a faster pipeline or a smaller increment.

Why incremental extraction pays

N_{\text{inc}} = \#\{\, r \;:\; t_r > T_{\text{last}} \,\}

Rather than re-reading the whole table, an incremental extract takes only records whose change timestamp

t_r

falls after the last successful run at

T_{\text{last}}

. On a 2.4 million row orders table with 40,000 daily changes that is a sixtyfold reduction in work, which is why the watermark must be stored and advanced only after a run actually succeeds.

Worked examples

Scenario

A nightly load of web orders failed halfway through and the team reran it. Next morning revenue was overstated by about 40 per cent for the previous day.

Solution

The pipeline appended rows rather than merging on a key, so the partial first run and the full rerun both landed. The fix is idempotency. Either load through a merge that matches on the order identifier and updates rather than inserts, or stage the batch, delete the target partition for that date, then insert. Both make a rerun produce the same result as a single clean run. The watermark should also advance only after a successful load, otherwise a crash silently skips rows instead of duplicating them.

Scenario

A team debates whether to transform before loading or to land raw data first and transform inside the warehouse.

Solution

Transforming first keeps the warehouse tidy and the storage bill small, and it suits sources whose shape is stable. Landing raw first keeps the original, so when a business rule turns out to be wrong the team rebuilds from what it already holds instead of asking the source for a year of history it no longer keeps. Cheap storage and elastic compute have made the second pattern common, and it only works if raw retention and access are governed, because raw data still carries personal information.

Common mistakes

✗ETL is a one-off project that finishes. Sources change their fields, their codes and their meanings, so a pipeline is a maintained product whose main cost arrives after go-live.
✗The transform step is purely technical. Deciding that a cancelled order still counts as a sale, or that a refund reduces the original month, are business rules, and burying them in pipeline code hides them from the people accountable for the number.
✗ELT is simply the modern replacement for ETL. It trades a smaller upfront design for a larger raw store and more compute at query time, which is a cost and governance choice rather than a strict upgrade.
✗Rerunning a failed load is always safe. Only an idempotent pipeline is safe to rerun; an appending pipeline duplicates whatever the failed run had already written.

Revision bullets

•Extract, transform, load; ELT swaps the last two and transforms in the warehouse
•Incremental extraction uses a watermark advanced only after a successful run
•Transform holds business rules, so those rules must be documented, not buried in code
•Idempotent loads (merge, or partition replace) make reruns safe
•The batch window sets the throughput the pipeline has to sustain

Quick check

Which change most reliably stops a failed nightly load from corrupting the warehouse when it is rerun?

A pipeline advances its watermark to the current time as soon as extraction begins. What goes wrong?

Connected topics

Data Warehouse Metadata Star Schema Why Data Goes Bad Data Cleansing Into Production Analytics Roles

More in Data Foundations

What Big Data Means Forms of Data Measures & Dimensions Cloud for Analytics Fitness for Use Quality Dimensions Sampling Bias

Sources

Kimball & Caserta (2004)
Kimball, R., & Caserta, J. The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data. Wiley, 2004.
Treats extraction, conforming and delivery as a set of named subsystems rather than as one script.
Kimball & Ross (2013)
Kimball, R., & Ross, M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Wiley, 2013.
Covers incremental extraction, surrogate key assignment and the handling of late-arriving data.

How to cite this page

Dr. Phil's Quant Lab. (2026). ETL and ELT. Derivatives Atlas. https://phucnguyenvan.com/concept/ba-etl