ETL engineer interview questions

As asked

You own a daily ETL job that loads orders from an upstream API into the warehouse. Yesterday's job failed halfway through and will be retried. How do you make the pipeline idempotent?

Sample answer outline

A strong answer defines the target invariant first: re-running the job for the same logical window should produce the same final table state, not duplicate rows or partial totals. Use a stable business key such as order_id, write into a staging table, validate counts and required columns, then merge into the target inside a transaction or partition swap. Track high-water marks separately from successful commits so a failed run does not advance the cursor. Candidates often trip up by relying only on append-only loads or by deleting a partition before they know the replacement data is complete.

Expect these follow-ups

• How would your answer change if the source sends updates to old orders?
• What metadata would you store for each pipeline run?
• How do you recover if the warehouse supports no multi-statement transaction?

As asked

Walk me through how you design an ETL load step so it can be safely re-run without duplicating or corrupting data. What patterns do you rely on, and where do they break down?

Sample answer outline

Strong answers cover MERGE/UPSERT vs. delete-then-insert strategies, using a natural or surrogate key to detect existing rows, and how staging tables isolate uncommitted writes. Candidates should mention where idempotency breaks down: sequence-generated keys, non-deterministic UUIDs at source, or sinks that lack ACID guarantees like S3 plus Athena.

Expect these follow-ups

• How do you handle sources that emit the same record with different timestamps on each pull?
• What changes if the target warehouse is BigQuery versus Postgres?

As asked

You are pulling data from a transactional Postgres database into a data warehouse. Compare timestamp-based incremental extraction with CDC via Debezium. When would you choose each, and what are the failure modes of the timestamp approach?

Sample answer outline

Timestamp extraction is simpler but misses deletes, is vulnerable to clock skew, breaks if updated_at is not indexed or is missing from some tables, and can lose rows during the overlap window if not handled carefully. CDC via Debezium reads Postgres WAL, captures inserts, updates, and deletes with low latency, but requires WAL level set to logical and adds operational overhead. The choice depends on whether deletes matter and whether the source schema is stable.

Expect these follow-ups

• What happens to your timestamp-based job if the source database has a maintenance window that causes a 4-hour gap?

As asked

You are building a daily load from an external vendor into your warehouse. The vendor has a history of sending files with missing columns, out-of-range values, and duplicate primary keys. How do you build data quality into the pipeline, and what happens when a check fails mid-run?

Sample answer outline

Strong answers describe using a framework like Great Expectations or dbt tests to validate after staging but before promotion to the production table. Candidates should explain quarantine patterns where bad rows go to a rejected table with error metadata, and discuss whether the pipeline should halt or continue with clean rows depending on business rules. They should also mention alerting and how to give the data team visibility into failure rate trends.

Expect these follow-ups

• How do you decide whether a 2% duplicate rate should fail the pipeline or trigger a warning?

As asked

Write a Python function that takes a list of event dictionaries, each with an 'id' and a 'ts' (Unix timestamp), and returns a list containing only the most recent record for each id. Optimize for the case where the list has millions of records.

Sample answer outline

A single-pass O(n) solution using a dict keyed by id and comparing ts values is the right answer. Candidates should avoid sorting the entire list first which is O(n log n). Bonus for noting that the dict approach works in constant space relative to unique keys and handles ties consistently.

Reference implementation (python)

def deduplicate(records: list[dict]) -> list[dict]:
    # records = [{'id': 'a', 'ts': 1000}, {'id': 'a', 'ts': 2000}, ...]
    latest = {}
    for rec in records:
        rid = rec['id']
        if rid not in latest or rec['ts'] > latest[rid]['ts']:
            latest[rid] = rec
    return list(latest.values())

Expect these follow-ups

• How would you parallelize this if the records were spread across 100 files on S3?

As asked

Write a Python generator that takes an iterable of arbitrary size and a chunk_size, and yields lists of up to chunk_size items. Use this to batch insert rows into Postgres without loading all rows into memory at once.

Sample answer outline

The generator should use islice or a simple accumulator loop and yield each batch. Strong answers note that generators are memory-efficient for large inputs and that the caller controls back-pressure by consuming one batch at a time. They should also mention that psycopg2's execute_values is more efficient than looping execute for Postgres inserts.

Reference implementation (python)

from itertools import islice

def chunked(iterable, chunk_size: int):
    it = iter(iterable)
    while True:
        batch = list(islice(it, chunk_size))
        if not batch:
            break
        yield batch

# Usage:
# for batch in chunked(rows, 1000):
#     cursor.executemany(sql, batch)

Expect these follow-ups

• How does your solution behave if the iterable raises an exception halfway through?