Azure Synapse vs Snowflake: the definitive guide

With the world on pace to reach 175 Zettabytes of data by 2025, it’s no wonder why organizations are placing such a high emphasis on building out their technology stacks. Now more than ever, companies need a way to collect and consolidate data into a single platform to derive insights quickly.

This is one of the core reasons that Snowflake and Azure Synapse Analytics have risen to such popularity. However, Synapse and Snowflake are different solutions and both should be analyzed from an unbiased lens. With that in mind, here are some of the core differences and pros/cons to Snowflake and Synapse.

What Is Snowflake

Snowflake is a SaaS (Software-as-a-Service) data platform that is built to run on any of the major cloud providers (i.e. Amazon Web Services, Microsoft Azure, Google Cloud Platform). At its core, Snowflake collects and consolidates data so that users can self-serve and easily query data using SQL to create reports and dashboards and drive business value.

As a native SaaS offering Snowflake handles all of the backend infrastructure and administration that usually comes with cloud offerings. Founded in 2012, Snowflake officially launched in 2014 and became the single largest software IPO in history in 2020. Today Snowflake is worth billions of dollars.

What Is Azure Synapse?

Azure Synapse is a PaaS (Platform-as-a-Service) data platform that is offered by Microsoft. Believe it or not, Synapse is a relatively new offering from Microsoft, only being released officially at the end of 2020. Similar to Snowflake, Synapse also provides a single platform where companies can collect and consolidate data and use SQL for analytics purposes.

In addition to analytics use cases, Synapse is designed to act as a central hub to connect additional Azure offerings. However, whereas Snowflake is focused on business intelligence workloads, Synapse integrates with Apache Spark to handle streaming, artificial intelligence, and machine learning workloads in addition to conventional SQL and Business Intelligence workloads.

Architecture

Snowflake

Snowflake is a data platform that is not built around any specific database technology or big data software platform. Snowflake is an ANSI SQL compliant and serverless solution that has completely separated storage and compute processing layers. It is based around a shared-disk and shared-nothing architecture.

To be specific, Snowflake uses a central data repository for all persisted data and makes it accessible to different compute nodes within the platform. This data is then processed using MPP (massively parallel processing) compute clusters known as data warehouses (i.e. the unit of scale), where a subset of the data is stored locally.

Once data is loaded into the platform, Snowflake automatically uses micro partitions to internally optimize and organize data into compressed columnar storage. This optimized data is then stored in cloud storage and Snowflake handles every aspect of file size, structure, compression, metadata, statistics, etc. None of this is visible to an individual user and it can only be accessed through SQL query operations run within Snowflake.

Every warehouse within Snowflake is on its own independent compute cluster. Warehouses do not share resources with other virtual warehouses. This means that Snowflake supports near-unlimited concurrency for both queries and users. On top of this, Snowflake is cloud-agnostic and runs on all three major clouds, AWS, GCP, and Azure

Azure Synapse

Azure Synapse is built solely to run on Azure Cloud. Storage and compute within Azure Synapse is separate just like Snowflake and the platform is ANSI SQL compliant. At the center of Azure Synapse is Synapse studio, which is the UI to monitor resources, perform tasks, write code, and manage user access.

Azure Synapse uses a distributed query system that leverages T-SQL (T-SQL is very similar to conventional SQL but it comes with a few added benefits and is primarily used with Microsoft services. Whereas Snowflake uses virtual warehouses, Azure Synapse offers what’s known as Dedicated SQL pools, Serverless SQL pools, and Spark Pools.

Dedicated SQL Pools

Computing power within Dedicated SQL pools (previously Azure SQL Data Warehouse) is determined by compute nodes known as data warehouse units (DWU). This data is stored in relational tables in a columnar format and is also sharded into smaller distributions to optimize query performance. Synapse then leverages MPP to distribute queries across multiple computing nodes. Concurrency for dedicated SQL Pools is limited to 128 queries and all remaining queries are placed into a queue.

Serverless SQL Pools

On the other hand, Serverless SQL pools are designed to query ADLS (Azure Data Lake Storage) and cannot query data stored in tables within Synapse. The architecture for Serverless SQL pools is based on a distributed query processing engine where control nodes are used automatically to optimize query performance and execution as needed on a per-query basis. This computing environment is largely used for ad-hoc analysis and big data discovery.

Spark Pools

Spark Pools is a fully managed service offered within Azure Synapse that lets users spin up and configure Apache Spark clusters to tackle more complicated workloads.

Scalability

Snowflake

Snowflake has a built-in auto-scaling and auto suspend feature for virtual warehouses during idle and busy periods. Since each Snowflake warehouse is on its own individual compute cluster, workloads can be isolated individually to enable unlimited concurrency. Snowflake also has a feature called zero-copy cloning which enables users to instantly clone databases without physically copying or storing the data.

Azure Synapse

Azure Synapse has fewer features around scalability compared to Snowflake since it is not a native SaaS offering. Serverless SQL Pools and Spark Pools have automatic scaling by default, but Dedicated SQL server pools have to be manually adjusted by the user because there is no auto-suspend/auto-resume feature like Snowflake.

Security & compliance

Snowflake and Synapse both automatically encrypt data at rest and each solution offers RBAC (role-based access control) to manage users and privileges. Both platforms also provide robust security around MFA (multi-factor authentication) and connectivity through the usage of VPNs (virtual private networks). Each tool supports Azure Private Link (Snowflake also supports AWS PrivateLink).

In addition, to this, Snowflake and Synapse are compliant with SOC 1 Type II, SOC 2 Type II, HIPAA, GDPR, ISO 27001, Fedramp, etc. Azure Synapse also has a feature known as Microsoft Defender that monitors resources and provides built-in threat detection to detect exploits and mitigate harmful activities.

Data Support

Synapse and Snowflake support structured and semi-structured data types like (Avro, Parquet, JSON, etc. In addition to SQL, Synapse lets developers use multiple programming languages like Python, SQL, Java, Scala, .NET, and R. Synapse's native integration with Azure Data Lake and Delta Lake make it an ideal choice for unstructured data

Snowflake just recently rolled out a new feature called Snowpark, which is a new developer tool that supports Java, Scala, and Python programming languages for unstructured data use cases.

Administration

Snowflake

With Snowflake, it's easy to manage users, roles, permissions, and data security. On top of this, all performance tuning happens automatically as data volume increases because Snowflake is a SaaS solution.

Companies using Snowflake do not need full-time administrators because there is built-in performance optimization and automatic clustering. Snowflake Cloud Services enables users to start getting immediate value out of the platform in an extremely short amount of time.

Azure Synapse

On the other hand, Azure Synapse is a PaaS offering, that has more features than Snowflake because it integrates natively with many other core Azure products. This makes it more difficult to get immediate value out of the platform because Synapse has more functionality, but also a much higher learning curve because it can address more use cases than Snowflake.

Synapse automatically moves data between compute nodes using its Data Movement Service (DMS). With no autoscaling for Dedicated SQL Pools, resources within Azure have to be monitored extremely closely by administrators because there are capacity and concurrency limits. Some fine-tuning is usually required around indexing, caching, partitions, and distribution keys.

Data Protection

Azure Synapse

Azure Synapse offers a number of features for data protection. Within Synapse, users have the ability to create snapshots to restore a specific point in time and revert the data warehouse to a previous state.

These snapshots can be manually triggered to create or restore points of the data warehouse after large modifications to ensure consistency. Synapse allows for 42 user-defined restore points at any point in time. Synapse also provides geo-back recoveries. This means that Synapse can be restored in another region.

Snowflake

Snowflake works slightly differently, using two features known as Time Travel and Fail-safe. With Time Travel, Snowflake preserves the state of the warehouse before it is updated. The standard period of time travel is one day, but Enterprise customers can specify any period in time up to 90 days. Fail-safe is a feature that begins automatically after the Time Travel retention period has ended. It is a 7-day period that is used to protect and recover historical data.

Pricing

Snowflake

Snowflake pricing is relatively simple because it is based on the usage of individual warehouses. Compute resources within Snowflake are defined by T-shirt sizes (X-Small, Small, Medium, Large, X-Large, etc. All of these sizes vary drastically in terms of cost and server/clusters. Pricing for an X-small Snowflake warehouse starts at around 0.0003 credits per second or one credit per hour.

This doubles with each increase in warehouse size. The number of virtual machines also doubles with each increase in size. Credit costs within Snowflake are calculated based on the business tier that is chosen. The on-demand version of Snowflake Standard Edition begins at $2 per credit. The largest warehouse (6X-Large burns approximately 512 credits per hour or .1422 credits per second.

Snowflake offers on-demand (pay-as-you-go) pricing and also reserved (pre-purchased) plans based on usage (this is calculated on a per-second basis). Paying upfront and purchasing credits usually provides lower rates compared to the on-demand options. However, credit costs within Snowflake vary substantially depending on the business tier. Storage within Snowflake is charged at a flat rate of $40 per month for each TB for on-demand customers and $23 per TB for upfront customers.

Azure Synapse

Pricing with Synapse is a bit more complex as there are more options compared to Snowflake. Computing resources or DWUs in Azure start at $1.51 per hour for the smallest size and can go all the way up to $453 for the largest size. Like Snowflake though, these costs can be drastically reduced by purchasing reserved capacity. On the other hand, Serverless SQL pools charge a flat rate of $5.65 for each TB of data that is processed. Storage costs within Azure are slightly more expensive than Snowflake, costing around $26 per TB per month.

One important thing to note is that Snowflake charges for compute on a per-second basis after the first minute and Azure Synapse charges based on an hourly basis. This means that if query execution in Snowflake takes 3 minutes users pay for 3 minutes of computing. Conversely, if Azure Synapse is active for 30 minutes, users would still be forced to pay for an entire hour. This is why there appears to be such a large discrepancy in the pricing model of both solutions.

Cloud Infrastructure

Snowflake runs on any major cloud provider (AWS, GCP, Azure). Synapse is native to Azure, so this means that Synapse is only available to Microsoft customers who are already leveraging Microsoft's existing cloud offerings. This gives Snowflake broader appeal from an infrastructure standpoint as companies are not locked to a single vendor because the cloud Snowflake is hosted on can be changed at any time.

However, companies already on Azure cloud will have an advantage in that they don’t have to go through a long-drawn-out sales and contracting process in order to use Synapse because it falls underneath the Microsoft suite of tools, whereas Snowflake will be considered a new vendor.

Performance

When it comes to performance it is difficult to do an “apples-to-apples” comparison of Snowflake and Synapse because both solutions are very different. As with every cloud data platform, Snowflake tends to substantially outperform Synapse out of the box with no fine-tuning because it's a SaaS service, whereas Synapse is PaaS based solution.

This is not to say that Synapse is bad by any means, but competing with Snowflake on a performance level is very difficult without a substantial optimization effort. From a strict data warehousing perspective Snowflake definitely has the edge, but from a data lake and overall data platform perspective, Synapse usually wins.

The Main Differences Between Azure Synapse & Snowflake

The major difference between Snowflake and Synapse lies in the fact, that Synapse is built to run as an analytics layer on top of Azure Data Lake and also act as a data warehouse for analytics workloads. At its core, Synapse integrates natively with other specific Azure services like Github, Azure DevOps, Azure Data Factory, Power BI, etc.

With its native Spark and Delta Lake integrations, Azure Synapse is also extremely robust at handling ML, AI, and streaming workloads. On the other hand, Snowflake is really designed for conventional business intelligence workloads and this is where it shines. This is not to say that Snowflake does not offer support in the other areas just mentioned. However, Synapse is much more robust when it comes to multiple workloads.

Most of Snowflake’s functionality in these areas is with specific partners and integrations. Synapse is much more complicated and has a much steeper learning curve because it comes with more bells and whistles. On the other hand, Snowflake is designed to be as simple as possible, offer unlimited scale, and provide immediate value right out of the box.

There is no all-encompassing unicorn solution that can do everything, Snowflake does some things better than Synapse and vice-versa, so there is no simple answer as to which one is better. Ultimately Snowflake and Azure Synapse are both great solutions, so when it comes to choosing between them, the use case and business requirements should be at the forefront of all decision making.

What comes after Snowflake & Azure Synapse? (Hint: Reverse ETL)

Market leaders like Snowflake and Synapse are great analytics tools designed to de-silo data sources and consolidate data into a central repository or analytics platform. The problem is, once data is ingested into these platforms it is only ever used to build reports and power dashboards for high-level business decisions. A higher focus is placed on understanding this data rather than taking action on it.

Business teams like Sales, Marketing, Support, Success, etc. want and need access to the data within the warehouse:

• Data models (ex: qualified user, lead score, churn rate, LTV, ARR, etc.)
• Custom audiences (ex: users who created a workspace in the last 30 days)
• Product data (ex: shopping cart abandonment, pages viewed, workspaces created, last login date, etc.)

All of this information is only accessible to technical users who know how to write SQL and this creates a problem because this data cannot be leveraged on an individual basis.

Syncing this information into the tools like Hubspot, Salesforce, Marketo, Braze, etc. empowers users to automate and personalize the experience of each individual customer or prospect to increase conversions and grow Revenue.

Hightouch solves the “last-mile” analytics problem and uses Reverse ETL to sync data directly from the warehouse back into the tools that run business processes.

Want to Learn More About Hightouch?

Want to learn more about Reverse ETL? Download our Reverse ETL Whitepaper where we touch on the technology and applications of Reverse ETL.