Data pipeline design is the set of choices that decide how raw data from your sources becomes tables people can trust. These choices include the order of the steps, how often jobs run, what happens when one fails, and how you check the output. Get them right and the pipeline fades into the background. Get them wrong and one morning your dashboard says sales tripled overnight. They didn't. Your pipeline did that.
This guide walks through the ideas that separate a pipeline you babysit from one you can forget about on a Friday afternoon. It covers ELT versus ETL, batch versus streaming, orchestrators, idempotency, bronze/silver/gold layers, and the tests, contracts and lineage that keep everything honest. Along the way we'll take apart exactly how that 'tripled' sales number happens, so you can make sure it never happens to you.
- Bad pipelines rarely crash. They quietly produce confident, wrong numbers.
- Load raw data first (ELT) so you can always rebuild when logic changes.
- Default to batch. Move to streaming only when a real decision needs it.
- Make every step idempotent so reruns never double your revenue.
- Layer your data and guard it with tests, contracts and lineage.
- 0:00Intro
- 0:31When the dashboard lies
- 1:29A pipeline is just plumbing
- 2:29Load first, transform later
- 3:29How fresh does it really need?
- 4:19The conductor for your jobs
- 5:20Make every step idempotent
- 6:22Rerunning October 2nd, safely
- 7:28Bronze, silver, gold
- 8:34Tests, contracts, and lineage
- 9:36Trust is a feature
- 10:14How sales 'tripled' overnight
- 11:20Shortcuts that bite later
- 12:21Plan for reruns and change
- 13:25Six ideas, one reliable pipeline
- 13:56Design for reruns, and sleep fine
What is a data pipeline, and why do they go wrong?
A data pipeline is plumbing for your company's information. Sources go in one end: your production database, payment provider, ad platforms and app event logs. Clean tables come out the other end, ready for analysts, dashboards and machine learning models. In between, rows get pulled from each source, landed in storage, cleaned and joined, then served to whoever needs them. Each stage hands off to the next, like pipes connecting rooms in a house.
The problem is leaks, and in data they make no noise. A row goes missing, a row shows up twice, or a column quietly changes type. A broken pipeline rarely throws a big red error. It usually runs fine, finishes on time, and hands you a beautifully formatted number that happens to be completely wrong.
That is why wrong is worse than missing. A blank dashboard gets noticed before lunch. A wrong one gets believed, acted on, and discovered only after budgets have moved. Trust is also slow to come back. After one fake spike, the sales team starts keeping its own spreadsheet, and now two versions of the truth argue in every meeting. The good news is that most of these failures come from design choices, not bad luck.
- Dropped rows: data that silently never arrives
- Duplicates: the same record counted more than once
- Silent type changes: a column that quietly means something else
ETL vs ELT: why load first and transform later?
The first big design decision is the order of the steps. The classic approach was ETL: extract, transform, load. The cleanup happened on a separate server before anything reached the warehouse. If you transformed something wrong, the original raw data was often gone, and your chance to fix it went with it.
Most modern teams have flipped this to ELT: extract, load, transform. The reason is economics. When storage and compute were expensive, you couldn't afford to keep messy data lying around. Cheap, scalable cloud warehouses removed that limit. Now you load data almost exactly as it arrived, then transform it, usually with SQL running inside the warehouse where the compute already lives.
The payoff is a safety net. Find a bug in your revenue logic months later? Fix the SQL and rebuild from the raw copy you kept. With ELT, the raw data works like an undo button, and you didn't have to plan for the mistake to be able to recover from it.
- ETL: transform before loading, raw details often discarded
- ELT: keep the raw copy, rebuild whenever logic changes or bugs appear
Batch or streaming: how fresh does your data need to be?
Every team argues about this one. The honest answer depends on a single question: how fresh does the data actually need to be? That is different from how fresh it would feel cool to have. Ask who uses the data and how fast they really act on it. A finance report reviewed every Monday morning gains nothing from second-by-second updates. A fraud check absolutely does.
Batch runs on a schedule, so it lags hours or a day behind. It's simpler to build and debug, and cheaper, because it runs and then stops. Streaming handles events within seconds, but it has more moving parts and stays on all the time, which costs more. Use batch for reports. Save streaming for fraud detection and live alerts.
A good rule of thumb: default to batch, and move to streaming only when someone can name a decision that loses money by waiting. Streaming is powerful, but it's also a pager that never sleeps.
- Batch: hours behind, simple, cheaper, best for reports
- Streaming: near real time, more complex, always on, best for fraud and live alerts
What does a data orchestrator actually do?
Once you have jobs that extract, load and transform, something has to decide what runs when. That's the orchestrator, a tool like Airflow or Dagster. Without one, you end up with a pile of scheduled scripts that each assume the others finished. Usually they did. Occasionally they didn't, and the transform runs happily on half-loaded data.
The core idea fits in a few lines. You declare dependencies: load runs after extract, transform runs after load, and the dashboard only gets published once the data tests pass. The orchestrator turns that graph into a plan and runs it.
The big upgrade is replacing clock-based guessing with real dependencies. Instead of scheduling the transform at 3 a.m. and hoping the load finished, you tell it to start exactly when its inputs are ready. You also get automatic retries, alerts when something fails, and one screen that shows what ran and what didn't.
# a tiny Airflow-style pipeline
extract >> load >> transform
transform >> [test_orders, test_revenue]
[test_orders, test_revenue] >> publishWhat is idempotency, and why does every step need it?
Retries come with a catch. If the orchestrator reruns a job, that job had better be safe to run twice. That property is called idempotency, and it means the result is the same no matter how many times you run it. Think of a light switch set to on. Flipping it to on again doesn't make the room twice as bright.
This is the single most important rule in data pipeline design. Jobs get rerun constantly, after a failure, a late file or a bug fix. Rerunning yesterday's job must never double yesterday's revenue. There are two common ways to get there.
Take a concrete case: yesterday's job failed halfway, and you rerun it for October 2nd. The source has 1,200 orders. The idempotent job deletes the October 2nd partition down to zero rows, inserts 1,200 fresh rows, confirms zero duplicate order IDs, and revenue comes out unchanged. Skip the delete step and the rerun appends another 1,200 rows on top: 2,400 rows, double the revenue, and a very confused finance team. One subtle detail is to wrap the delete and insert in a single transaction, so readers never see an empty day.
- Replace, don't append: each run owns a slice, like one day, and rewrites all of it
- Merge on a key: upsert by a unique ID such as order ID, so each order lands exactly once
- Delete and insert in one transaction so nobody sees a half-finished day
Bronze, silver, gold: the medallion layers
With every step safe to rerun, the next question is where the data lives. The cleanest pattern is three layers: raw, cleaned and business-ready, also known as bronze, silver and gold. The point is separation of concerns. Each layer has one job, so when a number looks off you check layer by layer instead of untangling one giant script.
Bronze keeps everything exactly as it arrived. Silver removes duplicates and junk and fixes types. Gold aggregates what's left into tables people actually query, such as daily revenue by region, with clear names shaped around business questions.
Treat bronze as read-only history. It's ugly and nobody should build dashboards on it, but it's your undo button. Whenever logic changes, you rebuild silver and gold from that untouched copy. This is ELT's safety net, made official.
- Bronze: raw and never edited
- Silver: deduplicated, cleaned, correctly typed
- Gold: aggregated tables in business language
Data tests, schema contracts and lineage
Building the pipes is half the job. You also need to prove the water is clean. Three tools do that, and they share one goal: catch problems at the pipeline's door, not in the CEO's inbox. Each guards against a different kind of failure, so they work best together.
Data tests are unit tests for tables. The example asserts that every order ID is unique and present, and that amounts are never negative. If any assertion fails, the pipeline stops before publishing anything. A schema contract is an agreement with the team that owns a source: these columns, these types, no surprise renames. When something has to change, it gets announced and versioned instead of discovered at 2 a.m.
Lineage is the map. It shows which sources feed which tables and which dashboards depend on them. When something breaks, you can see what's affected in minutes instead of guessing. Together, these three turn trust into something you can actually check. Trust is a feature, not a vibe. A halted pipeline is mildly annoying. A confidently wrong number is expensive.
# dbt-style tests on the orders table
columns:
- name: order_id
tests: [unique, not_null]
- name: amount
tests: [not_null, non_negative]How did sales 'triple' overnight? A failure, step by step
Here's how the opening disaster actually happens. A nightly job loads the day's orders from the payment provider into the warehouse. Nothing dramatic goes wrong: no outage, no hacker. A few small design gaps just line up, which is how most data incidents work.
The load job writes all the day's orders, but the connection times out before it reports success. The orchestrator reasonably assumes failure and retries. That happens once more before a run finally succeeds. Because the job appends instead of replacing, every attempt inserted the same orders again. The warehouse now holds three copies of the day. With no uniqueness test, nothing objects, and the CEO forwards the good news to the whole company before breakfast.
The fix uses ideas you've already seen: overwrite the day's partition instead of appending, add a unique-ID test, and publish only after tests pass. Any one of those would have stopped the spike.
Common data pipeline mistakes (and what to do instead)
Most pipeline mistakes feel like shortcuts when you make them. They save an afternoon today and cost you a weekend in six months. Every pipeline looks fine when the sources behave. The real test is the bad day: a late file, a renamed column, a rerun.
Two more classics. Going streaming because it sounds modern means that if the dashboard gets checked once a day, you're paying for an always-on system and on-call stress nobody benefits from. Then there's the monster script that extracts, cleans and aggregates in one go. When it breaks, you can't easily debug it or rerun just part of it.
The better mindset is that failures and changes aren't edge cases. They're just Tuesday. In a healthy loop, a run fails, the orchestrator alerts and retries, the idempotent rerun is safe, and tests verify the output before publishing. You also get backfills almost for free: fix the bug, then rerun the same job for past dates from bronze. You don't need a special script for it.
- Append-only loads with no keys → overwrite partitions or merge on keys
- Cron jobs that assume order → orchestrated dependencies
- Transforming before saving raw → land raw data first
- No tests, alerts by complaint → tests that block publishing
- Surprise schema changes → contracts plus tests that fail early and clearly
What to remember
- A pipeline is plumbing: sources in, trusted tables out, no silent leaks.
- Use ELT and keep the raw copy so you can always rebuild.
- Choose batch or streaming based on real decisions, not hype.
- Let an orchestrator run jobs on dependencies, with retries and alerts.
- Make every step idempotent so reruns and backfills are safe.
- Layer data as bronze, silver and gold, guarded by tests, contracts and lineage.
Questions people ask
What is the difference between ETL and ELT?
ETL transforms data on a separate server before loading it, and the raw details are often discarded. ELT loads raw data into the warehouse first and transforms it there, usually with SQL, so you can rebuild whenever logic changes or a bug turns up.
What does idempotent mean in a data pipeline?
It means running a job once or five times gives the same result. The usual techniques are overwriting a whole partition, such as one day, or merging on a unique key like an order ID.
Should I use batch or streaming for my data pipeline?
Start with batch. It's simpler, cheaper and fine for reports. Move to streaming only when someone can name a decision, like a fraud check, that loses money by waiting.
Do I really need an orchestrator like Airflow or Dagster?
Once you have several jobs that depend on each other, yes. An orchestrator runs each job when its inputs are ready rather than at a guessed time, retries failures, alerts you, and shows what ran.
What is the medallion architecture?
It splits data into three layers: bronze for untouched raw data, silver for cleaned and deduplicated data, and gold for business-ready aggregates. Each layer has one job, which makes problems easy to locate and lets you rebuild from bronze.
What is a data contract?
It's an agreement with the team that owns a source about which columns and types they'll provide. Changes get announced and versioned, and tests make any violation fail early instead of quietly corrupting your numbers.
