Building a Reusable Fabric Pipeline Template with Metadata-Driven Orchestration: Driving Copy Activities, Notebook Runs, and Dataflow Refreshes from a Configuration Table

Introduction

Imagine you're managing a data platform that ingests data from fourteen different source systems. Some are SQL databases, some are REST APIs, some are flat files in Azure Blob Storage. Some need raw copies into the Bronze layer. Some need transformation notebooks. Some feed Dataflow Gen2 refreshes that reshape data before it hits the Silver layer. Now imagine you've built all of this with individual, hand-crafted pipelines — one per source, each with its own parameters hardcoded in the canvas, its own schedule, its own retry logic. You have forty-two pipeline items cluttering your workspace, and when the security team asks you to rotate the connection credentials on eleven of them, you spend a Thursday afternoon clicking through canvas after canvas.

This is the problem metadata-driven orchestration solves. Instead of treating each data source as a unique snowflake requiring its own bespoke pipeline, you build one template pipeline — a single, parameterized orchestrator — and you drive every decision from a configuration table. Add a new source? Insert a row. Disable a source temporarily? Set an IsActive flag to 0. Change the destination lakehouse path? Update a column. The pipeline itself never changes. This is the same pattern that powers Azure Data Factory's copy data tool under the hood, and it's fully achievable in Microsoft Fabric with today's primitives.

By the end of this lesson, you'll have built a working, production-worthy metadata-driven orchestration framework in Fabric — one that reads from a control table in a lakehouse, dynamically dispatches Copy Activities, Notebook runs, and Dataflow Gen2 refreshes based on the configuration it finds, handles failures gracefully, and logs execution results back to the same control table for observability.

What you'll learn:

• How to design a metadata control table that describes all pipeline activities across activity types
• How to use a Lookup Activity and ForEach loop in a Fabric pipeline to iterate dynamically over configuration rows
• How to dispatch Copy Activities, Notebook Activities, and Dataflow Activities from within a single ForEach loop using an If Condition and Switch pattern
• How to pass metadata columns as dynamic expressions into child activity parameters at runtime
• How to log pipeline run outcomes back to the control table using a Notebook or Stored Procedure activity

---

Prerequisites

This lesson assumes you're comfortable with Fabric Data Pipelines at an intermediate level. Specifically, you should have already worked through the fundamentals of orchestrating loads with Fabric data pipelines, copy activities, parameters, and schedules. You should also understand how notebook parameters work in Fabric — if not, review using notebook variables and parameters in Microsoft Fabric before continuing.

You'll need:

• A Fabric workspace with at least Contributor access (F2 or Trial capacity minimum)
• A Lakehouse item to serve as both the control plane store and a data destination
• At least one Dataflow Gen2 and one Notebook already created in your workspace (even stubs work for the exercise)
• Familiarity with PySpark basics for the logging notebook

---

Why Metadata-Driven Beats Copy-Per-Source

Before we build anything, it's worth being precise about what we're trading away and what we're gaining, because there are real costs to this pattern.

What you gain:

The most obvious benefit is maintainability at scale. When you have a single pipeline template, you have one place to update retry logic, one place to add error handling, one place to modify logging. Changes propagate automatically to every configured source. You also gain a single scheduling surface — your master orchestrator runs on a schedule and processes all active rows. Need to skip a source today? Set the row to inactive. No pipeline disabling, no schedule changes.

There's also a governance benefit. The control table is data — it's queryable, versionable, and auditable. You can answer questions like "what sources were active last Tuesday?" by querying history records. You can join the control table against your run log to calculate average runtime per source. This observability is very hard to achieve with forty-two individual pipelines.

What you trade away:

The pattern adds indirection. When something fails, the error appears inside a ForEach iteration, which means your Monitoring Hub shows one pipeline run with many activity executions rather than one clean failed-pipeline-per-source signal. Debugging requires drilling into iteration-level logs. For large teams where different people own different sources, individual pipelines give clearer ownership boundaries.

There's also a performance consideration: ForEach loops in Fabric pipelines execute iterations sequentially by default (batch count = 1). You can increase the batch count to run iterations in parallel, but parallel ForEach has a maximum degree of parallelism of 50, and each inner activity consumes capacity units. For platforms with many sources and strict SLA windows, you may need to partition your control table into priority groups and run separate orchestrator pipelines per group.

---

Designing the Control Table

The control table is the heart of the entire framework. Getting its schema right matters more than any pipeline logic, because the schema determines what decisions your orchestrator can make.

Here's the production-worthy schema we'll use:

CREATE TABLE dbo.pipeline_control (
    control_id          INT IDENTITY(1,1) PRIMARY KEY,
    pipeline_group      VARCHAR(50)     NOT NULL,  -- e.g., 'bronze_ingestion', 'silver_transform'
    activity_type       VARCHAR(30)     NOT NULL,  -- 'Copy', 'Notebook', 'Dataflow'
    source_name         VARCHAR(100)    NOT NULL,  -- human-readable name for logging
    is_active           BIT             NOT NULL DEFAULT 1,
    run_sequence        INT             NOT NULL DEFAULT 100,  -- lower = runs first (future use)
    
    -- Copy Activity columns
    copy_source_type    VARCHAR(50)     NULL,  -- 'AzureSQLDatabase', 'BlobStorage', 'RestAPI'
    copy_source_object  VARCHAR(500)    NULL,  -- table name, file path, or endpoint
    copy_dest_lakehouse VARCHAR(200)    NULL,  -- workspace/lakehouse name
    copy_dest_path      VARCHAR(500)    NULL,  -- target table or file path
    copy_dest_format    VARCHAR(20)     NULL,  -- 'Delta', 'Parquet', 'CSV'
    
    -- Notebook Activity columns
    notebook_name       VARCHAR(200)    NULL,  -- exact notebook item name in workspace
    notebook_params     NVARCHAR(MAX)   NULL,  -- JSON string of parameters to pass
    
    -- Dataflow Activity columns
    dataflow_name       VARCHAR(200)    NULL,  -- exact Dataflow Gen2 item name
    dataflow_workspace  VARCHAR(200)    NULL,  -- workspace name (for cross-workspace refresh)
    
    -- Operational columns
    timeout_minutes     INT             NOT NULL DEFAULT 60,
    retry_count         INT             NOT NULL DEFAULT 2,
    retry_interval_secs INT             NOT NULL DEFAULT 30,
    depends_on_id       INT             NULL,   -- FK to another control_id (future dependency chaining)
    
    -- Audit columns
    created_at          DATETIME2       NOT NULL DEFAULT GETUTCDATE(),
    updated_at          DATETIME2       NOT NULL DEFAULT GETUTCDATE()
)

Let's talk through the design decisions embedded here.

activity_type as the dispatch column. This is the column your pipeline's Switch activity will branch on. Keeping it to three values (Copy, Notebook, Dataflow) matches the three native Fabric pipeline activity types you'll be using. If you later add a Stored Procedure activity or a Web activity for triggering an external API, you add a new value here and add a new Switch case — the rest of the framework is unchanged.

Nullable columns per activity type. You'll notice copy_source_type is NULL when the row is a Notebook activity. This is intentional. The alternative — a fully normalized three-table design with a parent row and child tables per activity type — is cleaner from a relational theory standpoint but requires a JOIN in your Lookup query and complicates the dynamic expressions inside the pipeline. For control tables in orchestration frameworks, denormalization is often the pragmatic choice.

notebook_params as a JSON string. When you need to pass multiple parameters to a notebook (source file path, target table name, watermark date, etc.), encoding them as a JSON string in one column is far more flexible than creating a fixed column per parameter. Your notebook receives the string and parses it with json.loads(). The pipeline passes it as a single string parameter to the Notebook Activity's parameter input.

pipeline_group for partitioning. Even if you start with a single orchestrator, designing the group concept in from the beginning lets you later split into multiple orchestrators (bronze_orchestrator, silver_orchestrator) each filtering by group, without changing the schema.

Now populate a few sample rows that represent a realistic Bronze ingestion group:

INSERT INTO dbo.pipeline_control 
(pipeline_group, activity_type, source_name, is_active, run_sequence,
 copy_source_type, copy_source_object, copy_dest_lakehouse, copy_dest_path, copy_dest_format,
 timeout_minutes, retry_count, retry_interval_secs)
VALUES 
('bronze_ingestion', 'Copy', 'CRM_Customers', 1, 10,
 'AzureSQLDatabase', 'dbo.Customers', 'sales_lakehouse', 'Tables/bronze_crm_customers', 'Delta',
 30, 2, 30),

('bronze_ingestion', 'Copy', 'ERP_Orders', 1, 20,
 'AzureSQLDatabase', 'dbo.SalesOrders', 'sales_lakehouse', 'Tables/bronze_erp_orders', 'Delta',
 45, 3, 60),

('bronze_ingestion', 'Notebook', 'API_WeatherData', 1, 30,
 NULL, NULL, NULL, NULL, NULL,
 60, 1, 30),

('bronze_ingestion', 'Dataflow', 'FTP_ProductCatalog', 1, 40,
 NULL, NULL, NULL, NULL, NULL,
 90, 2, 60);

-- Set notebook-specific columns in a separate update for clarity
UPDATE dbo.pipeline_control 
SET notebook_name = 'ingest_weather_api',
    notebook_params = '{"api_endpoint": "https://api.weather.example.com/historical", "target_table": "bronze_weather_daily", "lookback_days": 7}'
WHERE source_name = 'API_WeatherData';

UPDATE dbo.pipeline_control
SET dataflow_name = 'df_FTP_ProductCatalog',
    dataflow_workspace = 'Sales Analytics'
WHERE source_name = 'FTP_ProductCatalog';

---

Building the Master Orchestrator Pipeline

Now let's build the pipeline. Open your Fabric workspace and create a new Data Pipeline. Name it pl_master_orchestrator. This name matters — you'll reference it in your run logs.

Step 1: Pipeline-Level Parameters

Before adding activities, define two pipeline parameters. Click the pipeline canvas background (not an activity), then open the Parameters tab at the bottom:

• pipeline_group (String, default: bronze_ingestion) — determines which rows to fetch from the control table
• run_id (String, default: empty) — will be populated at runtime with the pipeline run ID for correlation

Having pipeline_group as a parameter rather than hardcoded means you can eventually call this same pipeline template from a parent orchestrator with different group values, effectively reusing the template across your medallion layers.

Step 2: Lookup Activity — Fetch Active Configuration Rows

Add a Lookup Activity to the canvas. Name it lkp_fetch_control_rows.

Configure it as follows:

• Source dataset: Point to your control table using a Lakehouse or Warehouse connection
• Use query: Select "Query" mode
• Query:

SELECT 
    control_id,
    activity_type,
    source_name,
    copy_source_type,
    copy_source_object,
    copy_dest_lakehouse,
    copy_dest_path,
    copy_dest_format,
    notebook_name,
    notebook_params,
    dataflow_name,
    dataflow_workspace,
    timeout_minutes,
    retry_count,
    retry_interval_secs
FROM dbo.pipeline_control
WHERE pipeline_group = '@{pipeline().parameters.pipeline_group}'
  AND is_active = 1
ORDER BY run_sequence ASC

• First row only: Set to Off — you want all rows, not just the first

The Lookup Activity will return a JSON array of objects, one per active row. Each object contains all the columns you selected. This becomes the input to your ForEach loop.

Step 3: ForEach Activity — Iterate Over Control Rows

Add a ForEach Activity connected to the success output of lkp_fetch_control_rows. Name it fe_process_control_rows.

Configure it:

• Items: @activity('lkp_fetch_control_rows').output.value
• Batch count: Start with 1 (sequential). You can increase this later after validating the pattern — but be aware that parallel Copy Activities against the same destination lakehouse can cause Delta table conflicts if they target the same table.
• Is Sequential: Initially check this box during development. It makes debugging far easier because failures in iteration 3 won't be interleaved with the logs from iterations 4 and 5.

Inside the ForEach, you now build the dispatch logic.

Step 4: Switch Activity — Dispatch by Activity Type

Inside the ForEach (double-click to enter its inner canvas), add a Switch Activity. Name it sw_dispatch_activity_type.

• On expression: @item().activity_type

This expression reads the activity_type column from the current iteration's row. Add three cases: Copy, Notebook, Dataflow. Add a Default case as well, which will serve as your catch-all for unexpected values — configure a Fail Activity inside Default that throws a descriptive error message.

---

Configuring the Copy Activity Case

Inside the Copy case of the Switch, add a Copy Activity. Name it cp_dynamic_copy. This is the activity that will actually move data, and its every settable field will be driven by the current iteration's metadata.

This is the most complex case because Copy Activities have source and sink configurations that differ meaningfully based on source type. For a metadata-driven approach to work here, you need to use the parameterized dataset pattern.

Before configuring the Copy Activity inside the pipeline, go to your workspace and create a parameterized linked service and dataset for each source type you support. For Azure SQL Database, your dataset should have parameters:

• server_name (String)
• database_name (String)
• table_name (String)

Then in the Copy Activity's Source tab:

• Dataset: Select your parameterized AzureSQL dataset
• Dataset properties: Map each parameter to the appropriate metadata value from @item()

For the Sink tab, configure a parameterized Lakehouse dataset with parameters for the target path and format.

In practice, your dynamic expressions inside the Copy Activity will look like this for the source table parameter:

@item().copy_source_object

And for the sink path:

@concat('Tables/', item().copy_dest_path)

For the retry and timeout settings on the Copy Activity, use the metadata columns:

• Timeout: @concat('00:', string(div(item().timeout_minutes, 60)), ':', string(mod(item().timeout_minutes, 60)), ':00') — you need to convert integer minutes to the hh:mm:ss format Fabric expects
• Retry: @item().retry_count
• Retry interval (seconds): @item().retry_interval_secs

This is a small but important detail. If you hardcode retry counts in the template, you've lost one of the key benefits of metadata-driven design.

---

Configuring the Notebook Activity Case

Inside the Notebook case of the Switch, add a Notebook Activity. Name it nb_dynamic_notebook.

Configure the Notebook field as:

@item().notebook_name

This dynamically selects the notebook to run based on the control table row. Fabric will resolve this to a notebook item in your workspace by name at runtime.

For parameters, this is where the JSON column pays off. Your notebook activity has a Base parameters section where you can add key-value pairs. Rather than mapping individual columns, add a single parameter:

• Name: control_params
• Value: @item().notebook_params

Inside the target notebook (in this case, ingest_weather_api), declare the parameter at the top of the first cell:

# Parameters cell - must be tagged as 'parameters' cell type
control_params = '{}'  # default empty JSON

Tag this cell as a Parameters cell (in the Fabric notebook UI, select the cell and click the "..." menu, then "Toggle parameter cell"). This tells the pipeline framework where to inject the value.

Then in the next cell, parse and use the parameters:

import json

params = json.loads(control_params)

api_endpoint = params.get('api_endpoint', '')
target_table = params.get('target_table', 'bronze_default')
lookback_days = int(params.get('lookback_days', 7))

print(f"Running ingestion for {target_table}")
print(f"Source: {api_endpoint}")
print(f"Lookback: {lookback_days} days")

This pattern — a single JSON string parameter — means you never need to change the pipeline's parameter mapping when you change what a notebook needs. You only change the notebook_params column in the control table and update the notebook code.

This approach pairs naturally with transforming data with Spark notebooks in Microsoft Fabric, which covers the broader notebook development patterns you'll use inside these dynamically invoked notebooks.

---

Configuring the Dataflow Activity Case

Inside the Dataflow case of the Switch, add a Dataflow Activity. Name it df_dynamic_dataflow.

The Dataflow Activity is simpler than Copy and Notebook — its main configuration is just pointing to the Dataflow Gen2 item to refresh. Configure the Dataflow field as:

@item().dataflow_name

If your dataflows live in a different workspace (a common pattern where you have separate workspaces for dev, test, and prod, or separate workspaces by team), set the Workspace field to:

@item().dataflow_workspace

For Dataflow Gen2 refreshes triggered from pipelines, Fabric supports passing parameters to Dataflows that have been configured with query parameters. If your dataflow uses parameterized Power Query, you can wire in the metadata columns to override parameter values at refresh time — but this requires the dataflow to have been authored with parameters exposed, which is covered in parameterizing Dataflow Gen2 queries with pipeline integration.

---

Adding the Logging Layer

A metadata-driven framework without execution logging is half-built. You need to record what ran, when it started and finished, what the outcome was, and what error occurred if it failed. This data is what turns your control table from a static configuration into a living operational record.

The cleanest approach in Fabric is to call a logging notebook at the end of each ForEach iteration, using the pipeline's standard success/failure dependency chain.

Create a new notebook named nb_log_pipeline_run. Its parameters cell:

# Parameters cell
control_id = 0
source_name = ''
activity_type = ''
pipeline_run_id = ''
status = ''           # 'Succeeded', 'Failed', 'Skipped'
error_message = ''
duration_seconds = 0

The notebook body:

import json
from datetime import datetime, timezone
from pyspark.sql import Row
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, TimestampType, LongType

# Build the log record
log_row = Row(
    control_id=control_id,
    source_name=source_name,
    activity_type=activity_type,
    pipeline_run_id=pipeline_run_id,
    status=status,
    error_message=error_message if error_message else None,
    duration_seconds=duration_seconds,
    logged_at=datetime.now(timezone.utc)
)

schema = StructType([
    StructField("control_id", IntegerType(), False),
    StructField("source_name", StringType(), False),
    StructField("activity_type", StringType(), False),
    StructField("pipeline_run_id", StringType(), False),
    StructField("status", StringType(), False),
    StructField("error_message", StringType(), True),
    StructField("duration_seconds", LongType(), True),
    StructField("logged_at", TimestampType(), False)
])

log_df = spark.createDataFrame([log_row], schema)

# Append to run_log Delta table in the control lakehouse
log_df.write.format("delta").mode("append").saveAsTable("pipeline_run_log")

print(f"Logged: {source_name} -> {status}")

Back in your pipeline ForEach inner canvas, after the Switch Activity, add two Notebook Activity steps that call nb_log_pipeline_run:

1. nb_log_success — connected to the Success output of the Switch
2. nb_log_failure — connected to the Failure output of the Switch

For nb_log_success, configure the parameters:

control_id:        @string(item().control_id)
source_name:       @item().source_name
activity_type:     @item().activity_type
pipeline_run_id:   @pipeline().RunId
status:            Succeeded
error_message:     (empty string)
duration_seconds:  0

For nb_log_failure, configure the same parameters but set:

status:            Failed
error_message:     @activity('sw_dispatch_activity_type').error.message

This logging pattern integrates naturally with monitoring Fabric capacity usage and pipeline activity with the Monitoring Hub — you now have two observability surfaces: the Monitoring Hub for real-time pipeline execution state, and your pipeline_run_log Delta table for historical analysis and trend reporting.

---

Handling Conditional Logic: Pre- and Post-Activity Dependencies

Real pipelines often need more than just "run this activity." They need "run this activity only if the previous one succeeded, and skip it if we're in a recovery run." The control table schema we designed has a depends_on_id column that hints at this — let's talk about how to actually implement dependency resolution.

For simple sequential dependencies (source B must run after source A), you can handle this in one of two ways:

Option 1: Sequential batch within ForEach. If you keep Is Sequential checked on the ForEach, the rows run in run_sequence order, and each iteration finishes before the next starts. This gives you implicit ordering. If iteration 2 fails, iterations 3-14 still run (the ForEach continues on failure by default). If you want a true "stop on first failure" behavior, you'd need a different architectural approach.

Option 2: Separate pipeline calls per dependency group. Put dependent activities in separate pipeline groups, each triggered by the success of the previous group's orchestrator. This is the pattern used in implementing the medallion architecture in Microsoft Fabric — Bronze completes, then a parent orchestrator triggers Silver, then Gold.

For the common case where you just need Bronze notebooks to run before Silver dataflows, the multi-group approach is cleanest. Your parent pipeline looks like:

1. Execute Pipeline Activity: pl_master_orchestrator with pipeline_group = 'bronze_ingestion'
2. Execute Pipeline Activity (success dependency): pl_master_orchestrator with pipeline_group = 'silver_transform'
3. Execute Pipeline Activity (success dependency): pl_master_orchestrator with pipeline_group = 'gold_aggregation'

Three activities in a linear chain. The template runs three times with different filter parameters. Each group's control rows are fully independent. This is elegant and requires no changes to the template pipeline itself.

---

Advanced Pattern: Dynamic Connection Resolution

One pain point in metadata-driven Copy Activities is connection management. If you have fourteen SQL sources from fourteen different servers, you need fourteen linked services — and you can't dynamically select a linked service from metadata at runtime (this is a Fabric platform limitation inherited from ADF).

The pragmatic workaround is connection parameterization within a single linked service type. For Azure SQL Database, create a single linked service that parameterizes the server and database:

• Server: @{linkedService().server_name}
• Database: @{linkedService().database_name}

Then in your control table, split copy_source_object into three columns: copy_source_server, copy_source_database, and copy_source_table. Your dataset parameters pass these through to the linked service.

This works as long as all your SQL sources use the same authentication method (usually a managed identity or service principal). If they use different credentials, you're back to needing separate linked services — and at that point, the Switch dispatch pattern becomes: match on copy_source_type + copy_source_server and route to the appropriate pre-configured Copy Activity template.

---

Parallelism and Performance Tuning

Once your template is working sequentially, the most common optimization request is "can we run these in parallel?" The answer is yes, with important caveats.

Set the ForEach Batch count to a value between 2 and 10. Start with 4. This runs 4 iterations concurrently. Each iteration may launch a Copy Activity, Notebook run, or Dataflow refresh simultaneously.

The bottleneck is Fabric capacity units (CUs). Notebook activities spin up Spark sessions — each session consumes CUs during initialization and execution. If you have 10 parallel notebook iterations and each notebook session needs 4 CUs, you're burning 40 CUs at once. On an F4 capacity (4 CUs), this will cause severe throttling and queue-backed failures.

Before increasing parallelism:

1. Know your capacity size and CU budget
2. Measure the CU consumption of each activity type in your control table
3. Set batch count to available_CUs / max_CUs_per_activity, rounded down conservatively

For Copy Activities (which don't consume Spark CUs the same way notebooks do), parallelism is much safer. If your control table is primarily Copy rows with a few notebook rows, you can run Copy rows in parallel by splitting the ForEach into two: one for Copy (high batch count), one for Notebook/Dataflow (lower batch count).

The orchestrating multi-notebook workflows in Microsoft Fabric article covers the Spark session sharing patterns that can dramatically reduce the CU cost of parallel notebook execution — worth reading before you scale up.

---

Hands-On Exercise

Build the complete metadata-driven framework from scratch in your Fabric workspace:

Step 1: Create the control table. In your lakehouse, create a Delta table named pipeline_control using the schema defined earlier. Use a PySpark notebook to create it:

spark.sql("""
CREATE TABLE IF NOT EXISTS pipeline_control (
    control_id      BIGINT GENERATED ALWAYS AS IDENTITY,
    pipeline_group  STRING NOT NULL,
    activity_type   STRING NOT NULL,
    source_name     STRING NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    run_sequence    INT NOT NULL DEFAULT 100,
    copy_source_type    STRING,
    copy_source_object  STRING,
    copy_dest_lakehouse STRING,
    copy_dest_path      STRING,
    copy_dest_format    STRING,
    notebook_name       STRING,
    notebook_params     STRING,
    dataflow_name       STRING,
    dataflow_workspace  STRING,
    timeout_minutes     INT NOT NULL DEFAULT 60,
    retry_count         INT NOT NULL DEFAULT 2,
    retry_interval_secs INT NOT NULL DEFAULT 30
)
USING DELTA
""")

Step 2: Insert two test rows. Insert one Copy row pointing to a small test source, and one Notebook row pointing to a simple stub notebook that just prints its parameters and succeeds.

Step 3: Create the stub notebook. Create nb_test_stub with a parameters cell declaring control_params = '{}'. Add a body cell that parses and prints the JSON. Verify it runs manually with a test parameter value first.

Step 4: Build the pipeline. Create pl_master_orchestrator with the two pipeline parameters. Add the Lookup Activity querying your control table. Add the ForEach. Inside the ForEach, add the Switch with Copy and Notebook cases. Keep the Copy case minimal at first — a Fail Activity with message "Copy not configured yet" is fine. Configure the Notebook case fully.

Step 5: Add the logging notebook. Create nb_log_pipeline_run with the schema above. Wire it into the ForEach on both success and failure paths from the Switch.

Step 6: Run and verify. Trigger pl_master_orchestrator manually with pipeline_group = 'bronze_ingestion'. Navigate to the Monitoring Hub and watch the execution. After it completes, query your pipeline_run_log Delta table:

display(spark.sql("SELECT * FROM pipeline_run_log ORDER BY logged_at DESC LIMIT 20"))

Verify you see one row per control table row, with the correct status.

Step 7: Test failure handling. Update the is_active flag to test disabling rows. Then temporarily break the stub notebook (add a raise Exception("Test failure")) and verify that the failure log entry appears with the error message.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting "First row only = Off" on the Lookup. This is the most common error. With "First row only" enabled, the Lookup returns a single object rather than an array. The ForEach then iterates over the object's properties rather than your rows, producing bizarre behavior. The fix is straightforward: disable "First row only."

Mistake 2: Dynamic notebook name fails at runtime. When you set the Notebook Activity's notebook field to a dynamic expression like @item().notebook_name, Fabric resolves this at runtime. If the notebook name doesn't exactly match an item in the workspace (case-sensitive, space-sensitive), the activity fails with "Resource not found." Keep a naming convention in your control table (lowercase, underscores, no spaces) and enforce it with a check constraint on the control table.

Mistake 3: ForEach items expression wrong. The correct expression is @activity('lkp_fetch_control_rows').output.value. A common mistake is using .output without .value, which returns the entire output object (including count and schema metadata) rather than the array of rows. Another mistake: if you named your Lookup Activity differently, the expression must match exactly.

Mistake 4: Copy Activity timeout format. Fabric expects timeout in dd.hh:mm:ss format. If you pass an integer or a string in a different format, the activity fails at startup with a configuration validation error. Test your timeout expression in the pipeline expression builder before running.

Mistake 5: Parallel ForEach and Delta table conflicts. If two parallel ForEach iterations both try to write to the same Delta table (possible if your control table has two rows targeting the same destination), you'll get ConcurrentAppendException from Delta Lake's optimistic concurrency control. Prevent this by ensuring each control row writes to a unique destination path, or by keeping IsSequential = true when writing to shared targets.

Mistake 6: Empty notebook_params column breaking json.loads(). If you insert a row with NULL in notebook_params and your notebook does json.loads(control_params), it will throw a json.JSONDecodeError because None is not valid JSON. Guard against this with:

params = json.loads(control_params) if control_params else {}

Troubleshooting ForEach iteration failures: In the Monitoring Hub, click your pipeline run, then click the ForEach activity. You'll see a list of iterations with individual status indicators. Click the failed iteration to see the inner activity execution details. The error message from the Switch Activity's inner activities will appear here. This is the diagnostic path — bookmark it, because it's not obvious the first time.

For end-to-end pipeline error handling including email alerts, you can extend the logging framework to trigger email notifications when the ForEach's total failure count exceeds a threshold — a useful operational addition once the core framework is validated.

---

Extending the Framework

Once your core template is running, there are several extensions that significantly increase its production value:

Run history table with analytics. Because your pipeline_run_log is a Delta table, you can build a Power BI report on top of it (via Direct Lake or the SQL Analytics Endpoint) that shows average runtime per source, failure rate by activity type, and trend lines over time. This turns your operational log into an engineering dashboard that proactively surfaces degrading sources before they cause SLA breaches.

A "recovery run" flag. Add a last_run_status and last_run_at column to the control table. After each run, your logging notebook updates these columns on the control row. Add a pipeline parameter run_failed_only (Boolean). When true, modify the Lookup query to only fetch rows where last_run_status = 'Failed'. This gives you a one-click recovery run that only retries the failed sources from the last execution — no need to re-run successfully completed sources.

Schema validation pre-checks. Add an optional Notebook row in each pipeline group at run_sequence = 1 that runs a schema validation notebook. This notebook checks that expected source tables exist and have the expected columns before any data movement starts. If validation fails, the ForEach stops early (via the error propagation), preventing partial loads.

Integration with watermark-based incremental loading. Extend the control table with watermark_column, last_watermark_value, and watermark_table columns. Your Copy Activity source query becomes parameterized to filter by watermark: SELECT * FROM @{item().copy_source_object} WHERE @{item().watermark_column} > '@{item().last_watermark_value}'. After a successful copy, the logging notebook updates last_watermark_value in the control table. This integrates the metadata-driven orchestration pattern with incrementally loading data into a Fabric lakehouse with watermarks into a single unified framework.

---

Summary & Next Steps

You've built something genuinely powerful here. A single pipeline template, driven by a configuration table, that can dispatch Copy Activities, Notebook runs, and Dataflow Gen2 refreshes — with per-source retry logic, dynamic parameters, execution logging, and extensible design for incremental loading and schema validation.

The core insight to carry forward: the control table is the product. The pipeline is infrastructure. When your team asks "can we add a new data source?", the answer is "insert a row" — and that answer is enormously more maintainable than "build a new pipeline."

Key things you built:

• A denormalized control table schema that covers all three Fabric activity types
• A Lookup + ForEach + Switch dispatch pattern that dynamically routes to the right activity
• Per-source retry and timeout configuration driven from metadata
• A PySpark logging notebook that records execution outcomes to a queryable Delta table
• Understanding of the parallelism trade-offs and when to increase or avoid concurrent iteration

Where to go next:

Your metadata-driven orchestrator now handles ingestion. The natural next step is to extend it into the Silver and Gold layers using the multi-group pattern — which means building out your Silver transformation notebooks and wiring them into a silver_transform pipeline group. The implementing the medallion architecture in Microsoft Fabric lesson is the right next read for designing what goes in those layers.

For the reporting side, once your Gold layer tables are populated by the orchestrator, connecting Power BI via Direct Lake gives you near-zero-latency reporting without an import refresh. That path starts at Direct Lake Mode in Power BI: How It Works and When to Use It.

And if you want to put your entire pipeline framework under version control — so that the pipeline JSON, the control table seed data, and the notebooks are all tracked in Git and deployable across environments — Fabric Git Integration and Deployment Pipelines covers exactly that.