Why Every Data Engineer Should Practice Explaining Their Pipeline Aloud

from datetime import datetime, timedelta
from airflow import DAG
from airflow.operators.python import PythonOperator

from etl.orders import extract_orders, transform_orders, load_mart

default_args = {"retries": 2, "retry_delay": timedelta(minutes=5)}

with DAG(
    dag_id="daily_orders",
    start_date=datetime(2024, 1, 1),
    schedule="@daily",
    catchup=False,
    default_args=default_args,
) as dag:
    extract = PythonOperator(task_id="extract", python_callable=extract_orders,
                             op_kwargs={"run_date": "{{ ds }}"})
    transform = PythonOperator(task_id="transform", python_callable=transform_orders,
                               op_kwargs={"run_date": "{{ ds }}"})
    load = PythonOperator(task_id="load_mart", python_callable=load_mart,
                          op_kwargs={"run_date": "{{ ds }}"})

    extract >> transform >> load

"It is a daily Airflow DAG with three tasks.

 Extract pulls orders updated on the run date from Postgres
 and writes Parquet to the raw zone, partitioned by date.

 Transform reads that partition, casts types, removes
 duplicates on order_id and applies the refund rules.

 Load rebuilds that date's partition in the fact table.

 Every task takes the run date as a parameter, so I can
 rerun any single day. Each task retries twice, five
 minutes apart, and a failure after that alerts our channel."

# Airflow 2.x CLI: run a week of history for one DAG
airflow dags backfill \
    --start-date 2024-03-01 \
    --end-date 2024-03-07 \
    daily_orders

# Rerun only the failed transform task for one day
airflow tasks clear daily_orders \
    --task-regex transform \
    --start-date 2024-03-05 \
    --end-date 2024-03-05 \
    --yes

# Note: Airflow 3 moved backfills to 'airflow backfill create'.
# Say which version you used in your project.

Q: How did you load the historical data?

A: "I kept catchup off so the DAG would not suddenly
 schedule hundreds of runs when it was first deployed.

 Then I ran a backfill one month at a time, because
 the source was a live Postgres database and I did not
 want to slow it down during business hours.

 Because every task writes only its own date partition,
 I could stop a backfill halfway and resume it later
 without creating duplicates."

-- Run after load; the task fails if any check returns a row

-- 1. Duplicate keys in today's partition
SELECT order_id, COUNT(*) AS copies
FROM mart.fact_orders
WHERE order_date = '2024-03-05'
GROUP BY order_id
HAVING COUNT(*) > 1;

-- 2. Null customer IDs
SELECT COUNT(*) AS null_customers
FROM mart.fact_orders
WHERE order_date = '2024-03-05'
  AND customer_id IS NULL
HAVING COUNT(*) > 0;

-- In the DAG, the date comes from {{ ds }} instead of a literal.

INCIDENT STORY (aim for about 90 seconds)

1. What I noticed
   "The daily dashboard showed revenue roughly double."
2. How I found the cause
   "The duplicate-key check had been added only on staging.
    A manual rerun had appended instead of overwriting."
3. The fix
   "I changed the load to overwrite the date partition
    and reran the affected days."
4. The prevention
   "I moved the uniqueness check to the mart and made it
    block the load task."
5. What I learned
   "Every load must be safe to run twice."

MY PIPELINE IN TWO MINUTES

Business purpose (one line): ______________________
Source system and format: _________________________
Schedule and run date logic: ______________________
Stage 1 in / does / out: __________________________
Stage 2 in / does / out: __________________________
Stage 3 in / does / out: __________________________
Who reads the output: _____________________________
How I know today's run worked: ____________________

QUESTIONS TO ANSWER ALOUD BEFORE THE INTERVIEW

- What happens if the extract task fails at 2 a.m.?
- Can you rerun a single day safely? How?
- How did you load history?
- What if the source adds or renames a column?
- How long does a run take, and what is the slowest step?
- What would you change if volume grew ten times?
- Why this tool and not a simpler one?
- What is one thing you would redesign today?

AFTER RECORDING YOUR EXPLANATION

[ ] Started with business purpose, not tools
[ ] Followed the data from source to output
[ ] Said input, step and output for each stage
[ ] Mentioned run date and rerun safety
[ ] Named at least two quality checks
[ ] Told one incident story with a prevention step
[ ] Finished under three minutes before follow-ups

Re-record until every box is ticked.