What Happens When a Candidate Cannot Explain Their Pipeline

Interviewer: Your resume says you built an automated
ETL pipeline. Tell me about it.

Candidate: Yes, so the pipeline takes data from the
database, does ETL on it, and then the data goes to
Power BI for the dashboard. It is fully automated.

Interviewer: What triggers it each day?

Candidate: ... it runs automatically.

Interviewer: Tell me about your pipeline.

Candidate: It is a small Python ETL. Every morning the
shop's billing system drops yesterday's orders as a CSV
in a shared folder. Task Scheduler runs my script at 7 am.

It removes duplicate order IDs, standardises city names
and converts dates, then appends the rows to a sales
table in PostgreSQL. Power BI refreshes from that table.

If the file is missing or a check fails, the script
logs the error and stops before loading anything.

import sys
from datetime import date, timedelta
import pandas as pd
from sqlalchemy import create_engine

CITY_MAP = {'Bangalore': 'Bengaluru', 'Gurgaon': 'Gurugram'}

def extract(day):
    return pd.read_csv(f'incoming/orders_{day:%Y%m%d}.csv')

def transform(df):
    df = df.drop_duplicates(subset='order_id', keep='last')
    df['city'] = df['city'].str.strip().replace(CITY_MAP)
    df['order_date'] = pd.to_datetime(df['order_date'], dayfirst=True)
    df['amount'] = df['amount'].astype(float)
    return df

def validate(df):
    if df.empty:
        raise ValueError('No rows after transform')
    if df['order_id'].duplicated().any():
        raise ValueError('Duplicate order_id after dedupe')

def load(df, engine):
    df.to_sql('sales', engine, if_exists='append', index=False)

if __name__ == '__main__':
    day = date.today() - timedelta(days=1)
    engine = create_engine('postgresql+psycopg2://etl_user@localhost/shop')
    data = transform(extract(day))
    validate(data)
    load(data, engine)
    print(f'Loaded {len(data)} rows for {day}')

"The script has four steps.

Extract reads yesterday's file, named by date, so each
run handles exactly one day.

Transform removes duplicate order IDs, keeping the last
version, because the billing system sometimes exports an
order twice. It also fixes city names and types.

Validate stops the run if the data is empty or still
has duplicate IDs, so bad data never reaches the table.

Load appends to the sales table, so history is kept and
Power BI just refreshes from it."

+------------------+     +---------------------+
| Billing system   | --> | incoming/ folder    |
| daily CSV export |     | orders_YYYYMMDD.csv |
+------------------+     +----------+----------+
                                    |
                       Task Scheduler, 7 am
                                    v
                 +-------------------------------+
                 | etl.py                        |
                 | extract -> transform ->       |
                 | validate -> load              |
                 +---------------+---------------+
                                 | append
                                 v
                 +-------------------------------+
                 | PostgreSQL: sales table       |
                 +---------------+---------------+
                                 | scheduled refresh
                                 v
                 +-------------------------------+
                 | Power BI sales dashboard      |
                 +-------------------------------+

If your pipeline was never scheduled, say so:

"In the project I ran the script manually each time I
got a new file, so strictly it was a repeatable ETL
script rather than a scheduled pipeline.

To schedule it, I would use Task Scheduler or cron to
run it daily, add logging to a file, and make the load
safe to rerun by deleting that day's rows first."

This is far stronger than claiming 'fully automated'
and being unable to say how.

PIPELINE: ______________________

1. Source:      ______________________________
   Access via:  file / API / database query
2. Trigger:     manual / cron / Task Scheduler /
                Airflow / Power BI refresh / other
3. Transform:   rule 1 ____  rule 2 ____  rule 3 ____
4. Destination: ______________  append / replace
5. On failure:  log? stop? alert? retry?

If any line is blank, either fix the pipeline
or change how the resume describes it.

VAGUE                         SPECIFIC

Built an automated ETL     -> Built a Python ETL script that
pipeline                      loads daily order CSVs into
                              PostgreSQL, scheduled with
                              Task Scheduler

Automated data processing  -> Automated a Power Query flow
                              combining monthly branch files
                              into one sales table

End-to-end pipeline        -> Extract-transform-load script
                              with validation checks that
                              stop bad loads

PIPELINE DRILL (20 minutes)

1. Draw your pipeline on paper. 2 minutes max.
2. Explain it aloud using the five questions.
3. Answer: what if it runs twice for the same day?
4. Answer: what if the source adds a new column?
5. Answer: what if the file arrives two hours late?

Write one sentence for each 'what if'.
These are the follow-ups you will get.