Why I Always Ask About Failed Pipelines, Not Just Wins

Interview Insights · Data Analytics

Why I Always Ask About Failed Pipelines, Not Just Wins

SECTION 01Why a broken pipeline is the better interview story

When candidates describe a pipeline that works, they all sound similar: extract, transform, load, dashboard. When they describe one that broke, they sound different, because the details are specific to their system. The failure story tells me what the pipeline was really like to run, not just to build.

It also tests an attitude I care about. Data teams depend on pipelines that are trusted. Trust is not built by pipelines that never fail, but by people who notice failures quickly, fix the cause rather than the symptom and add a safeguard so the same thing does not happen quietly again. When you describe that cycle from your own experience, even from a college or training project that ran for a few weeks, you sound like someone who has operated something real.

Interviewer: Tell me about a time your pipeline failed.

Candidate: One time the script gave an error because
of a column problem. I checked the code, fixed the
column name and ran it again. After that it was fine.

(Which column? Why did it change? What stops it
from happening again? Nothing is clear.)

Interviewer: Tell me about a time your pipeline failed.

Candidate: The daily sales load ran twice one morning,
because I restarted it after a network error. My script
appended rows, so that day's sales doubled. The store
manager noticed the spike in the dashboard.

I deleted the duplicate rows, then changed the load to
delete that date's rows before inserting, inside one
transaction. Now a rerun gives the same result as one run.
I also added a check comparing the row count with the
source file.

The strong story has a clear chain: cause (a restart), mechanism (append), symptom (doubled sales), detection (a user), fix (cleanup) and prevention (idempotent load plus a count check). You can tell a story like that in under a minute, and every part of it invites a good follow-up.

SECTION 02Rerun-safe loads: the most common failure and its fix

The single most common failure in beginner pipelines is the double load. The job runs twice for the same day, because of a retry, a manual restart or an overlapping schedule, and a plain append inserts the same rows again. Every number for that day doubles, and nobody may notice until someone reads the dashboard carefully.

The fix has a name worth using in interviews: making the load idempotent, which means running it once or many times gives the same final result. There are two simple patterns. Delete-insert removes the rows for the date being loaded and inserts them fresh, inside one transaction. Upsert inserts new rows and updates existing ones based on a unique key. Both are standard SQL, and being able to write either one puts you ahead of most freshers.

-- PostgreSQL: reload one day safely.
-- Both statements succeed or neither does.
BEGIN;

DELETE FROM sales
WHERE order_date = DATE '2026-11-10';

INSERT INTO sales (order_id, order_date, city, amount)
SELECT order_id, order_date, city, amount
FROM staging_sales
WHERE order_date = DATE '2026-11-10';

COMMIT;

-- PostgreSQL: insert new orders, update changed ones.
-- Requires a unique constraint on order_id.
ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (order_id);

INSERT INTO orders (order_id, status, amount, updated_at)
SELECT order_id, status, amount, updated_at
FROM staging_orders
ON CONFLICT (order_id) DO UPDATE
SET status     = EXCLUDED.status,
    amount     = EXCLUDED.amount,
    updated_at = EXCLUDED.updated_at
WHERE orders.updated_at < EXCLUDED.updated_at;

The WHERE clause on the upsert is a small, useful detail: it only overwrites a row if the incoming version is newer. That protects you from an old file being reloaded by mistake and undoing more recent changes. In a real pipeline the date would be a parameter rather than typed into the query.

SECTION 03Silent failures, schema changes and late files

Crashes are annoying but honest. The script stops, an error appears, and someone fixes it. Silent failures are worse. The job finishes with success, but it loaded zero rows because the file was empty, or it read last week's file again, or a renamed column was filled with blanks. The dashboard keeps showing yesterday's numbers and nobody knows.

Two other failures are common enough to prepare for. A schema change happens when the source adds, removes or renames a column, and your script either crashes or quietly reads the wrong field. A late file arrives after your scheduled run, so the run processes nothing, or processes a partial file. For each, the safeguard is a check that turns a silent problem into a loud one before the load happens.

import os
import pandas as pd

EXPECTED = ['order_id', 'order_date', 'city', 'amount']

def check_file(path):
    if not os.path.exists(path):
        raise FileNotFoundError(f'Source file not found: {path}')
    if not os.path.exists(path + '.done'):
        raise RuntimeError('File not marked complete yet, retry later')

def check_schema(df):
    missing = set(EXPECTED) - set(df.columns)
    extra = set(df.columns) - set(EXPECTED)
    if missing:
        raise ValueError(f'Missing columns: {sorted(missing)}')
    if extra:
        print(f'WARNING: new columns ignored: {sorted(extra)}')
    return df[EXPECTED]

def check_rows(df, day):
    if len(df) == 0:
        raise ValueError('Zero rows: refusing to load')
    dates = pd.to_datetime(df['order_date']).dt.date.unique()
    if list(dates) != [day]:
        raise ValueError(f'File contains dates {dates}, expected {day}')

"The worst failure was a quiet one. The source system
renamed 'amount' to 'net_amount'. My script used
df.get('amount'), so it did not crash, it just loaded
empty amounts. Revenue in the dashboard dropped to
almost nothing, and a user asked if sales had stopped.

I fixed the mapping and reloaded the affected days.
Then I added a schema check that compares columns to
an expected list and fails if any are missing, plus a
zero-row check. Since then, source changes stop the
run with a clear message instead of loading bad data."

Notice that the story admits a design mistake: using a method that hid the missing column. Owning a design choice like that is more convincing than blaming the source team, and it explains exactly why the new check exists.

In Uncodemy mock interviews, a follow-up we like is: 'How would you know if your pipeline loaded nothing yesterday?' A candidate with a row count check answers in one sentence.

SECTION 04Prepare your pipeline incident story

Even a short-lived training or college pipeline usually has at least one incident worth telling. Use these tabs to find it, structure it and get ready for follow-ups.

Think back. Did any of these ever happen?

[ ] A run happened twice and numbers doubled
[ ] The script crashed after the source changed
[ ] The dashboard showed old data for a while
[ ] A file arrived late or half-written
[ ] Dates or time zones shifted numbers by a day
[ ] A join suddenly produced many more rows
[ ] Someone asked 'why does this number look wrong?'

Pick the one where you remember the cause best.

PIPELINE INCIDENT (under 60 seconds)

Symptom:    "One day, ____ looked wrong: ____."
Detection:  "It was noticed by ____ when ____."
Root cause: "The cause was ____, because my
             pipeline ____."
Fix:        "I cleaned up by ____."
Safeguard:  "Then I added ____, so now ____."

Check: does the safeguard prevent the cause,
or only hide the symptom?

Prepare a short answer to each:

- How long was the bad data visible?
- Who did you tell, and how?
- What would have caught it earlier?
- Is your load idempotent now? How?
- What happens if the source changes again?
- What other failure is still possible?

The last one matters: no pipeline is perfect.
Naming a remaining risk shows maturity.

SECTION 05Habits that make your incident story credible

How you frame a pipeline failure decides whether it sounds like experience or carelessness.

• Start with the symptom — Open with what users saw, such as doubled sales or a stale dashboard, before the technical cause. It shows you think about impact first.
• Own your design choice — If an append or a lenient column lookup caused the problem, say so plainly. Owning design mistakes is a sign of seniority.
• Separate fix from prevention — Cleaning up bad data is the fix. The new check or design change is the prevention. Always mention both.
• Use correct terms — Words like idempotent, upsert, schema check and row count reconciliation are worth using, as long as you explain them in your own example.
• Name a remaining risk — End by saying what could still go wrong. It shows you understand pipelines are managed, not finished.

SECTION 06Interview Q&A — Failed pipelines

SECTION 07Test yourself — Failed pipelines quiz

SECTION 08Frequently asked questions

SECTION 09Related reads