ETL Interview Questions for Data Engineers

ETL interview questions sound like definitions, such as "what is incremental loading?", but the follow-ups are about what goes wrong with real data. These answers come from loading two real extracts that overlap, which is one of the most common situations in practice and one of the most common sources of double counting.

The data is UCI's Online Retail II: every order line from a UK online gift retailer, delivered as one workbook with two sheets, one per year, 1,067,371 rows in total (UCI: Online Retail II). The first sheet ends on 9 December 2010; the second starts on 1 December 2010. DuckDB reads both sheets straight from the workbook (DuckDB: Excel extension).

import duckdb

db = duckdb.connect()
db.execute("INSTALL excel; LOAD excel")


def extract(table, sheet):                                      # UCI Online Retail II: two yearly sheets
    db.execute(f"""CREATE TABLE {table} AS SELECT
        Invoice AS invoice, StockCode AS product, Description AS description, CAST(Quantity AS INTEGER) AS qty,
        TIMESTAMP '1899-12-30' + to_seconds(CAST(ROUND(CAST(InvoiceDate AS DOUBLE) * 86400) AS BIGINT)) AS ts,
        ROUND(CAST(Price AS DOUBLE), 2) AS price, CAST(CAST("Customer ID" AS DOUBLE) AS INTEGER) AS customer, Country AS country
        FROM read_xlsx('online_retail_II.xlsx', sheet = '{sheet}', all_varchar = true)""")


def one(sql):
    return db.execute(sql).fetchone()


extract("extract_1", "Year 2009-2010")
extract("extract_2", "Year 2010-2011")
print("E1 two extracts")
for t in ("extract_1", "extract_2"):
    print(f"   {t}: {one(f'SELECT COUNT(*) FROM {t}')[0]:,} rows, {one(f'SELECT MIN(ts)::DATE FROM {t}')[0]} to {one(f'SELECT MAX(ts) FROM {t}')[0]}")

print("\nE2 the extracts overlap")
overlap = one("SELECT COUNT(*) FROM (SELECT * FROM extract_1 INTERSECT ALL SELECT * FROM extract_2)")[0]
print(f"   identical rows in both: {overlap:,}")
dec = "SUM(qty * price) FILTER (WHERE ts >= '2010-12-01' AND ts < '2010-12-10')"
naive = one(f"SELECT ROUND({dec}) FROM (SELECT * FROM extract_1 UNION ALL SELECT * FROM extract_2)")[0]
true_ = one(f"SELECT ROUND({dec}) FROM extract_2")[0]
print(f"   1-9 December 2010 revenue after a plain append: {naive:,.0f} | actual: {true_:,.0f}")

print("\nE3 incremental load with a watermark")
watermark = one("SELECT MAX(ts) FROM extract_1")[0]
strict = one(f"SELECT COUNT(*) FROM extract_2 WHERE ts > TIMESTAMP '{watermark}'")[0]
loose = one(f"SELECT COUNT(*) FROM extract_2 WHERE ts >= TIMESTAMP '{watermark}'")[0]
print(f"   watermark {watermark} | ts > watermark loads {strict:,} | ts >= watermark loads {loose:,} ({loose - strict} rows again)")

print("\nE4 no natural key: duplicates inside each extract")
for t in ("extract_1", "extract_2"):
    print(f"   {t}: {one(f'SELECT COUNT(*) - (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM {t})) FROM {t}')[0]:,} exact duplicate rows")
union_distinct = one("SELECT COUNT(*) FROM (SELECT * FROM extract_1 UNION SELECT * FROM extract_2)")[0]
except_all = one("SELECT COUNT(*) FROM (SELECT * FROM extract_1 UNION ALL (SELECT * FROM extract_2 EXCEPT ALL SELECT * FROM extract_1))")[0]
print(f"   combined with UNION (distinct): {union_distinct:,} | with UNION ALL + EXCEPT ALL: {except_all:,}")

print("\nE5 quality rules on extract_2")
print("  ", dict(zip(["zero quantity", "negative price", "zero price", "cancellations (C...)", "adjustments (A...)"],
                    one("""SELECT COUNT(*) FILTER (WHERE qty = 0), COUNT(*) FILTER (WHERE price < 0), COUNT(*) FILTER (WHERE price = 0),
                                  COUNT(*) FILTER (WHERE invoice LIKE 'C%'), COUNT(*) FILTER (WHERE invoice LIKE 'A%') FROM extract_2"""))))
print("   the negative prices:", db.execute("SELECT invoice, description, price FROM extract_2 WHERE price < 0").fetchall())

print("\nE6 load and reconcile")
db.execute("CREATE TABLE sales AS SELECT * FROM extract_1")
db.execute(f"INSERT INTO sales SELECT * FROM extract_2 WHERE ts > TIMESTAMP '{watermark}'")
source = one("SELECT COUNT(*), ROUND(SUM(qty * price)) FROM (SELECT * FROM extract_1 UNION ALL (SELECT * FROM extract_2 EXCEPT ALL SELECT * FROM extract_1))")
target = one("SELECT COUNT(*), ROUND(SUM(qty * price)) FROM sales")
print(f"   expected {source[0]:,} rows / {source[1]:,.0f} | loaded {target[0]:,} rows / {target[1]:,.0f} | match: {source == target}")

E1 two extracts
   extract_1: 525,461 rows, 2009-12-01 to 2010-12-09 20:01:00
   extract_2: 541,910 rows, 2010-12-01 to 2011-12-09 12:50:00

E2 the extracts overlap
   identical rows in both: 22,523
   1-9 December 2010 revenue after a plain append: 754,977 | actual: 377,488

E3 incremental load with a watermark
   watermark 2010-12-09 20:01:00 | ts > watermark loads 519,387 | ts >= watermark loads 519,452 (65 rows again)

E4 no natural key: duplicates inside each extract
   extract_1: 6,865 exact duplicate rows
   extract_2: 5,268 exact duplicate rows
   combined with UNION (distinct): 1,033,036 | with UNION ALL + EXCEPT ALL: 1,044,848

E5 quality rules on extract_2
   {'zero quantity': 0, 'negative price': 2, 'zero price': 2519, 'cancellations (C...)': 9288, 'adjustments (A...)': 3}
   the negative prices: [('A563186', 'Adjust bad debt', -11062.06), ('A563187', 'Adjust bad debt', -11062.06)]

E6 load and reconcile
   expected 1,044,848 rows / 18,909,762 | loaded 1,044,848 rows / 18,909,762 | match: True

E1. What do you check first about a new extract?

Row counts and the time range it covers, compared with what you expected. Extract 1 runs from 1 December 2009 to 9 December 2010, extract 2 from 1 December 2010 to 9 December 2011. The ranges overlap by nine days. Writing down each extract's range before loading anything is how you spot that.

E2. What happens if you simply append both extracts?

Everything in the overlap is counted twice. 22,523 rows appeared, identically, in both files. Appending them made revenue for 1–9 December 2010 754,977 instead of 377,488, exactly double, and nothing failed: no error, no warning, just a wrong report. Overlapping extracts are normal, because source teams often resend a few days to catch late changes, so the load must expect them.

E3. How should an incremental load decide what is new?

With a watermark: the highest timestamp or key already loaded, stored by the pipeline (Microsoft Learn: incremental copy). After loading extract 1, the watermark was 20:01 on 9 December 2010. Loading extract 2 rows with ts > watermark took 519,387 rows; ts >= watermark took 65 more, which were rows already loaded with exactly that timestamp.

The follow-up to raise: the strict comparison is only safe if no new row can ever arrive with a timestamp at or below the watermark. Late-arriving rows break that, so robust pipelines re-read a window before the watermark and deduplicate against what is already loaded, rather than trusting the timestamp alone.

E4. How do you deduplicate when there is no unique key?

Carefully. These order lines have no line ID: 6,865 rows in extract 1 and 5,268 in extract 2 are exact copies of another row in the same file. Some may be real repeated lines on an order, others errors, and the data cannot tell them apart. Combining the extracts with UNION, which removes all duplicates, gave 1,033,036 rows. Appending only the rows of extract 2 not already in extract 1, using EXCEPT ALL, which removes the overlap but keeps duplicates within a file, gave 1,044,848 (DuckDB: set operations). The 11,812-row gap is a business decision, not a technical one. The right answer in an interview is to ask the source owner, and meanwhile to keep the rows and flag them rather than silently drop them.

E5. What quality rules would you apply, and who decides them?

Start with rules that catch broken data: no zero quantities (none here), no negative prices, valid invoice formats. Then look at what the rules catch before enforcing them. The two negative prices were not errors: they were "Adjust bad debt" entries of −11,062.06 on invoices starting with A, a deliberate accounting adjustment. 2,519 lines had a price of zero and 9,288 were cancellations. A rule that rejected all of these would discard legitimate business events. Agree the rules with the people who own the data, and route exceptions to a review table rather than deleting them.

E6. How do you prove a load is complete?

Reconcile. Compute what the target should contain from the sources, independently of the load logic, and compare counts and sums. The expected result was 1,044,848 rows and 18,909,762 in revenue, and the loaded table matched on both. The watermark logic and the set-based calculation agreed, which is good evidence that neither double-counted nor dropped rows. Run the same comparison after every load, and alert when it fails.

E7. When is a full reload better than an incremental one?

When the data is small enough to reload in the time available, when the source cannot say reliably what changed, or when rows can be updated or deleted at the source. A full reload is simple and self-correcting; an incremental load is fast but only as reliable as its change detection. Many teams combine them: incremental loads daily, a full reconciliation or reload weekly.

How to answer ETL questions

• Write down each extract's range and row count before loading.
• Expect overlap and retries; design loads that can absorb both.
• Never invent a key the business has not defined; flag ambiguous duplicates.
• Reconcile every load against an independent calculation.

Reruns are covered in idempotency in data pipelines, late rows in handling late-arriving data, and rule design in data quality testing and validation. Related rounds are data warehouse interview questions and data engineering interview questions and answers.

Building and testing ETL pipelines is part of the Data Engineering course.

Sources

• Online Retail II dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Incrementally load data from a source data store to a destination data store (Microsoft Learn) — Microsoft
• Set Operations (DuckDB documentation) — DuckDB
• Excel Extension (DuckDB documentation) — DuckDB