How to generate a date range in Azure Synapse without Spark notebooks

Every since I’ve started using Synapse for an assignment, I’ve preferred using Spark Notebooks to get anything done. However, they take time to spin up, something I wanted to mitigate by using Synapse-native components. In this article, we’ll generate a date range without Spark notebooks.

It’s unnecessarily complicated, but you can definitely create a data range using Synapse/ADF activities only. Let’s dive right in.

First, you should create pipeline parameters which mark the start and the end of the date range. Both should be string.

You should also set the pipeline variables. The date_range variable is an array will contain the constructed date range. The last_date_intermediate will constantly update with the next date in the until loop we’ll set later.

Next, create an until activity and provide it the following expression. This means it will keep looping until the last item of the array variable date_range is the same as the end parameter.

@equals(last(variables('date_range')),pipeline().parameters.end)

Within the until activity, we’ll create two sequential activities: a set variable activity and an append variable activity.

While the until loop runs, the first activity, the set variable activity, will set the last_date_intermediate variable to the next date between the start and end date specified in the pipeline parameters.

@if(
    equals(length(variables('date_range')),0),
    pipeline().parameters.start,
    substring(
        addDays(
            concat(last(variables('date_range')),'T01:00:00Z'),
            1
        ),
        0,
        10
    )
)

This is what it looks like in the GUI:

The append variable activity should be parametrized with the following dynamic content. It will read the newly set value from the last_date_intermediate variable, and append it to the date_range array variable.

@variables('last_date_intermediate')

This is what it looks like in the GUI:

If you though you could eliminate the first set variable activity by doing the calculation on the last date from the date_range array, inside the append variable activity, think again. You’ll run into this error:

The expression contains self referencing variable. A variable cannot reference itself in the expression.

Your until activity is is basically done now. If you’d like to loop over the constructed array, you should use a ForEach activity. Its items should refer to the date range array variables.

@variables('date_range')

In the GUI:

Finally, inside the ForEach activity, you can refer to the individual dates of the date range, simply by referring to the @item().

The final result should look something like this. I’ve used a subpipeline in the ForEach activity, but choose whatever floats your boat.