Microsoft Fabric
ETL vs ELT vs CDC vs Streaming: Data Integration Patterns and How to Implement Them in Microsoft Fabric
Compare ETL, ELT, ETLT, CDC, streaming, reverse ETL, mirroring, and shortcuts, with practical Microsoft Fabric implementations and technology choices.
On this page
ETL and ELT are often treated as competing architectures, but modern data platforms use a much broader landscape of data integration patterns as well as reusable data engineering design patterns.
A real platform might use:
- ETL for regulated or sensitive feeds that must be cleaned before landing;
- ELT for a Lakehouse or Warehouse where raw data lands first and transformations happen later;
- ETLT when some transformations must happen before loading and others after;
- CDC to move only changed rows from operational systems;
- streaming for events that need to arrive continuously;
- reverse ETL to send curated analytical data back into operational systems;
- mirroring to continuously replicate operational data into the analytical platform;
- shortcuts or zero-copy access when the best move is not to copy the data at all.
The important engineering question is not “Which pattern is best?”
It is:
Which pattern matches the latency, governance, transformation, cost, and operational requirements of this data flow?
Microsoft Fabric supports several of these approaches in the same platform, which makes it a useful place to see how the patterns fit together.
Quick comparison
| Pattern | Where transformation happens | Typical use | Latency |
|---|---|---|---|
| ETL | Before the destination | Strict schemas, governed landing, legacy DW | Batch |
| ELT | After data lands | Lakehouse/Warehouse, Medallion | Batch / near-real-time |
| ETLT | Before and after landing | Governance plus downstream analytics | Batch |
| CDC | Only changed rows move | Operational database replication | Near-real-time |
| Streaming | Events flow continuously | Telemetry, clickstream, IoT, logs | Seconds or less |
| Reverse ETL | From analytics back to operational systems | CRM/marketing/activation | Scheduled / near-real-time |
| Mirroring | Continuous replication into analytics | Operational DB analytics | Near-real-time |
| Shortcut / zero-copy | Data is referenced, not copied | Shared/external lake data | Depends on source |
These patterns are not mutually exclusive.
A production architecture can use several at once.
ETL: Extract, Transform, Load
ETL means:
- Source
- ExtractTransformation engineTransform before load
- LoadDestinationWarehouse / Lakehouse / application
The defining idea is that the transformation happens before the final destination receives the data.
When ETL makes sense
ETL is useful when:
- sensitive columns must be removed before data reaches the analytical platform;
- the destination requires a strict schema;
- source data needs heavy normalization before it is useful;
- governance requires a controlled landing shape;
- a legacy warehouse expects curated records rather than raw data.
Microsoft Fabric ETL example
A Fabric ETL flow can use Dataflow Gen2 to connect to a source, transform the data, and publish it to a Lakehouse or Warehouse.
Conceptually:
- SQL / CSV / API
- Dataflow Gen2
- filter
- rename
- cast
- remove PII
- Lakehouse / Warehouse
Fabric Dataflow Gen2 is designed for cloud-based data preparation: it connects to data, applies transformations, and publishes the result. That fits classic ETL well.
A pipeline can orchestrate the dataflow and downstream activities.
Example
Suppose an HR extract contains:
employee_id
employee_name
salary
ssn
department
If the analytical platform should never store Social Security numbers, ETL can remove that field before the final load:
- Source
- Transform
- Remove ssn
- Standardize department
- Cast salary
- Analytics destination
The transformation is not simply for convenience. It is part of the governance boundary.
ELT: Extract, Load, Transform
ELT reverses the last two stages:
- Source
- Extract + load raw dataLakehouse / Warehouse
- Transform inside the platform
- Curated tables
The raw or lightly processed data lands first.
Transformations happen using the compute engines of the analytical platform.
Why ELT became so common
Modern cloud platforms can store large volumes cheaply and provide scalable SQL, Spark, or other engines close to the data.
That makes it practical to retain the source and build multiple downstream representations.
ELT fits naturally with Medallion architecture:
- Source
- Bronze
- Silver
- Gold
Microsoft recommends Bronze, Silver, and Gold layers as a common way to implement Medallion architecture in Fabric and OneLake.
Microsoft Fabric ELT example
A common Fabric ELT flow is:
- Source
- Fabric Pipeline / Copy activity
- Lakehouse Files/raw or Bronze Delta tables
- Notebook / Spark SQL / Dataflow Gen2
- Silver Delta tables
- Notebook / SQL
- Gold tables
This is the pattern used by many Lakehouse implementations because the raw source remains available for replay and debugging.
Example with PySpark
from pyspark.sql import functions as F
bronze_orders = spark.table("bronze_orders")
silver_orders = (
bronze_orders
.dropDuplicates(["order_id"])
.withColumn("country", F.upper(F.trim("country")))
.withColumn("order_date", F.to_date("order_timestamp"))
)
(
silver_orders.write
.format("delta")
.mode("overwrite")
.saveAsTable("silver_orders")
)
The load has already happened.
The transformation takes place inside Fabric.
That is ELT.
ETLT: Extract, Transform, Load, Transform
ETLT is a useful hybrid.
Some transformations happen before the data lands, then additional business transformations happen after it lands.
- Source
- Light transformation
- Lakehouse
- Business transformation
- Gold / serving layer
Why use ETLT?
Because “transform everything before load” and “load everything untouched” are both too rigid for many real systems.
You may need to:
- remove secrets or PII before landing;
- decrypt or decode payloads;
- reject clearly corrupt records;
- standardize transport-level formats;
and still want:
- Bronze/Silver/Gold;
- business joins;
- dimensional modeling;
- enrichment;
- aggregations;
inside the analytical platform.
Fabric example
- API response
- Pipeline / Dataflow
- remove secrets
- normalize envelope
- Bronze
- Notebook
- deduplicate
- validate
- enrich
- Silver
- Gold
ETLT is often a better description of production architectures than forcing them into pure ETL or ELT.
CDC: Change Data Capture
A full load asks:
What does the source contain now?
CDC asks:
What changed since the last point in time?
That difference becomes important when a source table has millions or billions of rows.
CDC movement
- Operational DB
- INSERT / UPDATE / DELETE changes
- CDC / replication
- Analytical copy
Instead of copying the entire table every hour, the integration moves the changes.
CDC vs watermark loading
These are related but not identical.
A watermark often looks like:
SELECT *
FROM orders
WHERE modified_at > @last_watermark;This can capture new and updated rows when the source has a reliable modification timestamp.
But deletes can be difficult unless the source records them separately.
True CDC normally reads a database change mechanism or transaction log and can represent inserts, updates, and deletes.
For the full Fabric implementation, including bounded watermark windows, SQL Server Change Tracking vs CDC, late data and safe reruns, see Incremental Loads in Microsoft Fabric.
Microsoft Fabric options
Fabric can participate in incremental movement in several ways:
- Mirroring for supported operational data sources;
- pipeline-based incremental loads;
- source-specific CDC connectors or patterns;
- notebooks using watermarks when full CDC is not available.
Copy jobs add native watermark and CDC-based incremental copies without a pipeline. Pipelines vs Notebooks vs Dataflow Gen2 vs Copy Job compares these tools for each step.
Mirroring in Fabric continuously replicates supported operational database data into OneLake with minimal integration effort.
That makes mirroring particularly interesting when the goal is analytical access to operational data without maintaining a traditional copy pipeline yourself.
Streaming: continuous data in motion
Batch processing waits for a set of records.
Streaming processes events as they arrive.
- Events
- Kafka / Event Hubs / source
- Stream processor
- Real-time store / actions / analytics
Streaming is appropriate for:
- application telemetry;
- clickstream;
- IoT;
- fraud signals;
- logs;
- operational alerts;
- real-time customer events.
Microsoft Fabric implementation
Fabric Real-Time Intelligence is designed for event-driven scenarios, streaming data, and logs. Its building blocks include Eventstream, Eventhouse, Real-Time hub, dashboards, and Activator.
A simple flow can look like:
- Kafka / Event Hubs / events
- Eventstream
- Eventhouse
- KQL / Real-Time Dashboard
- Activator / Power BI / downstream action
Eventstream can ingest and optionally transform streaming events, while Eventhouse is designed to store and query streaming/event data at scale.
Reverse ETL: analytics back into operations
Traditional ETL moves data into the analytical platform.
Reverse ETL moves curated analytical results out.
- Lakehouse / Warehouse
- Gold customer segments
- CRM / marketing / support / product system
Example
A Gold table might contain:
customer_id
lifetime_value
churn_risk
customer_segment
The CRM team does not necessarily want to query your Lakehouse directly.
Instead, you can push:
customer_segment = "High Value"
back into the CRM.
That lets operational teams use analytical outcomes in their daily tools.
Fabric implementation
Fabric can be the analytical source, while outbound delivery can use:
- a Fabric pipeline;
- an API call;
- Azure Functions;
- Logic Apps;
- custom integration code;
- a specialized reverse ETL product.
The architectural pattern is more important than the specific tool.
Mirroring: replicate instead of building a copy pipeline
Mirroring deserves its own category because it changes the ETL/ELT decision.
Instead of designing:
- Source DB
- Copy pipeline
- Bronze
you may be able to use:
- Operational DB
- continuous replicationMirrored data in Fabric
For supported systems, Fabric Mirroring continuously replicates operational data into OneLake.
This reduces the amount of ingestion logic you must maintain.
It does not remove the need for transformation.
You may still build:
- Mirrored operational data
- Silver
- Gold
So mirroring can replace part of the Extract/Load mechanism while ELT still describes the downstream analytical transformation.
OneLake shortcuts: sometimes do not move the data
A shortcut represents another important pattern:
Reference the data where it already lives.
OneLake shortcuts can point to data in Fabric or supported external storage without physically copying the source into a new location.
Conceptually:
- External / shared data
- referenceOneLake Shortcut
- Lakehouse / KQL access
That can reduce:
- duplicated storage;
- copy latency;
- extra ingestion pipelines;
- synchronization problems.
But zero-copy access is not automatically the best choice.
You still need to consider:
- source availability;
- permissions;
- performance;
- network behavior;
- lifecycle ownership;
- schema changes;
- governance.
Batch vs CDC vs streaming
These are often confused because they all move data.
The key difference is how frequently and how selectively.
Batch
- Every night at 2:00 AM
- Process everything for yesterday
Good for:
- daily finance;
- reporting;
- low-change sources;
- workloads where latency is not important.
Incremental / CDC
- Every few minutes
- Process only changes
Good for:
- operational databases;
- large tables;
- near-real-time analytical copies.
Streaming
- Every event
- Process continuously
Good for:
- telemetry;
- fraud;
- monitoring;
- event-driven actions.
Do not use streaming simply because it sounds more modern.
If the business needs yesterday’s data by 7:00 AM, a reliable batch pipeline may be better than a streaming system with much higher operational complexity.
How these patterns map to Microsoft Fabric
| Need | Fabric approach |
|---|---|
| Transform before landing | Dataflow Gen2 / pipeline transformation |
| Land raw, transform later | Pipeline + Lakehouse + Notebook/Spark SQL |
| Medallion | Lakehouse/Warehouse Bronze → Silver → Gold |
| Declarative medallion transformations | Materialized lake views |
| Incremental database movement | Mirroring / incremental pipeline patterns |
| Streaming | Eventstream + Eventhouse / Real-Time Intelligence |
| External/shared lake data without copy | OneLake Shortcut |
| Analytical data back to operational systems | Pipeline / API / Azure integration |
| Visual low-code preparation | Dataflow Gen2 |
| Complex code-first transforms | Notebook / PySpark |
Fabric also supports materialized lake views for declarative Bronze → Silver → Gold transformation flows. If the target store is still undecided, compare the Fabric Lakehouse and Warehouse tradeoffs.
The same patterns in other technologies
The architectural pattern should be chosen before the product.
Azure Data Factory
ADF is commonly used for:
- orchestration;
- batch ETL/ELT;
- Copy activity;
- incremental loads;
- hybrid/on-premises connectivity.
A typical ELT design:
- SQL Server
- ADF CopyADLS
- Databricks / SQL transformation
Databricks
Databricks fits ELT and streaming particularly well:
- Cloud storage / events
- Spark / streaming ingestion
- Delta
- Bronze / Silver / Gold
It is especially strong when transformations are code-heavy, Spark-heavy, or streaming-heavy.
Snowflake
Snowflake architectures are commonly ELT-oriented:
- Source
- ingestionSnowflake raw tables
- SQL transformationCurated models
The same principle applies: land data first, transform using the analytical engine.
dbt
dbt is primarily a transformation framework.
It fits the “T” in ELT very well:
- Ingestion tool
- Warehouse
- dbt
- Curated models
dbt does not replace the ingestion layer by itself.
Kafka and Azure Event Hubs
These technologies are event backbones rather than complete analytical platforms.
They fit streaming architectures:
- Producers
- Kafka / Event Hubs
- Consumers / stream processing
Fivetran and Airbyte
Tools in this category focus heavily on ingestion and replication from operational/SaaS systems into analytical destinations.
They often support ELT architectures because the source is loaded into the destination before downstream transformations run.
One business scenario, multiple patterns
Imagine an online retailer.
It has:
- SQL transactional orders;
- SaaS CRM customers;
- website clickstream;
- supplier CSV files;
- a marketing platform.
There is no reason every source should use the same pattern.
Orders database
Use CDC or mirroring:
- Orders DB
- changesFabric
Supplier CSV
Use batch ELT:
- CSV
- pipelineBronze
- notebookSilver
Website events
Use streaming:
- Events
- EventstreamEventhouse
Customer master
Use ETL or ETLT if PII must be removed before analytics:
- CRM
- remove restricted fieldsLakehouse
- business enrichmentGold
Marketing activation
Use reverse ETL:
- Gold segments
- Marketing platform
That is a realistic modern architecture.
The pattern is chosen per data flow, not once for the entire company.
Decision matrix
| Requirement | Strong candidate |
|---|---|
| Nightly reporting | ETL or ELT |
| Keep raw data for replay | ELT |
| Remove sensitive fields before landing | ETL / ETLT |
| Transform with Spark/SQL in a Lakehouse | ELT |
| Large operational DB with frequent changes | CDC / Mirroring |
| Seconds-level event analytics | Streaming |
| Send analytical scores to CRM | Reverse ETL |
| Data already exists in accessible lake storage | Shortcut / zero-copy |
| Very low-code transformation | Dataflow Gen2 |
| Complex data engineering logic | Notebook / PySpark |
| Declarative Bronze/Silver/Gold in Fabric | Materialized lake views |
Common mistakes
“ELT is always better than ETL”
No.
ELT is excellent when you want raw-data retention and powerful destination-side compute.
ETL remains useful when governance or target constraints require transformation before landing.
“CDC means incremental load”
Not necessarily.
A timestamp watermark is incremental loading, but it may not capture deletes and may not be based on the database change log.
“Streaming is just very frequent batch”
They can solve overlapping business problems, but the operational model is different.
Streaming systems are designed around continuously arriving events.
“Mirroring eliminates transformation”
No.
Mirroring can reduce ingestion engineering.
You still need business transformation, data quality, modeling, and serving layers.
“A shortcut means there is no architecture”
Also no.
Zero-copy access still requires ownership, security, schema, performance, and lifecycle decisions.
A practical Fabric architecture
A realistic Fabric platform might combine all of these ideas:
- Sources
- Azure SQL
- Supplier CSV
- Website events
- Landing
- MirroringMirrored operational data
- PipelineBronze Lakehouse
- EventstreamEventhouse
- Curated
- Silver Lakehouse
- Gold Warehouse
- Serving
- Power BI
- Reverse ETLCRM
OneLake shortcuts can add shared external data without another physical copy.
This is why modern data architecture is no longer a debate between only ETL and ELT.
How to choose the right pattern
Ask these questions in order.
1. What latency does the business need?
- hours → batch;
- minutes → incremental/CDC;
- seconds → streaming.
2. Must raw data be retained?
If yes, ELT is often attractive.
3. Can sensitive data land unchanged?
If no, transform or mask before landing.
4. How large is the source?
Repeated full loads become expensive as the source grows.
5. Are deletes important?
If yes, a simple timestamp watermark may be insufficient.
6. Does the data already exist somewhere accessible?
A shortcut or similar zero-copy mechanism may avoid unnecessary duplication.
7. Where is the best compute?
Transform close to the data when possible.
8. Who will operate the pipeline?
A solution that is theoretically elegant but operationally unmanageable is not the right solution.
Final takeaway
ETL and ELT are foundational ideas, but they are only part of modern data integration.
The practical toolbox is broader:
ETL
ELT
ETLT
CDC
Streaming
Reverse ETL
Mirroring
Shortcuts / zero-copy
Microsoft Fabric can implement or participate in each of these patterns through Data Factory pipelines, Dataflow Gen2, Lakehouse, Warehouse, notebooks, Mirroring, Real-Time Intelligence, Eventstream, Eventhouse, and OneLake shortcuts.
The most important architecture rule is simple:
Choose the integration pattern per data flow, based on latency, governance, transformation, and operational requirements.
Do not choose a technology first and then force every data source into the same pattern.
Official references
- Microsoft Fabric Medallion architecture: https://learn.microsoft.com/en-us/fabric/onelake/onelake-medallion-lakehouse-architecture
- Microsoft Fabric Lakehouse end-to-end scenario: https://learn.microsoft.com/en-us/fabric/data-engineering/tutorial-lakehouse-introduction
- Microsoft Fabric Dataflow Gen2: https://learn.microsoft.com/en-us/fabric/data-factory/create-first-dataflow-gen2
- Microsoft Fabric Mirroring overview: https://learn.microsoft.com/en-us/fabric/database/sql/mirroring-overview
- Microsoft Fabric Real-Time Intelligence: https://learn.microsoft.com/en-us/fabric/real-time-intelligence/
- Microsoft Fabric Eventhouse: https://learn.microsoft.com/en-us/fabric/real-time-intelligence/eventhouse
- OneLake shortcuts: https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts
- Materialized lake views: https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/tutorial
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 EngineeringCompare ETL, ELT, ETLT, CDC, streaming, reverse ETL, mirroring, and shortcuts, with practical Microsoft Fabric implementations and technology choices.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.