What Is a Data Pipeline? Stages, Tools, and Failure Modes
The dashboard says 128 orders yesterday. The payment processor says 134. Both numbers are correct. The dashboard reads a table that a data pipeline last wrote at 02:10, and six orders landed after that. Nothing is broken, no alert fired, no engineer did anything wrong. The number on screen is simply older than the question being asked, and there is nothing on the screen that says so.
That gap is the whole subject. A data pipeline is the set of steps that moves data from where it is produced to where it is used, reshaping it along the way. When people argue about pipelines they are usually arguing about tools. The thing that actually determines whether you can trust a number is smaller and less glamorous: how the data got in, how recently, and whether anything was quietly dropped or written twice on the way.
This guide walks the stages end to end, then spends most of its length on how pipelines break in practice, because that is the part that costs money. It closes with a batch versus streaming recommendation that accounts for what each option really costs to run, and a short list of checks you can perform yourself before you repeat a number to anyone.
The five stages of a data pipeline, end to end
Every pipeline, from a 30-line Python script to a platform with a dedicated team, does the same five things. The vocabulary changes by vendor. The stages do not.
| Stage | What it does | Concrete example | What it looks like when it fails |
|---|---|---|---|
| Ingestion | Gets raw records out of a source system without changing them | Pull yesterday's charges from a payments API into a raw landing table | Rows silently missing because the source changed a field and the connector skipped them |
| Transformation | Casts types, joins, applies business definitions, builds the tables people query | Convert currencies, strip test orders, net refunds against the original order month | A closed month quietly changes because a refund got attributed to the wrong period |
| Storage | Holds raw and modeled data durably, with history | A warehouse holding a raw zone, a staging layer, and a modeled fct_orders table | Storage costs balloon because nobody set retention on raw JSON payloads |
| Orchestration | Decides what runs, in what order, with what retry and alert behavior | A scheduler that runs ingestion at 02:00 and refuses to run models if it failed | A retry re-runs a load that already half succeeded, duplicating rows |
| Serving | Delivers the modeled data to the humans and systems that consume it | A BI dashboard, an export to a CRM, an assistant answering in chat | The consumer displays a stale number with no visible timestamp |
Read that table twice. The failure column is the article. Most teams can describe the stages after five minutes of reading. Almost nobody instruments the fourth column, which is why so many pipelines are technically healthy and practically untrustworthy.
Two useful data pipeline examples to hold in your head as you read on. The small one: a single scheduled job that pulls support tickets from a helpdesk API each night, flattens the JSON, and appends to one table used by two people. The large one: a payments source captured through change data capture, a dozen SaaS connectors, a modeling layer with 400 tables, an orchestration graph with dependencies, and a serving layer feeding dashboards, a CRM sync, and an AI assistant. Both fail the same ways. The large one just fails in more places at once.
Ingestion: the stage that decides everything downstream
You cannot fix in transformation what you failed to capture in ingestion. Everything else in a data pipeline architecture is recoverable by rerunning code. Missing raw data is often gone forever, because APIs age out history and source databases overwrite rows in place.
There are four ways in, with different guarantees.
Full extract. Read the whole table or endpoint every run. Simple, obviously correct, and catches hard deletes for free because anything absent from the new copy is genuinely gone. Perfectly fine up to a few million rows. It stops working when the read locks a production database or a rate limit turns the pull into a six-hour job.
Incremental extract by watermark. Remember the highest updated_at you have seen and ask for everything above it. This is the default almost everywhere, and it rests on three assumptions people forget to verify. That every write path bumps updated_at, which a bulk UPDATE run by an engineer during an incident usually does not. That clocks agree, because a source server running a few hundred milliseconds ahead can commit rows just under a watermark you have already passed. And that deletes are soft, because a watermark query can never see a row that no longer exists.
Change data capture. Read the database's own replication log and turn every committed change into an event. CDC catches hard deletes and bulk updates, and puts almost no query load on the source. In exchange you now operate a component that can fall behind, retain log segments until a disk fills, and need careful handling during failovers and schema migrations.
Webhooks and event streams. The source pushes to you. Low latency, and the only realistic option for systems that do not expose a queryable history. Webhooks are also the least reliable input a pipeline has: they arrive out of order, they arrive twice, and when your endpoint is down during a deploy, some providers retry for a while and then stop. Anything built on webhooks needs a periodic reconciliation pull behind it, which is a point covered in more depth in Integrating APIs: A Practical Guide for Business Teams.
One rule keeps the rest of the pipeline manageable: ingestion should be dumb. Land the source shape exactly as it arrived, append-only, with an ingestion timestamp and a batch identifier attached. No renaming, no filtering, no casting. The moment ingestion contains business logic, you can no longer rebuild history without re-extracting, and re-extraction is frequently impossible.
How data pipelines actually break
Connector outages are the failure mode people plan for, and they are the least dangerous, because they are loud. A job that does not run produces an obvious hole. The expensive failures are the ones that produce a plausible number.
Schema drift
Someone in the product team renames plan_type to subscription_tier, or changes an enum from pro to professional, or starts sending a numeric field as a quoted string. The source system is fine. The application is fine. Your pipeline is now doing one of three things: erroring loudly (best case), dropping the column silently, or coercing the value to null and carrying on.
The version people underestimate is semantic drift, where nothing structural changes at all. The status field gains a new value called paused that your revenue model does not classify, so paused accounts fall out of both the active and churned buckets. No error appears anywhere. The churn number just becomes slightly wrong, and stays slightly wrong, until someone counts by hand.
Defenses that work: assert on schema rather than inferring it, fail the run when an unexpected enum value appears rather than passing it through, and keep raw payloads so you can rebuild once you understand what changed. Defenses that do not work: hoping the source team tells you.
Late-arriving data
Data does not arrive in the order it happened. A mobile app buffers events offline and uploads them the next morning. A payment settles two days after authorization. A partner sends yesterday's file at 11:00 today. A CDC stream falls behind and catches up in a burst.
If your daily job runs at 02:00 and writes yesterday's totals once, every record that arrives afterwards is invisible unless something reprocesses that day. The number was right when it was computed and wrong an hour later, and the pipeline has no opinion about that because it already finished successfully.
The fix is structural, not clever. Distinguish event time (when the thing happened) from ingestion time (when you received it), partition by event time, and reprocess a trailing window on every run rather than only the newest partition. A three-day rolling reprocess costs very little and eliminates most of this class. Then decide deliberately when a period is closed and stops being restated, and communicate that date to whoever reads the numbers.
Silent partial loads
A load writes 40,000 of 52,000 rows and then loses its connection. If the write was not transactional, those 40,000 rows are now committed. The orchestrator marks the task failed, someone reruns it, and depending on how the load is written you now have a table that is either short by 12,000 rows or long by 40,000.
The worst variant is a partial load that the orchestrator marks successful, which happens whenever ingestion paginates and treats an error on page 14 as the end of results. You get a complete-looking table containing two thirds of the data, and every downstream metric drops by a third with no error anywhere.
Two defenses. Load atomically: write to a temporary table, then swap it in as one operation, so a consumer never sees a half-written state. And validate volume, not just success: if today's row count is 34% below the trailing average, fail the run and hold the previous table rather than publishing a number that will be quoted in a meeting.
Retries that duplicate rows
This is the single most common data pipeline defect, and it comes from the interaction of two safety features. Orchestrators retry failed tasks because most failures are transient. Loads append because appending is fast. Retrying an append that partially succeeded writes the successful portion again.
Duplicates are worse than gaps because they inflate rather than deflate. A missing day looks obviously wrong. A day that is 8% high looks like a good day, gets celebrated, and gets discovered three weeks later when someone reconciles against the source system.
The property you want is idempotency: running the same job twice produces the same result as running it once. In practice that means a stable natural key on every record, merge or upsert semantics instead of blind inserts, deletion of the target partition before rewriting it, and a batch identifier stored on every row so you can trace which run produced what.
The failure nobody instruments
Underneath all four is one meta failure: the consumer of the data has no idea any of this happened. A dashboard renders whatever is in the table. A chat assistant answers in confident prose. Neither has a native way to say "this table last updated 11 hours ago and its row count is unusual today."
Freshness and volume are the two cheapest checks in the entire discipline, and the two most frequently skipped. If you do nothing else after reading this, add a monitor that alerts when a critical table's max(loaded_at) falls outside its expected window, and one that alerts when daily row count deviates sharply from its trailing average. Those two catch most of what silently costs you.
Data pipeline architecture: batch versus streaming, honestly
The batch versus streaming pipeline question gets answered with architecture diagrams when it should be answered with a question about decisions: is there a decision that gets made automatically inside the latency window you are buying? If a human looks at the number and acts on it hours later, streaming latency is spent on nothing.
| Dimension | Batch | Micro-batch (5 to 60 minutes) | Streaming |
|---|---|---|---|
| Typical freshness | Hours to a day | Minutes | Seconds |
| Engineering effort | Low | Low to moderate | High and ongoing |
| Cost profile | Cheap, predictable | Moderate, warehouse compute rises with frequency | Expensive: always-on infrastructure plus specialist time |
| Reprocessing history | Easy, rerun the job | Easy | Hard, requires replay design |
| Correctness under late data | Straightforward with trailing reprocess | Straightforward | Needs windowing and watermarks done properly |
| Debuggability | Read the failed run | Read the failed run | Distributed state, harder to reason about |
| Honest fit | Reporting, finance, most analytics | Operational dashboards, ops queues | Fraud checks, personalization, alerting on live signals |
The recommendation, stated plainly: start batch, move the two or three tables that genuinely need it to micro-batch, and adopt streaming only for the specific flows where an automated decision happens within seconds. Most organizations that build a full streaming layer end up running batch alongside it anyway for reporting, because finance needs restatable, reproducible history rather than a continuously moving number.
Note the hidden cost in row three. Increasing pipeline frequency from daily to every fifteen minutes multiplies warehouse compute by roughly the increase in runs, and it does so for every model in the dependency graph, not just the one table someone wanted fresher. Freshness is bought per table, not globally, and the discipline of deciding which tables deserve it is a big part of keeping a warehouse affordable. That trade sits alongside the sizing questions in Cloud Data Warehouse: When You Need One and When You Don't.
Data pipeline software: what each category is really for
The market is confusing because vendors describe themselves by outcome rather than by stage. Sorting data pipeline software by which stage it occupies makes selection much easier.
| Category | What it actually does | Choose it when | Does not solve |
|---|---|---|---|
| Managed connectors | Prebuilt extractors for common SaaS and database sources | You need 20 standard sources and no one wants to maintain API clients | Custom internal sources, your business definitions |
| Custom ingestion code | Scripts or a framework you write against APIs and logs | The source is unusual or the managed connector is missing fields you need | Everything else in the pipeline |
| Transformation frameworks | Version-controlled SQL or Python models with tests and lineage | You have more than a handful of derived tables | Getting the data in |
| Orchestrators | Dependency graphs, scheduling, retries, backfills, alerting | Any pipeline with more than a few interdependent steps | Data quality itself |
| Streaming platforms | Durable event logs and stream processing | Events must be consumed within seconds by automated systems | Reproducible historical reporting |
| Observability and testing | Freshness, volume, distribution and schema monitors | Someone has already been burned by a silent failure | Fixing the underlying pipeline |
| Reverse ETL | Pushes modeled data back into operational tools | Sales or support needs warehouse-derived fields inside their own tools | Any modeling upstream of it |
Two selection criteria matter more than feature lists. First, can you rebuild any table from raw on demand, or does the tool assume state you cannot reconstruct? Second, does it fail loudly? A tool that skips a bad row and keeps going will cost you more than one that stops.
Pipeline orchestration and the contracts underneath it
Pipeline orchestration is often described as scheduling, which undersells it. Cron runs things at times. An orchestrator runs things in an order, understands that a model depends on an ingestion task, refuses to publish downstream tables when an upstream step failed, retries with a policy you chose, and gives you a place to backfill a date range without hand-editing SQL.
Three orchestration habits separate pipelines that age well from ones that rot:
- Fail closed, not open. If ingestion fails, downstream models should not run on yesterday's data and republish it as today's. A missing dashboard prompts a question. A quietly repeated number does not.
- Make backfills routine. You will need to reprocess a date range after fixing a bug. If that requires a specialist and an afternoon, definitions stay broken because correcting them is too painful.
- Write down the contract. For each critical table: the owner, the expected freshness, the grain, and the definition of every metric column. Most of what people call a pipeline problem is a definition problem, which is the same failure the discipline in Master Data Management: MDM Without a Six-Figure Program exists to prevent, and the same reason field-level mapping matters so much in a Salesforce HubSpot Integration: Sync Fields Without Chaos.
The AI data pipeline changes the stakes, not the stages
An AI data pipeline adds steps to the end: chunking documents, generating embeddings, writing them to a vector index, and retrieving them at query time. The failure modes are the same ones described above wearing new clothes. Stale embeddings are stale data. A partial re-index is a partial load. Documents chunked before a schema change are schema drift with extra steps.
What genuinely changes is the presentation layer. A dashboard at least shows you a chart you can eyeball for a suspicious shape, which is exactly the reading skill that Data Visualization Examples That Change a Decision trains. A language model returns a sentence. Confident prose has no visual signal for staleness, so a number that is eleven hours old reads exactly like a number that is eleven seconds old. When several agents pass results between each other, as described in Multi Agent Systems Explained for Non Technical Teams, the original timestamp is usually the first thing lost.
The practical consequence: for AI systems, freshness metadata is not a nice-to-have, it is part of the answer. An assistant that cannot tell you when its source was last updated cannot be audited, and an answer you cannot audit is a guess with good grammar.
Where Skopx fits, and where it does not
Being direct, because this matters more than a soft product mention. Skopx does not build, run, schedule, or monitor data pipelines. It is not an ETL tool, not a data warehouse, and not a BI platform. If you need change data capture from Postgres, a modeling layer, or an orchestrator, none of that is what Skopx does, and no amount of chat will substitute for it.
What Skopx is: an AI workspace that connects to nearly 1,000 tools a company already uses, including Gmail, Slack, Stripe, HubSpot, QuickBooks and Google Analytics, and answers questions in chat with cited data pulled from those tools. It reads the live source through its API rather than a nightly copy, which sidesteps one class of staleness and creates a different obligation: because the answer is prose, the citation and the timestamp are how you check it. Every answer points at where the number came from, which is the check this entire article argues for.
Two other pieces are relevant to the failure modes above. The insights engine watches connected sources and surfaces anomalies, so a metric that drops by a third overnight can reach you as a notification rather than as a question in a meeting. And workflows let you describe a recurring check in plain language and have it run on a schedule, which is a reasonable way to build the reconciliation habit that catches silent pipeline failures.
Daily source-versus-report reconciliation
Every weekday at 09:00
Scheduled trigger
Yesterday's paid orders in Stripe
The system of record for payments
Yesterday's order count in the reporting table
Whatever your BI layer reads
Compare counts and totals
Flag any gap beyond a threshold you set
Post the gap to Slack
Both numbers plus the last-updated timestamp on each
Skopx runs on your own AI key with zero markup, and plans are Solo at $5 per month and Team at $16 per seat per month, which you can read in full on pricing. It sits at the serving end of the picture, next to your dashboards, not underneath them.
How to check freshness yourself, in five minutes
You do not need access to the orchestrator to sanity-check a number. Five checks, in order of how often they catch something:
- Find the last-updated timestamp. Every serious table has a
loaded_ator equivalent. If the table's maximum value is 14 hours old and you are asking about this morning, stop there. If nothing in the tool exposes a timestamp, that itself is the finding. - Compare against the source system. Open the payment processor, the CRM, the ad platform, and count the same thing for the same window. A small gap is usually timing. A gap over ten percent is usually a defect.
- Check row counts against the trailing average. A day that is far above or below its neighbours is either a real event or a duplicate load, and duplicates are more common than real doublings.
- Trace one record end to end. Pick a single order or contact, find it in the source, find it in the warehouse table, find it in the report. Most pipeline defects are visible on one row.
- Ask what changed upstream. Schema drift is almost always downstream of a product release, a field rename, or a new value in an enum. Check whether anything shipped in the source system in the last week.
When an AI assistant gives you a number, apply the same standard you would apply to a colleague: ask which system it came from and when that system was last read. A tool that cannot answer both questions has not earned the number.
Frequently asked questions
What is the difference between a data pipeline and ETL?
ETL is one pattern for the transformation stage of a data pipeline: extract, transform in a separate engine, then load the result. A pipeline is the broader path from source to consumption, including ingestion, storage, orchestration and serving. Most modern warehouses invert ETL into ELT, loading raw data first and transforming inside the warehouse, because warehouse compute is now cheap enough to make that the simpler option and it preserves raw data for rebuilds.
How fresh does my data actually need to be?
Ask what decision the freshness enables. If a human reads the number in a weekly meeting, daily is fine and hourly is waste. If an automated system acts on it within seconds, you need streaming for that specific flow. Most organizations need daily for finance and reporting, hourly or better for a handful of operational tables, and streaming for almost nothing. Set an explicit freshness target per table and monitor against it, rather than making everything as fresh as possible.
What causes duplicate rows in a data pipeline?
Almost always a retry of a non-idempotent load. A job appends rows, fails partway, gets retried by the orchestrator, and appends the already-written portion again. Unstable API pagination is the second cause: if a record is modified while you are paging through results sorted by a mutable field, it can be returned twice. The fix in both cases is a stable natural key plus merge or upsert semantics, so re-running a job overwrites rather than accumulates.
Do I need an orchestrator for a small pipeline?
Not on day one. A single scheduled script with good logging is a legitimate pipeline. Add an orchestrator when you have interdependent steps, because that is when fail-closed behavior starts to matter: without it, a failed ingestion is followed by a model run that republishes yesterday's data as today's. The trigger for adopting orchestration is dependency, not scale.
Can an AI assistant replace a data pipeline?
No, and treat any claim otherwise with suspicion. An assistant can query live sources through their APIs and answer questions with citations, which covers a real set of everyday questions without a pipeline in the middle. What it cannot do is store history the source system does not keep, reconcile conflicting definitions across systems, or produce reproducible restatable reporting for a closed accounting period. Those need modeled, stored data. Live API access and a warehouse solve genuinely different problems, and organizations of any size usually end up with both.
What should I monitor first if I have no monitoring at all?
Freshness and volume, on your five most-used tables. Alert when the last-updated timestamp falls outside the expected window, and alert when daily row count deviates sharply from its trailing average. Those two monitors cost an afternoon to build and catch the majority of silent failures, including partial loads, stalled connectors and duplicate writes. Schema and distribution tests are valuable next, but they are refinements on a foundation those two provide.
Skopx Team
The Skopx engineering and product team