BigQuery vs Snowflake Comparison: Choosing the Right Data Warehouse in 2020

on Data Warehouse • March 18th, 2020 • Write for Hevo

If you have been looking to find an answer to this question: BigQuery vs Snowflake – Which data warehouse to choose, then you have landed at the right place.  

We at Hevo, help our customers bring all their into the data warehouse of their choice without having to write any code. Naturally, many customers who are setting up their data analytics stack come to us seeking advice and recommendations on which warehouse to opt for – Google BigQuery or Snowflake. 

This blog aims to answer this question by providing nuanced differences between Google BigQuery vs Snowflake. These inputs, in turn, would help you evaluate which of these cloud data warehouses suit your needs best. 

Introduction

With the advent of cloud, many organizations who have had a significant investment in hardware and infrastructure, and are now at the end of life have a major decision to make;

Do you buy more / refresh your hardware or do you find some other solution that may be more cost-effective?

This is what has led most organizations on the path to the cloud. In terms of operational overhead, we have already seen opportunities to reallocate resources used to maintain hardware infrastructure and companies are now able to task them with more value-added activities.

On-premise Database Constraints

The biggest constraint in privately hosted databases is that table scans on large tables take an excessive amount of time and cause performance problems. 

Why is this so?

Disk I/O

Disks are slow, and they are not getting faster. If you’re reading a huge chunk of data from a small number disks, it is going to be painfully slow. You will also hit storage limits since you can only read from the storage that’s on the machine.

The parallelism of disk I/O is arguably one of the most important factors in improving throughput. The full-scan performance will increase linearly, in relation to the number of disk drives working in parallel. Some data warehouse appliances provide special storage units that allow you to run a query in parallel on hundreds of disk drives. But, this set up tends to be quite expensive as they are all on-premise and often involve proprietary hardware products.

CPU

CPUs are not getting faster. Gone are the days when CPU clock speeds increased dramatically from year to year. This largely due to the fact that designing faster chips is difficult.

Cost

On-prem RDBMS can’t scale affordably.

What if you can remove those constraints?

That is where database administration cloud platforms like Snowflake and BigQuery come in. They allow you to forget about things like backups, capacity management, and patch management at a competitive price.

Before we dive in to learn more about Google BigQuery vs Snowflake, let us look at the key features of each of these data warehouses.

Google BigQuery Data Warehouse

BigQuery is Google Cloud Platform’s enterprise data warehouse for analytics. This technology has been used by Google internally for various analytic tasks for over a decade. It is a good tool for analyzing huge amounts of data quickly to meet your Big Data processing requirements. BigQuery offers exabyte-scale storage and petabyte-scale SQL queries. Data in BigQuery is encrypted, durable and highly available.

Why Use BigQuery?

As the business grows, managing the data spread across the gazillion applications used by teams becomes hard. This, in turn, further makes it difficult to analyze the data within these systems to get meaningful insights. Often, precious engineering resources are deployed to set up a centralized data store that hosts all this data and opens the door for BI. 

By using BigQuery, developers can now get back to focusing on essential activities such as building queries to analyze business-critical data. Also, BigQuery’s REST API enables businesses to easily build App Engine-based dashboards and mobile front-ends. Companies can then truly unleash the power of this data and empower all the stakeholders of the organization to derive insights from this. 

Google BigQuery Advantages

  1. Managed storage: One of the major benefits of BigQuery is it’s managed storage. BigQuery offers durable and persistent storage for your Data Warehouse and this helps you dramatically reduce your data operations. Tables are stored in an optimized columnar format. Each table is compressed and encrypted. All tables in BigQuery will accept streaming ingestion.Storage on BigQuery is durable and consistent seeing that each table is replicated across multiple data centers. With BigQuery, you also don’t have to worry about data replication and Disaster Recovery because in BigQuery, when you’re running in a regional configuration, your data is in multiple zones within a region, and when you’re running in a multi-region configuration, your data is spread across multiple geographically distinct regions. Therefore, you don’t have to worry about downtime from just a region or a zone going down because you’ll have that durability and consistency across regions.
  2. BigQuery removes resource constraints: Their cloud-powered massively parallel query service can read from ~100k disks using thousands of CPUs in parallel. There is also the separation of storage and compute so you don’t have any scaling bottlenecks.
  3. BigQuery supports a wide variety of formats for data ingestion:
  • Avro
  • Parquet / ORC
  • CSV
  • JSON
    When optimizing for load speed in BigQuery, you should prefer using the Avro format in your ETL processes. Avro is a binary row-based format that enables BigQuery to split it and read it with multiple worker nodes.
  1. BigQuery has the capability to leverage nested and repeated fields for:
  • Tightly-coupled or immutable relationships for example:
    • Sessions with Events
    • Orders with Order Line items
    • Infrequently changing data (country, region, date, etc.)
  • Simplifying queries:
    This enables you to store semi-structured data very naturally within BigQuery and then query over it. Oftentimes this is a very different concept for people used to a row-based architecture where they are not used to storing semi-structured data within the data warehouse.
    For Nested Fields: Leverage them when you have tightly-coupled or immutable relationships. For example: Let’s take the case where we have an Order, and we have an Order Line Items table. In this case, it is safe to say that Line Items will never really be used without the order table, and these are naturally coupled often immutable relationships. The best practice here is to denormalize that into a single table as opposed to having two tables (Order and Order Line Items). Similarly, you can do that with a Sessions and Events table. So you can have an Event within a Session, as opposed to having separate tables for Sessions and Events.
  1. Built-in ML and GIS for Predictive Analytics:
    BigQuery has strong AI/ML capabilities and supports very broad analytical use cases using:
  • AutoML Tables – For problems that require best-in-class accuracy. This feature is fully automated and it will discover the best model for the problem. It has a code-less graphical UI. 
  • BigQuery ML – For problems that require fast experimentation and development time, for example, Logistic Regression, K-means, Naïve Bayes, etc. It has an SQL interface and also supports AutoML tables as a model type.

Snowflake Data Warehouse

Snowflake is a true multi-cloud data platform. They are able to offer their customers High Availability and secure data across 3 Clouds and in multiple Regions. Snowflake is available on AWS, Azure, and the Google Cloud Platform.

With Snowflake, you have a technology solution to build a scalable, highly-resilient cloud environment with the agility that your business demands while delivering valuable insights to help your business and customers.

Snowflake’s unique architecture and the flexibility of the cloud has meant that customers can use Snowflake across many use cases and workloads in their business.

Snowflake initially started out as a Data Warehouse but as they’ve been able to manage more and more data types, customers have started to use Snowflake as a SQL Data Lake.

Customers are also able to share their data securely across their businesses and also with external data partners using the Snowflake Data Exchange. This greatly enhances their own datasets to then run more advanced and complex Data Analytics for Data science use cases. 

What Makes Snowflake Unique?

  1. Architecture:
    Snowflake has a multi-cluster, shared data architecture, which means similar to BigQuery, they have an architecture that separates their storage and compute layer. This enables them to scale up and down automatically as demand requires without impacting performance. Their architecture features micro-partitioning. This means that they are able to manage semi-structured and structured data. So they can manage JSON, Parque, etc natively within Snowflake and they can do that at infinite scale. You can read more about Snowflake architecture here.
  2. Delivered as-a-service:
    This makes it incredibly easy to use with near-zero management. Once your data is in Snowflake, they take care of the rest, there is no need to index, prune, etc, allowing customers to focus on the value within their data.

Snowflake Advantages

  1. Snowflake is a complete ANSI SQL database and data warehouse. That means that they are a very good landing point for Legacy Data Warehouses and Data Platforms that want to move into the cloud. They have very good compatibility for multi-statements transactions and complex joins.
  2. Customers can isolate workloads across the business and allow different areas of the business and different applications to use Snowflake. The platform is, therefore, able to support data scientists, executive reporting, data analysts, and program managers within one platform while maintaining a single source of truth.
  3. Virtually unlimited query concurrency. Using Snowflake, you can scale up as demand requires, but when that demand is not required, Snowflake will automatically scale down. All of your users will be able to access all of the data they need at the same time.
  4. High performance queries on semi-structured data. Snowflake provides fast access to JSON, AVRO, ORC, and Parquet data thus enabling a more complete view of your business and customers, for deeper, more revealing insights.
  5. Quickly scale up, down, out elastically, without disruption to running queries. You also incur no compute charges when the system is idle.
  6. Per-second compute pricing and cost-effective compressed data storage pricing.

BigQuery vs Snowflake

Deciding on the right data warehouse for your business needs and objectives is a crucial component of your big data strategy. We can see that these two data warehouses are closely stacked together seeing that they both have rich feature sets. From the leading industry standard TPC Benchmark, there is little that separates Snowflake from BigQuery in terms of performance. They both deliver unlimited concurrency and complete elasticity. Therefore, we have chosen to score this based primarily on cost.

Cost – BigQuery vs Snowflake

How do Snowflake and BigQuery compare on price? 

Billing on Snowflake is based on the volume of data you store in Snowflake and the compute time you use with a minimum of 60 seconds. You can secure price discounts with pre-purchased Snowflake capacity options. Here is a pricing table for four different Editions of Snowflake.

Storage

 

$0.02 per GB, per month.

Standard

 

$2 Compute/Hour

Features:

  • Complete SQL Data Warehouse
  • Customer-dedicated virtual warehouses
  • Database replication
  • Always-on enterprise-grade encryption in transit and at rest
  • Federated authentication
  • Secure Data Sharing across regions/clouds
  • 1 day of time travel
  • Business hour support Monday – Friday
Streaming Inserts

 

$0.01 per 200 MB

Premier

 

$2.25 Compute/Hour

Standard features plus:

  • Premier Support 24 x 365
  • Faster response time
  • SLA with a refund for outage
Queries

 

$5 per TB

Enterprise

 

$3 Compute/Hour

Premier features plus:

  • Multi-cluster warehouse
  • Annual rekey of all encrypted data
  • Up to 90 days of time travel
  • Materialized views
  • AWS PrivateLink available for an extra fee
Tier 1

 

$5 per TB

Business Critical

 

$4 Compute/Hour

Enterprise features plus:

  • Database failover and failback for business continuity
  • Enhanced security policy
  • Tri-Secret Secure using customer-managed keys
  • Data encryption everywhere
  • HIPAA support
  • AWS PrivateLink support
  • PCI compliance

You can enable any number of “virtual data warehouses” in Snowflake to power query execution. Virtual data warehouses are available in eight different sizes: X-Small, Small, Medium, Large, and X- to 4X-Large. Each data warehouse size has a compute credit designation. As you go up in size, credits usage will increase with the x-small warehouse starting at 1 compute credit or $2 per hour and the largest virtual warehouse – the 4X-Large topping the list at 128 compute credits or $512 per hour with the top spec Business Critical plan.

                      Snowflake Warehouse Sizes and Credit Usage per Hour
SizeX-SmallSmallMediumLargeX-

 

Large

2X-

 

Large

3X-

 

Large

4X-

 

Large

Credit Usage / Hour1248163264128

BigQuery storage costs are based on the amount of data stored and the amount of data processed by each query you run.

PricingDetails
Active storage

 

$0.020 per GB

The first 10 GB is free each month.
Long-term storage

 

$0.010 per GB

The first 10 GB is free each month.
BigQuery Storage API

 

$1.10 per TB

The BigQuery Storage API is not free. 
Streaming Inserts

 

$0.010 per 200 MB

You are charged for rows that are successfully inserted. Individual rows are calculated using a 1 KB minimum size.
Queries (on-demand)

 

$5.00 per TB

First 1 TB per month is free.

To estimate compute cost in BigQuery, when you run a query in the CLI, you can use the –dry_run flag to estimate the number of bytes read. You can then use this estimate to calculate query cost in the Pricing Calculator.

bq query 
--use_legacy_sql=false 
--dry_run 
'SELECT
  column1,
  column2,
  column3
FROM
  `project_id.dataset.table`
LIMIT
  1000'

After running this query, the response will be similar to this:

Query successfully validated. Assuming the tables are not modified, running this query will process 10399 bytes of data.

You can then estimate the on-demand query costs in the Google Cloud Pricing Calculator

Depending on usage patterns, choosing the wrong vendor or pricing option can get you into trouble fast. With a large dataset, and an on-demand pricing model based on bytes read, choosing BigQuery can result in unbounded and unpredictable monthly bills. Snowflake is, therefore, the clear winner here as it offers a more predictable pricing model.

Ease of Data Load – BigQuery Vs Snowflake

Importing data into BigQuery or Snowflake is the first challenge to overcome when working with them. You can choose to build custom ETL script to move data from all of your data sources into these data warehouses. Both these data warehouses have an option to load data using a GUI interface. This, however, works only when the data being loaded in batches manually would suit the use case. 

A fully managed platform like Hevo Data offers the path of the least resistance.

Loading Data into BigQuery and Snowflake Using Hevo

Hevo Data is an Automated Data Pipeline Platform that can help you load data to BigQuery or Snowflake without having to write any code.

As a fully-managed service, Hevo requires no capacity planning, provisioning, or monitoring. You simply connect any of source data and stream into BigQuery / Snowflake and let the platform manage the rest. This significantly reduces your total cost of ownership (TCO) for a data pipeline solution and ensures you have reliable data in your warehouse at all points.

Hevo brings in reliability, data security, and data accuracy – making it the right Automated Data Pipeline Platform for you.

Still unsure which path to choose between Google BigQuery vs Snowflake? Explore Amazon Redshift, an equally famous cloud data warehouse. Read about Snowflake vs Redshift and BigQuery vs Redshift here.

No-code Data Pipeline for your Data Warehouse