Scaling Your Data Warehouse: What are DWUs and why do they matter?

Setting up a modern data platform is not easy. Technologies change rapidly and in order not to fall behind, as a data engineer you have to keep your mind on all kinds of possible new scenarios in the future. A (cloud) data platform is therefore never really fully ‘finished’ and should be monitored at all times to see if there are no improvements, that can be made, to make data processing as smooth as possible.

A part of this monitoring process, that is sometimes overlooked, are Data Warehouse Units (DWUs). These are set up when, for example creating a SQL Dedicated Pool in Azure, but sometimes forgotten to update, while this is a crucial part in the smooth processing of data throughout the data solution. In this blog, we tackle the concept of DWUs and explain why they are and remain important.

What are Data Warehouse Units (DWUs) 

A Data Warehouse Unit (DWU) represents a fundamental concept utilized in cloud-based data warehousing solutions, including Azure Synapse Analytics. It represents the computational resources that are needed for managing (daily) data workloads. DWUs serve as an abstraction that integrates CPU (Central Processing Unit), memory, and input/output throughput – a measure of how much data can be transferred in a given amount of time -, facilitating efficient and scalable operations within a data warehouse.

By assigning DWUs to a data warehouse, you effectively define the system’s ability to execute queries, process data, and perform various tasks. An increase in the number of DWUs allocated enhances performance capabilities, although this comes with an associated increase in costs (we will talk about this later). A DWU serves as a conceptual, standardized metric for evaluating computational resources and performance.

Azure Synapse Analytics

Azure Synapse Analytics is a cloud platform from Microsoft that integrates big data and data warehousing functionalities. It facilitates the smooth combination of structured and unstructured data from diverse sources, providing (ETL) tools for data ingestion, transformation, and orchestration, including Azure Data Factory. The platform is designed for scalable data processing, offering both serverless and dedicated compute options, which are optimized for executing queries on large datasets using SQL and Apache Spark. It also integrates with tools like Power BI for real-time data visualization and Azure Machine Learning for sophisticated analytics, including AI and predictive modelling. Its pay-as-you-go and serverless pricing options improve cost efficiency, and its ability to scale lets organizations adjust resources based on their requirements. Tailored for seamless analytics workflows, the platform supports data storage, processing, and analysis within a unified environment. Azure Synapse is well-suited for developing modern data warehouses, processing IoT or application data, and facilitating data-driven decision-making on a large scale.

The Role of SQL Dedicated Pools in Azure Synapse

A SQL Dedicated Pool (formerly SQL Data Warehouse) in Azure Synapse is a Platform as a Service (PaaS) provided by Microsoft that handles Massively Parallel Processing (MPP) workloads. To capture this concept shortly, it means that rather than relying on one high-capacity machine, the processing tasks are spread out over several machines known as compute nodes. Each compute node processes a segment of the data at the same time, thereby accelerating the processing of complex queries. Depending on the total DWUs chosen, they can vary from 1 to 60. It is thus very important to remember that Synapse Analytics services are billed based on total DWU.

SQL Dedicated Pools organise and store data in relational tables using columnar storage. This format greatly decreases the expenses associated with data storage while enhancing the efficiency of query performance. This makes it an excellent choice for big data querying.

Pricing and Performance

Now that we went over the key concepts of working with a Data Warehouse and how the DWUs fit in this solution, it is very important to take into account the performance level of the Dedicated Pool (and thus the number of DWUs) so that the workload is distributed in the best possible way while keeping a close eye on cost efficiency. The big advantage here is that the process of creating – or afterwards adjusting – the performance level, is very easy and intuitive.

As you can see from the image above, the performance level is chosen directly when creating the dedicated SQL Pool. By default, this is set to DW1000c, which is more than enough to handle – on average – daily loads. The lowest number of DWUs that can be chosen is 100, the calculation that then follows differs slightly from setup to setup but can be shown as an example as follows:

As you have probably already noticed, the use of a Dedicated Pool is charged by the hour. Therefore, it is also very important to pause the pool in which it should not be used. In this, it also differs from a serverless pool. There, you pay per TB processed, as opposed to a dedicated pool where you pay per DWU provisioned. If you want to know more about the differences between those two pools, please read the following blog: https://lytix.be/synapse-dedicated-sql-pool-vs-serverless-sql-pool/

Determining the performance level should ideally be preceded by a comprehensive assessment of the available computing resources and server capacity. Following this initial step, it is crucial to consistently observe the daily fluctuations in the utilization of Data Warehouse Units (DWUs).

This can be done by going to your dedicated pool in your Azure environment. There, under the monitoring tab, you can find several functionalities to keep an eye on the performance of the dedicated pool. In the example below, you can see why it is helpful to check this kind of metrics regularly.

In this example, the limit of the total DWUs is reached daily, sometimes even twice a day. This is of course a sign that it’s best that you upgrade your performance level. It is clear that the objective is not to attain the maximum threshold of Data Warehouse Units (DWUs). Specifically, this indicates that once the limit is reached, computing resources become (temporarily) unavailable, preventing the initiation of any new queries. This situation evidently affects the overall performance of the system at that particular time. Normally, there will be no system failures. However, it is evident that the processing time for specific queries will be extended. This could become a potential problem if the amount of data to be processed were to suddenly increase rapidly.

Conclusion

In summary, we can say that in a modern Azure Synapse data platform, DWUs are crucial for managing performance, scalability, and costs in dedicated SQL pools. They act as the foundation for distributing compute resources, allowing businesses to tackle a range of tasks, from simple queries to heavy-duty analytics. With the ability to scale resources on the fly, DWUs fit perfectly with the needs of modern data operations, like real-time analytics, predictive modelling, and complex data transformations. However, to make the most of DWUs, it’s important to keep an eye on usage to avoid overspending or facing performance issues. By regularly reviewing workload trends and tweaking DWU levels as needed, businesses can enhance resource efficiency and maintain smooth performance even when demand changes.

Written by