Avoiding expensive iterators in Fabric Pipelines with T-SQL

Did you know it costs 20.16 CUs for every non-data movement activity inside a Microsoft Fabric data factory pipeline? This is a serious problem when you use a ForEach iteration activity. I will show you how to avoid expensive iterators and, from this point on, make sure you Obliterate Before You Iterate!

I have a pipeline that takes a parameter of numberStartRange and numberEndRange. For each number within the range, I want to turn it into a list of dates as an offset number from today. For example, 0 to 2 will return [‘2025-12-16′,’2025-12-16′,’2025-12-17’].

The code and configuration to do this is not very complicated:

@range(
    pipeline().parameters.numberStartRange,
    pipeline().parameters.numberEndRange
)

@formatDateTime(
    addDays(pipeline().TriggerTime, item()),
    'yyyy-MM-dd'
)

For a date range of 91 days, the pipeline consumed 1,834.56 CUs. That’s the processing equivelant of using copy data fromn a database to a 10 million row delta table. That’s ridiculous!

Rather than iterating in a Fabric pipeline, you can execute a T-SQL statement to do the same thing. You can do this against any lakehouse or warehouse because the function is data independent, but if the code re-used in the future you could turn it into a stored procedure in a central warehouse.

(The point of this post is not the T-SQL code, there are a zillion ways to do this and a billion better ways to write it)

DECLARE @start INT      = @{pipeline().parameters.numberStartRange};
DECLARE @end   INT      = @{pipeline().parameters.numberEndRange};
DECLARE @date  DATETIME2 = '@{pipeline().TriggerTime}';
SELECT
    '[' +
    STRING_AGG(
        '''' + CONVERT(char(10), DATEADD(DAY, n, @date), 23) + '''',
        ','
    )
    + ']' AS DateArray
FROM (
    SELECT TOP (@end - @start + 1)
           @start + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM sys.objects
) x;

Now our pipeline used 20.26 CUs, 20.16 for the pipeline activity and .1 for the T-SQL compute. That is bananas!

T-SQL provides an incredibly flexible, powerful, and cheap way to avoid Fabric pipeline iterators.