You are building a data loading pattern by using a Fabric data pipeline. The source is an Azure SQL database that contains 25 tables. The destination is a lakehouse. In a warehouse, you create a…

This question tests the metadata-driven (dynamic) ingestion pattern in Microsoft Fabric data pipelines, where a control table drives parameterized copy operations across multiple source tables in a single pipeline execution.

Design and implement data ingestion and transformation

Question

You are building a data loading pattern by using a Fabric data pipeline. The source is an Azure SQL database that contains 25 tables. The destination is a lakehouse. In a warehouse, you create a control table named Control.Object as shown in the exhibit:
schema_nametable_name
1WarehouseColdRoomTemperatures
2WarehouseColors
3WarehousePackageTypes
4WarehouseStockGroups
5WarehouseStockItems
6dboBuildVersion
7dboErrorLog
8ApplicationSystemParameters
9PurchasingPurchaseOrderLines
10PurchasingPurchaseOrders

You need to build a data pipeline that will support the dynamic ingestion of the tables listed in the control table by using a single execution. Which three actions you should perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

	schema_name	table_name
1	Warehouse	ColdRoomTemperatures
2	Warehouse	Colors
3	Warehouse	PackageTypes
4	Warehouse	StockGroups
5	Warehouse	StockItems
6	dbo	BuildVersion
7	dbo	ErrorLog
8	Application	SystemParameters
9	Purchasing	PurchaseOrderLines
10	Purchasing	PurchaseOrders

Explanation

Approach. The correct three-step sequence is: (1) Add a Lookup activity configured to query SELECT * FROM Control.Object in the warehouse - this returns all schema/table name pairs as an array. (2) Add a ForEach activity connected to the Lookup output, setting its Items property to @activity('Lookup').output.value to iterate over every row. (3) Inside the ForEach, add a Copy activity with dynamic source expressions - table schema set to @item().schema_name and table name set to @item().table_name - pointing to the Azure SQL source, and the lakehouse as the sink. This pattern allows a single pipeline execution to dynamically ingest all 25 tables without hard-coding any table names, purely driven by the control table rows.

Concept tested. Metadata-driven (control table) dynamic ingestion pattern in Microsoft Fabric Data Pipelines using Lookup → ForEach → Copy activity chaining with dynamic content expressions

Reference. Microsoft Learn - Implement a metadata-driven pipeline by using Microsoft Fabric (DP-700 / Fabric Data Engineer)

Topics

#Fabric Data Pipelines#Dynamic Ingestion#Azure SQL Database#Lakehouse

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice