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.
On this page
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.
- Source
- Every hour / day / week
- Batch pipeline
- Destination
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.
- Source table
- Read all rows
- Replace target
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.
- Source
- ExtractTransform
- Load
- Destination
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:
- HR source
- Transform
- Remove SSN
- Normalize department
- Validate salary
- Warehouse
4. ELT
ELT means Extract → Load → Transform.
- Source
- Raw landing
- Lakehouse / Warehouse
- Transform
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:
- Bronze
- Silver
- Gold
In Microsoft Fabric:
- Source
- Fabric Pipeline
- Lakehouse Bronze
- Notebook / Spark SQL
- Silver
- Gold
5. ETLT
ETLT means Extract → Transform → Load → Transform.
This hybrid is common when some controls must happen before landing while business transformations happen after.
- API payload
- Remove secrets / normalize envelope
- Bronze
- Deduplicate / enrich / apply business rules
- Silver / Gold
6. Incremental loading
Incremental loading moves only records that changed since the previous run.
A watermark pattern may look like:
SELECT *
FROM orders
WHERE modified_at > @last_watermark;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.
- Operational DB
- Change / transaction log
- CDC consumer
- Analytical target
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.
- Operational DB
- Continuous replication
- Analytical copy
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.
- Producer
- Event stream
- Stream processor
- Real-time store / action
Typical uses include telemetry, clickstream, IoT, fraud detection, operational logs, and real-time alerts.
In Fabric:
- Event Hubs / Kafka
- Eventstream
- Eventhouse
- KQL / Dashboard / Activator
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.
- OrderCreated
- Event bus
- Inventory service
- Notification service
- Analytics consumer
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.
- Producer
- File drop
- Storage / SFTP
- Pipeline
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.
- Query engine
- Source A
- Source B
- Source C
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.
- External storage
- Reference / Shortcut
- Analytical platform
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.
- Lakehouse / Warehouse
- Customer segment / score
- CRM / marketing / support
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.
- 1Copy data
- 2Run notebook
- 3Validate
- 4Publish
- 5Notify
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:
- Orders DB
- CDC / MirroringAnalytics platform
- Supplier CSV
- BatchBronze
- Website events
- StreamingEvent store
- CRM
- ETLTCustomer model
- External product data
- Shortcut / federationLakehouse
- Gold customer segments
- Reverse ETLMarketing platform
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
- Define required latency.
- Understand what the source can provide: full extract, watermark, CDC, events, files, or APIs.
- Define replay and recovery requirements.
- Define governance and whether raw data may land.
- Estimate scale and source-system impact.
- Define what the consumer actually needs: history, current state, events, curated models, or on-demand access.
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- 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
- RoadmapProduction Data EngineeringA practical guide to batch, ETL, ELT, CDC, streaming, replication, APIs, files, federation, zero-copy, reverse ETL, synchronization, and orchestration.View roadmap
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.