PrepPilotPractise free

ETL / ELT Pipelines interview questions

Batch design, incremental loads, idempotency, dbt

16 questions · dbt · updated November 2026

Practise these as flashcards. One or two lines each; the ones you miss come back first.

Start free

Core questions

What is the main difference between ETL and ELT?

ETL transforms data before loading it into the destination, while ELT loads raw data first and then transforms it within the destination system, usually a data warehouse.

Why is ELT preferred in modern cloud data platforms?

Cloud data warehouses like BigQuery and Snowflake are optimized for in-database transformations, making ELT more scalable and cost-effective by leveraging their compute power after loading.

What is a watermark in the context of ETL pipelines?

A watermark is a marker, such as a timestamp or an ID, that tracks the latest data processed, enabling incremental loads by only processing new or updated records.

Give an example of an idempotent ETL operation.

Using an UPSERT or MERGE statement to insert or update records ensures that running the job multiple times with the same data does not create duplicates.

Intermediate questions

How would you implement an incremental load from a source table with an updated_at column?

I would store the maximum updated_at value processed in each run and use it as a filter in the next run, selecting only records where updated_at is greater than the last watermark.

What are the advantages of using Parquet over CSV for data storage?

Parquet is a columnar, binary format that provides better compression and query performance, especially for analytical workloads, while CSV is text-based and less efficient for large-scale analytics.

How does dbt handle incremental models?

dbt incremental models process only new or changed data by using filters based on watermarks or unique keys, and it manages the logic to append or update records in the target table.

What is late-arriving data and how can you handle it in a batch pipeline?

Late-arriving data is data that arrives after its expected processing window. To handle it, I might reprocess affected partitions or use upserts to update previously loaded data.

What is Change Data Capture (CDC) and when would you use it?

CDC tracks and captures changes (inserts, updates, deletes) in source data, enabling near-real-time or incremental updates. It's useful when you need to keep the target system in sync with source changes efficiently.

How do Delta Lake and Apache Iceberg improve on standard Parquet tables?

Delta Lake and Iceberg add ACID transactions, schema evolution, and time travel capabilities to Parquet tables, making them more suitable for production data lakes with concurrent writes and evolving schemas.

What types of tests can you define in dbt?

dbt supports tests for uniqueness, non-null values, referential integrity, and custom SQL-based checks to ensure data quality and correctness in models.

Senior questions

How would you design a batch ETL pipeline to be idempotent and support backfills?

I would use upserts or merge operations to ensure repeated runs don't create duplicates. For backfills, I'd parameterize the pipeline to process specific date ranges and ensure historical data can be safely reprocessed without affecting current data.

Describe a strategy for handling schema changes in a production ETL pipeline.

See the answer in the app, free

How do you ensure data completeness and correctness when dealing with late-arriving data?

See the answer in the app, free

What are the trade-offs between full and incremental loads in ETL pipelines?

See the answer in the app, free

How would you use dbt to manage both raw and transformed data layers in a warehouse?

See the answer in the app, free

More Data Engineering topics