Data Engineering interview questions: ETL & ELT fundamentals

Fraud alerts must fire within seconds of a transaction, so waiting for a scheduled batch run would make the alert useless; streaming ingestion processes each event as it arrives. A monthly finance report, a one-time historical backfill, and an annual archive export all tolerate delay and are naturally handled in scheduled or one-off batch jobs.

In ETL, data is pulled from the source first, then transformed in an intermediate processing layer before anything is written to the warehouse. Only the already-transformed data is loaded, so the warehouse tables are ready to be queried by dashboards last. The transform step must sit between extract and load because the defining feature of ETL is that transformation happens before loading.

The morning reconciliation report only needs yesterday's data by the start of the next business day, so a scheduled batch load is sufficient and simpler to operate. The monthly full file export is already a bounded dataset that arrives on a fixed cadence, which is a natural fit for batch. The fraud decision and the live inventory counter both require results within seconds of the event, which requires streaming ingestion rather than a periodic load.

All three raw rows are loaded unchanged into raw_orders, with amount stored as TEXT. The transform step is a SQL CREATE TABLE ... AS SELECT that runs inside the database. The GLOB filter '[0-9]*' keeps only amounts beginning with a digit, so the value 'x' is dropped and the values '10' and '5' remain. Casting them to INTEGER gives 10 and 5, so orders contains 2 rows whose amounts sum to 15. fetchone returns the tuple (2, 15), which prints as (2, 15).

Because the source files are immutable, the rerun presents the same input rows. A MERGE keyed on a business identifier updates existing rows and inserts missing ones, so repeating it converges to the same final state instead of duplicating rows. This idempotency is what makes batch reruns safe for retry after partial failure, and it is the property the statement describes.

The condition is parsed as (r["status"] == "shipped") or "returned". The right-hand operand is the non-empty string "returned", which is truthy, so the whole expression is always truthy and every row is appended regardless of its status. The intended check is r["status"] in ("shipped", "returned"). The other claims are wrong: append correctly adds a dictionary to the list, rows being a list means iterating it directly is correct, and the function does return out.

The defining difference is where the transform step runs relative to the load. ETL transforms data in an intermediate processing tier before it reaches the warehouse, so only the transformed result is loaded. ELT loads raw data into the warehouse first and uses the warehouse's own compute to transform it, which keeps raw history available for reprocessing and lets the warehouse's scalable engine do the transformation work. Both blanks are the standard acronyms for these two orderings.

The key is that streaming windows must be closed by a watermark, which is a heuristic about event-time progress. A watermark delay of roughly 10 minutes means a window's end is not declared reached until event time has advanced 10 minutes past it, so events delayed by the mobile buffer arrive while the window is still open and are counted as on-time data. Allowed lateness plays a different role: it keeps the window's state open AFTER the watermark has already passed the window end, so events arriving later than the watermark bound can still update the already-emitted result through an idempotent sink (upsert keyed by window start and dimensions). Events arriving beyond the allowed-lateness grace period are dropped or sent to a side output for correction. The accepted cost versus a nightly batch is that the watermark delay adds latency before each window is final, and events later than the allowed-lateness bound may be missed without a separate correction path, whereas batch reprocesses everything at once but delivers results hours later.

A watermark is the engine's progress estimate for event time, typically derived as the maximum observed event time minus an allowed out-of-orderness bound. It is what allows windows to close and emit results despite unbounded input, because the system commits to not expecting older events. Consequently an event below the watermark has missed the window's completion point; what happens next is a policy choice (drop, allowed lateness with updates, or side output) rather than an automatic correction. The key point is that watermarks trade completeness against latency: a larger bound admits more late data at the cost of delayed results.

The requirement is about what is persisted, not who can read it. In ELT, raw data is loaded into the warehouse first, so the raw identifiers are written to storage no matter what access controls or masking policies are applied afterward; restricted schemas and dynamic masking only govern reads, and time travel and fail-safe retain prior versions of stored data. Only an ETL design that tokenizes or masks identifiers in an external transformation service before the load ensures the warehouse never stores the raw values. That is why tokenizing in a transformation service before loading is the only option that satisfies the guarantee.