Azure Synapse Analytics: Parameters in ASA [Introduction to Synapse - Ep. 14]

In this episode of Pragmatic Works' "Introduction to Synapse," Mitchell Pearson walks viewers through the process of creating dynamic datasets using parameters in Azure Synapse Analytics (ASA). These dynamic datasets are reusable across various pipelines and activities, providing a more flexible and scalable solution for working with data. This blog will summarize the key steps in the video, demonstrating how to leverage parameters within ASA to create dynamic, efficient workflows.

The Challenge: Static Datasets and Metadata

Mitchell starts by revisiting a previous project where a For Each activity iterates over data files in Azure Data Lake, processes them, and writes the information to a database table. However, a limitation arises when the metadata activity only returns basic file information (name and type). The goal of this session is to go beyond that and retrieve the file's last modified date using parameters, making the process more dynamic and reusable.

Step 1: Creating a Dynamic Dataset

To achieve this goal, the first step is to create a dynamic dataset. Mitchell demonstrates how to create a dataset that connects to Azure Data Lake Gen 2, specifically designed for CSV files. The key difference here is the use of parameters to replace hard-coded values. This enables the dataset to be reused for multiple files rather than being static and tied to a single file.

• Click on "New" to create a new dataset.
• Choose Azure Data Lake Gen 2 as the source, and select delimited text as the file type.
• Provide a name for the dataset, such as "Dynamic DataSet."
• Select the link service (the storage account you want to connect to).
• Choose a file to use initially, which will later be parameterized for dynamic use.

Mitchell emphasizes that instead of importing a schema, the dataset should be left flexible so it can accommodate various file structures within the data lake.

Step 2: Adding Parameters to the Dataset

Once the dataset is created, the next step is to add parameters. A parameter is a placeholder for dynamic values. Mitchell walks through creating a new parameter called fileName that will hold the name of each file iterated over in the For Each activity.

• Under the "Parameters" section, click on "New" to create a new parameter.
• Set the parameter's name (e.g., fileName) and default value to "placeholder."
• This placeholder value will trigger a validation error if not properly configured, helping ensure everything runs smoothly.
• Go back to the connection settings and link the parameter to the correct file name dynamically using dynamic content.

By doing this, each time the For Each activity iterates, the fileName parameter dynamically updates to the name of the file being processed.

Step 3: Modifying the For Each Activity

With the dynamic dataset in place, Mitchell now revisits the For Each activity. Here, he shows how the activity iterates over the files and invokes the Get Metadata activity to fetch details like the last modified date of each file.

• The Get Metadata activity is updated to pull the last modified date using the newly configured dynamic dataset.
• Mitchell explains how to configure the activity so that each iteration fetches the metadata of a different file based on the current iteration's file name.
• He also walks through adjusting the stored procedure to use the dynamic last modified date instead of a hard-coded UTC date.

Step 4: Running the Pipeline

After configuring everything, Mitchell runs the pipeline to test the setup. The For Each loop successfully iterates through the files, retrieving their last modified dates and writing the information into the database table. He highlights that by using dynamic datasets and parameters, the process becomes scalable and avoids creating multiple static datasets for each file.

• The output shows that the metadata is successfully updated, with different last modified dates for each file.
• Mitchell emphasizes the benefit of using dynamic parameters to streamline workflows and reduce the complexity of managing multiple datasets.

Conclusion

Mitchell concludes the session by reinforcing the importance of dynamic datasets and parameters in Azure Synapse Analytics. By leveraging these tools, users can create reusable, scalable pipelines that avoid unnecessary bloat in the workspace. This method allows for more efficient management of data processes, making it easier to handle a variety of file structures and metadata.

For those interested in learning more, Mitchell suggests reviewing the previous episodes in the series to fully understand the concepts and build upon this knowledge. Dynamic datasets are a powerful tool for streamlining your data operations in ASA, and with the right configurations, they can save you significant time and effort.