In today’s segment of Azure Every Day, I’d like to talk about Azure Data Warehouse and to help you understand some of the underlying architecture and its impact on the workloads you’re planning to put into Azure. Azure Data Warehouse is a parallel data warehousing solution in the cloud, useful for large data workloads.
When you turn on Azure Data Warehouse for a workload, it will instantiate 60 SQL Server databases underneath, which means it’s looking at how to parallelize your workload across 60 databases. This is a standard Platform as a Service (PaaS), so unlike an appliance or APS, you don’t have control over the number of servers that you run. It is designed to handle this without you having to do anything to manage that workload.
So, when starting to use Azure Data Warehouse, there are 3 aspects that you should understand as to how it will affect how you plan and decide if the workload makes sense in Azure DW.
1. First thing to realize is that all the tables going into Azure DW are, by default, cluster column stored indexes. These indexes are great for compression and performance, but typically need a million rows in them before they become compressed. So, for example, if you think about a distributed workload across 60 databases, you need 60 million rows before you can see an effective compression solution or take advantage of the compression utilities in a cluster column store.
2. Be aware when you’re partitioning a table, that the table is already distributed. Hence, if you have a scenario where a table already has 100 partitions, that creates 6000 partitions across the Azure instance. To break this down further, you’re going to need about 6 billion rows before you can take advantage of the column store indexes, including compression. Therefore, if you’re not running a large system, partitioning may not give you the same benefit as on a SQL Server or Azure SQL database.
3. Lastly, realize how the data is loaded into the data warehouse. The Azure Data Warehouse, with its 60 databases, makes it a great platform for loading data because you can parallelize the data load across those 60 databases. One tool that helps you is C-tabs, created tablized SQL statements that were designed for use within Azure DW and can distribute workloads across partitions and load in parallel, resulting in a very fast load.
Used in conjunction with PolyBase, you can pull data from storage, like Azure Blob Storage or Azure Data Lake, and pull that data through and use C-tabs to write that data out. A final thought on this scenario is if you use Azure Data Factory and its capabilities to further use that parallelism. With this, you can load a staging table or make sure it sends 60 files at a time so every server is being used.
These are some things to consider if you’re looking at Azure Data Warehouse to decide if it fits the needs of your organization. If you’d like to learn more about this, or anything Azure related, click the link below to get information and help with all your Azure needs.
-1.png)
Leave a comment