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.

By JaviPublished 13 min readIntermediate

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:

  1. Source
  2. Extract
    Transformation engineTransform before load
  3. Load
    DestinationWarehouse / Lakehouse / application
ETL

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:

  1. SQL / CSV / API
  2. Dataflow Gen2
    • filter
    • rename
    • cast
    • remove PII
  3. Lakehouse / Warehouse
ETL with Dataflow Gen2

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:

  1. Source
  2. Transform
    • Remove ssn
    • Standardize department
    • Cast salary
  3. Analytics destination
Removing sensitive fields before load

The transformation is not simply for convenience. It is part of the governance boundary.

ELT: Extract, Load, Transform

ELT reverses the last two stages:

  1. Source
  2. Extract + load raw data
    Lakehouse / Warehouse
  3. Transform inside the platform
  4. Curated tables
ELT

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:

  1. Source
  2. Bronze
  3. Silver
  4. Gold
ELT with Medallion layers

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:

  1. Source
  2. Fabric Pipeline / Copy activity
  3. Lakehouse Files/raw or Bronze Delta tables
  4. Notebook / Spark SQL / Dataflow Gen2
  5. Silver Delta tables
  6. Notebook / SQL
  7. Gold tables
ELT in Microsoft Fabric

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.

  1. Source
  2. Light transformation
  3. Lakehouse
  4. Business transformation
  5. Gold / serving layer
ETLT

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

  1. API response
  2. Pipeline / Dataflow
    • remove secrets
    • normalize envelope
  3. Bronze
  4. Notebook
    • deduplicate
    • validate
    • enrich
  5. Silver
  6. Gold
ETLT in Microsoft Fabric

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

  1. Operational DB
  2. INSERT / UPDATE / DELETE changes
  3. CDC / replication
  4. Analytical copy
CDC movement

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:

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

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.

  1. Events
  2. Kafka / Event Hubs / source
  3. Stream processor
  4. Real-time store / actions / analytics
Streaming

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:

  1. Kafka / Event Hubs / events
  2. Eventstream
  3. Eventhouse
  4. KQL / Real-Time Dashboard
  5. Activator / Power BI / downstream action
Streaming in Microsoft Fabric

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.

  1. Lakehouse / Warehouse
  2. Gold customer segments
  3. CRM / marketing / support / product system
Reverse ETL

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:

  1. Source DB
  2. Copy pipeline
  3. Bronze
A copy pipeline

you may be able to use:

  1. Operational DB
  2. continuous replication
    Mirrored data in Fabric
Mirroring

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:

  1. Mirrored operational data
  2. Silver
  3. Gold
Transformation after mirroring

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:

  1. External / shared data
  2. reference
    OneLake Shortcut
  3. Lakehouse / KQL access
OneLake shortcut

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

  1. Every night at 2:00 AM
  2. Process everything for yesterday
Batch

Good for:

  • daily finance;
  • reporting;
  • low-change sources;
  • workloads where latency is not important.

Incremental / CDC

  1. Every few minutes
  2. Process only changes
Incremental / CDC

Good for:

  • operational databases;
  • large tables;
  • near-real-time analytical copies.

Streaming

  1. Every event
  2. Process continuously
Streaming

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:

  1. SQL Server
  2. ADF Copy
    ADLS
  3. Databricks / SQL transformation
ELT with Azure Data Factory

Databricks

Databricks fits ELT and streaming particularly well:

  1. Cloud storage / events
  2. Spark / streaming ingestion
  3. Delta
  4. Bronze / Silver / Gold
Databricks

It is especially strong when transformations are code-heavy, Spark-heavy, or streaming-heavy.

Snowflake

Snowflake architectures are commonly ELT-oriented:

  1. Source
  2. ingestion
    Snowflake raw tables
  3. SQL transformation
    Curated models
Snowflake

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:

  1. Ingestion tool
  2. Warehouse
  3. dbt
  4. Curated models
dbt

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:

  1. Producers
  2. Kafka / Event Hubs
  3. Consumers / stream processing
Kafka and Azure Event Hubs

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:

  1. Orders DB
  2. changes
    Fabric
Orders database

Supplier CSV

Use batch ELT:

  1. CSV
  2. pipeline
    Bronze
  3. notebook
    Silver
Supplier CSV

Website events

Use streaming:

  1. Events
  2. Eventstream
    Eventhouse
Website events

Customer master

Use ETL or ETLT if PII must be removed before analytics:

  1. CRM
  2. remove restricted fields
    Lakehouse
  3. business enrichment
    Gold
Customer master

Marketing activation

Use reverse ETL:

  1. Gold segments
  2. Marketing platform
Marketing activation

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:

  1. Sources
    • Azure SQL
    • Supplier CSV
    • Website events
  2. Landing
    • MirroringMirrored operational data
    • PipelineBronze Lakehouse
    • EventstreamEventhouse
  3. Curated
    • Silver Lakehouse
    • Gold Warehouse
  4. Serving
    • Power BI
    • Reverse ETLCRM
A practical Fabric architecture. Each source lands through its own pattern. Mirrored and Bronze data feed Silver; Eventhouse serves real-time queries directly.

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

Tags

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