Script vs Operable Pipeline
Most pipelines begin as a script written by a single engineer and understood by the same engineer. This one reads yesterday's orders, sums revenue by region, writes the totals to a warehouse table and prints a line at the end. On its author's server it has worked every night for months. The first night it fails, the people who notice are the ones reading the revenue numbers.
import datetime
import db
import warehouse
def main():
day = datetime.date.today() - datetime.timedelta(days=1)
rows = db.query("SELECT region, amount FROM orders WHERE order_date = %s", day)
totals = {}
for row in rows:
totals[row["region"]] = totals.get(row["region"], 0) + row["amount"]
warehouse.insert("daily_revenue", day, totals)
print("done")
main()
What the script leaves unanswered
- print("done") writes to a terminal nobody watches. Seen from anywhere else, a failed night and a good night look the same.
- date.today() ties the processed day to the clock, so once Monday has passed there is no way to ask for Monday again.
- warehouse.insert appends. Two runs for the same day write that day's totals twice, the duplicate-row bug from Idempotent Reruns.
- Nothing records the row count or the run time, so nobody can say whether a night's 312,880 orders is normal or a warning.
orders.py fails at 02:00 on Tuesday. The Wednesday 02:00 run succeeds. At 09:00 on Wednesday an analyst spots a gap and runs the script once more by hand. What does daily_revenue now hold for Monday and Tuesday?
- Monday and Tuesday once each, because the hand run filled in the day that failed to load.
- Monday is missing and Tuesday appears twice, because both Wednesday runs processed Tuesday and appended.
- Monday once and Tuesday missing, because the hand run went back and processed Monday's orders.
- Tuesday once and Monday missing, because the second insert overwrote the totals from the first.
Operable means a stranger can run it
An operable pipeline can be run as a service by people who did not write it. Without opening the code, a stranger can find out whether last night's run happened, whether its output can be trusted, who must hear when it cannot, and what to do next. Each of those 4 answers is work the script never did.
- Working script
- Started by cron on a single server
- Processes whatever day the clock says
- Appends on every run
- Reports success to a terminal
- Breakage found by the people reading dashboards
- Operable pipeline
- Scheduled and retried by an orchestrator
- Takes the date to process as an input
- Replaces the day's rows, so reruns are safe
- Sends its logs and metrics somewhere searchable, along with the status of each run
- Monitors watch the output and alerts reach an owner, with a runbook to follow
The rest of this lesson works on orders_daily, the same job rebuilt as a DAG under an orchestrator. Its facts stay fixed in every section:
| Property | Value |
|---|---|
| Pipeline | `orders_daily` |
| Schedule | Daily at 02:00 UTC, processing the previous day |
| Tasks | `extract_orders`, `transform_orders`, `load_revenue` |
| Source | Orders database, read through a replica |
| Output | `daily_revenue`, a row per region per day |
| Normal runtime | About 40 minutes, done by 02:40 |
| Normal volume | 1.0 to 1.4 million orders a day |
| Freshness promise | Yesterday's rows present by 06:00 UTC |
| Consumer | Finance dashboard, opened at 08:00 UTC |
Which change to orders.py lets a stranger recover Monday's revenue from that incident without making anything worse?
- Wrap db.query in a retry loop, so a failed extract is attempted 3 times before the script exits.
- Print the row count next to done, so every run reports how many orders it loaded.
- Move the cron entry to a larger server, so the script has more memory and fails less often.
- Take the date as an argument and replace that date's rows on write, so Monday can be rerun safely.
About this lesson
This section is part of the Pipeline Operations: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.