This blog will discuss how to achieve write back (writeback) functionality when data is in Azure Synapse.
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.
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.
- 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
Note: Insert and Update to Synapse steps uses “Execute a SQL query” action
In some cases, you may have Azure Databricks for data analytics just before Synapse like below
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.