7.1 Pipeline Activities
7.1.1 What is a Pipeline Activity?
A pipeline activity is an individual task performed inside a Microsoft Fabric Data Factory pipeline.
Think of a pipeline as a workflow and activities as the individual steps:
Pipeline │ ├── Activity 1 → Read configuration │ ├── Activity 2 → Copy data │ ├── Activity 3 → Transform data │ └── Activity 4 → Load Warehouse
For example, an ETL pipeline could be:
SQL Server
│
▼
Lookup Configuration
│
▼
Copy Data
│
▼
Notebook
│
▼
Warehouse
│
▼
Audit
7.1.2 Major Categories of Activities
Fabric Data Factory activities can broadly be thought of as:
Pipeline Activities
│
├── Data Movement
│ └── Copy Data
│
├── Transformation / Processing
│ ├── Notebook
│ ├── Dataflow Gen2
│ └── SQL-related activities
│
├── Control Flow
│ ├── ForEach
│ ├── If Condition
│ ├── Switch
│ ├── Until
│ └── Wait
│
├── Lookup / Metadata
│ └── Lookup
│
├── SQL / Database
│ └── Stored Procedure / SQL execution
│
└── External Integration
└── Web / HTTP-based activities
The exact activity catalog can change as Microsoft adds capabilities to Fabric Data Factory.
7.1.3 Copy Data Activity
The Copy Data activity is one of the most important activities for data engineers.
Its purpose is:
Move data from a source to a destination.
Example:
SQL Server
│
▼
Copy Data
│
▼
Fabric Lakehouse
Another example:
Azure Blob
│
▼
Copy Data
│
▼
Warehouse
Example: SQL Server → Lakehouse
SQL Server.Customers
│
▼
Copy Data
│
▼
Bronze_Customers
A typical configuration includes:
Source
Connection: SQL Server
Table: Customers
Destination
Connection: Fabric
Destination: Lakehouse
Table: Bronze_Customers
7.1.4 Copy Data Modes
Depending on the connector and scenario, Copy Data can support patterns such as:
Full Load
Incremental Load
Partitioned/Parallel Copy
Full Load
Source │ ▼ Read all records │ ▼
Destination
Incremental Load
Source │ ▼ Changed records only │ ▼
Destination
Example:
SELECT *
FROM Orders
WHERE ModifiedDate > @LastWatermark;
7.1.5 Lookup Activity
Lookup retrieves data that the pipeline can use to make decisions.
Example:
PipelineConfig
│
▼
Lookup
│
▼
Configuration
Suppose the configuration table contains:
| SourceTable | TargetTable | LoadType |
|---|---|---|
| Customers | Bronze_Customers | Full |
| Products | Bronze_Products | Full |
| Orders | Bronze_Orders | Incremental |
The Lookup can retrieve these records.
7.1.6 Lookup + ForEach
This is a very important real-world pattern.
Lookup
│
▼
[Customers, Products, Orders]
│
▼
ForEach
│
┌────────┼────────┐
▼ ▼ ▼
Customers Products Orders
│ │ │
▼ ▼ ▼
Copy Copy Copy
Instead of creating three pipelines, you can create one metadata-driven pipeline.
7.1.7 ForEach Activity
ForEach repeats activities for each item in a collection.
Suppose Lookup returns:
Customers
Products
Orders
ForEach executes:
For each item:
Copy source → target
Conceptually:
ForEach │ ├── Customer → Copy ├── Product → Copy └── Order → Copy
Inside ForEach, the current item is typically referenced through dynamic content such as:
item()
For an object:
item().SourceTable
7.1.8 Switch Activity
Switch chooses a branch based on an expression.
Example:
LoadType │ ├── Full │ ↓ │ Full Load │ ├── Incremental │ ↓ │ Incremental Load │ └── CDC
↓
CDC Load
For example:
LoadType = Incremental
results in:
Incremental branch
This is particularly useful in metadata-driven pipelines.
7.1.9 If Condition Activity
If Condition evaluates a Boolean expression.
Example:
IsActive?
│ ┌─┴───────┐ Yes No │ │ ▼ ▼
Copy Skip
Example condition:
IsActive == true
Another example:
RecordCount > 0
Then:
TRUE
↓
Continue processing
FALSE
↓
Skip / Alert
7.1.10 Notebook Activity
Fabric pipelines can orchestrate notebook execution.
Example:
Copy Data
│
▼
Bronze
│
▼
Notebook
│
▼
Silver
The notebook could perform:
df = spark.read.table("Bronze_Orders")
clean_df = df.dropDuplicates(
["OrderID"]
)
clean_df.write \
.format("delta") \
.mode("overwrite") \
.saveAsTable("Silver_Orders")
This creates a powerful combination:
Data Factory
+
Spark
7.1.11 Dataflow Gen2 Activity
A pipeline can orchestrate Dataflow Gen2 for low-code transformation.
Example:
SQL Server
│
▼
Dataflow Gen2
│
├── Filter
├── Clean
├── Transform
└── Join
│
▼
Lakehouse / Warehouse
Use Dataflow Gen2 when a graphical transformation experience is preferable.
7.1.12 Stored Procedure Activity
A pipeline can execute stored-procedure-based SQL logic where supported.
Example:
Copy Data
│
▼
Stored Procedure
│
▼
Update Audit
For example:
EXEC dbo.usp_UpdateSalesSummary;
A procedure could:
Update audit information
Perform warehouse transformations
Reconcile data
Execute reusable SQL business logic
7.1.13 SQL Execution Activity
SQL execution can be used for database-related processing where supported by the Fabric Data Factory experience.
Example:
Silver Load
│
▼
SQL Processing
│
▼
FactSales
For example:
INSERT INTO dbo.FactSales
(
SalesKey,
CustomerKey,
ProductKey,
SalesAmount
)
SELECT
SalesKey,
CustomerKey,
ProductKey,
SalesAmount
FROM dbo.StageSales;
7.1.14 Web Activity
A Web/HTTP activity can be used to call an external web endpoint where supported.
Example:
Pipeline │ ▼ Web Activity │ ▼
REST API
For example:
Fabric Pipeline
│
▼
HTTP Request
│
▼
External API
│
▼
JSON Response
This is useful for integrations with external systems.
7.1.15 Wait Activity
Wait pauses pipeline execution.
Example:
Start │ ▼ Copy Data │ ▼ Wait 5 minutes │ ▼
Check Status
Possible use cases:
Waiting for an external process
Polling patterns
Coordinating asynchronous operations
Avoid unnecessary waits because they increase pipeline execution time.
7.1.16 Until Activity
Until repeats activities until a condition becomes true.
Conceptually:
Start │ ▼ Check Status │ ├── Not Ready ──► Wait ──► Check Again │ └── Ready ──────► Continue
Example:
Until
IsFileAvailable = true
This can be useful when waiting for an external file or process.
Use appropriate timeout limits to avoid infinite loops.
7.1.17 Set Variable Activity
Used to assign a value to a pipeline variable.
Example:
Start │ ▼ Set Variable │ ▼
Copy Data
Variable:
BatchID = 20260822_001
Then later activities can use the value.
7.1.18 Append Variable Activity
Useful when building an array/list during pipeline execution.
Conceptually:
Array Variable
│
├── Customer
├── Product
└── Order
This can be useful for collecting processing results.
7.1.19 Activity Dependencies
Activities don't necessarily execute independently.
You can define dependencies such as:
Success
Failure
Completion
Skipped
Example:
Copy Customers
│
Success
▼
Transform Customers
│
Success
▼
Load Warehouse
7.1.20 Success Path
┌──────────────┐
│ Copy Data │
└──────┬───────┘
│ Success
▼
┌──────────────┐
│ Notebook │
└──────┬───────┘
│ Success
▼
┌──────────────┐
│ Warehouse │
└──────────────┘
7.1.21 Failure Path
┌──────────────┐
│ Copy Data │
└──────┬───────┘
│ Failure
▼
┌──────────────┐
│ Log Error │
└──────┬───────┘
▼
┌──────────────┐
│ Alert │
└──────────────┘
This pattern is essential for production pipelines.
7.1.22 Completion Path
Sometimes cleanup should happen regardless of whether the previous activity succeeded or failed.
Copy Data
/ \
Success Failure
\ /
\ /
Cleanup
Use the appropriate completion dependency.
7.1.23 Activity Retry
Transient failures can sometimes be retried.
Copy Data
│
Failure
▼
Retry
│
┌──┴──────────┐
▼ ▼
Success Failure
│ │
▼ ▼
Continue Alert
Good candidates:
Temporary network failures
Service throttling
Temporary unavailable resources
Bad candidates:
Invalid SQL
Invalid credentials
Invalid table names
Schema errors
7.1.24 Activity Timeout
Long-running activities should have sensible timeout settings.
For example:
Copy Data │ └── Timeout → Failure Handler
This prevents a pipeline from waiting indefinitely.
7.1.25 Activity Outputs
Many activities produce outputs that can be consumed by later activities.
Example:
Lookup │ ▼ Output │ ▼
ForEach
A Lookup might return:
[
{
"Table": "Customers"
},
{
"Table": "Products"
}
]
ForEach can consume this collection.
7.1.26 Dynamic Content
Dynamic expressions allow pipeline activities to work with runtime values.
Common concepts include:
pipeline parameters
pipeline variables
activity outputs
current item
system/runtime values
Examples:
@pipeline().parameters.TableName
@variables('BatchID')
@item().SourceTable
@activity('LookupConfig').output
The exact output structure depends on the activity.
7.1.27 Example: Dynamic Copy Pipeline
Suppose configuration contains:
SourceTable TargetTable -----------------------------------
Customers Bronze_Customers
Products Bronze_Products
Orders Bronze_Orders
Pipeline:
Lookup │ ▼ ForEach │ ▼
Copy Data
Inside Copy:
Source:
@item().SourceTable
Target:
@item().TargetTable
Now one pipeline can process all three tables.
7.1.28 Parallel Execution
Suppose:
Customers
Products
Orders
are independent.
You can process them concurrently:
ForEach
│
┌────────┼────────┐
▼ ▼ ▼
Customers Products Orders
│ │ │
▼ ▼ ▼
Copy Copy Copy
This can reduce total execution time.
But don't use unrestricted parallelism when:
Source DB cannot handle the load.
API has rate limits.
Destination capacity is limited.
Tables have dependencies.
7.1.29 Sequential Execution
If dependencies exist:
Customer │ ▼ Product │ ▼
Order
use sequential processing where appropriate.
For example, if Orders depend on Products being loaded first:
Load Products
│
▼
Load Orders
7.1.30 Real-World Pipeline Example
Let's build:
SQL Server → Bronze Lakehouse → Silver Lakehouse → Warehouse
Pipeline:
┌─────────────────────┐
│ Lookup Configuration│
└──────────┬──────────┘
▼
┌─────────────────────┐
│ ForEach │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Switch │
│ Full/Incremental │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Copy Data │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Bronze Lakehouse │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Notebook │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Silver Lakehouse │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Warehouse Processing│
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Audit / Logging │
└─────────────────────┘
Failure:
Any Activity
│
Failure
▼
Error Handler
│
┌───┴────┐
▼ ▼
Audit Alert
7.1.31 Activity Selection Guide
| Requirement | Recommended Activity |
|---|---|
| Move data | Copy Data |
| Read configuration | Lookup |
| Process list | ForEach |
| Choose processing path | Switch |
| Boolean decision | If Condition |
| Run Spark transformation | Notebook |
| Low-code transformation | Dataflow Gen2 |
| Execute SQL logic | SQL/Stored Procedure activity where supported |
| Call external service | Web |
| Wait | Wait |
| Repeat until condition | Until |
| Store runtime value | Set Variable |
| Collect values | Append Variable |
7.1.32 Recommended Pipeline Design
For an enterprise ingestion pipeline:
START
│
▼
Set Variables
│
▼
Lookup Configuration
│
▼
ForEach
│
▼
Switch
│
┌──────────┴──────────┐
▼ ▼
Full Incremental
│ │
└──────────┬──────────┘
▼
Copy Data
│
┌─────┴─────┐
▼ ▼
Success Failure
│ │
▼ ▼
Notebook Log Error
│ │
▼ ▼
Silver Alert
│
▼
Warehouse
│
▼
Audit
7.1.33 Best Practices
1. Make pipelines reusable
Use parameters instead of hardcoding table names.
2. Prefer metadata-driven processing
Use:
Lookup → ForEach → Copy
instead of creating hundreds of nearly identical pipelines.
3. Separate responsibilities
For example:
Pipeline → Orchestration
Notebook → Spark transformation
Warehouse → SQL serving
4. Implement failure paths
Every important activity should have appropriate failure handling.
5. Use meaningful names
Instead of:
Copy1
Copy2
Activity3
use:
Copy_Customers
Copy_Products
Transform_Orders
Load_FactSales
6. Monitor execution
Track:
Pipeline run ID
Activity status
Duration
Rows read
Rows written
Error message
7. Avoid unnecessary activities
Don't create a complicated pipeline when a simpler design will work.
7.1.34 Interview Questions
What is a pipeline activity?
An individual task executed within a data pipeline.
What is Copy Data used for?
Moving data between supported source and destination systems.
What is Lookup used for?
Retrieving configuration or data for use by subsequent pipeline activities.
Why use ForEach?
To process multiple items using the same workflow.
Why use Switch?
To select one processing branch based on a value.
Difference between If Condition and Switch?
If Condition
→ True / False
Switch
→ Multiple possible cases
What is a Notebook activity used for?
To orchestrate Spark notebook-based transformations.
Why are dependencies important?
They control execution order and allow success/failure handling.
How do you build a metadata-driven pipeline?
Configuration
↓
Lookup
↓
ForEach
↓
Dynamic Copy
How do you handle failures?
Use:
Failure dependency
↓
Error logging
↓
Retry where appropriate
↓
Notification
7.1.35 Final Cheat Sheet
COPY DATA
↓
Move data
LOOKUP
↓
Get configuration/data
FOREACH
↓
Loop through items
SWITCH
↓
Choose one of multiple branches
IF CONDITION
↓
True/False decision
NOTEBOOK
↓
Run Spark/PySpark processing
DATAFLOW GEN2
↓
Low-code data transformation
STORED PROCEDURE
↓
Execute reusable SQL logic
WEB
↓
Call external HTTP service
WAIT
↓
Pause execution
UNTIL
↓
Repeat until condition
SET VARIABLE
↓
Set runtime value
ACTIVITY DEPENDENCY
↓
Control execution flow
RETRY
↓
Recover from transient failures
Key Concept
The most important pipeline pattern to remember is:
CONFIGURATION
│
▼
LOOKUP
│
▼
FOREACH
│
▼
SWITCH
│
┌─────┴─────┐
▼ ▼
FULL INCREMENTAL
│ │
└─────┬─────┘
▼
COPY DATA
│
▼
BRONZE
│
▼
NOTEBOOK
│
▼
SILVER
│
▼
WAREHOUSE
│
▼
POWER BI
This pattern gives you the foundation for building reusable, metadata-driven, production-grade Fabric ETL pipelines.
↑ Back to top