Writing an UPDATE SQL query in Azure Data Factory

Question

I'm relatively new to ADF. We currently have a pipeline running that reads data from 5 separate csv files in blob storage, copies them to tables in Azure SQL database, and then calls 4 separate SQL server stored procedures which update, clean, and transform the data in the SQL tables.

We'd like to have the logic of the SQL stored procedures run within ADF itself rather than as stored procs on the SQL database.

1. Can someone advise on what tool would be the best to get started (e.g. Power Query?)
2. As an example, how would I implement a basic UPDATE SQL query in ADF that joins two tables and updates rows in one table based on values in the other table?

Answer 1

Hello David Lang,

Welcome to the Microsoft Q&A forum.

If you want to move the logic of the SQL stored procedures to ADF, you can use Mapping Data Flows in ADF. Mapping Data Flows is a visual data transformation tool that allows you to build data transformation logic without writing any code. You can use it to perform complex data transformations, including joins, aggregations, and conditional logic.

To implement a basic UPDATE SQL query in ADF that joins two tables and updates rows in one table based on values in the other table, follow these steps:

1. Create a new Mapping Data Flow in your ADF pipeline.
2. Add two source transformations to your data flow, one for each table you want to join.
3. Add a Join transformation to your data flow and configure it to join the two tables based on the common column(s).
4. Use the Derived Column transformation to create or modify columns based on your business logic. Ensure you maintain the existing columns you want to update.
5. Add a Sink transformation to your data flow and configure it to specify the destination for the transformed data. Configure the Sink to perform an upsert or overwrite operation to update existing rows.
6. Test and debug your Data Flow to verify the transformations work as expected.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

I hope this helps.