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.

By JaviPublished 16 min readIntermediate

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.

  1. Source
  2. Raw
  3. Validated
  4. Curated
  5. Serving
From source to 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:

  1. ingestion;
  2. transformation;
  3. orchestration;
  4. storage and modeling;
  5. reliability;
  6. 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:

  1. Delete everything
  2. Reload everything
Full reload

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:

  1. 1Read last successful watermark
  2. 2Read source rows after watermark
  3. 3Transform
  4. 4Write destination
  5. 5Commit new watermark
Watermark-based incremental load

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:

    1. Raw
    2. Clean
    3. Curated
    1. Bronze
    2. Silver
    3. Gold
    1. Landing
    2. Standardized
    3. Serving
Common names for the three layers

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. 1 invalid row
  2. Entire batch fails
Fragile: one bad row fails the batch

A more resilient pattern separates invalid records.

  1. Incoming records
  2. Validate
    • valid
      Target
    • invalid
      Quarantine
Quarantine pattern

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:

  1. Failure
  2. wait 1s
    Retry
  3. wait 2s
    Retry
  4. wait 4s
    Retry
Retry with exponential backoff

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:

  1. Extract
  2. Parse
  3. Validate
  4. Normalize
  5. Enrich
  6. Publish
Staged processing

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:

  1. External API
  2. Adapter
  3. Canonical customer contract
Adapter for an external system

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.

  1. Input
  2. Parse
    • Quality metrics
    • Main transformation
    • Audit
    • Notifications
Fan-out

That is fan-out.

Later, results may be brought together:

    • Task A
    • Task B
    • Task C
  1. Final publish
Fan-in

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.

  1. UI request
  2. Request-time computation
    • 12 joins
    • 3 large aggregations
    • window functions
  3. Response
Anti-pattern: computing at request time

The materialized serving pattern moves expensive reusable work earlier.

  1. Raw data
  2. Transform
  3. Curated / materialized result
  4. API / dashboard / report
Materialized serving

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

  1. Watermark
  2. Read changes
  3. Idempotent merge
  4. Commit watermark
Incremental load with watermark and idempotency

CDC + deduplication + ordering

  1. CDC stream
  2. Order / checkpoint
  3. Deduplicate
  4. Apply change
CDC with ordering and deduplication

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.

Tags

  • Data Architecture
  • Data Pipelines
  • Design Patterns