Compare Data Warehouse Concepts in Snowflake versus SQL Server

By: Sergey Gigoyan | Updated: 2023-04-28 | Comments | Related: More > Data Warehousing

Problem

In today's data-driven world, data warehousing has become an essential tool for organizations of all sizes. Data warehousing enables organizations to collect, store, and analyze large volumes of data to gain valuable insights into their business operations. Snowflake and SQL Server are the most popular data warehousing solutions available today. In this article, we will compare the warehouse concept in Snowflake versus SQL Server.

Solution

SQL Server developers may be accustomed to using the term "warehouse" to refer to the data warehouse itself, which, in short, is a physical or logical collection of data used for reporting and analysis. However, in Snowflake, the term "warehouse" refers to the compute resources used to process data rather than the data warehouse itself. This difference in terminology can be confusing for SQL Server developers who are new to Snowflake, as they may assume that the "warehouse" in Snowflake is the same as a traditional data warehouse. This can lead to misconceptions about how Snowflake works. Hence, we will discuss this Snowflake-specific terminology of a "warehouse" in contrast with SQL's traditional warehouse conception to shed light on it.

Snowflake Warehouses

In Snowflake, a warehouse is a "compute" resource, not "data storage." In other words, a warehouse is a collection of virtual machines (compute resources) used to process queries and perform operations on the data stored in the Snowflake data warehouse. The warehouse is responsible for processing SQL queries, loading data, and performing other tasks related to data processing.

One of the key advantages of using Snowflake is the ability to easily create and scale warehouses based on your organization's needs. When creating a new warehouse in Snowflake, you can select the size of the virtual machine and the number of virtual machines in the warehouse. These two factors determine the total computing power available to the warehouse. For example, if you need to process a large volume of data, you can create a warehouse with multiple virtual machines to increase the computing power available.

Snowflake also provides the ability to scale the warehouse up or down based on demand. For example, during peak usage periods, you can increase the size of the warehouse to handle the increased workload. Once the workload decreases, you can scale the warehouse back down to save costs.

Here's an example of how to create a new warehouse in Snowflake using the Snowflake SQL command line tool:

CREATE WAREHOUSE test_wh
  WITH WAREHOUSE_SIZE = 'MEDIUM'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

Let's copy this code into a Snowflake UI worksheet and run it. As we can see, there is a warehouse already created by default called "COMPUTE_WH". In this example, we'll create a new warehouse called "test_wh" with a medium-sized virtual machine. The warehouse will automatically suspend after 60 minutes of inactivity and will automatically resume when a query is submitted to the warehouse:

After completion, we can see that a new warehouse has been added to the list of warehouses:

These warehouses can be used as computing resources to perform operations on data or, in other words, for processing the data in actual "traditional data warehouses."

SQL Server Data Warehousing Features

In contrast, SQL Server does not have a concept of a "warehouse" like Snowflake. Instead, SQL Server offers a variety of data warehousing features, such as columnstore indexes and clustered columnstore indexes, which are used to optimize performance for large data sets.

For example, columnstore indexes store data in a column-wise format, allowing for faster querying and processing of large data sets. Clustered columnstore indexes are similar, but they store data in a clustered index format, which allows for even faster data retrieval and processing.

SQL Server also offers a separate product called SQL Server Analysis Services for online analytical processing (OLAP) and data mining. Analysis Services provides features for creating multidimensional data models, data mining algorithms, and advanced data visualization tools.

It is worth mentioning that besides traditional SQL Server data warehousing features, Microsoft Azure has robust data warehousing features, which are out of the scope of this article.

Conclusion

In summary, while Snowflake and SQL Server offer features for data warehousing, they approach them in different ways. Snowflake's approach focuses on providing a fully managed, scalable data warehouse in the cloud. In contrast, SQL Server's approach provides features to optimize data warehousing performance within an on-premises RDBMS. The choice between the two will depend on a business's specific needs, including data volume, scalability, and budget.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-04-28

SQL Server vs. Snowflake for Data Warehousing