Data Pipelines & Dataflows in Microsoft Fabric

Pipelines Overview

Fabric pipelines are the orchestration engine, based on Azure Data Factory (ADF). If you’ve used ADF before, the pipeline designer will look familiar — same activities, same drag-and-drop UI, but now integrated into Fabric.

Key Activities

Copy Activity Deep Dive

The Copy Activity is the workhorse for data ingestion. It connects to 100+ sources and writes to Fabric lakehouses, warehouses, or KQL databases.

Common Source → Destination Patterns

-- Example: Watermark-based incremental pattern
-- Control table tracks the last loaded timestamp
CREATE TABLE dbo.watermark (
    table_name   VARCHAR(100),
    last_loaded  DATETIME2
);

-- Pipeline Copy Activity query (parameterized):
-- SELECT * FROM source_table
-- WHERE modified_date > '@{activity('Lookup').output.firstRow.last_loaded}'
-- AND modified_date <= '@{pipeline().parameters.current_time}'

Dataflows Gen2

Dataflows Gen2 bring Power Query Online into Fabric. If you’ve used Power Query in Excel or Power BI Desktop, you already know the interface. The key difference: Dataflows Gen2 write output to Fabric destinations (lakehouses, warehouses) instead of just loading into a Power BI model.

Dataflow Gen2 vs Gen1

Orchestration Patterns

Pattern 1: Simple ETL

Copy → Transform → Load

-- Pipeline structure:
-- 1. Copy Activity: Source DB → Bronze lakehouse (raw_sales table)
-- 2. Notebook Activity: Bronze → Silver (clean, deduplicate)
-- 3. Notebook Activity: Silver → Gold (aggregate for reporting)
-- 4. (Optional) Refresh semantic model for Power BI

Pattern 2: Dynamic Pipeline with ForEach

Process multiple tables dynamically:

-- Pipeline structure:
-- 1. Lookup Activity: Read table list from control table
--    SELECT table_name, source_schema FROM dbo.etl_config WHERE is_active = 1
-- 2. ForEach Activity: Loop over lookup results
--    2a. Copy Activity (parameterized): Copy source_table → lakehouse
--    2b. Notebook Activity (parameterized): Transform table
-- 3. On completion: Send Teams notification via Web Activity

Pattern 3: Conditional Branching

-- Pipeline structure:
-- 1. Lookup Activity: Check if new data exists
--    SELECT COUNT(*) as row_count FROM staging WHERE processed = 0
-- 2. If Condition: row_count > 0
--    True path: Run Notebook → Update watermark
--    False path: Log "no new data" → Skip processing

Scheduling & Monitoring

Every pipeline can be triggered in three ways:

Monitoring

Fabric provides a Monitoring Hub that shows all pipeline runs, notebook runs, dataflow refreshes, and Spark job history across the workspace. Key metrics to watch:

• Duration — track trends; sudden increases signal problems
• CU consumption — monitor capacity usage to avoid throttling
• Rows read/written — verify data volumes match expectations
• Error details — drill into failed activities for stack traces

Fabric Pipelines vs ADF vs Airflow