Microsoft Fabric

Data Integration Patterns: A Practical Guide for Modern Data Platforms

A practical guide to batch, ETL, ELT, CDC, streaming, replication, APIs, files, federation, zero-copy, reverse ETL, synchronization, and orchestration.

By JaviPublished 8 min readIntermediate

Data integration is not one pattern. A modern platform may run nightly file loads, replicate database changes every few seconds, consume events continuously, call APIs on demand, expose external data without copying it, and push curated results back into operational applications.

The key architecture question is not Which tool should we use? It is:

Which integration pattern best fits the latency, scale, source behavior, governance, ownership, and consumer requirements of this data flow?

This guide explains the major integration patterns and how they map to Microsoft Fabric and other common technologies.

For a focused comparison of the four most common choices and concrete Fabric implementations, see ETL vs ELT vs CDC vs Streaming.

Integration patterns at a glance

Pattern Best for Typical latency Core idea
Batch Scheduled processing Hours / days Process data in windows
Full load Small/simple datasets Scheduled Reload everything
ETL Governed pre-processing Batch Transform before loading
ELT Lakehouse/warehouse analytics Batch / near-real-time Load first, transform later
ETLT Mixed governance + analytics Batch Transform before and after load
Incremental Large changing sources Minutes / hours Move only new/changed rows
CDC Operational DB changes Seconds / minutes Capture inserts, updates, deletes
Replication / Mirroring Analytical copy of operational data Near-real-time Keep a copy synchronized
Streaming Events and telemetry Seconds or less Process continuously
Event-driven Decoupled system reactions Near-real-time Events trigger actions
API-based On-demand integration Request-time Request data when needed
File-based Partner/batch exchange Batch Exchange files as contracts
Messaging Reliable async work Seconds / minutes Queue messages between systems
Federation Cross-system querying Query-time Query data without centralizing it
Zero-copy Shared external data Query-time Reference instead of copy
Reverse ETL Analytics back to operations Scheduled / near-real-time Activate curated data
Synchronization Keep systems aligned Continuous / scheduled Propagate state between systems

1. Batch integration

Batch processes data on a schedule.

  1. Source
  2. Every hour / day / week
  3. Batch pipeline
  4. Destination
Batch integration

Use it for nightly finance loads, daily CSV files, partner feeds, scheduled API pulls, and any requirement where low latency is unnecessary.

Batch can still be incremental. A batch job does not have to reload the entire source.

2. Full-load integration

A full load copies the complete source dataset.

  1. Source table
  2. Read all rows
  3. Replace target
Full load

For a small reference table, this can be safer and cheaper than building CDC. The trade-off is that source I/O, network traffic, and target writes grow with the dataset.

3. ETL

ETL means Extract → Transform → Load.

  1. Source
  2. Extract
    Transform
  3. Load
  4. Destination
ETL

ETL is appropriate when sensitive fields must be removed before landing, the destination requires a strict schema, or governance rules must be enforced before storage.

Example:

  1. HR source
  2. Transform
    • Remove SSN
    • Normalize department
    • Validate salary
  3. Warehouse
ETL for HR data

4. ELT

ELT means Extract → Load → Transform.

  1. Source
  2. Raw landing
  3. Lakehouse / Warehouse
  4. Transform
ELT

ELT is common on modern cloud platforms because raw data can be retained and transformed with scalable compute close to storage.

A classic example is Medallion architecture:

  1. Bronze
  2. Silver
  3. Gold
Medallion architecture

In Microsoft Fabric:

  1. Source
  2. Fabric Pipeline
  3. Lakehouse Bronze
  4. Notebook / Spark SQL
  5. Silver
  6. Gold
ELT in Microsoft Fabric

5. ETLT

ETLT means Extract → Transform → Load → Transform.

This hybrid is common when some controls must happen before landing while business transformations happen after.

  1. API payload
  2. Remove secrets / normalize envelope
  3. Bronze
  4. Deduplicate / enrich / apply business rules
  5. Silver / Gold
ETLT

6. Incremental loading

Incremental loading moves only records that changed since the previous run.

A watermark pattern may look like:

SQL query: 6. Incremental loadingT-SQL
SELECT *
FROM orders
WHERE modified_at > @last_watermark;
T-SQL3 linesRead-only query display. It is not connected to a database.

This reduces source load and processing volume, but it depends on reliable timestamps and careful handling of late-arriving records and deletes.

7. Change Data Capture (CDC)

CDC captures actual data changes, typically inserts, updates, and deletes.

  1. Operational DB
  2. Change / transaction log
  3. CDC consumer
  4. Analytical target
Change Data Capture

A watermark asks for rows that look newer. CDC asks for the changes recorded by the source system. That difference matters when deletes and exact ordering are important.

8. Replication and mirroring

Replication keeps a destination synchronized with a source.

  1. Operational DB
  2. Continuous replication
  3. Analytical copy
Replication

This is not the same as ETL. Replication solves synchronization; transformations can happen afterward.

In Microsoft Fabric, Mirroring can replace part of the ingestion logic for supported sources, after which Silver and Gold transformations can continue normally.

9. Streaming

Streaming processes events continuously as they arrive.

  1. Producer
  2. Event stream
  3. Stream processor
  4. Real-time store / action
Streaming

Typical uses include telemetry, clickstream, IoT, fraud detection, operational logs, and real-time alerts.

In Fabric:

  1. Event Hubs / Kafka
  2. Eventstream
  3. Eventhouse
  4. KQL / Dashboard / Activator
Streaming in Microsoft Fabric

Streaming introduces concerns that batch does not: event ordering, late events, duplication, checkpoints, windows, replay, and backpressure.

10. Event-driven integration

Event-driven integration focuses on triggering downstream actions from events.

  1. OrderCreated
  2. Event bus
    • Inventory service
    • Notification service
    • Analytics consumer
Event-driven integration

The goal is decoupling: the producer publishes an event without knowing every consumer.

11. API-based integration

APIs are useful for on-demand enrichment, metadata lookup, operational workflows, and low-volume access.

They are often poor choices for very large analytical extraction because of rate limits, pagination, request overhead, and throttling.

12. File-based integration

Files remain one of the most common enterprise contracts.

Common formats include CSV, JSON, Parquet, Avro, and XML.

  1. Producer
  2. File drop
  3. Storage / SFTP
  4. Pipeline
File-based integration

A robust file pattern handles duplicates, partial files, schema drift, missing deliveries, naming conventions, encoding, and replay.

13. Messaging

Message queues distribute work asynchronously.

Examples include Azure Service Bus, RabbitMQ, and cloud queues.

This differs from event streaming because queues are often optimized for reliable work distribution and consumption rather than replayable event history.

14. Federation and virtualization

Federation allows queries across data that remains in separate systems.

  1. Query engine
    • Source A
    • Source B
    • Source C
Federation

Benefits include less duplication and faster access to new sources. Trade-offs include remote-source performance, availability, security, and cross-system query cost.

15. Zero-copy and shortcut patterns

Zero-copy patterns reference data without physically duplicating it.

  1. External storage
  2. Reference / Shortcut
  3. Analytical platform
Zero-copy access

Microsoft Fabric OneLake Shortcuts are an example. They can reduce duplicate storage and copy pipelines, but the analytical platform remains dependent on the source’s permissions, schema, lifecycle, and availability.

16. Reverse ETL

Reverse ETL moves curated analytical data back into operational applications.

  1. Lakehouse / Warehouse
  2. Customer segment / score
  3. CRM / marketing / support
Reverse ETL

Examples include churn scores, customer segments, product recommendations, and account-health signals.

17. Data synchronization

Synchronization keeps systems aligned.

System A
  ↕
System B

Bi-directional synchronization is particularly difficult because you must define the authoritative system, conflict resolution, delete handling, retries, and offline behavior.

18. Orchestration vs integration

Integration moves or exposes data. Orchestration coordinates tasks.

  1. 1Copy data
  2. 2Run notebook
  3. 3Validate
  4. 4Publish
  5. 5Notify
Orchestration

A Fabric Data Pipeline or Azure Data Factory pipeline may orchestrate multiple integration patterns without itself being the pattern.

19. Push vs pull

Pull means the consumer requests data from the source. Examples include polling an API or querying a database.

Push means the producer sends data. Examples include webhooks, events, message queues, and file drops.

Push often improves latency and decoupling. Pull is often easier to schedule, control, and retry.

20. Synchronous vs asynchronous integration

Synchronous integration waits for a response. Asynchronous integration decouples the producer and consumer using queues, events, or files.

Analytical ingestion is often better as asynchronous integration because the operational producer should not depend on the analytical platform being immediately available.

Microsoft Fabric pattern map

Need Fabric capability
Scheduled batch Data Pipeline
Full or incremental copy Copy activity
Low-code transformation Dataflow Gen2
Code-first transformation Notebook / PySpark
ELT / Medallion Lakehouse + Delta
SQL transformation Warehouse / SQL endpoint
Operational replication Mirroring
Streaming ingestion Eventstream
Event analytics Eventhouse / Real-Time Intelligence
Zero-copy access OneLake Shortcuts
Orchestration Data Pipeline
Reverse delivery Pipeline / API / Azure integration

Mapping patterns to other technologies

Pattern Common technologies
Batch ingestion Fabric Pipelines, ADF, Airbyte, Fivetran
ELT Fabric, Snowflake, Databricks, dbt
CDC Database logs, Debezium, Mirroring, replication tools
Streaming Kafka, Event Hubs, Fabric Eventstream
Transformation Spark, SQL, dbt, Dataflow Gen2
Reverse ETL APIs, pipelines, specialized tools
Federation Virtualization engines, external tables
Zero-copy OneLake Shortcuts, external references
Orchestration Fabric Pipelines, ADF, Airflow, Dagster

Decision matrix

Requirement Strong candidate
Nightly reporting Batch
Small reference table Full load
Keep raw history ELT
Clean sensitive data before landing ETL / ETLT
Large DB with frequent changes CDC / replication
Seconds-level analytics Streaming
Event-triggered workflow Event-driven
On-demand lookup API
Partner data exchange File-based
Reliable asynchronous work Messaging
Avoid copying external lake data Shortcut / zero-copy
Query across multiple systems Federation
Push scores into CRM Reverse ETL
Keep two operational systems aligned Synchronization

One architecture can use many patterns

Imagine an online retailer:

    1. Orders DB
    2. CDC / Mirroring
      Analytics platform
    1. Supplier CSV
    2. Batch
      Bronze
    1. Website events
    2. Streaming
      Event store
    1. CRM
    2. ETLT
      Customer model
    1. External product data
    2. Shortcut / federation
      Lakehouse
    1. Gold customer segments
    2. Reverse ETL
      Marketing platform
One retailer, six integration patterns

The pattern is chosen per data flow, not once for the entire company.

Common mistakes

  • Using streaming when batch is enough.
  • Calling every incremental load CDC.
  • Copying data that could be referenced.
  • Using APIs for high-volume extraction.
  • Ignoring deletes in incremental designs.
  • Building two-way synchronization without an authoritative owner.
  • Mixing orchestration and transformation responsibilities unnecessarily.

How to choose the right pattern

  1. Define required latency.
  2. Understand what the source can provide: full extract, watermark, CDC, events, files, or APIs.
  3. Define replay and recovery requirements.
  4. Define governance and whether raw data may land.
  5. Estimate scale and source-system impact.
  6. Define what the consumer actually needs: history, current state, events, curated models, or on-demand access.
  7. Choose the technology last.

Final takeaway

Modern data integration is much broader than ETL versus ELT.

A practical architecture uses a portfolio of patterns:

Batch
Full load
ETL
ELT
ETLT
Incremental load
CDC
Replication
Streaming
Event-driven
API-based
File-based
Messaging
Federation
Zero-copy
Reverse ETL
Synchronization

The best architecture does not standardize every source onto a single pattern.

It standardizes how the team chooses the right pattern.

Choose the pattern from the data-flow requirements first, then choose the technology that implements it.

Tags

  • Microsoft Fabric
  • Data Ingestion
  • Data Pipelines
  • Incremental Loads
  • Data Architecture
  • Design Patterns