When a Pipeline Is Not Needed
The shop's data team is 2 people, and in 1 week they received 4 requests. A pipeline for each would leave them maintaining jobs whose readers have moved on, so the first decision on any request is whether a pipeline is the cheapest thing that closes the gaps it shows. Most of what looks like pipeline work is covered by 3 cheaper tools.
A query
A question asked once is answered by a query, run by hand, against a copy of the database rather than the checkout instance. The CEO wants the total number of orders ever placed, for 1 slide in an investor deck. A count over 2 million rows on a replica takes 4 seconds and costs an analyst 10 minutes, and once the slide exists nothing about the answer changes. A pipeline built for it would run every night for a reader who looked once.
A read replica
Postgres streaming replication keeps a second instance a few seconds behind the first, holding the same tables in the same shape. Every analytical read moves to the replica, and the checkout database never sees a scan again. For the shop's data size a replica is 1 more instance, roughly 150 dollars a month, and nothing to build or schedule. It closes the load gap entirely and closes nothing else: the amounts are still text, the test orders still there, and every reader still writes the same cleaning into every query. A developer checking whether order 88213 was charged twice is exactly the replica's reader, and so is any analyst exploring the data before anyone knows what the recurring question will be.
A cache
If the same answer is read many times, it is cheaper to compute it once and keep it. The shop's homepage shows how many orders were placed today, and the page is loaded 4,000 times an hour. A materialized view refreshed every minute, or a Redis key with a 60-second expiry, answers every load from memory in under a millisecond and runs the count 60 times an hour instead of 4,000. A cache closes the load gap for a repeated identical read and can reshape within 1 database, since a materialized view may aggregate. Its cost is staleness equal to its refresh interval, and it cannot move data to a different system or clean it on the way.
When the pipeline wins
Customer support wants each day's refunds by reason in a shared sheet before their 9:00 standup. That recurs on a schedule, the output is a different shape from the refund rows, it crosses from Postgres to a sheet, and a team acts on it every morning. A replica would stop the scan hurting checkout and still leave someone running the query at 8:45. A cache would hold yesterday's answer and still not put it in the sheet. Only the pipeline closes all 3 gaps at once. It also brings code to maintain and a scheduler to run, and someone gets paged when it fails. That ongoing cost is justified because the alternative is a person doing the same work by hand every day.
| Tool | Load gap | Shape gap | Schedule gap | Ongoing cost |
|---|---|---|---|---|
| Query on a replica | Closed | For 1 run only | Open | Minutes of an analyst's time |
| Read replica | Closed | Open | Open | A second instance, seconds of lag |
| Cache or materialized view | Closed | Within 1 database | A refresh timer | Staleness equal to the refresh interval |
| Pipeline | Closed | Closed, across systems | Closed | Code, a scheduler and an on-call owner |
Marketing's request, a list of every customer who bought a kettle in 2023, sits between the rows. It reshapes data, so a replica alone leaves the work to an analyst, and it covers a closed period, so once delivered the answer never changes. A single query on the replica, saved in case the list is asked for again, is the whole solution. The signal that would change the answer is a second request for the same list with a newer window.
Four requests reached the shop's data team in the same week: the CEO's one-time order count for an investor deck, support's daily refunds by reason in a sheet before 9:00, a developer's check on whether order 88213 was charged twice, and marketing's list of 2023 kettle buyers. Which request should become a pipeline first?
- The CEO's order count, because it comes from the top of the company
- Support's daily refunds by reason
- The developer's check on order 88213
- Marketing's list of 2023 kettle buyers
- Do
- Write down which of the 3 gaps a request shows before choosing a tool.
- Point every analytical read at a replica, so the checkout database only ever serves the checkout.
- Answer a question asked once with a query, and save the query in case it is asked again.
- Build a pipeline when the work recurs, reshapes data across systems and feeds a decision.
- Avoid
- Build a pipeline for a one-off question; it will run nightly for nobody.
- Run reporting scans against the production database to save the cost of a replica.
- Treat a cache as a pipeline; it holds 1 answer and cannot move data to another system.
- Let a recurring report live in 1 person's calendar; the first holiday breaks it.
About this lesson
This section is part of the Pipeline Anatomy: 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.