Modern BI cloud data warehouse is emerging in the market in which a lot of big giants offering cloud data warehousing solutions like AWS, Azure, Google, and Snowflake, etc. Here, we will check the features of two popular cloud data warehousing solutions, called Azure Synapse Analytics from Microsoft and Snowflake. We are going to discuss on performance, cost, compute and storage, etc., of these two popular tools.

What is Azure Synapse Analytics?

Azure Synapse Analytics is a limitless analytics service that combines data integration, enterprise data warehousing, and big data analytics. It gives you the freedom to query data on your terms, using either serverless, Azure Spark, or dedicated resources—at scale. Azure Synapse brings these worlds together with a unified experience to ingest, explore, prepare, manage, and serve data for immediate BI and machine learning needs.

What is Snowflake?

Snowflake’s data cloud is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings.

Common Features in Synapse Analytics and Snowflake

Let see the common features available in Synapse Analytics and Snowflake:

• Massively Parallel Processing (MPP) – MPP to distribute the computation of data across multiple nodes in the cloud. MPP allows to handle massive amounts of data and provides much faster analytics based on large datasets.
• Separate storage from compute – It will provide the benefit of scaling, storage, and compute independently when compared to tightly coupled storage and compute in traditional architectures like Hadoop. Snowflake and Synapse fully support SQL, which tremendously helps with the speed of adoption.
• Relational SQL databases – Both data warehouses using relational SQL database concept and columnar storage to minimize data size growth while delivering excellent performance.
• Visualization tools – Both platforms can be accessed from various data visualization tools such as Power BI, Tableau, etc., to deliver insights to end-users. 
• Semi-Structured Files – Extract and parsing of semi-structured files like JSON, CSV, etc., is possible in both platforms. Both have a separate extension for extracting and parsing semi-structured files.

Despite the standard features, Azure Synapse Analytics and Snowflake have significant differences in architecture, billing, compute, scalability, etc.,

Differences in Synapse Analytics and Snowflake

Below we will compare differences in these two modern data warehousing platforms across compute, costs, and scalability.

1. Compute

Azure Synapse Analytics and Snowflake compute are different in their working nature when using the compute resource.

Snowflake

Databases created in Snowflake are completely decoupled from the compute resources that load or query those databases. In Snowflake, the warehouse is called compute, which can query on any database. This approach enables us to use multiple compute resources to use the same database concurrently. You may be aware of the different compute sizes provided by Snowflake, like small, medium to XL, and more.

Snowflake’s compute resource can be auto-paused after a period of inactivity. Thus, resources will resume when we fire the query in the Snowflake. This will help us to reduce the cost more efficiently.

Azure Synapse Analytics

• Dedicated SQL Pool
• Serverless SQL Pool
• Spark Pool

Azure Synapse Analytics takes a different approach to compute resources. A SQL pool is required to execute the SQL query.

In a dedicated SQL pool, distributions map to Compute nodes for processing. SQL database is tightly coupled to the dedicated SQL pool compute resource. As you pay for more compute resources, the pool remaps the distributions to the available Compute nodes. The number of compute nodes ranges from 1 to 60 and is determined by the service level for the dedicated SQL pool. Dedicated SQL pools can be paused and resumed manually or using API, which will reduce the compute cost. The architecture of a dedicated SQL pool (formerly SQL DW) separates storage and compute, allowing each to scale independently. As a result, you can scale compute to meet performance demands independent of data storage. In a serverless SQL pool, each Compute node is assigned a task and set of files to execute a task on. The task is distributed query execution unit, which is actually part of the query that the user submitted. Automatic scaling is in effect to make sure enough Compute nodes are utilized to execute user queries. Serverless SQL pool is serverless. Hence there’s no infrastructure to set up or clusters to maintain. No charge for resources reserved, you are only being charged for the data processed by queries you run. Hence this model is a true pay-per-use model.

Apache Spark (Spark Pool) provides primitives for in-memory cluster computing. A Spark job can load and cache data into memory and query it repeatedly. In-memory computing is much faster than disk-based applications. Spark also integrates with multiple programming languages to let you manipulate distributed data sets like local collections. There’s no need to structure everything as a map and reduce operations. Spark pools in Azure Synapse are compatible with Azure Storage and Azure Data Lake Generation 2 Storage.

Azure Synapse implements a massively parallel processing engine pattern that will distribute SQL commands across a range of compute nodes based on your selected SQL pool performance level.

2. Billing

Snowflake

Snowflake offers a cost based on Pay-As-You-Go billing for computing calculated on a per-second basis or with a minimum of 60 seconds with auto-suspend and auto-resume capabilities. For example, if the query execution takes 2 minutes, you will pay for only 2 minutes of computing. After, Snowflake will auto suspend based on our configuration.

Azure Synapse Analytics

Azure SynapseAnalytics offers a cost based on compute usage that is charged on an hourly basis. For example, if your data warehouse is active for 1 hour, you will only be billed for the 1 hour that your data warehouse is in an active state. Likewise, if your data warehouse exists for only 30 minutes a month, you will be billed for 1 hour.

The auto suspend feature is not available in Azure SynapseAnalytics. However, we can manually pause the data warehouse or by using API or PowerShell script automation.

3. Scalability

Snowflake

Snowflake is a multi-cluster and shared data architecture that promises the best performance in the modern cloud data warehouse. Using Snowflake, different workloads can be isolated concurrently on a shared data layer. Virtual warehouses on different configurations allow us to achieve unlimited scale and concurrency based on unprecedented compute needs without any downtime. Snowflake is designed to have the feature of micro partition and re-cluster, which improves the performance.

Azure Synapse Analytics

Azure Synapse Analytics provides a dedicated SQL pool option with a pre-defined unit of scale (Data Warehouse Unit (DWU)), a serverless SQL pool option where scaling is done automatically to meet different scaling needs, and Apache Spark pool tells Azure Synapse how many Spark resources to use. When you actively stop using the pool, the resources automatically time out and are recycled.

This results in a model where you only pay for the resources that you use. The data is moved between compute nodes using Data Movement Service (DMS) within a dedicated Synapse SQL pool. For each Azure Synapse instance, there are capacity limits. The administration might need to manage multiple Azure Synapse Analytics services when these limits are exceeded.

A quick view of features available in Snowflake and Azure Synapse Analytics

Conclusion

Both Snowflake and Azure Synapse Analytics will be feature market leaders and having own unique features which enable the customer to use a modern cloud data warehouse to build a scalable and cost-efficient BI solution. User needs to choose the data warehouse solution based on their requirement, the volume of data, security, and type of business they are handling (small to enterprise-level business).

Reach out to us here today if you are interested in evaluating if Snowflake is suitable for you. Learn more about Visual BI’s Snowflake offerings here and Microsoft  Azure offerings here.