ETL Interview Questions and Answers

ETL changes the data before it loads it into the target. ELT loads the raw data into the target first and changes it there.

ELT became common because warehouses such as Snowflake and BigQuery scale compute on its own. They can run SQL transforms cheaply. Keeping the raw data lets you reprocess it when business rules change.

ETL still fits in two cases. One is when sensitive fields must be masked before they land. The other is when the target cannot do the transformation.

What they are checking: Whether you can explain the shift and the trade-offs

Batch processes a fixed set of data on a schedule, such as hourly or daily. Streaming processes a never-ending flow of events as they arrive.

• Latency: batch takes minutes to hours. Streaming takes seconds or less.
• Complexity: streaming must handle event order, late events, state, and exactly-once results. It is harder to build and run.
• Cost: batch can use cheap spot compute for short windows. Streaming runs all the time.
• Examples: nightly sales reports use batch. Fraud detection and live dashboards use streaming.
• Tools: Airflow with Spark or SQL for batch. Kafka with Flink, Spark Structured Streaming or Kinesis for streaming.

Pick the slowest speed the business can accept. Many systems use micro-batches as a middle path, for example loads every 1 minute.

What they are checking: Whether you choose based on the speed needed, not fashion

A data pipeline is an automatic flow that moves data from sources to a destination. It also prepares the data for use.

Typical stages:

1. Ingest: pull data from databases, APIs, files, or event streams.
2. Land raw: store the data unchanged (in object storage or a raw schema).
3. Clean and transform: fix types, remove duplicates, join tables, apply business rules.
4. Model: build analytics tables such as facts and dimensions.
5. Serve: give the data to BI tools, ML features, or applications.
6. Orchestrate, monitor and test: scheduling, retries, alerts, data quality checks.

A good pipeline is repeatable and idempotent (running it twice gives the same result). It is also easy to watch and well documented.

Give one example. Orders from PostgreSQL land in S3 each day. dbt transforms them, and they load into a warehouse for dashboards.

What they are checking: Whether you see the pipeline from start to end, including operations

A full load reloads the whole dataset each run. An incremental load moves only the new or changed records since the last run.

• Full load: simple and fixes itself, but slow and costly as data grows. Fine for small reference tables.
• Incremental: fast and cheap, but you need a reliable way to detect changes and handle deletes.

Common ways to detect changes:

• An updated_at timestamp.
• An id that always increases.
• A log-based CDC feed (reads the database change log).
• File arrival partitions.

SELECT * FROM orders
WHERE updated_at > :last_watermark
  AND updated_at <= :run_end;

Save the watermark (the last point you loaded) only after the load succeeds. Use a small overlap window, for example read the last 10 minutes again. Use an upsert (update or insert). Then late commits are not missed and duplicates do no harm.

What they are checking: Whether you know watermarks, deletes and overlap

Check at the boundaries: after ingestion and before publishing.

• Completeness: row counts match the source, required columns are not null.
• Uniqueness: primary or business keys have no duplicates.
• Validity: types, formats, allowed values, ranges (age between 0 and 120).
• Referential integrity: every fact foreign key exists in its dimension.
• Freshness: the latest timestamp is inside the expected window.
• Reconciliation: totals match the source system, and sums agree across layers.
• Anomaly checks: sudden drops or spikes in volume compared with recent days.

Tools: dbt tests, Great Expectations, Soda, Deequ.

Decide how serious each failure is. Block the pipeline for critical failures. Only warn for small ones. Put bad rows in quarantine instead of dropping them silently. Send alerts to the owner and keep the results to see trends.

What they are checking: Whether you build quality gates, not just move data

CSV is a plain text format that stores data row by row. It has no schema or types. Parquet is a binary format that stores data column by column. It has a built-in schema, compression and statistics.

Analytics queries touch a few columns of many rows. Columnar reads save I/O, and compression works well on similar values. So Parquet is preferred for analytics. CSV is still common for sharing with vendors and for small files.

What they are checking: Whether you know why columnar formats win for analytics

Plan for it, because source schemas change without warning.

Orchestration schedules tasks and coordinates them with dependencies, retries and monitoring. Cron only starts a command at a set time.

• DAG: a directed acyclic graph. It defines tasks and their dependencies, with a schedule.
• Task: one unit of work in the DAG. A task instance is that task for one run.
• Operator: a template for a type of task (BashOperator, PythonOperator, a SQL or Spark operator). The TaskFlow API lets you write tasks as decorated...

Row stores keep all fields of a record together. Column stores keep all values of one column together.

dbt (data build tool) is the transform step in ELT. You write SELECT statements. dbt builds them as tables or views in the warehouse. It also works out dependencies, runs tests, and creates documentation.

Kafka is a distributed, durable log that only adds new records at the end. It moves event streams between systems.

It organises a lakehouse into layers. Quality goes up with each layer.

Many tiny files (a few KB each) in a data lake slow everything down. Listing and opening each file adds work. Query engines create too many tasks. The metadata grows too big.

Causes: frequent streaming micro-batches, too many partitions, and many small writes.

Lineage shows where data came from and what depends on it. It shows which source tables and jobs made a column, and which dashboards use it. Observability watches the health of the data and the pipelines.

A scheduler starts jobs at fixed times. An event-driven trigger starts them when something happens.

Event time is when the event actually happened. Processing time is when your system handles it.

They differ because of network delays, mobile devices going offline, or retries. A purchase at 23:59 may arrive at 00:03 the next day.

A staging table is a temporary or middle area. Data lands there before it is checked and merged into the final target.

Compare simple measures that would show lost or duplicated data.

A cycle would create a circular dependency. Task A would wait for B, and B would wait for A. Then no task could run first. A directed acyclic graph guarantees a valid run order (a topological order). It also guarantees that every run finishes.

Decide a policy for each field and apply it the same way every time.

An idempotent pipeline gives the same result however many times you run it for the same input. This makes retries and backfills safe.

Techniques:

• Overwrite by partition: delete and reload one date partition (or use INSERT OVERWRITE) instead of appending.
• Use upsert or MERGE on a business key, not a plain INSERT.
• Make outputs predictable: build file names and ids from the input (a hash or batch date), not from now() or random UUIDs.
• Write to a temp location and swap it in at once on success.
• Keep a run log keyed by batch id, so a finished batch is skipped.
• Avoid side effects that cannot be repeated, such as sending emails. Or guard them.

BEGIN;
DELETE FROM fact_sales WHERE sale_date = :run_date;
INSERT INTO fact_sales SELECT * FROM stg_sales WHERE sale_date = :run_date;
COMMIT;

What they are checking: Whether you design for safe reruns, the core DE skill

CDC (Change Data Capture) finds inserts, updates and deletes in a source database. It sends them to other systems as they happen.

Approaches:

• Log-based: read the database transaction log (PostgreSQL WAL, MySQL binlog). It puts little load on the source, captures deletes, and keeps order. Tools: Debezium, AWS DMS.
• Trigger-based: triggers write changes to an audit table. This adds load to the source.
• Timestamp or version column: query updated_at > last_run. It is simple, but it misses hard deletes. It can also miss rows if the timestamps are not reliable.
• Snapshot diff: compare full snapshots. This is heavy.

Log-based CDC is the usual answer for production. Plan for an initial snapshot plus an ongoing stream. Think about order and schema changes. Also decide how deletes appear (a tombstone or an op field). The target applies changes with MERGE on the primary key.

What they are checking: Whether you know log-based CDC and its trade-offs

First separate event time (when it happened) from processing time (when you received it). Then decide how long you will wait.

• Avro: a binary format that stores data row by row, with the schema inside. It handles schema changes well and is compact for writes. Best for streaming (Kafka with a schema registry) and for ingesting one record at a time.
• Parquet: a column-based format with wide support in Spark, Presto/Trino, BigQuery, Snowflake and Athena. Best for analytics on a data lake.
• ORC: a column-based format that began in Hive. It has built-in indexes and ACID support in Hive. Strong in Hive and Hadoop setups.

• At-most-once: a message arrives zero or one times. There are no duplicates, but data can be lost.
• At-least-once: a message arrives one or more times. Nothing is lost, but duplicates are possible after retries.
• Exactly-once: each message changes the result only once.

Partition by the column that queries filter on most, if it has a moderate number of distinct values. Date is the usual choice for event data.

Keep a control table with the last successful high-water mark. Extract rows after it, upsert them, and move the mark forward only on success.

Materializations control how a model is stored:

• view: a saved query, always fresh, no storage.
• table: rebuilt fully on each run.
• incremental: after the first run, only new or changed rows are processed.
• ephemeral: inlined as a CTE, not created in the database.

They are open table formats. They add warehouse-like features on top of Parquet files in object storage.

Separate temporary failures from bad data.

Protect the data at every stage.

Test at several levels.

Both combine historical and real-time processing.

A data contract is an agreement between a data producer and its consumers. It defines the schema, meaning, quality needs, freshness, ownership, and how changes are shared.

First make things stable, then fix the cause, then prevent it from happening again.

1. Assess: read the logs. Find the failed task, the error, and what was already written. Check that the target is in a consistent state for users.
2. Communicate: tell downstream owners that the data is partial, and until when.
3. Fix the cause: bad data, schema change, timeout, credentials, or capacity.
4. Rerun safely: loads should be idempotent, so rerun the failed task and its downstream tasks for the same logical date. Do not rerun the finished ones. In Airflow, clear the failed task instance.
5. Reconcile: compare row counts and key totals between source and target.
6. Prevent: write to staging and swap or commit at once. Add retries with backoff, data quality gates, and alerts on failure and freshness.

If the pipeline is not idempotent, fix that first. Make the affected loads partition-overwrite or MERGE, so a rerun cannot create duplicates.

What they are checking: Whether you can recover calmly using idempotency and reconciliation

Measure the problem, find the source, fix it, then prevent it.

Find the duplicates:

SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

Check the load timestamps or batch ids on the duplicate rows. If the copies came from different batches, a rerun appended instead of overwriting. If they came from the same batch, the source or a join made them. A one-to-many join can multiply rows. The source may also have sent the record twice.

Fix: keep one row per key with ROW_NUMBER on a rule such as the latest loaded_at. Then rewrite the table or partition.

Prevent:

• Make loads idempotent (MERGE on the business key or partition overwrite).
• Add a unique constraint or a dbt unique test on the key.
• Remove duplicates in staging.
• Reconcile counts with the source after each load.

Do not just delete rows without knowing the cause. The duplicates will come back.

What they are checking: Whether you look for the root cause, not only a cleanup query

Treat it as a controlled reprocessing job.

Monitor both the jobs and the data.

Job health: failures, retries, run time compared with normal, queue time, resource use, missed schedules.

Data health: freshness (the latest timestamp is recent), volume (row count in an expected range), schema changes, null rate, duplicates, and reconciliation with the source.

Start from the needs: freshness, volume, history, and users.

Design for limits, retries and restarts.

Do not depend on column position.

Measure first, then cut the biggest items.

Incremental loads based on updated_at never see a hard delete. So you need an explicit approach.

Treat it as a performance regression and find where the time went.

I compare run-time limits, data volume, cost model and effort to operate.