Cloud & Automation
Design Patterns for Data Engineering: Practical Patterns for Reliable Data Platforms
A practical guide to data engineering design patterns for ingestion, transformation, orchestration, storage, reliability, and serving.
On this page
When developers hear design patterns, they often think about software patterns such as Factory, Strategy, Observer, or Repository.
Those patterns can still be useful in data systems, but data engineering has its own recurring problems:
- ingesting data from unreliable sources;
- processing the same file or event more than once;
- detecting only what changed;
- separating raw data from trusted data;
- handling bad records without stopping an entire pipeline;
- retrying transient failures safely;
- rebuilding downstream tables without corrupting history;
- serving the same dataset to different consumers;
- keeping transformations understandable as a platform grows.
These problems repeat across technologies such as SQL Server, Microsoft Fabric, Spark, Delta Lake, Kafka, APIs, warehouses, and lakehouses.
That makes them good candidates for patterns.
A design pattern is not a product feature and it is not a mandatory architecture. It is a reusable solution shape for a problem that appears repeatedly.
This article focuses on practical patterns that can be applied across data platforms.
Data patterns are not the same as software design patterns
A software pattern usually describes how classes or objects collaborate.
A data engineering pattern often describes how data moves, changes, fails, recovers, and becomes consumable.
- Source
- Raw
- Validated
- Curated
- Serving
That is not an object-oriented design pattern. It is still a pattern because the same separation solves the same class of problem across many systems.
The most useful data patterns tend to fall into six groups:
- ingestion;
- transformation;
- orchestration;
- storage and modeling;
- reliability;
- serving and integration.
The important part is not memorizing names. The important part is recognizing the problem each pattern solves.
1. Idempotent processing
If I had to choose one pattern that every production data engineer should understand, it would be idempotency.
An operation is idempotent when running it again with the same input produces the same final state.
That matters because pipelines fail.
A job can time out after writing data, lose its acknowledgment, be retried by an orchestrator, receive the same file twice, receive a duplicated event, or be manually rerun.
Without idempotency, retries can create duplicate data.
A stable business or ingestion key can be based on:
source_file + source_row_id
or:
event_id
or:
tenant_id + order_id + event_timestamp
Then enforce the rule that the same logical input cannot create another logical copy.
The exact implementation can be a unique constraint, MERGE, INSERT ... WHERE NOT EXISTS, Delta merge, or a deduplication step.
The pattern is the important part:
A retry must be safe.
Do not confuse technical uniqueness with business uniqueness. A generated row ID does not prevent duplicate business events if each retry generates a new ID.
2. Incremental load
A full reload is simple:
- Delete everything
- Reload everything
It can also become extremely expensive.
The incremental-load pattern processes only records that are new or changed since the previous successful run.
Common change indicators include:
updated_at;- increasing identity;
- source sequence number;
- change tracking;
- change data capture;
- transaction-log position;
- file arrival time;
- watermark.
A common watermark flow looks like this:
- 1Read last successful watermark
- 2Read source rows after watermark
- 3Transform
- 4Write destination
- 5Commit new watermark
The key word is successful. The watermark should advance only after the corresponding data is safely committed.
A timestamp alone can also be dangerous. If several records share the same timestamp, a safer watermark may include a tie-breaker such as (updated_at, primary_key).
3. Change Data Capture
Incremental loads answer: What changed since my previous run?
Change Data Capture, or CDC, goes further by representing source changes as a stream of inserts, updates, and deletes.
INSERT customer 42
UPDATE customer 42
DELETE customer 17
This is useful for operational replication, near-real-time analytics, event-driven architectures, lakehouse ingestion, and keeping serving stores synchronized.
CDC also introduces new engineering responsibilities:
- ordering;
- duplicates;
- deletes;
- schema changes;
- checkpoints;
- replay;
- late events.
CDC does not remove complexity. It moves change detection closer to the source and makes change history explicit.
4. Raw → validated → curated layers
One of the most common platform patterns is separating data by level of trust.
The exact names vary:
- Raw
- Clean
- Curated
- Bronze
- Silver
- Gold
- Landing
- Standardized
- Serving
The names matter less than the responsibilities.
The raw layer preserves what arrived. The validated layer normalizes types, applies quality checks, deduplicates, and standardizes keys. The curated layer shapes data for analytics, APIs, reports, machine learning, or domain consumption.
This separation provides something extremely valuable: replayability.
If a transformation rule changes, you can often rebuild trusted layers from the preserved raw data instead of asking the source to resend history.
Do not create three layers because a diagram told you to. If each layer simply copies the previous layer without adding responsibility, you created overhead rather than architecture.
5. Quarantine / dead-letter pattern
Production data is rarely perfect.
A fragile pipeline treats one bad row like this:
- 1 invalid row
- Entire batch fails
A more resilient pattern separates invalid records.
- Incoming records
- Validate
- validTarget
- invalidQuarantine
The quarantine record should contain enough information to investigate the failure:
original payload
error reason
validation rule
source
run ID
timestamp
For event systems, the equivalent is often called a dead-letter queue.
A quarantine table must not become a data graveyard. It needs monitoring, ownership, retention, and retry or remediation rules.
6. Retry with exponential backoff
Transient failures are normal in distributed systems:
- HTTP 429;
- HTTP 503;
- temporary database connection failure;
- storage throttling;
- short network interruption.
Immediate repeated retries can make the situation worse.
Instead:
- Failure
- wait 1sRetry
- wait 2sRetry
- wait 4sRetry
A random component, or jitter, is often added so thousands of workers do not retry at exactly the same instant.
Retries should normally be combined with idempotency. Otherwise a request can succeed, the response can be lost, and the retry can create a duplicate write.
Retry + idempotency + bounded attempts is the stronger pattern.
7. Pipeline / staged-processing pattern
Large transformations become difficult to reason about when every rule lives in one huge notebook, stored procedure, or Python function.
A pipeline pattern breaks processing into stages:
- Extract
- Parse
- Validate
- Normalize
- Enrich
- Publish
Each stage has a defined responsibility and contract.
records = extract(source)
records = parse(records)
records = validate(records)
records = normalize(records)
records = enrich(records)
publish(records)
Benefits include easier testing, clearer failures, reusable stages, better observability, and simpler ownership.
Logical stages do not automatically need to become separate network services.
8. Strategy pattern for source-specific behavior
A data platform may start with one CSV feed and eventually need to support:
CSV
JSON
SFTP
API
Database
Eventually code can become a long chain of if/elif statements.
This is where the classic Strategy pattern becomes useful.
Define common behavior:
class SourceReader:
def read(self):
raise NotImplementedError
Then implement source-specific strategies:
class CsvReader(SourceReader):
...
class ApiReader(SourceReader):
...
class SftpReader(SourceReader):
...
The orchestration depends on the interface rather than the source details.
This is especially useful for multi-tenant platforms where tenants use different transports or formats but share the same ingestion lifecycle.
9. Adapter pattern for external systems
External systems rarely expose exactly the contract your platform wants.
One API may return:
{
"customerNumber": 42,
"created": "2026-10-09T14:00:00Z"
}
while another returns:
{
"id": 42,
"created_at": "2026-10-09 14:00:00"
}
Do not let source-specific contracts spread throughout the platform.
Use an Adapter:
- External API
- Adapter
- Canonical customer contract
The adapter translates field names, dates, null semantics, status values, authentication behavior, and source-specific errors.
Downstream transformations operate on the canonical model.
10. Fan-out / fan-in
Sometimes one input must trigger several independent operations.
- Input
- Parse
- Quality metrics
- Main transformation
- Audit
- Notifications
That is fan-out.
Later, results may be brought together:
- Task A
- Task B
- Task C
- Final publish
That is fan-in.
This pattern is useful for processing multiple partitions or files, generating several report formats, calculating independent aggregations, or writing to several downstream stores.
Fan-out introduces coordination. You must decide what one failed branch means, whether successful branches remain committed, how retries work, and how completion is detected.
11. Snapshot + history pattern
Sometimes a consumer wants the current state:
Customer 42
status = Active
country = US
Other consumers need to know how that state changed.
A useful pattern is to maintain both a current table and history, or model history using effective dates:
customer_id
status
valid_from
valid_to
is_current
This is closely related to Slowly Changing Dimension patterns.
The broader idea is:
Separate “what is true now?” from “what was true at a point in time?”
This is useful for customer attributes, merchant configuration, pricing rules, organizational hierarchy, account status, and reference data.
12. Materialized serving pattern
A common mistake is forcing every consumer to execute complex transformations at request time.
- UI request
- Request-time computation
- 12 joins
- 3 large aggregations
- window functions
- Response
The materialized serving pattern moves expensive reusable work earlier.
- Raw data
- Transform
- Curated / materialized result
- API / dashboard / report
Possible implementations include aggregate tables, materialized views, Gold tables, pre-rendered Parquet, cached report results, and search indexes.
The tradeoff is freshness.
Do not ask only, Should this be precomputed?
Ask:
Is the result expensive enough, repeated enough, and stable enough that precomputation is cheaper than recomputation?
Configuration-driven processing
As data platforms grow, hardcoding every source often creates hundreds of nearly identical pipelines.
Instead of:
Pipeline A for tenant A
Pipeline B for tenant B
Pipeline C for tenant C
you can use:
Generic ingestion engine
+
configuration
Example:
{
"tenant": "tenant-a",
"source_type": "sftp",
"format": "csv",
"delimiter": ",",
"encoding": "windows-1252",
"date_format": "%Y-%m-%d",
"target": "sales"
}
The configuration selects strategies rather than embedding every decision in orchestration code.
This works particularly well when differences are parameters.
It works poorly when tenants have fundamentally different business processes and configuration becomes a programming language disguised as JSON.
Patterns that are commonly combined
Some combinations are especially useful.
Incremental load + watermark + idempotency
- Watermark
- Read changes
- Idempotent merge
- Commit watermark
CDC + deduplication + ordering
- CDC stream
- Order / checkpoint
- Deduplicate
- Apply change
Retry + backoff + circuit breaker
Useful when calling unstable downstream systems. A circuit breaker temporarily stops making calls after repeated failures instead of continuously hammering an unhealthy dependency.
Raw layer + quarantine + replay
Raw preserves evidence, quarantine isolates problems, and replay makes recovery possible.
Strategy + Adapter + configuration
Useful for multi-source or multi-tenant ingestion engines.
When a pattern becomes overengineering
Patterns are useful because they give names to proven structures.
They become harmful when teams apply them without the corresponding problem.
- Do not implement CDC for a 5,000-row table loaded once per day if a full read costs almost nothing.
- Do not build five data layers when two have clear responsibilities.
- Do not introduce a message bus just because asynchronous systems are fashionable.
- Do not build a generic plugin framework for two fixed file formats.
- Do not create a configuration engine so flexible that nobody can understand what it will execute.
- Do not introduce event sourcing when simple history tables solve the requirement.
The best architecture is not the architecture with the most patterns.
It is the one where each pattern removes a specific operational or maintenance problem.
A practical selection guide
| Problem | Pattern to consider |
|---|---|
| Retries create duplicate data | Idempotency |
| Full loads are too expensive | Incremental load |
| Need database-level change stream | CDC |
| Need replay and auditability | Raw → validated → curated |
| Bad rows stop whole batch | Quarantine / dead-letter |
| Temporary remote failures | Retry + exponential backoff |
| Transformation is one huge block | Pipeline / staged processing |
| Multiple source behaviors | Strategy |
| External schemas leak everywhere | Adapter |
| Independent work can run in parallel | Fan-out / fan-in |
| Need current and historical values | Snapshot + history / SCD |
| Complex queries repeat constantly | Materialized serving |
| Hundreds of nearly identical pipelines | Configuration-driven processing |
What I would standardize on a data platform
If I were defining engineering standards for a production data platform, I would expect most pipelines to answer these questions:
Identity: What uniquely identifies the input?
Replay: Can the pipeline safely run again?
Incrementality: How does it know what changed?
Quality: Where do invalid records go?
State: Where are watermarks and checkpoints stored?
Observability: Can I identify the run, tenant, source, start/end time, rows read, rows written, rejected rows, error, and retry?
Recovery: What happens after a partial failure?
Serving: Is the consumer reading raw transformations or a deliberate serving model?
These questions are more valuable than asking which technology the team used.
A pipeline can use the newest platform in the market and still be fragile if these concerns are missing.
Design patterns make architecture easier to discuss
One underrated benefit of patterns is communication.
Compare:
We have a process that stores the original file, validates records, puts invalid rows somewhere else, retries temporary API failures, and prevents duplicates.
with:
The ingestion is replayable and idempotent, uses a raw landing layer, quarantines invalid records, and retries transient calls with exponential backoff.
The second description compresses a lot of architectural information.
That shared vocabulary becomes useful during architecture reviews, code reviews, incident analysis, onboarding, platform standards, and design documents.
Patterns are not just implementation techniques. They are also a language for engineering decisions.
Final thoughts
Good data architecture is rarely about finding one perfect platform.
It is about handling recurring problems predictably.
Data arrives twice. Sources change. APIs fail. Files are malformed. Jobs are retried. Business rules evolve. Consumers want both fresh data and fast responses.
The technologies will keep changing, but those problems will continue to appear.
That is why patterns such as idempotency, incremental loading, CDC, layered processing, quarantine, retry, Strategy, Adapter, fan-out/fan-in, history modeling, and materialized serving remain useful.
The strongest data platforms are not the ones with the most complicated diagrams.
They are the ones where common failure modes have already been anticipated—and where the solution is repeatable instead of reinvented in every pipeline.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- ArticleData Integration Patterns: A Practical Guide for Modern Data PlatformsA practical guide to batch, ETL, ELT, CDC, streaming, replication, APIs, files, federation, zero-copy, reverse ETL, synchronization, and orchestration.Read article
- Cheat sheetFabric Medallion ArchitectureDesign a practical Bronze, Silver, and Gold architecture in Microsoft Fabric with clear data contracts, replayability, incremental processing, quality gates, and serving responsibilities.Open cheat sheet
- ResourceMicrosoft Fabric Certification Study PlannerAn adaptable 6–8 week example plan that connects official Fabric certification objectives with hands-on practice, review, and exam readiness.View resource
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.