How Did You Diagnose and Fix a Slow ETL Pipeline in Production?

Tell Me About a Time You Encountered a Performance Problem in an ETL Process. How Did You Troubleshoot and Fix It?

This is a staple behavioral question for data engineering interviews. It tests your diagnostic thinking, your technical depth, and your ability to communicate a complex investigation clearly. The best answers are specific, follow a logical thread, and show impact at the end.

Why This Question Gets Asked

ETL slowdowns are one of the most common real-world problems on data engineering teams. Whether it’s a nightly batch job creeping past its SLA or a real-time pipeline falling behind, performance issues cost time, erode stakeholder trust, and can block downstream reporting entirely. Interviewers want to know you’ve been in the weeds on this and can handle it without hand-holding.

STAR Answer Example

Situation

I was working at a logistics company where we had a critical nightly ETL job that aggregated shipment data from five regional databases and loaded it into our Snowflake data warehouse. The job ran every night at 11 PM and was expected to finish by 2 AM so that the operations team had fresh dashboards first thing in the morning. Over six weeks, the runtime had crept from 2.5 hours to just over 6 hours. The operations team started escalating because their 7 AM reports were delayed, and the business was making decisions on stale data.

Task

My manager asked me to investigate and fix the issue without touching the underlying source databases, as those were owned by a separate infrastructure team and any changes needed to go through a lengthy change request process. I had full access to the transformation logic, the Snowflake environment, and our pipeline orchestration layer, which ran on Apache Airflow.

Action

My first step was to pull the Airflow task logs and identify which stage of the pipeline was expanding. The extraction steps were stable. The slowdown was entirely in the transformation phase — specifically, one dbt model that joined three large tables was taking nearly 90 minutes on its own, compared to 25 minutes when I first benchmarked it.

I ran an EXPLAIN ANALYZE on that query in Snowflake and spotted two problems immediately. First, the join predicate was doing an implicit type cast on a date column, which was preventing partition pruning. Every query was doing a full table scan on a table with nearly 400 million rows. Second, we had added a new column to the source table two weeks earlier, and the SELECT * in the transformation code was now pulling in 12 additional columns that nothing downstream actually used, adding unnecessary I/O.

I fixed the type cast by aligning the data types at the source — I worked with a script to standardize the column in the transformation layer rather than modifying the source. I also rewrote the SELECT * to explicitly name the columns we needed, reducing the data volume moving through the join by about 35%.

Then I looked at the broader pipeline structure. Several transformation steps were running sequentially when they had no interdependency. I restructured the Airflow DAG to run eight of those tasks in parallel, using Snowflake’s multi-cluster warehouse to absorb the concurrent load. I also added a materialized table for a date dimension lookup that was being recomputed on every run, even though it only changed weekly.

I set up query profiling in Snowflake’s Query History dashboard and added a few dbt test assertions to catch unexpected row count drift, which is often an early signal of a data quality issue causing slow scans.

Result

After deploying these changes, the total runtime dropped from 6.2 hours to 1 hour and 48 minutes — well inside the original SLA. Snowflake compute costs for that warehouse dropped by roughly 28% in the first billing cycle, which my manager flagged as a win in a team review. The operations team noticed immediately. Within two days of the fix, I received a message from the operations lead saying the dashboards were loading faster than they’d seen in months.

I also added a Slack alert that fires if any individual Airflow task exceeds a threshold that’s 120% of its rolling 14-day average runtime, giving us an early warning system before another creep becomes a crisis.

The Diagnostic Approach That Works

When you’re troubleshooting ETL performance, work through these layers in order:

Start with observability. Before touching any code, look at logs, query plans, and execution history. You need to know where the problem lives before you fix anything.
Separate extraction from transformation from load. Each stage has different failure modes. Knowing which one is slow narrows the problem space significantly.
Check for cardinality issues and missing partition pruning first. These are the most common causes of unexpected slowdowns in SQL-based transformations.
Look at what changed recently. Schema changes, new columns, added rows, or modified join conditions are frequent culprits.
Parallelize where safe. Many sequential pipelines were built that way out of habit, not necessity. Review your DAG dependencies carefully.

Key Takeaway for Your Interview

The best answers to this question are investigative, not reactive. Show that you worked through a logical process, used the right observability tools, and understood why the fix worked — not just that you randomly tried things until it got faster. Quantify the result wherever you can. A runtime drop of 65% and a 28% cost reduction are the kinds of numbers that stick in an interviewer’s memory.