Data Engineering System Design: Designing a Pipeline End to End

Data engineering system design is the work of deciding how data moves from the systems that create it to the people and programs that use it. A complete design covers eight parts: requirements, ingestion, storage, transformation, orchestration, data quality, serving, and cost. Every one of those choices should follow from a few numbers, such as how many events arrive each day, how late they can arrive, and how fresh each consumer needs the result to be.

The difference from backend system design is the time dimension. A backend service answers a request and moves on, while a pipeline must produce correct tables every day, survive reruns, accept data that arrives days late, and stay affordable as the history grows. Most of the design effort goes into those four properties.

This article walks through one pipeline from start to finish, explains each decision with its numbers, and ends with a decision table you can reuse for other designs.

The running example: reading data for a news app

A news app has 1.5 million readers on an average day, and each reader opens about 6 articles, which makes 9 million article views a day. While an article is on screen, the app sends an event when the article opens, a heartbeat every 15 seconds, an event at each quarter of the page the reader scrolls past, and an event when the article closes. An average view lasts about 75 seconds and reaches half the page, so it sends 9 events.

Views therefore produce 81 million events a day, and app opens, searches, and shares add about 9 million more. Data that records what people do in an app or on a website is called clickstream data. All the figures in this example are planning figures, which a real design would replace with measurements from the first week of production.

Step 1: Requirements and freshness budgets

A pipeline design starts from its consumers, not from its tools. For each consumer, the useful requirement is a freshness budget: the oldest data that consumer can accept. The news app has three consumers.

• The editors' trending panel shows the most read articles of the last hour, at most 5 minutes old.
• The daily article report shows views, readers, and engaged views per article, ready by 06:00 for the previous day.
• The recommendation team needs a daily table of which device read which article, for training a model.

A fourth request, the paywall check that allows 10 free articles a month, does not belong in the pipeline at all. The app must decide at the moment of the tap, so it reads the app's own database. Recognizing what a pipeline should not do is part of the design.

The numbers come next. Ninety million events over 86,400 seconds is about 1,042 events per second on average. If the busiest hour carries 11 percent of the day, the peak is 9.9 million events in 3,600 seconds, or 2,750 per second, so the service that receives events must handle 2.64 times the average. An event sent as JSON is about 700 bytes, which makes 63 GB a day of raw input.

Step 2: Batch or streaming?

There are two ways to process data: in batches, where a job runs on a schedule over a fixed set of input, or as a stream, where a job runs continuously and handles each event shortly after it arrives. The freshness budgets decide between them.

Only the trending panel has a budget under an hour, so the real choice is how to serve that one panel. Streaming everything would make every table minutes old, but the compute would run all day, and every table would have to handle duplicates and late events continuously. A daily batch for the report and the training table, plus one small streaming job that counts article opens for the panel, meets every budget at a lower cost.

The cost of the mixed design is two code paths, so the panel can disagree with the report until the batch runs. That is acceptable here, because the panel only ranks articles. Streaming everything becomes the better choice when several consumers need minute-level data, for example a live dashboard sold to advertisers.

Step 3: Ingestion and the event format

Ingestion is the step that brings data from its sources into storage the pipeline controls. For the news app, a collector service receives events from phones and appends them to storage every 10 minutes. When the source is an application database instead of an app, the usual method is change data capture (CDC): reading the database's change log so that every insert, update, and delete becomes an event.

The event format prevents most of the problems that later stages would otherwise need to solve. Every event carries a fixed set of typed fields, called the envelope. Three of those fields matter most:

• An event_id set on the phone. A phone that gets no reply sends the same event again. If 0.4 percent of events arrive twice, the raw data gains 360,000 extra rows a day, and only an ID created at the source can tell a resend from a second real event.
• A view_id copied into every event of one view. The pipeline can group a view's events directly, without guessing where one view ends.
• Two timestamps. The event time, by the phone clock, says which day a read belongs to. The receive time, by the server clock, says how late it arrived.

Step 4: Storage layers

Most pipelines keep data in three layers. The raw layer stores events exactly as they arrived and is only ever appended to. The cleaned layer removes duplicates and invalid rows. The modeled layer holds the tables that consumers query. Keeping the raw layer unchanged means every other table can be rebuilt from it after a bug fix.

The format of the files matters as much as the layers. Analytics queries read a few columns across many rows, so columnar files such as Parquet, which store each column together, read far less data than row-based JSON. In this design, the plan assumes about 90 bytes per event in Parquet, or 8.1 GB a day instead of 63 GB.

The raw table is partitioned by event day, which means each day's rows live in their own folder, so a query for one day reads only that day's files. A table format such as Delta Lake or Apache Iceberg adds a transaction log on top of the files, so a reader sees a whole load or none of it. Loading every 10 minutes creates 144 small files a day per partition, so a nightly compaction job merges them into fewer, larger files.

Step 5: Transformation and modeling

Transformation turns raw events into tables people can use. In the ELT approach (extract, load, transform), raw data is loaded first and transformed inside the warehouse or lake with SQL. In the older ETL approach, data is transformed before it is loaded. Many modern pipelines use ELT, because keeping the raw data makes every transformation repeatable.

The first modeling decision is the grain of each table, which is what one row stands for. The news app needs four grains:

The rebuild of a day runs in a fixed order. It first keeps one copy of each event_id, then removes bot traffic, then builds one row per view. The order matters: checking for bots before removing copies would count a resent event twice and could mark a fast human reader as a bot.

Preparing for a data engineering interview? Grokking the Data Engineering Interview builds this clickstream pipeline and three other complete designs step by step, with SQL you can run and the numbers behind every choice.

Step 6: Orchestration

An orchestrator is the program that runs pipeline tasks in the right order, on a schedule, and retries them when they fail. Apache Airflow is a widely used example. Tasks and their dependencies form a directed acyclic graph (DAG): a set of steps where each arrow means "this step runs after that one", with no loops.

The nightly graph for the news app has four tasks. It lists which event days received new rows, rebuilds each of those days, runs the data tests, and only then publishes the new tables to the warehouse. Publishing after the tests means a bad rebuild never reaches the dashboards.

Step 7: Idempotency, late data, and backfills

A pipeline task is idempotent when running it twice on the same input produces the same result as running it once. This is the most important property in data engineering system design, because every pipeline is eventually rerun: after a failure, after a bug fix, or after late data arrives.

The simplest way to make a daily task idempotent is to rebuild a whole partition and replace it, rather than appending to it. Appending yesterday's rows a second time would double the counts, while replacing the partition for that day produces the same table every time.

Late data is why replacement matters. A phone without a connection queues its events and sends them later. In the plan, 97 percent of events arrive within 10 minutes and 99.5 percent within one day, and the app deletes queued events older than 7 days. Every day's tables can therefore change for 7 days, so each night the job rebuilds every event day that received new rows, not only yesterday.

A backfill is the same job run over past days, for example after fixing the bot rule. Because the job is idempotent and the raw layer is complete, a backfill is a normal run with a different date range rather than a special project.

Step 8: Data quality

Data quality means the tables are correct enough for the decisions people make with them. The practical tools are tests that run inside the pipeline after each rebuild and stop the publish step when they fail.

• Uniqueness and null checks. Each event_id appears once in the cleaned table, and required fields are never empty.
• Volume checks. A day with half the usual row count is more likely a broken source than a quiet day.
• Reconciliation. The total views in daily_report must equal the number of rows in article_views for the same day.
• Data contracts. The app team and the data team agree on the event schema, so a renamed field fails a check instead of silently producing empty columns.

Step 9: Serving

Serving is how consumers read the results. The daily report and the training table are loaded into the warehouse, where dashboards and analysts query them with SQL. The trending panel reads the small table that the streaming job updates every few minutes. A dashboard that queries raw events directly instead is slower, more expensive, and may count duplicates that the cleaned tables have already removed.

Step 10: Monitoring and cost

Monitoring for a pipeline watches the data as well as the jobs. A job can succeed and still produce an empty table, so the useful alerts are freshness (the newest partition is older than its budget), volume (the row count moved far from its usual range), and test failures.

Cost in a warehouse usually depends on the bytes each query reads, so partitioning is a cost decision as much as a speed decision. Suppose a dashboard tile counts readers for today and refreshes every 5 minutes, which is 288 times a day. If the events table holds 90 days and is not partitioned, every refresh reads all 90 days to count one. Partitioning by event day lets each refresh read one day, which cuts the bytes read by about 90 times.

Retention is the other lever. Raw events with a device ID count as personal data, so they should be kept only as long as they are needed. Keeping 90 days of raw events in Parquet takes 90 times 8.1 GB, or about 729 GB, while the modeled tables can keep a longer history without the identifier.

The decisions in one table

Frequently asked questions

Is system design important for data engineers? Yes. Data engineering interviews commonly include questions about designing a pipeline end to end, and in daily work the design decisions determine whether a pipeline can be rerun, scaled, and afforded. The focus differs from backend design: correctness over time, late data, and cost matter more than request latency.

What is asked in a data engineer system design interview? A typical interview question asks you to design a pipeline for a product, such as clickstream analytics, a daily reporting job, or loading database changes into a warehouse. You are expected to state the requirements and numbers, choose batch or streaming, define the tables and their grain, and explain how the pipeline handles failures, late data, and backfills.

What is the difference between batch and streaming pipelines? A batch pipeline runs on a schedule over a fixed set of input, such as yesterday's events. A streaming pipeline runs continuously and processes each event shortly after it arrives. Batch is simpler and cheaper to rerun, while streaming is necessary only when a consumer needs data within seconds or minutes.

What makes a data pipeline idempotent? A pipeline is idempotent when running it twice on the same input gives the same result as running it once. The common method is to rebuild and replace whole partitions, such as one day, instead of appending rows, and to remove duplicates by a unique event ID.

Related reading

• Designing a Real-Time Event Pipeline with Kafka, Flink, and Elasticsearch
• Kafka Streams vs Apache Flink vs Apache Storm
• Exactly-Once Delivery: Why It's Harder Than You Think
• RabbitMQ vs Kafka vs ActiveMQ
• Kafka vs RabbitMQ: Log vs Queue, and How to Choose
• Event-Driven Architecture: Patterns, Diagrams, and When to Use It Over Request-Response
• Data Engineering Interviews: How to Stand Out

Ready to design pipelines in an interview? Grokking the Data Engineering Interview covers SQL, data modeling, pipeline design, streaming, and data quality, and ends with four complete pipeline designs you can compare against your own.