In this article
Before you start
This page belongs to the data engineer interview guide. Reading the SQL guide first helps, because most pipelines end in tables you query with SQL.
Why interviewers ask this
A data pipeline is the everyday work of a data engineer, so a data engineering round often starts here, either as a definition or as "design a pipeline for this feed". Interviewers use the question to see whether you can name the stages, say what breaks when a step fails halfway, and avoid counting the same row twice.
A weak answer lists tools: "Kafka, Spark, Airflow, a warehouse." A strong answer describes the stages first and then the failure modes: what a retry does to the data, what happens when the source sends late or duplicate records, and how you would notice bad data. A Staff-level answer adds the trade-off between fresh data and a simple system, and says how a missed day is repaired.
Data pipeline from first principles
- Data pipeline
- A data pipeline is a chain of automated steps that ingests raw data from sources, optionally transforms it, and stores the result in a destination such as a data warehouse or data lake, so that analysts, dashboards and other programs can use it without handling the raw feeds.
- Idempotence
- Idempotence is a property of a step that makes running it twice leave the destination in the same state as running it once, so a retry after a failure cannot double-count rows.
In plain words
Think of a water system. Rain falls in many places (the sources), pipes bring it to a tank (the store), a plant cleans it (the transform), and taps deliver it (the consumers). A schedule says when the plant runs and what to do when a pump breaks. The analogy breaks in one place: water that flows through twice is simply wasted, but data that flows through twice can be counted twice, which is a wrong answer in a report.
In one precise sentence, a data pipeline is a repeatable program. It reads data from a source and applies defined steps to it. It then writes the result to a destination, on a schedule or continuously.
Step 1: Ingest the data from its sources
Ingest copies data from the sources into the pipeline. A source can be a database, a file drop, an application programming interface (API) or a stream of events from a message system such as Apache Kafka.
Input: a source and a way to read it. Output: raw records inside the pipeline, usually unchanged. Two choices matter here. First, the pipeline reads either everything again or only what is new, using a timestamp or an increasing id as a bookmark. Second, the source may deliver a record more than once, because delivery systems retry when they are unsure a message arrived. Kafka's documentation describes these delivery semantics: a message can be delivered at most once, at least once or exactly once, and the stronger promises cost more.
You now know where the data enters and why duplicates can already exist. The next step decides what to do about them.
Step 2: Store the data so a rerun is safe
The store keeps the data so a failed step can be rerun. The destination is usually a data warehouse or a data lake. A data warehouse is a database built for analytical queries. A data lake is cheap file storage that holds raw files in their original form. Landing the raw data before changing it also means a bug in a later step can be fixed and replayed.
The failure to design for is the retry. Suppose a step writes ten thousand rows and crashes after five thousand. A scheduler reruns it. If the step appends, the first five thousand rows now exist twice. If the step writes by key, meaning each row has a unique id and an existing id is replaced instead of added, the rerun lands on the same final state. That property is idempotence. Another common route is to replace a whole day's partition (one slice of a table) in one atomic swap.
Input: records plus a key. Output: a destination that looks the same after one run or three. Everything later builds on this.
Step 3: Transform the data into something usable
Transform cleans and reshapes the data for consumers. Typical work is removing bad rows, converting types, joining tables, and computing totals. In a warehouse most of this is written in SQL.
Order matters, and it gives two named designs. ETL, short for extract, transform, load, transforms the data before loading it, so in ETL the transform step sits before the store and only the cleaned result reaches the destination. IBM Think notes that an ETL pipeline is a specific type of data pipeline that follows a predefined process for moving and preparing data, so the two terms are not interchangeable. The second design, extract, load, transform (ELT), loads the raw data first and transforms it inside the warehouse using the warehouse's own compute. ELT keeps the raw copy, which makes replays easy; ETL can keep sensitive or messy fields out of the destination. Neither is the better design in general.
Tools differ by size. A single machine can run a SQL script; a cluster engine such as Spark spreads the work over many machines (see the PySpark tutorial and the notes on Apache Hadoop). Input: stored raw data. Output: tables shaped for a use, which is where data modeling decisions appear.
Step 4: Orchestrate the steps, retries and reruns
An orchestrator schedules the steps, orders them and retries failures. Tools such as Apache Airflow describe a pipeline as a directed acyclic graph (DAG), a directed acyclic graph: tasks as nodes, dependencies as one-way arrows, and no loops, so every task runs after the ones it depends on.
The orchestrator does not transform data itself; it runs the code that does. Its jobs are triggering on a schedule, waiting for upstream tasks, retrying a failed task a limited number of times with a delay, alerting a person when retries run out, and supporting a backfill. A backfill is a rerun of the pipeline over past dates to fill a gap or apply a fixed rule. Retries and backfills are safe only because Step 2 made the writes idempotent.
Step-by-step description
- Data is copied in from the sources.
- Raw data is kept so a rerun is safe.
- Data is cleaned for consumers.
- A scheduler runs, orders and retries.
- Ingest copies data from the sources into the pipeline.
- The store keeps the data so a failed step can be rerun.
- Transform cleans and reshapes the data for consumers.
- An orchestrator schedules the steps, orders them and retries failures.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Worked example
Take an illustrative orders feed of 1,000,000 events a day, where retries cause 2% of events to arrive twice (both figures illustrative). That is 20,000 duplicates.1 A plain append writes 1,020,000 rows after retries, so every total built on it is inflated. Writing by key keeps 1,000,000 rows, because a repeated id replaces the earlier copy.1
Freshness has a cost too. The feed averages about 41,667 events an hour.1 If an hourly batch takes 10 minutes to run (illustrative), an event that lands just after a cutoff waits 60 minutes for the next run plus 10 minutes to finish, so the worst data age is 70 minutes.1
Retries have a ceiling. Say each attempt fails with probability 10% (illustrative) and the orchestrator allows three retries, so four attempts. All four fail with probability 0.01%, and across 24 hourly runs the chance that at least one run exhausts its retries in a day is about 0.24%.1 A person must be alerted for that case.
| Quantity | Value (computed from illustrative inputs) |
|---|---|
| Events per day | 1,000,000 |
| Duplicate events after retries | 20,000 |
| Rows after a plain append | 1,020,000 |
| Rows after a write by key | 1,000,000 |
| Average events per hour | 41,667 |
| Worst data age, hourly batch of 10 minutes | 70 minutes |
| Chance one run fails all four attempts | 0.01% |
| Chance of at least one exhausted run in a day | about 0.24% |
Text description of the diagram
- A plain append writes 1,020,000 rows after retries.
- Writing by key keeps 1,000,000 rows.
Trade-offs and edge cases
The biggest design choice is when the data moves. Batch processing is a style in which a pipeline runs on a schedule over a bounded chunk of data, such as yesterday's orders. Stream processing is a style in which a pipeline handles each event continuously as it arrives. Batch is simpler to reason about and to rerun; streaming gives fresher data but needs more care with late events, ordering and state, which engines such as Spark Structured Streaming handle with checkpoints that record progress. The Amazon Web Services (AWS) overview of data pipelines covers the same split, and the batch versus streaming trade-offs in detail are worked through on their own page.
Text description of the diagram
- Batch processing runs on a schedule over a bounded chunk of data.
- Stream processing handles each event continuously as it arrives.
Edge cases to name: late records that belong to an earlier day; a source that changes a column and silently breaks a transform; empty runs that look successful, so a check on row counts or freshness belongs in the pipeline; and "exactly once". Delivery promises describe messages between systems, but the number a user sees depends on whether the final write is idempotent, so check the end result, not only the label. Finally, a pipeline that grows into dozens of tasks needs ownership and tests like any other code.
How to answer it in an interview
What is a data pipeline, and what would you watch for when building one?
A data pipeline is a set of automated steps that ingests data from sources, stores it, transforms it and serves it to consumers, on a schedule or continuously. ETL is one kind of pipeline, not a synonym. The thing I watch is failure. Sources can send duplicates and steps can crash halfway, so I make writes idempotent by keying rows, and I let an orchestrator retry with limits and alert me. Then I add checks on row counts and freshness, and I choose batch or streaming by how fresh the data must be.
The spoken structure: define the stages, say ETL is a subset, name the retry problem and the idempotent fix, then end on checks and the batch or streaming choice.
Common mistakes
- "A data pipeline is the same as ETL." (wrong) ETL is a specific type of pipeline with a predefined process. (right) "ETL is one kind of data pipeline; ELT and streaming pipelines are others."
- "If the job fails, the orchestrator just reruns it." (wrong) A rerun of an appending step double-counts rows. (right) "I make the write idempotent, then the rerun is safe."
- "We use exactly-once delivery, so duplicates are impossible." (wrong) The label covers one hop, not every write. (right) "I verify the final destination does not change on a repeated write."
- "Airflow transforms the data." (wrong) An orchestrator schedules and orders tasks. (right) "Airflow runs the code that transforms the data."
- "Streaming is always better." (wrong) It adds state and ordering problems. (right) "I choose streaming only when the use needs fresh data."
Follow-up questions
How do you design a data pipeline in a system design interview?
Clarify the sources, the freshness the users need and the volume, then walk the four stages: ingest, store, transform, serve. Say how each step is retried and made idempotent, where data quality checks sit, and how a missed day is backfilled. A full walk-through of that round, with capacity and failure questions, is in how to answer a senior data engineering system design round.
What is the difference between a data pipeline and ETL?
A data pipeline is any automated movement and preparation of data. ETL is a specific type that transforms before loading. A pipeline that loads raw data first (ELT) or streams events is a data pipeline but not an ETL pipeline.
What is the difference between batch and streaming pipelines?
Batch runs on a schedule over a bounded chunk of data; streaming handles each event continuously as it arrives. Choose by how old the data may be when someone reads it; the streaming vs batch comparison works through both cases.
What does exactly-once mean in a data pipeline?
Exactly-once means each event affects the result once, even if messages are retried. It can come from a delivery system that supports it or from an idempotent write at the destination, and the second is often the simpler property to verify.
What is a backfill?
A backfill reruns the pipeline over past dates to fill a gap or apply a corrected rule. It works safely when each run replaces its own slice of data instead of appending to it.
What is the difference between data parallelism, model parallelism and pipeline parallelism?
Data parallelism, model parallelism and pipeline parallelism are ways to split machine learning training across devices, not stages of a data pipeline. Data parallelism copies the whole model to each device and gives each a different slice of the batch. Model parallelism splits one model's layers or tensors across devices. Pipeline parallelism places consecutive groups of layers on different devices and feeds micro-batches through them, so the stages work on different micro-batches at the same time.
- A data pipeline ingests, stores, transforms and serves data through automated steps.
- ETL is one kind of data pipeline, not the whole category.
- Retries and duplicates are normal, so make writes idempotent by keying rows.
- An orchestrator schedules, orders and retries tasks; it does not transform the data itself.
- Choose batch or streaming by how fresh the data must be, and add checks on row counts and freshness.
Sources
- What Is a Data Pipeline? — IBM Think (accessed 2026-10-26), 26 Oct 2026
- Apache Airflow documentation: DAGs — Apache Airflow (accessed 2026-10-26), 26 Oct 2026
- Apache Kafka documentation: message delivery semantics — Apache Kafka (accessed 2026-10-26), 26 Oct 2026
- Apache Spark Structured Streaming programming guide — Apache Spark (accessed 2026-10-26), 26 Oct 2026
- What is a data pipeline? — Amazon Web Services (accessed 2026-10-26), 26 Oct 2026
