ETL engineer role-specific 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

Product engineering teams keep changing event payloads and breaking analytics and ML pipelines. How would you introduce data contracts?

Sample answer outline

Start with the highest-value event streams and define owners, schemas, compatibility rules, and review paths. Put validation close to the producer so bad events are caught before they enter the lake, while still storing rejected samples for debugging. Use schema registry or contract tests in CI, and make breaking changes explicit with versioning and deprecation windows. The cultural part matters: producers need to understand which downstream decisions depend on their data. Candidates trip up when they propose contracts as a data-team document rather than an enforceable engineering interface.

Expect these follow-ups

• How do you handle a producer that needs to ship a breaking change urgently?
• Where should validation run: client, service, stream, or warehouse?
• How do you stop contracts becoming stale?

As asked

Explain how the Airflow scheduler decides when to trigger a DAG run. What is the relationship between schedule_interval, logical date, and data_interval_start, and why does a DAG with schedule_interval of daily not run at midnight on the day you think it will?

Sample answer outline

Strong answers explain that Airflow's scheduler triggers a run for interval [T, T+1] only after T+1 has passed, so the first run of the day arrives after the day ends. Candidates should clarify the shift from execution_date to data_interval_start in Airflow 2.x and explain how catchup interacts with start_date to produce backfill runs.

Expect these follow-ups

• What happens if the scheduler process is down for 48 hours and then restarts with catchup=True?

As asked

A colleague stores a 500 MB Pandas DataFrame in an Airflow XCom so the next task can use it. What will actually happen, and how would you fix this design?

Sample answer outline

XCom is backed by the Airflow metadata database and is not meant for bulk data. Storing large objects will bloat the metadata DB and cause slow serialization. With a Postgres backend, Airflow does not enforce a hard byte limit on XCom values by default, so large payloads silently degrade scheduler performance rather than failing fast. The correct fix is to write the intermediate result to S3, GCS, or a staging table and pass only the path or identifier through XCom. In Airflow 2.7 and later, a custom XCom backend can be configured to offload large values to object storage automatically.

Expect these follow-ups

• How does configuring a custom XCom backend in Airflow 2.7 change how large payloads are stored and retrieved?

As asked

Walk me through the incremental strategies available in dbt: append, merge, delete+insert, and insert_overwrite. Which one would you use for a fact table that receives late-arriving rows for the previous 7 days, and what config would you write?

Sample answer outline

Merge is the right choice here because it can update or insert rows based on a unique key. The candidate should show the unique_key and incremental_strategy config, explain how the is_incremental() macro gates the WHERE clause, and discuss writing a manual date filter inside the is_incremental() block (for example, WHERE event_date >= dateadd(day, -7, current_date)) to catch late arrivals. They should also mention that on BigQuery, insert_overwrite on a date partition is often more efficient than row-level merge.

Expect these follow-ups

• What happens on the very first run of a dbt incremental model?
• How would you force a full refresh in production without modifying the model?

As asked

You have a transformation that joins a 200 GB event log with a 5 GB lookup table and then does a window function to compute rolling 30-day sums per user. Would you run this in PySpark or push it to Snowflake SQL? Justify your answer.

Sample answer outline

For data already in Snowflake, pushing the computation to Snowflake SQL avoids unnecessary data movement and takes advantage of columnar storage, automatic clustering, and parallel query execution. PySpark is preferable when the data lives in a data lake, when custom Python logic is needed between transformations, or when the warehouse compute cost is prohibitive. Candidates should mention broadcast joins in Spark for the small lookup table and partitionBy for the window function.

Expect these follow-ups

• How would your answer change if the event log were in S3 and you had no warehouse license?

As asked

Your ETL job writes 10 million rows to a Postgres target table in batches of 100k. On batch 7 the job crashes. When it restarts, what strategy ensures you do not end up with duplicate or missing rows, and how do you implement it without a distributed transaction manager?

Sample answer outline

The cleanest approach is to write to a staging table with a job_run_id column, truncate and reload the staging table on retry, then do a single atomic MERGE or INSERT INTO ... SELECT from staging into the target at the end. This avoids partial state in the final table entirely. Alternatively, checkpointing the last successful batch_id in a control table lets the job skip already-written batches, but requires the target to deduplicate on a natural key.

Expect these follow-ups

• How do you handle this if the target is a BigQuery partitioned table with no MERGE support in your version?

As asked

You receive a daily file with 50 million JSON records, each with a nested array of events inside a nested object of user attributes. You need to flatten this into rows in a warehouse table. What is your approach in PySpark, and what are the gotchas with schema inference on this data?

Sample answer outline

Use spark.read.json with a pre-defined StructType schema rather than inferring, because inferring requires a full pass over the data and can produce wrong types if early rows have nulls. Use explode() on the events array, then select with dot notation or getField() for nested attributes. Candidates should mention handling schema drift by catching unexpected fields with PERMISSIVE mode and storing corrupt records in a _corrupt_record column.

Expect these follow-ups

• How do you handle a new field appearing in the JSON that is not in your defined schema?

As asked

How do you manage database passwords and API keys in an ETL system that runs on Airflow, and what do you do to ensure a credential rotation does not break running DAGs?

Sample answer outline

Credentials should be stored in a secrets backend like Vault, AWS Secrets Manager, or Airflow's native Connections encrypted with the Fernet key, never in environment variables checked into version control. For rotation, the pattern is to update the secret in the backend and have the pipeline fetch it at runtime rather than at import time, so restarting tasks picks up the new value. Candidates should mention IAM roles or service accounts as the preferred alternative to static credentials.

Expect these follow-ups

• How do you audit which DAGs accessed a given credential in the last 30 days?

As asked

A source system quietly added three new columns and renamed one existing column. Your ETL job is now failing in production. Walk me through a defensive architecture that detects and handles schema drift before it breaks the pipeline.

Sample answer outline

Strong answers describe schema validation at the extraction layer using something like Avro, Protobuf, or Great Expectations to catch unexpected changes before data hits the staging table. On detection of a new column, the pipeline should log and optionally add the column to the target schema automatically. Renamed columns should fail loudly because they indicate lost data. Candidates should also discuss schema registry for event streams and using a contract-first approach with source teams.

Expect these follow-ups

• What do you do if the source team says they cannot give you advance notice of schema changes?

As asked

Describe the specific metrics and alerts you would set up to monitor a production ETL pipeline that loads 20 tables daily into a data warehouse. What thresholds would you use and what tool would you route them through?

Sample answer outline

Key metrics include row count delta per table run (flag if count drops more than N% from the 7-day average), null rate on critical columns, freshness lag (time since last successful load), pipeline duration (alert if 2x the p95 baseline), and error rate. Tools include Datadog, Prometheus, or dbt's source freshness command. Candidates should distinguish between SLO-breaking alerts and informational warnings and mention the importance of runbooks.

Expect these follow-ups

• How do you differentiate between a legitimately quiet day with low row counts and a pipeline failure?

As asked

Your Kafka topic delivers at-least-once messages and your downstream warehouse table must have exactly one row per event_id. Describe a deduplication strategy that works at scale without reading the entire target table on each micro-batch.

Sample answer outline

Use a Bloom filter or a fast key-value store like Redis to track seen event_ids within a time window that covers the expected duplication lag. For warehouse inserts, use MERGE with event_id as the match key. In Spark Structured Streaming, use dropDuplicates with a watermark on event_time so the dedup state does not grow unbounded. Candidates should discuss the tradeoff between state size and the window over which duplicates can arrive.

Expect these follow-ups

• How long does your dedup window need to be, and how do you determine that from the source team?

As asked

A data analyst discovers a metric in the dashboard is wrong. How do you trace backward from the dashboard number through the warehouse to the raw source to find where the error was introduced? What tooling or practices make this investigation faster?

Sample answer outline

dbt generates a lineage graph from model definitions that shows exactly which source tables each model depends on. OpenLineage or Marquez can capture runtime lineage including the actual jobs that produced each dataset. Without tooling, candidates describe manually tracing through dbt docs or pipeline definitions, checking audit columns like etl_inserted_at to narrow the time window, and diffing row counts at each stage. The key insight is that instrumenting pipelines from the start prevents hours of manual forensics.

Expect these follow-ups

• How do you track lineage across different tools, for example from an Airflow DAG into a dbt model into a Looker view?

As asked

Your team's Snowflake bill tripled last quarter because a new set of hourly dbt models runs long transformation queries on the XL warehouse. What specific changes would you investigate first to reduce cost without degrading freshness SLAs?

Sample answer outline

First check if models are materializing as tables when views or incremental models would suffice, because full table rebuilds are the most expensive pattern. Then audit the warehouse size: many short queries on XL are wasteful; right-size with a multi-cluster Medium. Look for models that scan the same base table multiple times and consolidate them. Enable result caching and query acceleration if models read the same data repeatedly. Suspend the warehouse after idle time.

Expect these follow-ups

• How do you measure the credit consumption of an individual dbt model run?

As asked

Describe how you test an ETL pipeline end to end. What types of tests do you write, what do you use for test data, and how do you avoid running tests against production databases?

Sample answer outline

A layered approach includes unit tests for transformation logic using small in-memory DataFrames (pytest plus pandas or pyspark local mode), integration tests against a containerized Postgres or a Snowflake sandbox schema with synthetic data, and dbt schema tests for data contracts. Candidates should mention using Docker Compose to spin up source and target databases locally, parameterizing connection strings via environment variables, and mocking external API calls. Contract tests with a schema registry prevent production breakage from source changes.

Expect these follow-ups

• How do you generate realistic synthetic test data for a 30-column transaction table without using production data?

As asked

You have a new ETL pipeline that needs to load 3 years of historical data into a warehouse before going live. The source is still active. How do you structure the backfill to avoid overloading the source, ensure the historical load does not interfere with daily incremental loads, and validate completeness?

Sample answer outline

Run the backfill in date-partitioned chunks, off-peak hours, with a configurable concurrency limit to avoid saturating the source. Load into a separate staging schema or partition and only swap it into production tables after validation. Pause or queue the incremental pipeline during the backfill window or give it higher priority if both must run. Validate by comparing row counts and checksums against source aggregates and by running reconciliation queries after load.

Expect these follow-ups

• What do you do if the backfill reveals that source data quality for 2021 is significantly worse than for 2023?

As asked

Your ETL pipeline ingests customer records that include email addresses and credit card last-four digits. What specific controls do you put in place at the extraction, transformation, and load stages to meet GDPR and your company's data classification policy?

Sample answer outline

At extraction, minimize what you pull: only take columns needed downstream. At transformation, hash or pseudonymize identifiers using a keyed HMAC so that analysis can still join on the hash without exposing the raw value. At load, enforce column-level encryption or masking policies in the warehouse (Snowflake dynamic data masking, for example), restrict access by role, and log all access. The pipeline itself should never write PII to unencrypted logs or XCom. Candidates should also mention data retention policies and the right-to-erasure challenge.

Expect these follow-ups

• A GDPR erasure request comes in for 500 users. How does your pipeline handle deleting their data from a warehouse that uses immutable partitions?

As asked

Dagster has both the Software-Defined Asset model and the older op-and-job model. Explain the conceptual difference and describe a real ETL scenario where you would prefer one over the other.

Sample answer outline

Software-Defined Assets model data artifacts and make lineage first-class; they are a better fit when the team cares about asset freshness, cross-pipeline dependencies, and a data catalog. Ops and jobs are better for procedural workflows that do not produce persistent artifacts, like sending an email or calling an API. Candidates should mention that assets support materialization policies and partition definitions that simplify incremental loads.

Expect these follow-ups

• How does Dagster's asset materialization event differ from an Airflow task's success state?

As asked

A PySpark ETL job joining two large datasets is spending 70% of its time on shuffle. What specific configuration parameters and design changes would you try, and in what order?

Sample answer outline

Start by checking if one side is small enough for a broadcast join (spark.sql.autoBroadcastJoinThreshold). If not, check partition count: too few partitions means large tasks and spill, too many means scheduler overhead. Tune spark.sql.shuffle.partitions based on data size. Enable AQE (spark.sql.adaptive.enabled) in Spark 3.x to let Spark coalesce partitions at runtime. Also check for data skew: a small number of join keys producing huge partitions needs salting.

Expect these follow-ups

• How do you detect data skew in a Spark job and what does salting actually do?

As asked

You have a DAG where 5 extraction tasks run in parallel, then a staging task runs only if at least 3 of the 5 succeed, and then a final load runs. How do you model this in Airflow without writing a custom sensor?

Sample answer outline

This requires a trigger rule. The staging task uses trigger_rule=TriggerRule.NONE_FAILED_MIN_ONE_SUCCESS (available since Airflow 2.2), which proceeds if at least one upstream task succeeded and none failed outright. To enforce exactly 3 of 5, candidates might use a BranchPythonOperator after the parallel group that inspects XCom results and skips or proceeds based on the count of successes. They should also mention that setting trigger_rule on downstream tasks to ALL_DONE or NONE_FAILED avoids the default ALL_SUCCESS blocking behavior when some upstreams are skipped.

Expect these follow-ups

• What happens to the final load task if you leave its trigger_rule at the default ALL_SUCCESS and one extraction task fails?

As asked

Every morning at 8 AM your Airflow task queue backs up because 200 tasks all become ready at once and you only have 10 workers. What are your options for handling this burst, and what are the tradeoffs of each?

Sample answer outline

Options include: adding more persistent workers (simple but costly when idle), using the KubernetesExecutor or CeleryExecutor with autoscaling to spin up pods on demand (complex but cost-efficient), adding priority queues to ensure critical tasks run first, and redesigning DAGs to stagger start times or reduce fanout at a single hour. Candidates should understand the difference between the LocalExecutor, CeleryExecutor, and KubernetesExecutor and when each is appropriate.

Expect these follow-ups

• How does the KubernetesExecutor handle task startup latency, and is that a problem for short 10-second tasks?