Data Transformation in Azure Data Factory (ADF) – An overview

Introduction

Microsoft’s Azure Data Factory (ADF) is a cloud-based data integration service. The platform provides users with the ability to construct, schedule, and manage data pipelines that transfer and transform data between different sources and destinations. A key component of any data integration process is data transformation, which involves converting data from its original format into a format the target system can understand. You can manipulate data during pipeline execution by using ADF data transformation activities.

With ADF, users can transform data, such as converting, mapping, and cleansing, by simply dragging and dropping files. There are several built-in transformations incorporated into the service that can be used to transform data in various ways, such as aggregating data, splitting data, or combining data from different sources. As an additional feature, ADF supports custom transformations, allowing users to write and deploy their own code to transform data. By transforming their data into the desired format with ADF, users can make it easier to use downstream applications and analytics.

we will discuss the data transformation capabilities of ADF and provide step-by-step instructions on how to implement them.

Prerequisites

To follow this guide, you must have the following as prerequisites:

1. An Azure subscription.
2. An ADF instance.
3. A basic understanding of data transformation concepts and technologies.

Data Transformation in ADF

As part of ADF, you can perform a variety of data transformation activities that enable you to transform data at scale. These activities include:

1. Mapping Data Flows: 

The mapping data flow tool provides you with the ability to design and execute complex data transformations visually and in an intuitive way. It is possible to use mapping data flows to transform data stored in a number of different sources, such as Azure Blob Storage, Azure SQL Database, and others, in order to create new data.

2. Wrangling Data Flows:

This is a powerful data preparation tool that can be used for the cleansing, transforming, and presenting of data without the need for any coding knowledge. As a data preparation task, you can use wrangling data flows to split columns, pivot data, and filter rows in order to prepare your data for analysis.

3. Stored Procedures:

This is a SQL-based data transformation tool that allows you to execute custom SQL scripts against your data in order to transform it according to your specifications. Using stored procedures, you will be able to perform complex data transformations that are not possible if you map or wrangle your data flows.

Now let’s take a closer look at each of these data transformation activities.

Mapping Data Flows

Mapping data flows provide a graphical interface through which you can design and execute data transformations based on your requirements. Data can be transformed from various sources by using mapping data flows, such as Azure Blob Storage, Azure SQL Database, and other data stores, like Azure Blob Storage and Azure SQL Database.

Here are the steps to create a mapping data flow:

1. Login to your Azure Data Factory portal – https://adf.azure.com/
2. In ADF, click on the “Author & Monitor” tab and select the relevant data factory.
3. Click on the “Author” button, and then click on the “Create pipeline” button.
4. Drag the “Mapping Data Flow” activity from the “Data Flow” tab and drop it onto the canvas.
5. Click on the “Mapping Data Flow” activity to open the mapping data flow editor.
6. Use the editor to design your data transformation by dragging and dropping various data flow transformations, such as source, sink, join, and aggregate.
7. Save and publish your data flow.

For detailed guide on how to use mapping flow data, see Data transformation using Mapping data flows in Azure Data Factory

Wrangling Data Flows

By using Wrangling data flows for cleaning and transforming data, you don’t have to write any code. With the help of wrangling data flows, you will be able to perform a number of data preparation tasks, including splitting columns, pivoting data, and filtering rows.

Here are the steps to create a wrangling data flow:

1. In ADF, click on the “Author & Monitor” tab and select the appropriate data factory.
2. Click on the “Author” button, and then click on the “Create pipeline” button.
3. Drag the “Wrangling Data Flow” activity from the “Data Flow” tab and drop it onto the canvas.
4. Click on the “Wrangling Data Flow” activity to open the wrangling data flow editor.
5. Use the editor to perform your data preparation by adding and configuring various data transformations, such as split column, pivot, and filter rows.
6. Save and publish your data flow.

For detailed guide on how to use wrangling flow data, see Data transformation using Wrangling data flows in Azure Data Factory.

Stored Procedures

In the SQL language, stored procedures can be configured in various ways to implement custom data transformations against your data. In addition to mapping and wrangling data flows, stored procedures can also be used to perform complex data transformations that are not possible when using mappings or wranglings.

Here are the steps to create a stored procedure:

1. In ADF, click on the “Author & Monitor” tab and select the appropriate data factory.
2. Click on the “Author” button, and then click on the “Create pipeline” button.

3. Drag the “Stored Procedure” activity from the “Data Flow” tab and drop it onto the canvas.
4. Click on the “Stored Procedure” activity to configure it.
5. Enter the SQL script for your custom transformation.
6. Configure the input and output datasets for your stored procedure.
7. Save and publish your pipeline.

For detailed guide on how to use stored procedures, see Data transformation using Stored procedure in Azure Data Factory.

Use Cases for data transformation in ADF

Here are some examples of how you can use ADF for data transformation:

1. Convert CSV files to JSON format: 

Data mapping flows can be used to transform CSV files into JSON format through the process of mapping data flows. Incorporating data into a JSON-based system, such as Azure Cosmos DB, can be very useful if you need to ingest data into a JSON-based system.

2. Clean and transform data for reporting: 

You can use wrangling data flows to clean and transform data for reporting purposes. In the case of business intelligence dashboards, you can use flow wrangling to pivot the data and calculate aggregates based on the data.

3. Execute custom SQL scripts against your data: 

Using stored procedures, you can run customized SQL scripts against your data using SQL scripts that you write. For instance, a stored procedure can be used for a variety of purposes such as cleaning, enriching, and aggregating data.

Conclusion

To conclude, ADF is an integration service that offers a wide range of data transformation capabilities that can help organizations transform their data from various sources to the desired destinations using a wide range of data integration capabilities. With ADF, users are able to easily create and manage data pipelines that incorporate data transformation tasks, such as data conversion, mapping, and cleansing, using an intuitive and easy-to-use interface, which provides a quick and easy method for data transformation tasks. With the service, users have access to a range of built-in transformations as well as custom transformations, which allows them to transform data in anyway they see fit.

By leveraging the power of Azure Data Factory’s data transformation capabilities, organizations can improve their data quality, streamline their data integration processes, and make their data more accessible and usable for downstream applications and analytics. Overall, Azure Data Factory is a valuable tool for any organization that needs to integrate and transform their data in a cloud-based environment, in a cost-effective and efficient manner.