In this tutorial, Austin Libal, a trainer at Pragmatic Works, demonstrates a powerful technique to optimize both cost and performance when running ETL processes in Azure. By dynamically scaling an Azure SQL Database up before execution and down afterward, organizations can significantly reduce expenses while maintaining high performance during heavy workloads.
Azure SQL Databases operate on a pricing model based on compute resources, measured in DTUs (Database Transaction Units). A database configured at a lower tier, such as Basic, is cost-effective but may not provide sufficient performance for intensive ETL workloads.
Austin explains that instead of permanently running a database at a higher tier, users can:
This approach ensures that users are billed at higher rates only when necessary, optimizing both cost and efficiency.
The first step in this process involves creating stored procedures within the Azure SQL Database:
Austin highlights that scaling operations in Azure SQL are performed online, meaning they can occur without downtime. This allows ETL processes to run seamlessly without interrupting database availability.
To automate this process, Austin uses Azure Data Factory to create a pipeline that orchestrates the scaling and ETL workflow. The pipeline includes several key components:
This activity triggers the stored procedure responsible for scaling the database up. It connects to the Azure SQL Database via a linked service.
The pipeline uses an Until loop to wait until the database has fully scaled to the desired compute tier (e.g., S3) before proceeding.
Inside the loop, a lookup query retrieves the current compute size of the database. This information is used to determine whether scaling has completed.
An optional wait step (e.g., 15 seconds) prevents excessive polling and reduces unnecessary load on the system.
Austin demonstrates how to configure an expression within the Until activity to evaluate whether the database has reached the desired tier. The logic compares the current compute size retrieved from the lookup activity with the target value (S3).
Once the condition is met, the pipeline continues execution, ensuring that ETL processes only begin when sufficient resources are available.
When the pipeline is executed in debug mode, users can observe the scaling process in real time. Outputs from the lookup activity reveal whether the database is still in the Basic tier or has transitioned to S3.
Austin notes that scaling time depends on the size of the database, typically taking about one minute per gigabyte of data. Once scaling is complete, the ETL process can proceed efficiently.
After the ETL process finishes, a second stored procedure is triggered to scale the database back down to its original tier. This ensures that higher compute costs are incurred only during active processing.
Austin also suggests adding validation logic to confirm that the database has successfully scaled down, especially when working with higher-cost tiers.
Austin Libal emphasizes that this method is one of several ways to manage database scaling in Azure. Alternatives include using web activities or embedding logic directly within stored procedures. However, this approach provides a straightforward and accessible solution, especially for those new to Azure Data Factory.
For those looking to deepen their knowledge, Pragmatic Works offers additional training through boot camps and on-demand learning platforms, covering Azure SQL Database, Azure Data Factory, and Azure Synapse Analytics.
By implementing dynamic scaling strategies, users can achieve a balance between performance and cost—making their data workflows more efficient and scalable.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Data Factory and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.