Transforming Data With Azure Data Factory Data Flow

Azure Data Factory (ADF) offers a convenient cloud-based platform for orchestrating data from and to on-premise, on-cloud, and hybrid sources and destinations. But it is not a full Extract, Transform, and Load (ETL) tool. For those who are well-versed with SQL Server Integration Services (SSIS), ADF would be the Control Flow portion.

You can scale out your SSIS implementation in Azure. In fact, there are two (2) options to do this: SSIS On-Premise using the SSIS runtime hosted by SQL Server or On Azure using the Azure-SSIS Integration Runtime.

Azure Data Factory is not quite an ETL tool as SSIS is. There is that transformation gap that needs to be filled for ADF to become a true On-Cloud ETL Tool. The second iteration of ADF in V2 is closing the transformation gap with the introduction of Data Flow.

Let’s build and run a Data Flow in Azure Data Factory v2.

Overview

Just to give you an idea of what we’re trying to do in this post, we’re going to load a dataset based on a local, on-premise SQL Server Database, copy that data into Azure SQL Database, and load that data into blob storage in CSV Format.

In this post we discuss the following topics in a step-by-step fashion:

1. Azure Data Flow
2. Data Transformation in Azure Data Factory with Data Flow
3. Data Flow Components:
   • Select
   • Derived Column
   • Aggregate
   • Pivot
   • Sort
   • CSV Sink

First thing first: If this is the first time you’re using Azure Data Factory, you need to create a data factory in Azure, and for the purpose of the demo in this post, you also need to set up a blob storage.

Prerequisite: Copy the Working Data

Create a new pipeline with copy activity. If you follow the instruction from the previous post, Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory, that is our first step.

Change the copy activity source and sink as follow:

Source Query:

SELECT c.CustomerName                    
       , p.FullName AS SalesPerson
       , o.OrderDate
       , s.StockItemName
       , l.Quantity
       , l.UnitPrice
 FROM Sales.Orders o
 JOIN Sales.OrderLines l
   ON o.OrderID = l.OrderID
 JOIN Sales.Customers c
   ON o.CustomerID = c.CustomerID
 JOIN Warehouse.StockItems s
   ON l.StockItemID = s.StockItemID
 JOIN Application.People p
   ON p.PersonID = o.SalespersonPersonID ;

Sink (New table)

CREATE TABLE SalesReport ( 
 CustomerName NVARCHAR (100), 
 SalesPerson NVARCHAR (50), 
 OrderDate DATE, 
 StockItemName NVARCHAR (100), 
 Quantity INT, 
 UnitPrice DECIMAL (18, 2)) ;

Create A Data Flow

Navigate to your Azure Data Factory. Click the Author & Monitor tile to open the ADF home page. From there, click on the pencil icon on the left to open the author canvas.

1. Click on the ellipsis next to Data Flows (which is still in preview as of this writing).
2. Select Add Dataflow in the context menu.
3. Let’s name our Data Flow DataFlowTest001.

Add the Data Source

As of this writing, there are only a few supported sources in Data Flow: Azure Data Warehouse, Azure SQL Database, CSV, and Parquet (column-oriented data storage format of Hadoop). Other sources such as Amazon S3, Oracle, ODBC, HTTP, etc. are coming soon.

Let’s set up the Source.

1. Click on the Add Source tile.
2. Let’s name our source as DataFlowTest001DataSource.
3. Next, create a new dataset.

Create New Source Dataset

We need to set up a new dataset. You should have completed the Prerequisite (see above) by now so you will have a working data for this demo.

There are only a few supported data sources as of this writing: Azure SQL Data Warehouse, Azure SQL Database, CSV, and Parquet (column-oriented data storage format for Hadoop).

1. In the Source Dataset, click New.
2. Select Azure SQL Database. Click Finish.

Set up the Source Dataset

Set the dataset as follows:

1. Name the dataset as AzureSQLSalesReport.
2. Select the Linked Service AzureSqlDatabase.
3. For the Table, select the SalesReport. Click Finish.

If you are wondering what that Linked Service is, that would be the connection in SSIS. It is easy to set up. At the bottom of the left-hand blade, click on Connection. This gives you the list of your Linked Services and Integration Runtimes. You can also create new Linked Services from this page.

The AzureSqlDatabase linked service was set up as follows:

Add a Select Component

1. To add a component to the Data Flow, click the Plus sign at the bottom of the DataFlowDataSource component.
2. In the Row Modifier section of the context menu, select Select.

Set up the Select Component

What the Select component does is just that, selecting columns like you would if execute a Select command.

1. Name the component as SalesPersonData.
2. Add the following columns if they aren’t yet added.
   • SalesPerson
   • OrderDate
   • Quantity
   • UnitPrice

You may notice the information on the left-hand side that says, “4 items: 2 column(s) from the inputs left unmapped.” That’s what we want.

You may want to Inspect and Preview the data. But make sure you switch the Debug mode on top before you preview.

Add a Derived Column Component

1. Name the Derived Column component or stream as DerivedYearOrderDate. Make sure that the incoming stream is the SalesPersonData (Component).
2. Add a new derived column called Year and set the expression to year(Orderdate)

Add an Aggregate Component

1. Name the Aggregate component as LineSalesTotal. The incoming stream should be DerivedYearOrderDate.
2. Click the Group By Column and add the following Columns
   • SalesPerson
   • Year (derived column based on the OrderDate)
3. Click the Aggregates tab. Add a new column and call it Linesales. Set the expression to sum(multiply(UnitPrice, Quantity))

Add a Pivot Component

1. Name the component as PivotSalesPersonPerYear. Make sure that the incoming stream is from our aggregate, LineSalesTotal.
2. Click on the Group By tab. Select SalesPerson. Keep the alias as is or assign a different alias.
3. Click on the Pivot Key tab. Select Year.
4. Click on the Pivoted Column tab. Set up as follows:
   • For the Column name pattern, select the first pattern in the dropdown.
   • Select Lateral for Column arrangement.
   • Type [space] Total Sales in the Suffix box.
   • In the expression box, add sum(LineSales)
   • Leave the column prefix empty.

Add a Sort Component

Let’s sort the stream by name, alphabetically sorted ascending.

1. Name the Sort component as SortSalesPersonByName. Make sure that the incoming stream is PivotSalesPersonPerYear.
2. On the Sort Condition, select SalesPerson.
3. Toggle the Order arrow so it is pointing up.

If you preview the data on the Sort component, you should now see something like this:

Add Destination Sink Component

1. Let’s call the Sink, SalesReportByYearCSV.
2. I have previously created a CSV dataset on Azure Blob Storage . You can also create a New dataset in your Data Flow. Create, or in our case, select SalesReportCSV dataset from the Sink dataset dropdown.
3. Navigate to the Settings tab. On the File name option, select Output to single file.
4. For the File name, type 2013-2016_SalesReportByYear.

Note: The Column Count on the last 3 components don’t look right. Even the mapping is not showing the right number of the streamed columns. The preview shows the expected results, though.

Overview of the Azure Data Factory Data Flow

If you’ve completed all the above steps, you should now have something like this:

Run the Data Flow

Just like in SSIS, you can not run the Data Flow itself in the data flow context. It needs to be in the Control Flow, or Pipeline in ADF, for its execution.

1. Create a new Pipeline. Name it DataFlowSalesReport.
2. Navigate to Activities > Move & Transform. Drag the Data Flow activity component to the canvas. That opens the blade on the left-hand side where you can set up the Data Flow that we just created above with the pipeline activity.
3. In the Adding Data Flow blade, select Use Existing Data Flow.
4. Select the Existing Data Flow dropdown, DataFlowTest001. Click Finish.

The only setting that I would change in the pipeline setting is the Timeout. Set the pipeline to time out after 30 minutes.

Publish the Changes and Trigger the Activity Now

1. Click Publish (should be on top, right-hand-side.
2. Click on the Trigger, then select Trigger Now to execute the pipeline (and essentially the Data Flow).

Here are some of the few ways of checking the status of the pipeline activity processing:

Check the CSV File Output

1. To check the file, open your Azure Storage Explorer and Navigate to your Blob storage. You can download the Azure Storage Explorer here.
2. And you should find the CSV output file produced by the Data Flow.