ETL engineer technical fundamentals interview questions

As asked

What is the practical difference between ETL and ELT, and when would you choose one over the other in a modern data stack?

Sample answer outline

ETL transforms data before loading it into the warehouse (common when the warehouse is expensive or lacks compute power, or when PII must be removed before storage). ELT loads raw data first and transforms inside the warehouse using SQL or dbt (preferred in modern stacks because warehouses like Snowflake or BigQuery have abundant, scalable compute and SQL is easier to test and version than pipeline transformation code).

Expect these follow-ups

• Are there compliance or security reasons to prefer ETL over ELT?

As asked

Explain the star schema pattern and describe the specific advantages it provides over a normalized third normal form schema for analytical queries in a data warehouse.

Sample answer outline

Star schema has a central fact table with foreign keys to denormalized dimension tables. It reduces join complexity for analytical queries (fewer tables to join), makes columnar storage more efficient because dimension attributes are repeated rather than indirected through joins, and makes query intent clearer to BI tools. The tradeoff is storage redundancy and update anomalies in dimensions, which is why SCD patterns exist.

Expect these follow-ups

• When would you use a snowflake schema instead of a star schema, and what is the cost of doing so?

As asked

Explain the core architectural difference between a data warehouse and a data lake, and describe a scenario where you would recommend one over the other for a new company building its first data platform.

Sample answer outline

A warehouse stores structured, curated data with schema-on-write and is optimized for SQL analytics. A data lake stores raw data in any format with schema-on-read and is cheaper for storing high volumes of semi-structured or unstructured data. A new company with primarily structured relational sources and a small team should start with a warehouse (Snowflake, BigQuery) for simplicity. A company with heavy ML or semi-structured data workloads benefits from a lake or a lakehouse (Delta Lake, Iceberg).

Expect these follow-ups

• What is a lakehouse and how does it try to combine the benefits of both?

As asked

Explain what ACID means and describe which of those properties a typical cloud data warehouse like Snowflake or BigQuery provides versus a traditional RDBMS like Postgres. Where does the lack of full ACID support in a warehouse matter for ETL design?

Sample answer outline

Snowflake and BigQuery both support multi-statement transactions with BEGIN and COMMIT, providing atomicity across a sequence of DML statements. However, Snowflake uses snapshot isolation rather than serializable isolation: concurrent readers see a consistent snapshot from the transaction start, and write-write conflicts cause one transaction to fail. Postgres offers stricter serializable isolation by default. For ETL, the practical implication is that a sequence of INSERTs and UPDATEs inside a Snowflake transaction will roll back atomically on failure, but two concurrent ETL jobs modifying overlapping rows may conflict and require retry logic.

Expect these follow-ups

• How does Snowflake's snapshot isolation differ from serializable isolation, and when does that difference matter in an ETL context?

As asked

Explain why columnar storage formats like Parquet and ORC outperform row-oriented storage for analytical queries, and why row-oriented storage is still better for OLTP workloads.

Sample answer outline

Columnar storage stores all values for a column contiguously, so a query that reads 3 columns out of 100 only scans 3% of the data. Values within a column have similar types and distributions, making compression (run-length encoding, dictionary encoding) far more effective. Row storage is better for OLTP because inserting or reading a single full record requires touching one contiguous block, whereas in columnar storage a single-row insert must append to every column file.

Expect these follow-ups

• How does Parquet's row group concept affect query performance on large files?

As asked

What does exactly-once processing mean in a data pipeline, why is it hard to achieve, and what practical guarantees do systems like Kafka, Spark Structured Streaming, and Airflow actually provide?

Sample answer outline

Exactly-once means each message or record is processed and affects the output exactly once despite failures and retries. Kafka provides exactly-once between producers and consumers within the Kafka ecosystem using idempotent producers and transactional APIs. Spark Structured Streaming achieves end-to-end exactly-once only when the sink supports idempotent writes or transactional commits. Airflow provides at-least-once retry semantics; idempotency in the task logic is the engineer's responsibility. True exactly-once in distributed systems almost always requires idempotent sinks.

Expect these follow-ups

• If exactly-once is too expensive to implement, how do you design a system to be idempotent instead?

As asked

Explain how indexes on a source Postgres database can both help and hurt your ETL extraction queries, and what index changes you might ask the source team to make to support efficient incremental extractions.

Sample answer outline

An index on updated_at enables efficient incremental extractions by avoiding full table scans. A composite index on (updated_at, id) supports pagination via keyset pagination without offset blowup. However, every index on the source table slows down inserts and updates, so there is a cost the source team must accept. Candidates should also mention that large ETL extractions using LIMIT/OFFSET become slower as offset grows, and keyset pagination is the correct alternative.

Expect these follow-ups

• What is keyset pagination and why is it better than OFFSET for large incremental pulls?

As asked

Explain what a watermark is in a streaming data pipeline, why it is necessary, and what happens to events that arrive after the watermark has passed.

Sample answer outline

A watermark is an estimate of the maximum event time that has been observed, minus a configurable lag to allow for late arrivals. It tells the streaming engine when a time window is safe to close and emit results. Events arriving after the watermark for a closed window are either dropped, emitted as corrections in a separate late-data output, or accumulated depending on the engine's configuration. Without watermarks, stateful operations like aggregations must keep state forever to handle arbitrarily late data.

Expect these follow-ups

• How do you choose the watermark lag for a stream where 99% of events arrive within 2 minutes but 1% can arrive up to 30 minutes late?

As asked

When you design the target schema for an ETL pipeline feeding a BI tool, why do you typically denormalize into wide flat tables rather than normalizing into 3NF? What are the specific performance and usability tradeoffs?

Sample answer outline

Columnar warehouses are optimized for reading many rows of a few columns, not for the many small random reads that normalized schemas require. BI tools perform poorly with many-table joins because each join is a potential bottleneck and makes queries harder for non-engineers to write. Denormalized wide tables trade storage (cheap in cloud warehouses) for query speed and usability. Normalization is still valuable in the raw/staging layer to preserve source fidelity before transformation.

Expect these follow-ups

• What problems arise from denormalizing too aggressively, and how do you avoid them?

As asked

What information does the Airflow metadata database store, what happens to your pipelines if it becomes unavailable, and why is it important to keep it lean?

Sample answer outline

The metadata database stores DAG definitions (parsed from files), DAG runs, task instances, XCom values, connections, variables, and user/role data. If it goes down, the scheduler cannot trigger new runs and workers cannot update task state, effectively halting all pipeline operations. It bloats over time from XCom data, old task logs stored as BLOBs, and unarchived run history. Keeping it lean requires regular log archival, limiting XCom payload sizes, and running airflow db clean periodically.

Expect these follow-ups

• What is the recommended strategy for scaling the Airflow metadata database as the number of DAGs grows to thousands?