Azure Synapse Analytics: Introduction To For Each Activity [Introduction to Synapse - Ep. 12]

In this Pragmatic Works training video, Mitchell Pearson introduces Azure Synapse Analytics and dives deep into the For Each Activity. This feature is used to iterate over data, allowing users to process items individually, such as files or records in a list. The For Each Activity is valuable when automating processes, such as moving data, executing transformations, or performing various operations for each item in a dataset.

What is the For Each Activity?

The For Each Activity in Azure Synapse Analytics allows users to iterate over a list of items, performing specific tasks for each item. This functionality is particularly useful when working with a collection of files, rows, or other types of data that require individual attention. In this video, Mitchell explains how to configure and use this activity within an Azure Synapse pipeline.

Setting Up the Pipeline

The tutorial begins by demonstrating how to set up the environment:

• Create a Data Set: Mitchell explains how to create an Azure Data Lake Gen 2 connection for accessing files in the "internet sales" folder. The dataset is used to connect to the data source and define the type of data (in this case, CSV files).
• Creating the Pipeline: After the dataset is configured, Mitchell sets up a pipeline to automate the process. He creates a new pipeline named "For Each Activity" and connects it to the previous dataset configuration. This step allows the For Each Activity to operate on the list of files from the data lake.

How the For Each Activity Works

1. Getting Metadata: The Get Metadata activity is used to retrieve the list of files in a specified folder. This list is returned as an array of objects, which can then be passed to the For Each Activity.
2. Passing Metadata to the For Each Activity: Mitchell explains how the output from the Get Metadata activity is passed into the For Each Activity. This is done by using dynamic content to reference the "child items" property, which represents the files in the folder.
3. Iterating Over Files: Inside the For Each Activity, Mitchell configures the pipeline to loop through each file in the array. For each file, specific activities can be triggered, such as writing metadata to a database, copying files, or running data transformations (ETL processes). This flexibility allows users to apply the same set of actions to each item in the list.

Using Dynamic Content

Dynamic content is used to reference specific properties of the files being iterated over. Mitchell shows how to use dynamic content to select attributes such as "name" or "type" from the metadata. This dynamic referencing is a key feature in Azure Synapse Analytics, enabling users to handle data programmatically without hardcoding values.

Simplified Example

To keep things simple and focused, Mitchell demonstrates how to add a Wait Activity inside the For Each Activity. This ensures that each iteration is executed sequentially, with a pause between steps. Although the video doesn't cover complex operations, it provides a foundation for using the For Each Activity and preparing for more advanced integrations, such as writing to a database.

Conclusion

The For Each Activity in Azure Synapse Analytics is a powerful tool for automating repetitive tasks across multiple data items. Whether you're working with files, records, or any other type of data, this activity enables you to perform complex workflows in an efficient, scalable manner.

In the next video in the series, Mitchell plans to expand on this topic by demonstrating how to pass data from the For Each Activity into a Stored Procedure Activity and store the results in a database. This follow-up will complete the end-to-end pipeline for processing files, transforming data, and writing results to a table.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Synapse Analytics and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.