Data Engineering
ELT vs ETL: multiple-choice review
Crux Multiple-choice synthesis across the ELT-vs-ETL unit: where the Transform runs, replayability, warehouse cost, idempotency, and the medallion contract.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each one is a decision you actually make designing a pipeline — not a definition to recite, but a tradeoff to weigh against cost, replay, and compliance.
Goal
Confirm you can connect where the Transform runs to its downstream consequences: replayability, the warehouse bill, schema discipline, and the idempotency that keeps a retry from doubling your data.
Quiz
Completed
What single architectural change in cloud warehouses (Snowflake, BigQuery) is the real reason the industry flipped from ETL to ELT?
Heads-up Raw query speed is not the driver — Spark still wins many heavy jobs. The flip was economic and architectural: cheap decoupled storage made landing raw affordable, which ETL could never do.
Heads-up dbt rode the wave; it did not cause it. The enabling change was storage/compute separation. dbt is just the dominant way to author the in-warehouse T once that became viable.
Heads-up Volumes grew, not shrank. ELT became viable despite larger volumes precisely because separated storage made keeping all of it cheap.
Quiz
Completed
You discover a timezone bug in a transform that has shipped wrong numbers for six months. Under ELT with a medallion architecture, what is the fast, correct fix?
Heads-up That is the ETL failure mode — you only re-extract when raw was discarded. ELT keeps raw permanently in bronze precisely so you can replay transforms without touching a source that may have changed or rate-limited you.
Heads-up Mutating gold by hand breaks the rebuildable contract and is not reproducible — the next full run silently reverts your patch. Fix the transform, not the output.
Heads-up That is only true in ETL where raw was thrown away. ELT's entire premise is that bronze is preserved, so transformed history is always replayable.
Quiz
Completed
A dbt model was set to full-refresh by default and scheduled hourly; it rebuilds a 2 TB fact table from scratch every run and the Snowflake bill jumped 40%. The output is correct. Where is the bug and what is the fix?
Heads-up Correctness and cost are independent axes in ELT. A perfectly correct transform materialized as an hourly full rebuild is a four-figure line item — the bill is the bug even when the numbers are right.
Heads-up The pricing works as designed — you pay per warehouse-second for the compute you requested. The fix is on your side: stop rescanning 2 TB hourly by going incremental.
Heads-up A bigger warehouse finishes faster but bills more credits per second; total cost for the same full scan barely moves. The leverage is scanning less data (incremental), not scanning the same data faster.
Quiz
Completed
A regulated fintech ingests payment events containing card PANs, and compliance forbids raw cardholder data from ever sitting in the analytics warehouse. Which pattern fits, and why is the modern ELT default wrong here?
Heads-up This is the right default for most data but loses on the hard constraint: raw PANs physically land in bronze before any masking runs, so cardholder data sits in the warehouse — exactly what compliance forbids.
Heads-up Dynamic masking hides PII from readers, but the raw values are still physically stored in bronze. Masking is a view-time control, not a storage control — stored-but-hidden still violates 'never sits in the warehouse'.
Heads-up Access policies govern who reads the data, not whether it is stored. The rule here is about storage, so no in-warehouse control satisfies it — the masking must happen before load.
Quiz
Completed
An EL tool retried a partially-succeeded load and your revenue fact table now shows inflated totals. What property was missing, and what is the durable design fix?
Heads-up Retries on transient failure are correct behaviour and you cannot rely on never re-running. The fix is to make the load safe to re-run (idempotent), not to forbid re-running it.
Heads-up An append-style load has no key to dedupe on, so the warehouse happily stores duplicates. Deduplication is your design choice — a unique_key with merge — not an automatic guarantee.
Heads-up A cleanup job is a band-aid that races every new load and can delete legitimate rows. Idempotent loads prevent the duplicate from ever being written, which is the real fix.
Quiz
Completed
Someone calls schema-on-read 'pure freedom — no schema to fight at load time.' What does the unit's framing say they are missing?
Heads-up It removes the schema work at load time only. The work reappears downstream: you must enforce structure in silver with tests, or bad data silently corrupts gold.
Heads-up The point is not query speed — it is when validation happens. The cost of skipping load-time validation is silent data-quality bugs that surface late, not slower reads.
Heads-up It applies to any format landed raw. The framing is about deferring enforcement to read/transform time regardless of the source shape.
Recap
The through-line: where the Transform runs decides everything downstream. Decoupled storage/compute made landing raw cheap, which buys replayability through the medallion contract (immutable bronze, cleaned silver, business-ready gold). But the T now meters on the warehouse bill, so you go incremental by default. And because loaders retry, every load must be idempotent — merge on a unique_key — or a retry doubles your data. ELT is the default; ETL still wins when a hard rule says raw PII must never touch the warehouse.