ETL pipelines, explained with a coffee run

Somebody in your office copies numbers from one system and pastes them into another. Maybe it's sales figures into a spreadsheet, maybe it's timesheet hours into the invoicing tool. It happens every week, it takes an hour or two, and every so often a number lands in the wrong row and nobody notices until a customer calls. There's a name for the thing that replaces that job: an ETL pipeline.

ETL stands for extract, transform, load. The jargon makes it sound complicated. It isn't. Here's the whole idea, explained with a coffee run.

The coffee run

Say you're the person who does the office coffee run. Every morning the routine is the same.

Extract: you walk around and collect everyone's order. Dave wants a large black coffee, Maria wants an oat milk latte, the new guy wants something with four modifiers. You're pulling raw information out of its source: people's heads.

Transform: the coffee shop doesn't want to hear "the thing Maria always gets." So you rewrite the orders into the shop's language. You group the three drip coffees together, translate "the usual" into "medium roast, room for cream," and put it all in the order the barista expects. Same information, reshaped so the destination can use it.

Load: you hand the finished list to the barista. The order enters their system, drinks come out.

That's ETL. Pull data out of a source, reshape it to match what the destination expects, deliver it. A data pipeline is just software doing the coffee run on a schedule, without getting bored, and without writing "Dabe" instead of "Dave."

What this looks like in a real business

A concrete example we see constantly. A company takes orders in Shopify, does accounting in QuickBooks, and tracks customers in a CRM. The owner wants one report: revenue by customer by month, with costs included.

A pipeline handles it like this:

• Extract: every night, the pipeline pulls yesterday's orders from Shopify, expenses from QuickBooks, and customer records from the CRM, using each system's API. No human logs in and exports anything.
• Transform: it cleans up the mess. Shopify calls a customer "ACME Corp" while the CRM says "Acme Corporation," so the pipeline matches them. Dates get standardized. Refunds get subtracted. Test orders get dropped.
• Load: the clean rows land in one database (or even a shared spreadsheet, for a small operation), and the report reads from there. When the owner opens it Monday morning, the numbers are already current through last night.

Nobody hand-copied anything. That's the entire pitch.

Scheduling: the runs happen without you

Pipelines run on a schedule, like a coffee run that happens at 9am sharp whether or not you remembered. Nightly is the most common cadence for small-business reporting, because most decisions don't need up-to-the-minute data and nightly runs are cheap and simple. Hourly is common for inventory and orders. Real-time is possible but rarely worth the cost for a small company.

Tools that do this range from simple to industrial. Off-the-shelf connectors like Fivetran or Airbyte can sync common systems with almost no code. A custom Python script on a scheduler covers the odd cases. Most small businesses need a mix: connectors for the standard stuff, a small script for the one weird system that has no connector.

Failure alerts: the part people skip

Here's where the coffee analogy earns its keep. Suppose one morning the coffee shop is closed. A good runner comes back and tells everyone "no coffee today, shop's closed." A bad runner silently comes back empty-handed and lets people discover it themselves.

Pipelines fail the same way. Shopify changes its API, a password expires, a server hiccups. The failure itself isn't the problem; every pipeline fails eventually. The problem is silent failure. A pipeline that dies quietly leaves your report showing last Tuesday's numbers, and you make this week's decisions on last week's data without knowing it.

So every pipeline we build gets two safeguards:

• Alerts on failure. If a run breaks, someone gets an email or a Slack message that morning, naming which step failed. Fixing a known failure takes minutes. Discovering an unknown one takes weeks.
• Freshness checks. The report itself shows when the data was last updated. If it says "as of 3 days ago," anyone reading it knows to be suspicious.

How to know it's done right

A pipeline is working when the manual copy-paste job is fully gone, not just reduced. When the numbers in the report match the numbers in the source systems, and someone has actually checked that instead of assuming it. When a failure produces an alert within a day, not a surprise within a month. And when someone other than the person who built it knows how to restart it.

If your business still has a human coffee runner for data, moving orders between systems by hand every week, that job can almost certainly be automated for less than it costs you in staff time each year. We build these regularly and can usually tell you within one conversation how big or small the job would be.