Snowflake vs. BigQuery: Key Differences and Similarities

Unveiling the Nuances Between Two Cloud Data Warehouse Giants

Photo: Propel

This post will dive deep into the key differences and similarities between Snowflake and BigQuery, comparing their main concepts, pricing models, features, and performance. By the end of this post, you'll gain valuable insights to help you make an informed decision when choosing between Snowflake and BigQuery for your organization's data warehousing needs.

Main Concepts

In this section, we'll compare the primary concepts behind Snowflake and BigQuery, including storage architecture, data clustering, resource allocation, and databases and schemas, to help you understand their fundamental differences and similarities.

Concept	Snowflake	BigQuery	Similarities / Differences
Storage Architecture	Micro-partitions	Capacitor	Both use columnar storage but with different approaches to managing the data.
Data Clustering	Automatic clustering	Clustered tables	Both support clustering for optimized query performance, with Snowflake providing automatic clustering.
Data Warehouses / Slots	Virtual Warehouses	Slots	Both allocate resources for query execution but use different terminology and approaches.
Security	Role-based Access Control (RBAC)	Identity & Access Management (IAM)	Both support granular access control, with different models for managing permissions.
Databases and Schemas	Databases & Schemas	Datasets & Schemas	Snowflake uses databases and schemas, while BigQuery uses datasets and schemas for organizing and structuring data.

Pricing Model

Snowflake and BigQuery differ in their pricing models, with each offering distinct advantages depending on your use case. Snowflake separates compute and storage costs, while BigQuery employs a more unified approach.

Snowflake:

Snowflake's pricing model revolves around two components:

Storage costs: You are billed for the amount of data stored in your account. For example, if you store 1 TB of data, you would be billed $40 per month (assuming on-demand pricing in the US East region).
Compute costs: You are billed for the time that virtual warehouses are running. For instance, if you use a Medium virtual warehouse (8 credits/hour) for 10 hours, the cost would be 80 credits. With on-demand pricing of a standard account at $2 per credit, this translates to $160.

BigQuery:

BigQuery's pricing model combines storage and compute costs:

Storage costs: You are billed for the amount of data stored in your account. For example, if you store 1 TB of data, you would be billed $20 per month.
Query costs: You are billed for the amount of data processed by your queries. For instance, if you run a query that processes 500 GB of data, the cost would be $2.50 (assuming on-demand pricing in a US region).

Features

Comparing the features of Snowflake and BigQuery can give you valuable insights into their capabilities. We'll present tables comparing equivalent features, as well as unique features for each platform, to help you identify which solution best meets your needs.

Feature	Snowflake	BigQuery
Time Travel	Up to 90 days	Up to 7 days
Materialized Views	Supported (only on enterprise edition)	Supported
Data Sharing	Secure Data Sharing	Authorized Views
Stored Procedures	Supported	Supported

Snowflake’s Unique Features

Feature	Description
Data Excahnge	A marketplace for sharing and consuming live data from multiple sources.
Zero-Copy Cloning	Create instant, low-cost clones of databases, schemas, or tables.
Fail-safe	Provides a 7-day historical data archive for disaster recovery.

BigQuery’s Unique Features

Feature	Description
BigQuery ML	Build and use machine learning models directly within BigQuery.
BigQuery GIS	Analyze and visualize geospatial data with built-in GIS functions.
BigQuery Omni	Query data across multiple clouds and regions.
BigQuery BI Engine	Accelerates SQL queries by intelligently caching the data you use most frequently.

Performance

Performance is often the most critical aspect of data warehouse solutions. In a 2022 benchmark conducted by Fivetran, Snowflake and BigQuery were found to have excellent execution speeds for ad-hoc and interactive querying. The test also included Databricks, Redshift, and Synapse as part of the comparison.

Fivetran generated the industry-standard TPC-DS data set, with a size of 1TB, which consists of 24 tables representing a retailer's web, catalog, and store sales. The largest table in the data set had 4 billion rows.

For the benchmark, Fivetran used 99 TPC-DS queries, which include several joins, aggregations, and subqueries. They ran the test once to ensure that the warehouses did not save previous results. The results are complex and worth digging through. As they state in the blog post: “We should be skeptical of any benchmark claiming one data warehouse is dramatically faster than another.”

It's important not to jump to conclusions about which platform is faster. Comparing both 2022 and 2020 benchmarks, Fivetran's results showed that the performance of both Snowflake and BigQuery has improved over time. In the case of Snowflake vs. BigQuery, BigQuery made the most significant improvements. As both systems are continually being developed and optimized, we can expect further performance enhancements in the future.

Conclusion

In this post, we have explored the key differences and similarities between Snowflake and BigQuery, two leading cloud data warehouse solutions. We compared their main concepts, pricing models, features, and performance to provide insights that can help you make an informed decision.

It's useful to consider the benchmarks but not jump right to conclusions without looking at the different tradeoffs, ongoing development, and improvements of both platforms.

Managing Timezone Settings in Snowflake

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.