TL;DR
Data pipeline architecture is the design that decides how data moves from source systems to the people and applications that use it. A sound design has four layers, which are ingestion, processing, storage and serving, with orchestration running them in order. Batch and streaming decide how often data moves.
Lambda and kappa decide how many processing paths you maintain, and medallion layers organize the stored data under either one. Reliability comes from idempotent writes, bounded retries, a quarantine for bad records and a raw copy you can replay. Start from the freshness your users actually need, then pick the simplest pattern that meets it.
Key Takeaways Data pipeline architecture separates ingestion, processing, storage and serving so each layer can change and fail on its own. Batch versus streaming is a speed decision, while lambda versus kappa is a decision about how many code paths you run. Medallion architecture (bronze, silver, gold) organizes stored data and works with batch, streaming, lambda or kappa. Orchestration, idempotent writes, checkpoints and replayable raw data decide how a pipeline recovers from failure. Written contracts at each layer boundary stop schema changes upstream from silently breaking reports downstream. Choose by freshness, volume, replay needs, cost and team skills, and add streaming only where a user truly needs it. Watch on YouTube
Migrating Data Pipelines from SSIS to Microsoft Fabric for SSMH | Case Study
Kanerika walks through a real move of legacy SSIS pipelines onto Microsoft Fabric. It is the kind of re-architecture where layers, orchestration and recovery rules get redrawn.
What a City Water Network Teaches About Data Pipelines
Turn on a tap in a mid-sized city, and the water that comes out has crossed an entire engineered system. Water comes in from a river and a reservoir, then passes through a treatment plant. It waits in storage tanks and water towers, and finally flows through mains to every tap. A control room watches pressure and flow across the whole network, all day.
The engineers who built it drew that network long before anyone dug a trench. They placed valves so a burst main in one district does not drain the rest of the city. They also added overflow paths, backflow preventers and spare capacity. Tearing up a street to fix a design mistake costs far more than planning for it.
Data teams often build in the opposite order. A request comes in, someone writes a job, and the job ships.
Two years later there are two hundred jobs, and nobody can say which ones feed the revenue dashboard. Then one failed load at 3 a.m. leaves half the company looking at stale numbers. The network plan those jobs never had is what a data pipeline architecture provides.
What Data Pipeline Architecture Decides
Data pipeline architecture is the set of design decisions that govern how data is captured, transformed, stored and delivered. It also covers how all of that recovers when something breaks. Our guide to data pipelines and their components covers the basics. This article picks up where that one stops.
The architecture is the layer above any single job. It answers questions such as where raw data lands and which layer owns business rules. It also sets how fresh each output must be and what happens to a record that fails validation. Those answers stay the same whether a team writes the jobs in SQL, Python or a low-code tool.
Logical Design Versus Physical Platform
A useful habit is to separate the logical design from the physical one. The logical design says to capture order changes continuously, keep an immutable raw copy and build daily sales facts. The physical design says which services do that work on Microsoft Fabric, Databricks, Snowflake or AWS.
Keeping the two apart pays off when platforms change, which they do every few years. A team with a clear logical design can move from one stack to another and keep its layer boundaries, contracts and recovery rules. A team whose only design lives inside tool configuration usually has to rediscover it during the migration.
Requirements That Set the Shape: Freshness, Volume, Replay and Cost
Five requirements shape nearly every design choice that follows, so write them down before choosing a pattern. Agree on each one with the people who consume the data, because they usually have different answers than the engineers expect.
Freshness. How old can the data be when someone uses it? Seconds, minutes, hours and days lead to very different designs.Volume and growth. Rows per day today, the expected rate in two years, and how spiky the arrival pattern is.Replay needs. Whether you must rebuild outputs from history after a logic change, an audit request or a bad load.Cost ceiling. Always-on compute for streaming costs more than a nightly batch window, so the budget limits the options.Team skills. A pattern your team cannot operate at 3 a.m. is the wrong pattern, however elegant it looks on a slide.Freshness deserves the most scrutiny. A finance team that asks for “real-time” often means “updated before the morning meeting,” and that request is a batch job, not a streaming platform. Pinning the number down early prevents the most expensive mistake in pipeline design, which is building low-latency infrastructure nobody needs.
The Four Layers of a Data Pipeline Architecture
Every data pipeline architecture, from a single nightly job to a global streaming platform, can be read as four layers. Ingestion captures data, processing changes it, storage keeps it, and serving delivers it. Orchestration, monitoring and security run across all four.
Ingestion Layer: Capture and Land Source Data
Ingestion moves data out of source systems and into a landing zone the pipeline controls. Sources include operational databases, SaaS APIs, files dropped by partners and event streams from applications or devices. Our guide to data ingestion methods covers the mechanics of each source type in more depth.
The design choice here is how you detect what changed. Full extracts are simple but grow slower every month. Incremental extracts use a timestamp or ID column to pull only new rows, though they miss deletes and rows whose timestamp was never updated.
Change data capture (CDC) reads the database’s own change log instead. Debezium’s architecture documentation shows the common setup. A MySQL connector reads the binlog, a PostgreSQL connector reads a logical replication stream, and each table’s changes land in a Kafka topic.
CDC captures inserts, updates and deletes in order, so it suits sources where history and deletes matter.
Whatever the method, land the data as it arrived. Keep the raw payload, the source name, the extract time and a batch or offset ID with every record. That metadata turns later debugging from guesswork into a query.
Processing Layer: Transform and Prepare Data
Processing is where data gets cleaned, standardized, joined, enriched and aggregated. The main architecture decision is where the transformation runs.
ETL transforms data before loading it, while ELT loads raw data first and transforms it inside the warehouse or lakehouse. Our comparison of ETL vs ELT walks through that trade-off.
ETL is only one shape a pipeline can take, as our article on data pipeline vs ETL explains. For architecture purposes, two other choices matter more. The first is incremental processing versus full recomputation, and the second is how you split transformation into stages.
Incremental processing touches only new or changed data, so it scales well. But it needs reliable change tracking and careful handling of late records. Full recomputation is slower and costlier, yet it is simple and self-correcting, so it remains a sensible default for small reference tables.
Storage Layer: Preserve Raw and Processed States
Storage holds three kinds of state, which are raw data as it arrived, intermediate cleaned data, and curated outputs. Teams usually pick a data lake, a warehouse or a lakehouse that combines both. Our article on data lake vs lakehouse lays out the trade-offs.
Two storage rules carry most of the reliability weight. Raw data should be immutable and retained long enough to rebuild any output you might need to fix. Tables should also use a format with transactional writes, such as Delta Lake or Apache Iceberg. Then a failed job never leaves a half-written table behind.
Checklist
Data Integration Checklist
A practical checklist for connecting sources, landing raw data, validating schemas and keeping integrated data trustworthy before it reaches the warehouse or lakehouse.
Get the Checklist → Serving Layer: Deliver Data to Consumers
Serving shapes data for the people and systems that use it. That means dimensional tables and semantic models for BI, plus feature tables for machine learning. Applications may need low-latency stores or APIs, and sales tools sometimes get data back through reverse ETL .
Workload isolation is the design concern most teams miss here. Heavy transformation jobs should not run on the same compute that answers dashboard queries at 9 a.m. Some platforms also reduce copying at this layer. Microsoft’s OneLake overview describes one copy of data used by multiple analytical engines without duplication.
The Cross-Cutting Planes
Some concerns belong to no single layer. Orchestration decides what runs, when and in what order. Observability tracks freshness, volume, schema and lineage, while security and governance control who can read what, and where sensitive fields get masked.
The table below summarizes each layer and what goes wrong when a design skips it. It also works as a quick review sheet for an existing pipeline.
Table 1: The Four Layers of a Data Pipeline Architecture
Layer Job Main design choices What breaks if it is skipped
Ingestion Capture source data and land it unchanged Full, incremental or CDC; push or pull; landing format and metadata Missed deletes, no audit trail, sources hit repeatedly by every job Processing Clean, join, enrich and aggregate ETL or ELT; incremental or full; stage boundaries Business rules scattered across dashboards and scripts Storage Keep raw, cleaned and curated states Lake, warehouse or lakehouse; table format; retention No way to replay history after a logic bug Serving Deliver shaped data to BI, apps and AI Semantic models, feature tables, APIs, reverse ETL Slow dashboards and every team building its own copy Orchestration and observability Run, sequence and watch every layer Triggers, dependencies, state, alerts, lineage Silent failures found by business users first
Layers stay clean only when the hand-offs between them are explicit. Teams with dozens of sources often bring in data engineering services at this stage, before the job count grows.
Kanerika Service
Data Engineering Services
Kanerika designs and builds data pipelines with clear ingestion, processing, storage and serving layers. Each boundary gets a written contract, and recovery rules make reruns safe.
Explore Data Engineering Contracts at Every Layer Boundary
A data contract is a written agreement between the team that produces a dataset and the teams that consume it. It names the schema, the meaning of each field, the delivery schedule, the owner and what counts as a breaking change. In a pipeline, every layer boundary is a place where such a contract should exist, even if it starts as a short document.
Contracts matter because source systems change without warning. A product team renames a column, an API starts returning nulls, or a partner adds a field to a CSV file. Ingestion keeps running, so nothing looks broken, but every downstream number is now slightly wrong.
A few habits turn contracts from paperwork into protection. Validate the schema at ingestion and stop or quarantine data that breaks it. Version contracts the way you version APIs, so consumers can move on their own schedule. Then record ownership, so an alert at 3 a.m. reaches a person who can act on it.
Contracts define what correct data looks like, while tests prove the data still meets that definition. Our guide to data integrity testing covers how to design those checks. This article stays with where they sit in the architecture.
Data Pipeline Architecture Patterns: Batch, Streaming, Lambda, Kappa and Medallion
Pattern names get mixed together in most discussions, which makes the choice look harder than it is. Batch and streaming describe how often data moves. Lambda and kappa describe how many processing paths you run, while medallion describes how stored data is organized.
Because these are three separate decisions, a real design usually combines them. A pipeline can be streaming, kappa-style and medallion-layered all at once. Another can be batch with medallion layers and no streaming at all. The types of data pipelines describe what a pipeline does, whereas these patterns describe how it is built.
Batch Architecture for Scheduled Processing
Batch pipelines collect data over a window and process it on a schedule, such as hourly, nightly or weekly. They suit reporting, finance close, historical analysis and large transformations where minutes of delay do not matter. Much of enterprise analytics still runs this way, and for good reason.
Batch is also the easiest pattern to reason about and to recover. A failed run can simply be rerun for the same window, and each run has a clear start, end and output. The cost is latency, since data is only as fresh as the last completed run.
Streaming Architecture for Continuous Processing
Streaming pipelines process events as they arrive, usually within seconds. They fit fraud checks, inventory updates, operational monitoring, personalization and any case where acting late has a real cost. The usual building blocks are a durable event log such as Kafka or a cloud event hub, plus a stream processor.
Streaming changes the engineering problem in two ways. First, the processor must keep state, such as running totals or open sessions, and that state must survive restarts. Second, events arrive late and out of order, so the design needs rules for when a time window is complete.
Platforms now package much of this. For example, Real-Time Intelligence in Microsoft Fabric combines event ingestion, a query engine and alerting in one service. Even so, the operating burden stays higher than batch, because a streaming job has no natural end where you can check its work.
Lambda Architecture for Separate Batch and Speed Paths
Lambda architecture runs two paths side by side. A batch layer periodically recomputes accurate results from all history, while a speed layer processes recent events quickly to fill the gap. A serving layer then merges both views for queries.
The appeal is that the batch path corrects whatever the speed path gets wrong. The drawback is that you maintain the same business logic twice, often in two different frameworks. Jay Kreps put it bluntly in an O’Reilly essay questioning the lambda architecture . Keeping two systems producing the same result, he wrote, is “exactly as painful as it seems like it would be.”
Kappa Architecture for Stream-First Processing
Kappa architecture, proposed by Kreps in that same essay, drops the batch path. As a result, everything is a stream.
When logic changes, you start a new version of the stream job and replay the retained event log from the beginning. Once it catches up, consumers switch to the new output.
Kappa works when the event log keeps enough history to replay and the team is comfortable operating streaming systems. It struggles when history lives in large historical tables rather than events, or when the replay would take days. In those cases a batch backfill job beside the stream is often the practical compromise.
Medallion Architecture for Progressive Refinement
Medallion architecture organizes stored data into three layers. Microsoft’s medallion architecture guidance describes bronze as raw, silver as validated and gold as enriched. It also calls the pattern a recommended practice, not a requirement. Our deeper guide to medallion architecture covers layer design in detail.
What matters for pipeline design is that medallion is a storage layout, not a processing pattern. Data can flow from bronze to gold in nightly batches, in micro-batches every few minutes or as a continuous stream. So medallion sits alongside batch, streaming, lambda or kappa rather than competing with them.
Event-Driven Triggers Are Not the Same as Streaming
One more distinction saves a lot of confusion. An event-driven pipeline starts a job when something happens, such as a file landing or an upstream table finishing. That job can still be a batch job. Event-driven triggering gives you lower latency than a fixed schedule without the cost of a streaming platform.
Table 2: Data Pipeline Architecture Patterns Compared
Pattern What it decides Best suited for Main strength Main trade-off
Batch Execution timing Reporting, finance, history, large transforms Simple runs and easy reruns Data only as fresh as the last run Streaming Execution timing Fraud, inventory, monitoring, personalization Results in seconds State, late events and always-on cost Lambda Number of processing paths Fast results that must later be corrected from full history Batch path repairs speed-path errors Same logic built and run twice Kappa Number of processing paths Event-centric systems with a replayable log One code path for live and historical data Depends on long log retention and streaming skills Medallion Storage layout Lakehouse and warehouse refinement Clear raw-to-curated boundaries and replay from bronze Extra storage and processing hops
How to Choose a Data Pipeline Architecture
Choosing a data pipeline architecture comes down to three questions asked in order. How fresh must each output be? Do you need fast results and full-history corrections at the same time? And how should stored data be layered for the people who use it?
Watch on YouTube
Why Is Real-Time Data Important for Faster Decision-Making?
A short Kanerika explainer on when fresher data really changes a decision, the question that should set how fast each pipeline output needs to be.
Answer them per output, not per company. A single platform often has a nightly finance mart, an hourly operations dashboard and one streaming fraud feed. Treating all three the same way either overspends on the slow ones or underserves the fast one.
Table 3: Decision Rules for Choosing a Pipeline Pattern
If this is true Choose Why
Users act on data hours or days old Batch, often event-triggered Lowest cost and simplest recovery Freshness target is 5 to 15 minutes Micro-batch or incremental CDC loads Near-real-time without always-on stream state A decision loses value within seconds Streaming Only streaming meets the deadline Streaming results must be recomputed from years of tables Lambda, or kappa plus a batch backfill job The log alone cannot hold that history All history lives in a replayable event log Kappa One code path to build and test Many teams consume the same data at different quality levels Medallion layers on any of the above Shared raw and cleaned layers, many gold outputs
Two Biases That Skew the Choice
We see two biases in almost every design review. The first is building streaming because it feels modern, then finding that the dashboard it feeds refreshes once an hour. The second is the opposite. A team forces a nightly batch onto a use case that needs answers in minutes, then patches it with ever more frequent reruns.
Skills and platform matter as much as latency. If the team already runs a lakehouse with good incremental processing, micro-batches every few minutes cover most “real-time” requests. Streaming should be the exception you justify, not the default you start from.
Orchestration: The Control Plane of a Pipeline
Orchestration is the control room of the water network. It does not move data itself. Instead, it decides which task runs, when it runs, what it waits for and what happens when it fails.
Our roundup of data orchestration tools compares products, so this section stays with the design decisions.
Dependencies and DAGs
Most orchestrators model a pipeline as a directed acyclic graph, or DAG, where each task runs after the tasks it depends on. Apache Airflow’s documentation on DAGs describes the orchestrator’s concern as how to execute tasks, meaning the order, the retries and the timeouts.
Good DAG design runs independent ingestion tasks in parallel, then joins them only where a transformation truly needs both inputs. It also keeps each task small enough to rerun alone. A single giant task that ingests, transforms and publishes makes every failure a full restart.
Scheduled, Event-Driven and Data-Aware Triggers
A schedule is the simplest trigger, but it guesses when upstream data is ready. Event triggers start work when a file lands or a message arrives. Data-aware triggers go one step further and start a task when the specific tables it reads have been updated.
Mixing triggers is normal. For example, a nightly schedule can start ingestion, while each downstream model waits on its own inputs rather than a fixed clock time. That removes the classic failure where a 6 a.m. job reads yesterday’s data because the 5 a.m. load ran late.
State: Run IDs, Offsets, Watermarks and Checkpoints
Every run should record what it processed. That means a run ID, the source offsets or timestamps it read up to, the watermark it reached and the output version it committed. With that state stored outside the job, a restart resumes from the last committed point instead of guessing.
Stream processors handle part of this through checkpoints. Batch pipelines need the same discipline written into their metadata tables. Either way, recovery is only as good as the state you saved before the failure.
Keep Transformation Logic Out of the Orchestrator
The orchestrator should call transformation code, not contain it. When business rules live inside orchestration scripts, they cannot be tested on their own, reused by another pipeline or moved to a new platform. Keep SQL models, notebooks and functions in version control, and let the orchestrator only sequence them.
This split also makes platform moves cheaper. Teams moving from legacy tools to Microsoft Fabric data pipelines find that clean, separate transformation code moves quickly. Logic buried in package configuration is where most of the migration effort goes.
Case Study
40% Lower Costs: SSIS to Microsoft Fabric Migration
An enterprise moved complex SSIS data pipelines to Microsoft Fabric. Data processing got 30% faster, operational costs fell 40% and manual maintenance effort dropped 25%.
Read the Case Study → Failure Handling, Idempotency and Replay
Every pipeline fails eventually, however well it is built. Sources go offline, credentials expire, files arrive malformed and clusters run out of memory. What separates a reliable data pipeline architecture from a fragile one is whether those failures stay contained and whether rerunning the job is safe.
Classify the Failure Before Choosing the Response
Different failures need different responses, so the design should sort them first. Transient failures, such as timeouts or throttling, usually succeed on retry. Permanent failures, such as a missing table or bad credentials, need a person, so retrying them only delays the alert.
Bad data is a third class. A single malformed record should not stop a million good ones, but a file where every record is malformed probably should. Define the threshold in the contract, then let the pipeline route records accordingly.
Idempotent Writes
An idempotent operation produces the same result whether it runs once or five times. In a pipeline, that means a retried or rerun job must not create duplicate rows or double-count revenue. AWS’s guidance on timeouts, retries and backoff with jitter makes the same point for services, since only idempotent calls can be safely retried.
In practice, idempotent writes come down to a handful of habits. Use stable business keys or event IDs instead of generated row numbers. Write with MERGE or upsert logic rather than blind appends. For partitioned tables, overwrite the whole partition for a given date instead of appending to it.
The Delta Lake documentation on MERGE shows how a single statement can insert, update and delete in one pass.
-- Idempotent upsert of CDC order changes into the silver table.
-- Rerunning this for the same batch gives the same result.
MERGE INTO silver.orders AS t
USING (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY order_id ORDER BY source_lsn DESC) AS rn
FROM bronze.order_changes
WHERE batch_id = :batch_id
) WHERE rn = 1 -- keep the latest change per order
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.source_lsn > t.source_lsn THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT *;The version check in that statement matters as much as the MERGE itself. Because an update only applies when the incoming change is newer, replaying an old batch can never overwrite fresher data.
Retries With Backoff and Jitter
Retries should be bounded and spaced out. Exponential backoff waits longer after each attempt, and jitter adds a random delay so hundreds of tasks do not retry at the same instant. Cap the attempts at a small fixed number, then let the task fail loudly and page someone.
Place retries at the smallest sensible unit. Retrying one API page is cheap, while retrying a whole two-hour job because one page timed out is not.
Quarantine and Dead-Letter Paths
Records that fail validation should go to a quarantine table or dead-letter queue, together with the reason and the run ID. The rest of the batch then continues. A daily review of that table, or an alert when it grows past a threshold, keeps bad data visible instead of silently dropped.
This also isolates failure between stages. A broken transformation should stop its own output from publishing, while upstream ingestion keeps landing data for the eventual rerun. That is the pipeline’s version of the valve that stops one burst main from draining the city.
Replay, Backfills and Atomic Publishing
Replay is the ability to rebuild outputs from retained raw data. It is how you fix a logic bug, recover a corrupted table or answer an auditor’s question about last March. It depends on immutable raw storage, partitioned by date, and on idempotent processing, so rebuilding a partition gives the same answer twice.
Atomic publishing makes sure consumers never see half-finished work. One common approach is write, audit, publish. The job writes to a staging table and runs its checks there, and only then swaps the result into the table people query. If the checks fail, the last good version stays in place.
Plan backfills from day one, too. The first load of a CDC pipeline is usually a full snapshot, followed by a switch to incremental changes. That switch needs a clear starting offset, or rows changed during the snapshot get lost or duplicated.
Late and Out-of-Order Data
In streaming systems, an event’s timestamp and its arrival time can differ by minutes or hours. A mobile app may send events after a phone reconnects, for example. If a pipeline closes a time window too early, those late events either vanish or land in the wrong window.
Watermarks set how long a window waits for stragglers. The Spark Structured Streaming guide explains watermarking as tracking the current event time in the data. That tells the engine when old state can be dropped. Choose the lateness allowance from real arrival data, then decide whether very late events update past results or go to a correction path.
Observability, Lineage and Security as Design Decisions
A pipeline that fails silently is worse than one that fails loudly. The architecture should decide up front which signals each layer emits and who receives the alerts. Adding monitoring after the first incident means learning about problems from a business user, which is the most expensive way to find them.
Four signals cover most needs. Freshness tells you whether data arrived on time, while volume tells you whether the row count looks normal. Schema checks catch added, dropped or retyped columns, and distribution checks catch values that drift, such as a currency field that suddenly holds zeros.
Our guide to data observability explains each signal, and the data pipeline monitoring tools roundup compares products.
Lineage, in turn, is the map that makes those alerts useful. When a gold table goes stale, lineage shows which upstream job or source caused it and which dashboards are affected. Capture it from the orchestrator and the transformation layer automatically, since hand-drawn lineage goes stale within weeks. The data lineage guide covers how to collect it.
Security belongs in the same early design pass. Give each pipeline its own service identity with only the access it needs, and keep secrets in a vault rather than in job code. Mask or tokenize personal data at the silver layer, so gold tables and their many consumers never see raw identifiers. Row-level and column-level rules then apply at serving, where data governance services usually set the policy.
A Worked Data Pipeline Architecture: Order Analytics From Source to Dashboard
Patterns make more sense when they meet a real set of requirements. The example below is illustrative, not a client case. It follows an online home-goods retailer that wants sales dashboards fed by its order system and its product catalog.
The Requirements
Orders live in an operational SQL database and change constantly as customers buy, cancel and return items. The product catalog arrives once a day as a file from a merchandising system. Sales dashboards should be no more than about five minutes behind the order system. Finance needs to restate any past month after a pricing or tax logic change. The data team has strong SQL and Spark skills but no dedicated streaming operations staff. Those requirements already narrow the choice. Five-minute freshness points to incremental CDC loads in micro-batches, not a full streaming stack. The restatement need calls for immutable raw history and idempotent processing. And with several consumers at different quality levels, medallion layers fit the storage side.
The Reference Architecture Diagram
The design uses two ingestion paths into one medallion lakehouse. Order changes flow through CDC into a durable event log, then into bronze every few minutes. The product file lands in bronze once a day. Silver holds cleaned, deduplicated orders joined to the current product data, and gold holds the sales facts and aggregates that dashboards read.
Orchestration spans all of it. A daily trigger handles the catalog, while a micro-batch trigger every few minutes handles orders. Each silver and gold model waits on its own inputs, and every run writes its state to a control table.
The Flow in Six Stages
Capture. A CDC connector reads the order database’s change log and writes each insert, update and delete to an event log, keyed by order ID.Land the catalog. A daily job copies the product file into bronze unchanged, tagged with the file name and load time.Preserve raw history. Bronze appends every change event and file with its source position and batch ID. Nothing in bronze is ever updated.Clean and conform. Silver applies the latest change per order with an idempotent MERGE, validates against the contract, and sends failures to quarantine. It also joins each order line to the product attributes that were valid on the order date.Build business outputs. Gold computes daily sales facts, margin by category and return rates, refreshed incrementally for the affected dates only.Publish. Gold writes to staging, runs its checks, and swaps into the serving tables that the semantic model reads.An AI step can sit inside this flow as well. Free-text return reasons, for instance, can be classified into categories during the silver stage. Our article on Databricks AI functions shows how that works as a SQL call inside the pipeline.
What Happens When a Stage Fails
Suppose the silver job fails halfway through a batch because a new source column breaks the join. The publish step never runs, so dashboards keep showing the last good gold tables and nobody sees half-written numbers.
Meanwhile, CDC capture and bronze keep landing order changes, because ingestion does not depend on silver. The alert reaches the silver owner, who updates the contract and the model. When the job reruns from its last committed offset, the MERGE applies each change exactly once, and gold catches up within a few cycles.
Restating last March works the same way. Finance approves the new tax logic, and the team reruns silver and gold for March partitions from bronze. Because every step is idempotent, the rerun replaces the old figures instead of adding to them.
Mapping the Design to Fabric, Databricks or AWS
The logical design stays the same on any major platform, so only the services change. On Microsoft Fabric , for example, Fabric’s eventstreams include a CDC source connector for Azure SQL Database. Data Factory pipelines then group activities into the end-to-end workflow. The table below maps each layer to typical services on three stacks.
Watch on YouTube
Microsoft Fabric Case Study: FoodPharma Cuts Reporting to 90 Minutes
Kanerika’s Amit Chandak explains how FoodPharma unified six operational systems on Microsoft Fabric with automated daily loads. Cross-functional reporting went from two business days to about 90 minutes.
Table 4: The Worked Architecture Mapped to Three Platforms
Layer Microsoft Fabric Databricks AWS
CDC ingestion Eventstreams CDC connector or database mirroring Kafka or a managed CDC connector, read with Structured Streaming AWS DMS or Debezium on Amazon MSK File ingestion Data Factory copy activity Auto Loader AWS Glue job triggered by an S3 event Bronze, silver, gold storage Lakehouse Delta tables in OneLake Delta tables governed by Unity Catalog Amazon S3 with Apache Iceberg tables Processing Spark notebooks or Dataflow Gen2 Spark jobs or Lakeflow Spark Declarative Pipelines AWS Glue or Amazon EMR Orchestration Data Factory pipelines Lakeflow Jobs Amazon MWAA (managed Airflow) or Step Functions Serving Power BI semantic model Databricks SQL warehouse with a BI tool Amazon Redshift or Athena with Amazon Quick Sight
On Databricks, declarative options also deserve a mention. A DLT pipeline (now Lakeflow Spark Declarative Pipelines) can express the bronze, silver and gold steps with built-in data quality expectations. Whichever stack you choose, keep the six stages, the contracts and the recovery rules identical, and the platform becomes a replaceable part.
Scaling a Data Pipeline Architecture Without Runaway Cost
Pipelines rarely hit a wall because a platform cannot handle the volume. They get slow and expensive because of choices that worked fine at a tenth of the size. A bigger cluster hides that for a quarter or two, and then the bill arrives.
Partitioning comes first, because it decides how much data every job reads. Partition large tables by the column most queries filter on, usually a date, so jobs and dashboards read only what they need. Avoid partitions so small that a table ends up with millions of tiny files, because file overhead then dominates the run time.
Incremental processing is the second lever. Rebuilding a three-year fact table every night is a common source of runaway compute cost. Process only the changed partitions, and keep full rebuilds for small dimensions or rare restatements.
Case Study
80% Faster Data Processing for a Logistics Firm
A global logistics provider integrated CargoWise data into Azure and SQL Server. Data processing time fell 80%, total cost of ownership dropped 48% and data security improved 91%.
Read the Case Study → Isolation is the third lever, and it is often the cheapest. Separate compute for ingestion, transformation and serving lets each scale on its own and stops a heavy backfill from slowing the CEO’s dashboard. For streaming, design for backpressure, so a slow consumer causes the log to buffer rather than the pipeline to drop data.
Finally, measure cost per pipeline, not just per platform. When each job reports its compute time and data scanned, the expensive ones become obvious. Our guide to data pipeline optimization walks through tuning once the architecture is sound.
Data Pipeline Architecture Best Practices and Anti-Patterns
The practices below come up in nearly every architecture review. Use them as a checklist before a new pipeline goes to production, and again when an old one starts to wobble.
Write requirements per output. Record freshness, volume, replay and cost targets for each dataset, signed off by its consumers.Keep raw data immutable. Land everything unchanged with source metadata, and retain it long enough to rebuild any output.Make every write idempotent. Use stable keys, MERGE or partition overwrite, and version checks so reruns are always safe.Publish atomically. Write, check, then swap, so consumers only ever see complete and validated data.Put contracts at boundaries. Validate schemas at ingestion and quarantine what breaks them instead of loading it.Separate orchestration from logic. Keep transformations in version-controlled code that the orchestrator only calls.Design alerts with owners. Every freshness, volume or schema alert should reach a named person who can fix it.Avoid streaming by default. Start with batch or micro-batch, then justify each streaming path by a real deadline.Avoid one giant job. Small, rerunnable tasks turn a failure into a short rerun instead of a full restart.Avoid logic in dashboards. Business rules hidden in reports cannot be tested, reused or traced back through lineage.These habits overlap with general engineering discipline, which is covered more broadly in our data engineering best practices guide. The difference here is that each one maps to a specific layer or boundary in the architecture.
Checklist
Data Engineering Checklist
Review pipeline design, orchestration, testing, monitoring and governance against one checklist before a new data pipeline goes to production.
Get the Checklist → How Kanerika Designs Data Pipeline Architectures
Kanerika’s data engineering teams treat pipeline architecture as a design exercise first and a build exercise second. Through our data integration services and the wider data engineering practice, each engagement is scoped to the client’s platform, data volumes and team structure. Clients pick one of three engagement models.
Pipeline audit and design. Map existing pipelines and gaps, define the target architecture and tooling, and deliver a build-ready technical blueprint.Build and modernize. Rebuild legacy ETL on a cloud-native stack, implement lakehouse and streaming layers, and hand off tested, production-ready pipelines.Ongoing support. Extend the client’s team with dedicated engineers who monitor pipeline health and data quality and ship new workstreams as priorities shift.Migration-heavy work uses the migration accelerators in FLIP, Kanerika’s automation platform , which automate discovery, conversion and validation of legacy pipelines. That frees engineers to spend their time on the design decisions above rather than on rewriting packages by hand.
What the Results Look Like
The results show up in delivery numbers. In a Microsoft customer story, FoodPharma worked with Kanerika to build a foundation layer on Fabric. The team consolidated more than 50 tables and about 1 TB of history from six systems, with automated daily loads.
Cross-functional reporting dropped from two business days to about 90 minutes, as the FoodPharma Fabric reporting story describes.
In another engagement, moving an enterprise’s SSIS pipelines to Fabric improved processing speed by 30% and cut operational costs by 40%. Manual maintenance effort also fell by 25%, according to the SSIS to Microsoft Fabric case study .
Pitfalls Our Teams Watch For
A few pitfalls show up again and again in this work. Teams underestimate the CDC cut-over from initial snapshot to incremental changes. They skip the quarantine path and lose bad records silently. And they let business logic spread into dashboards, which makes later migrations far slower than they need to be.
Talk to Kanerika
Review Your Data Pipeline Architecture With Kanerika
Bring one business-critical pipeline and walk through its layers, patterns, contracts and failure paths with Kanerika’s data architects before you scale it.
Book a Working Session → Draw the Network Before You Lay the Pipe
A good data pipeline architecture looks a lot like that city water network. It has clear layers for ingestion, processing, storage and serving, plus an orchestration control room. Valves keep a single failure contained, and a raw reservoir lets you replay history when logic changes.
So pick the pattern per output. Use batch or micro-batch by default and streaming only where seconds matter. Choose kappa when the log holds your history, and use medallion layers to organize what you store. Then make every write idempotent, so the network can be repaired without draining the city.
Frequently Asked Questions
What is data pipeline architecture?
Data pipeline architecture is the design that decides how data is captured from sources, transformed, stored and delivered to the people and systems that use it. It also sets how the pipeline recovers from failure. It covers the layers, the processing pattern, orchestration, data contracts and the rules for retries and replay.
What are the main layers of a data pipeline architecture?
Most designs have four layers. Ingestion captures data from databases, APIs, files and event streams. Processing cleans, joins and aggregates it, storage keeps raw and curated versions, and serving delivers it to dashboards, applications and AI models. Orchestration, monitoring and security run across all four layers rather than sitting in one.
How do you design a data pipeline architecture?
Start by writing down freshness, volume, replay, cost and skills requirements for each output. Then choose a processing pattern per output, define the four layers and the contracts between them, and design orchestration and failure handling. Finally, build one representative pipeline end to end and prove that reruns and recovery work before scaling.
What is the difference between batch and streaming pipeline architecture?
Batch pipelines process data in scheduled windows, such as hourly or nightly, so they are simpler and cheaper but only as fresh as the last run. Streaming pipelines process events within seconds of arrival. They need durable state, late-data rules and always-on compute, so use them only where a decision truly loses value quickly.
How do you choose between lambda and kappa architecture?
Choose lambda when you need fast streaming results and also must recompute them from large historical tables that are not kept as events. Choose kappa when all history lives in a replayable event log and your team can run streaming well. Kappa avoids maintaining the same logic twice, which is lambda’s main cost.
Can medallion architecture support real-time data pipelines?
Yes. Medallion architecture is a storage layout of bronze, silver and gold layers, not a processing schedule. Data can move between those layers in nightly batches, in micro-batches every few minutes or as a continuous stream. Many lakehouse platforms support incremental and streaming reads between Delta or Iceberg tables for exactly this purpose.
What is the role of orchestration in a data pipeline?
Orchestration decides which tasks run, when they run, what they wait for and what happens if they fail. It manages dependencies, triggers, retries, timeouts and run state. Good orchestration calls transformation code kept in version control rather than containing business logic itself, which keeps that logic testable and easier to move between platforms.
How do you make a data pipeline idempotent?
Use stable business keys or event IDs instead of generated row numbers. Write with MERGE or upsert logic, or overwrite whole partitions, rather than appending blindly. Add a version or timestamp check so older changes never overwrite newer ones. With those rules in place, rerunning the same batch produces the same result every time.
How should a data pipeline handle failed records and stages?
Classify the failure first. Retry transient errors with bounded exponential backoff and jitter, and alert a person for permanent ones. Send malformed records to a quarantine table with the reason and run ID while good records continue. Publish outputs atomically, so a failed stage leaves the last good version visible to users.
How do you handle late-arriving data in streaming pipelines?
Process by event time rather than arrival time, and set a watermark that defines how long a time window waits for late events. Base the allowance on measured arrival delays. Events later than the watermark either update past results through a correction path or go to a separate table for review, depending on the use case.
How do you support historical replay in a data pipeline?
Keep raw data immutable and retained, usually partitioned by date in a bronze layer or a long-retention event log. Make every downstream step idempotent and record the offsets each run processed. To replay, rerun the affected partitions from raw data, and the outputs are rebuilt with the new logic without creating duplicates.
What should a data pipeline architecture diagram include?
A useful diagram shows source systems, ingestion methods such as CDC or batch files, the storage layers, processing steps and serving outputs. It should also show orchestration across the flow, where state and checkpoints are stored, quarantine and retry paths, and which teams own each boundary. Without the recovery paths, a diagram describes only the happy path.
