This blog will discuss how to achieve write back (writeback) functionality when data is in Azure Synapse.
Business Requirement
In some cases, data are in Azure Synapse and need write-back functionality. Power BI has a connector to Azure Synapse for reports and visualization, but PowerApps does not.
Please refer this blog for what is writeback and multiple ways to achieve the functionality or requirement.
Solution
Since no connector to Synapse, we can’t directly connect from PowerApps. I came up with the workaround to have an Azure SQL Server in the middle, which has a subset of data that is only required for writeback. This solution works perfectly since power automates writes to Synapse in near real-time.
Architecture
- Using AzCopy moves the data from on-premises to Azure Synapse
- Another AzCopy (or via other Integration) from Synapse to Azure SQL Server
- Copy only the data needed for writeback
- PowerApps has a connector to Azure SQL to have writeback
- Power Automate automatically triggers for any update event
- Power Automate update Synapse
Power Automate Steps
- Power Automate get triggered when an update event happens in SQL
- Send this data to another table (History) for Auditing
- Get a record from Synapse for that Id
- Verify record exists for that id
- Insert new row/record if not exists
- Update the record if exists
Sample Flow (Click an image to enlarge)
Formula Used on the Flow
empty(outputs(‘Get_rows_from_Synapse’)?[‘body/value’])
Note: Insert and Update to Synapse steps uses “Execute a SQL query” action
Other Option
In some cases, you may have Azure Databricks for data analytics just before Synapse like below
Conclusion
For Power BI, the data source will be only Azure Synapse and user interaction is very smooth when using the “PowerApps embedded in Power BI” method.
Related Articles
https://learntoilluminate.com/2021/06/power-bi-write-back-functionality-comparison/
https://learntoilluminate.com/2021/08/writeback-to-synapse-using-powerapps-part-2/
Can you pls share the definition of the Power Automate flow?
Hi Hiram
Thanks for reading my blog. I have updated the blog post to reflect the Power Automate that integrates Azure SQL and Synapse.
Thanks
Stalin
Thanks for the update Stalin! Very well done.
Thank you.
Hey Stalin, Do you have to run AzCopy everyday or is a one time thing? If it is daily, can it be automated.
Hi Filimon,
Yes, we have to run AzCopy whenever data changes in Azure Synapse. For one client, We have copied from on-premises to Synapse everyday night. So we run this AzCopy after that job is completed.
Ok Stalin.
Is there any way to automate the change from synapse to SQL either with AzCopy or without?
Also Did you face issues passing inputs directly to Power Automate instead of Azure SQL?
We can use the Azure Data Factory to load data from Synapse to SQL. We can call Power Automate directly from PowerApps but not better architecture. Users may not see the latest data When they try to edit them in PowerApps since Synapse to SQL is not near real update.