Recently I worked with a team having a requirement to send ad-hoc queries to an Azure Synapse Analytics database using front-end tools such as Power BI and Excel. Users would need to query Synapse periodically a few times a week without a set schedule or specific working hours. Behind the SQL endpoint, the underlying Azure Synapse Dedicated SQL Pools provide the power of a Cloud MPP database for massive data volumes (potentially billions of rows) receiving complex queries. When the Synapse Dedicated SQL Pools are running, they are also running up a bill. The situation presented a unique problem - How can I give non-technical ad-hoc users the ability to resume Synapse without taking the risk that they forget to pause it and run up unnecessary charges by leaving it running while nobody is sending queries?
Fortunately, integration with other tools in the Microsoft ecosystem provided a solution. In less than an hour (a few minutes once you've done it before) you can enable those non-technical ad-hoc users to simply enter a keyword into Teams to Resume Synapse, run it for a set length of time, and then have it automatically Pause again.
I've embedded two videos below that walk you through the process of setting up a Microsoft Teams "Keyword Trigger" to Resume and then Pause Synapse Dedicated SQL Pools. Here's a quick list of the Microsoft tools you will need to build it yourself:
- Instance of Azure Synapse Analytics with Dedicated SQL Pools (you can deploy a demo and test version from this GitHub site - https://github.com/kunal333/E2ESynapseDemo)
- Azure Data Factory (can be deployed within the same Azure Resource Group as Synapse). In the future, it may be possible to use Azure Synapse Pipelines with Power Automate and avoid a separate Azure Data Factory.
- A Team in Microsoft Teams
- Power Automate
Video #1 - Create Azure Data Factory Pipelines to Pause & Resume Azure Synapse Dedicated SQL Pools
Steps:
- Create a New Data Factory (or use an existing one!)
- Give “Power Apps and Flow” access to use that Data Factory
- Create a Pipeline with a Web Activity
- Add the API for “Resume Synapse”
- Repeat steps 3-4 for another Pipeline to “Pause Synapse”
- Give the Data Factory access to the Azure Synapse Workspace
Video #2 - Use a Keyword in Microsoft Teams with a Power Automate Flow to Resume & Pause Azure Synapse
Steps:
- Create a Power Automate Flow
- Designate a Teams Keyword to Trigger the Flow
- Choose the "Resume Synapse" Data Factory Pipeline created in Video #1
- Determine the duration of time that Synapse will run ("Delay" step in the Flow)
- Choose the "Pause Synapse" Data Factory Pipeline created in Video #1
- Test the Power Automate Flow in Teams
Those of you who have experience with either Azure Data Factory or Power Automate will realize that this architectural pattern can be extended to a great deal of new possibilities. For example:
- With Azure Data Factory
- Extend the Synapse Pause/Resume pipelines in Azure Data Factory to be part of larger ELT/ETL processes
- Trigger Pipelines completely unrelated to Synapse - Update forecasting reports, refresh the daily data in an Azure SQL DB, etc.
- With Power Automate
- Create multiple Power Automate Flows that different people can use for different durations of Synapse uptime. Maybe a manager can Resume Synapse for 8 hours, while an analyst can resume it for 1 hour?
- Use different triggers to turn on Synapse. Maybe a Power BI button or an action from another tool?
- Add steps to log who is using the Power Automate Flow, and when.