Skip to content
Tech Interview Prep
Technical interview guide

ETL vs. ELT

Transform-before-load versus load-then-transform, and why modern warehouses shifted the order.

Read
28 min
Practice MCQs
25
Interview Q&A
25
Edition
v4
Editorial status
Reviewed

Scope: BigQuery documentation current 2026-08-31; Apache Spark 4.2; Apache Airflow stable current 2026-08-31; Apache Iceberg and Parquet current 2026-08-31; PostgreSQL 18.

Curated: · Written: · Reviewed:

25 questions

MCQ-23A support tool keeps its own account records and has no connection to the analytics warehouse. Each night a warehouse job recomputes a per-account health score, and support agents need that score showing in the tool's account panel the next morning. Which pattern does this work describe?
MCQ-19A nightly transformation job runs on a Spark cluster sized for peak load and billed by the hour whether or not it is busy. The team rewrites the job as SQL that runs inside the warehouse. What does this change about how the transformation's compute cost is budgeted?
MCQ-2A team lands 4 TB of raw events per day in a cloud warehouse and keeps 730 days of history: about 2.9 PB. Their ETL job transforms each day's data on a 20-node cluster: 6 hours at $3 per node-hour, so $360 a night. They replace it with a nightly ELT job, `INSERT INTO clean SELECT * FROM raw`, and the warehouse bills $6.25 per TB scanned. What does the first night cost, and why?
MCQ-14A team finds a parsing bug that has corrupted a derived metric for the past six months. The upstream source keeps only 30 days of point-in-time history and no change-data-capture stream. The pipeline is ELT, with raw payloads landed in object storage before transformation. What does retaining that raw landing layer change about remediation compared to a traditional ETL pipeline?
MCQ-4An orders CDC stream lands append-only into a raw layer as one Parquet file per hour; every row carries the source `lsn` and an `_ingested_at` timestamp. Between 14:00 and 16:00 a source bug wrote `total = 0` for 4,000 orders, and the fix re-emitted the correct totals with higher `lsn` values. Auditors must be able to see exactly what the source emitted, and the revenue model must report the correct totals. Which design meets both?
MCQ-18A team replaces a load that validated every record against the warehouse's declared columns before insert with an ELT pipeline that lands raw source files and applies structure only when models read them. Two source systems both publish a file named export.csv to the same landing prefix each night, and models read every file under the prefix. Which guarantee must the load step provide?
MCQ-1A payments team moves 2 TB/day from an on-prem Oracle database into a cloud warehouse, where analysts run the reporting. Security policy forbids unmasked card numbers at rest in the warehouse. The team's Spark cluster already masks the feed in 40 minutes; the warehouse's SQL engine could do the same masking in 6 minutes. Which pipeline should they build?
MCQ-3A payments team must load 6 TB/day of card-authorization events from an on-prem Kafka cluster into a cloud warehouse, where analysts need transaction-level detail. The card networks' contracts prohibit raw PANs from being written to storage outside the source VPC, and the warehouse cannot mask columns on ingest. Which design fits?
MCQ-20A retailer streams order events into a cloud warehouse. Each event carries `order_id`, `amount`, `region`, and `card_pan`. Compliance requires that `card_pan` never be written to the warehouse at all. Analysts must keep running ad-hoc SQL over raw `order_id`, `amount`, and `region` without waiting for modeled tables. Which pipeline design meets both requirements?
MCQ-25A team writes its transformations as SQL inside the warehouse, using engine-specific scripting, UDFs, and semi-structured types. A new requirement says the analytical workload must be movable to a different warehouse within a year. What does expressing transformations in that dialect do to portability, and how should it weigh in the ETL-versus-ELT decision?
MCQ-22A retailer moves its transformation logic out of a separate ETL tier into SQL models that run inside the warehouse. Ingestion is a vendor-managed connector the team cannot modify, and the models encode revenue logic that finance revises about weekly. Last month a source table began emitting duplicate rows; the join fanned them out and doubled reported revenue for two days before anyone noticed. Which arrangement for ownership, versioning and testing fits this setup?
MCQ-17A lineage service records column-level edges for each instrumented run. Job A reads raw.orders(id, email, amount) and writes mart.orders_daily(id, amount). Job B reads mart.orders_daily and writes exec_dashboard. An analyst exports exec_dashboard to a CSV that contains email. What does the recorded lineage show about email's path to the CSV?
MCQ-12A nightly job loads `orders` from Postgres shards into `fact_orders`. Last night it reported success: it read 1,204,880 rows and `fact_orders` holds 1,204,880 rows for the date, with `amount` summing to the cent against the shards it read. The shard registry's control total for the date across all active shards is 1,348,100 rows. Finance reports the day's revenue is about 12% below the same weekday last month.
MCQ-5A nightly job loads orders into `fact_orders`, which has no unique constraint, with `INSERT INTO fact_orders SELECT * FROM src_orders WHERE updated_at >= :wm AND updated_at < :run_start`. It commits that insert, then sets `:wm = :run_start` in a separate transaction. Run 1 inserts 40,000 rows and commits; the process dies before the watermark update commits. The job retries with the same `:wm` and succeeds. What does `fact_orders` hold after the retry?
MCQ-21A retail warehouse loads `dim_customer` and `fact_order`. Customer 4471 was in the Silver tier until 2024-02-14, when they moved to Gold. Finance re-runs last year's reports and expects February orders placed before the 14th to report Silver and orders from the 14th onward to report Gold. Which dimension design delivers that?
MCQ-10A 90-day backfill of `events_daily` rewrites each day-partition in place with new dedup logic that removes 20% of rows from every day. The job runs as 10 batches of 9 days, one batch every 6 minutes, the first finishing at minute 6; each batch replaces its 9 partitions atomically, so no reader sees a half-written day. A dashboard counts events across the full 90 days and refreshes every 5 minutes. What does its 90-day count read at minute 35, relative to the count before the job started?
MCQ-6A nightly extractor runs `SELECT ... FROM orders WHERE updated_at > :watermark ORDER BY updated_at` against the primary, then stores `max(updated_at)` as the next watermark. Monday's run finished at 02:00:00 and stored `2024-03-11 01:59:58.412`. Order 88213 carries `updated_at = 2024-03-11 01:58:00.050`; it was written by a transaction that began at 01:58:00 and committed at 02:00:03, and no later update touched it. Tuesday's run did not extract it. Both hosts are NTP-synced to within 5 ms. What is the correct fix?
MCQ-7A team bootstraps a 200 GB `orders` table into a warehouse. To avoid holding WAL during the load, they run a separate consistent snapshot first (5 hours), then create the logical replication slot and start the Debezium Postgres connector in streaming mode. 40,000 rows were updated during the 5-hour snapshot window. After streaming catches up, the warehouse still shows the pre-update values for those 40,000 rows and never converges. What is the cause?
MCQ-9A Kafka topic `orders` carries Avro records registered under subject `orders-value`, whose compatibility level is BACKWARD (the Schema Registry default). Consumers always deserialize with the latest registered schema. A producer submits a new version that removes `currency` (string, no default) and adds `region` (string, no default). What does the registry do, and why?
MCQ-15An hourly revenue job windows on the Kafka broker's append timestamp. A regional outage made the mobile SDK buffer purchases for up to 8 hours, so the dashboard books them in the hour they were uploaded. Which change fixes the misplacement?
MCQ-24A payments team must publish a fraud-risk score for every card authorization within 10 minutes of the event. The pipeline handles 100M events/day (about 1,200/s average, 4,000/s peak). Their batch job processes a full day's 100M events in 42 minutes; a streaming version of the same job costs about 4x as much to run. Which option meets the 10-minute freshness requirement at the lowest cost?
MCQ-13A Flink job aggregates purchases into 1-minute tumbling event-time windows, keyed by merchant. The watermark strategy is bounded-out-of-orderness of 20 seconds, and allowed lateness is 30 seconds. A window emits its result when the watermark passes the window end, and keeps accepting late elements until the watermark passes the window end plus the allowed lateness; each accepted late element produces another result for that window. The sink is append-only: it cannot update or delete a record it has already written. The window [10:00:00, 10:01:00) for merchant M has already emitted a count of 40. The maximum event timestamp seen so far is 10:01:45. A purchase for M with event time 10:00:50 arrives now. What happens?
MCQ-16A consumer reads from an at-least-once queue and writes to a Postgres table `events` that has `UNIQUE (dedup_key)`. For each message it computes `dedup_key = md5(event_id || ':' || emitted_at)` and runs `INSERT ... ON CONFLICT (dedup_key) DO NOTHING`. The producer stamps `emitted_at` once, when it first emits the message, and reuses that same value on every retry of that message. Over one hour the producer emits 1,000 events. The queue redelivers 37 of those messages once after a consumer restart. Separately, a producer bug re-emits 12 events with the same `event_id` but a freshly stamped `emitted_at`. Nothing else repeats. Once the backlog drains, how many rows does `events` hold?
MCQ-11An Airflow DAG runs hourly. Its `charge_customer` task calls a payment API that supports an `Idempotency-Key` header, then writes the returned charge ID to Postgres. The task has `retries=3`. A worker is killed after the payment API returns 200 but before the task records success. On retry, the API charges the customer a second time. The team wants to stop duplicate charges without losing retry resilience. What should they change?
MCQ-8A Flink 1.18 job reads from Kafka with exactly-once checkpointing enabled (10 s interval), aggregates, and writes to two sinks: a Kafka topic through the transactional producer, and Postgres through the JDBC sink using a plain INSERT. A TaskManager dies and the job restores from the last completed checkpoint. The Kafka topic has no duplicates, but Postgres holds 1,412 duplicate rows for the events processed in the 8 seconds between that checkpoint and the crash. Which change makes the pipeline produce one row per event end to end?