Cloud Data Warehouse Performance Testingv1.0

Product Profile and Evaluation: Amazon Redshift, Microsoft Azure SQL Data Warehouse, Google BigQuery, and Snowflake Data Warehouse

1. Cloud Analytics Platform Offerings

Big data analytics platforms load, store, and analyze volumes of data rapidly, providing timely insights to businesses. Data-driven organizations leverage this data, for example, for advanced analysis to market new promotions, as operational analytics to drive efficiency, or for predictive analytics to evaluate credit risk and detect fraud. Customers are leveraging a mix of relational analytical databases and data warehouses to gain insights.

This report focuses on relational analytical databases in the cloud, because deployments are at an all-time high and poised to expand dramatically. The cloud enables enterprises to differentiate and innovate with these database systems at a much quicker pace than was ever possible before. The cloud is a disruptive technology, offering elastic scalability vis-à-vis on-premises deployments, enabling faster server deployment and application development, and allowing for less costly storage. For these reasons and others, many companies have leveraged the cloud to maintain, or gain, momentum as a company.

This report outlines the results from a GigaOm Analytic Field Test derived from the industry standard TPC Benchmark™ DS (TPC-DS)1 comparing Amazon Redshift, Azure SQL Data Warehouse, Google BigQuery, and Snowflake Data Warehouse — four relational analytical databases based on scale-out cloud data warehouses and columnar-based database architectures. Despite these similarities, there are some distinct differences between the four platforms.

Amazon Redshift

Amazon Web Services Redshift was the first managed data warehouse cloud service and continues to maintain a high level of mindshare in this category. It does indeed tick all the table stakes boxes for a cloud analytic database.

Amazon Redshift Spectrum can create an external table to store raw data on Amazon S3. Redshift Spectrum has some future-proofing that a modern data engineering approach might utilize. In support of diverse data, Amazon Redshift has a few nice features like the JSON_EXTRACT_PATH_TEXT function for noSQL key value databases.

Amazon Redshift is a fit for organizations needing a data warehouse with little to no administrative overhead and a clear, consistent pricing model. Amazon Web Services supports most of the databases in this report, and then some.

Azure SQL Data Warehouse

Azure SQL Data Warehouse storage is separate from the compute Data Warehouse Unit (DWU). This enables Azure SQL Data Warehouse to scale columnar storage capacity and compute resources independently. This capability adjusts to varying workload demands, offering potential cost savings when demand is low. Azure SQL Data Warehouse can also pause and resume compute billing, meaning only the storage is billed for during the paused time. Azure SQL Data Warehouse achieves a good balance in both configurability and simplicity, in a way that is both easy to administer and flexible in handling almost any usage pattern.

Azure SQL Data Warehouse is fully ANSI-SQL compliant and users familiar with SQL Server will be very comfortable using this environment.

Azure SQL Data Warehouse can export data to a local file the same way an on-premises SQL Server can, e.g., via the SQL Server Import and Export Wizard.

Google BigQuery

Google BigQuery is a managed service with some interesting distinctions. Google abstracts the details of the underlying hardware, database, and all configurations.

BigQuery is a hands-off database without indexes or column constraints. Defragmentation and system tuning are not required. It is truly serverless. Google Cloud manages the servers in a fully hands-off manner to the customer, dynamically allocating storage and compute resources. The customer does not define nodes and capacity of the BigQuery instance. The provisioning of compute is particularly fast and seamless.

You pay for the amount of data you query and store. Customers can pre-purchase flat-rate computation “slots” or units in increments of $10,000 per month per 500 compute units.

We simply consumed the results for this field test, but should we have been looking to do more with the data, such as exporting it in different formats, BigQuery has the capabilities to do so.

Also, although we did not time data ingest, this is an area of strength for BigQuery. BigQuery ingest does not impact the performance of queries, because separate compute slots are used for loading apart from the ones performing the SELECT statements for users. Ingest stats are auto-generated and data with an unknown schema can be loaded using schema auto-suggestion. There is no charge for ingest.

Snowflake Data Warehouse

As a cloud-only, fully managed solution, Snowflake has a clear separation between compute and storage. For Snowflake on AWS, which is what we used for the queries, data is stored in AWS S3 and is cached when queries are executed to bring the data in closer proximity to compute resources. Snowflake essentially offers two configuration “levers” — the size of the warehouse cluster and how many clusters are permitted to spin up to handle concurrency. Snowflake scales by cluster server count in powers of 2 (i.e., 1, 2, 4, 8, 16, and so on). If enabled, Snowflake will spin up additional clusters to handle multi-user concurrent query workloads. Snowflake would then automatically spin the additional clusters down once demand has passed. If not enabled, it will place paused queries in a queue until resources free up.

In our estimation, Snowflake performance largely hinges on cache. If Snowflake must access Amazon S3 for additional data, query execution slows dramatically. Historically, Snowflake has not disclosed the server class or size of its AWS EC2 instances. Thus, one cannot calculate beforehand what configuration would be most optimal for workloads.

Snowflake supports an ANSI-compliant form of SQL. However, we experienced syntax differences when converting existing queries to Snowflake. Snowflake supports data export to S3 (on AWS), though it is possible to use the Snowflake command line interface results to direct data to a file in a Linux shell.

Table 1. Platform Summary

Azure SQL Data Warehouse Snowflake Data Warehouse Google BigQuery Amazon Redshift
First Released 1989 (SQL Server)

2005 (Analytics Platform System)

2016 (Azure SQL Data Warehouse)

2014 2010 2014
Current Version 10.0.10106.0 3.13 March 1, 2019 release 1.0.5833
SQL Transact-SQL Snowflake SQL Ansi-2011 Compliant PostgreSQL 8

1More can be learned about the TPC-DS benchmark at http://www.tpc.org/tpcds/.

2. Field Test Setup

The benchmark equivalency was established by price across the four platforms. Microsoft DW15000C costs $183.86/hour to run. 3X-Large Snowflake @ $3.00/node costs $192/hour at their Enterprise support rate. (However, in the Price-Performance calculation below, we used the lowest, limited support $2.00/node price for Snowflake.) Thirty nodes of Redshift dc2.8xlarge @ $4.80/node costs $144.00/hour to run. For Google, we used the cost-per-hour from BigQuery flat rate, which currently is $55.00/hour.

Field Test Data

The 30TB data set used in the benchmark was a workload derived from the well-recognized industry standard TPC Benchmark™ DS (TPC-DS). The parameter values for the queries used across all vendors are informed by the TPC Benchmark™ DS (TPC-DS)2 spec validation queries. This field test is not an official TPC benchmark.

From tpc.org: “The TPCDS is a decision support benchmark that models several generally applicable aspects of a decision support system, including queries and data maintenance. The benchmark provides a representative evaluation of performance as a general purpose decision support system… The purpose of TPC benchmarks is to provide relevant, objective performance data to industry users. TPC-DS Version 2 enables emerging technologies, such as Big Data systems, to execute the benchmark.”

The queries were executed using the following setup, environment, standards, and configurations. The data model consists of 24 tables — 7 fact tables and 17 dimensions. To give an idea of the data volumes used in our field test, the following table gives row counts of fact tables in the database when loaded with 1TB and 10TB of GigaOm Analytic Field Test data:

Table 2. 30TB Database Row Counts

GigaOm Analytic Field Test Table Scale Factor 30,000 30TB Row Count
Catalog Returns 4,319,925,093
Catalog Sales 43,200,404,822
Inventory 1,627,857,000
Store Returns 8,639,952,111
Store Sales 86,399,341,874
Web Returns 2,160,007,345
Web Sales 21,600,036,511

 

Cluster Environments

Our benchmark included four (4) different cluster environments:

  • Azure SQL Data Warehouse DW15000C (30 compute nodes)
  • Amazon Redshift dc2.8xlarge (30 compute nodes)
  • Snowflake Computing 3X-Large (64 compute nodes)
  • Google BigQuery, for which the underlying architecture is unknown and the environment is scaled automatically.

Queries

The GigaOm Analytic Field Test is a fair representation of enterprise query needs. The GigaOm Analytic Field Test suite has 99 queries — four of which have two parts (14, 23, 24, and 39), for a total of 103 queries. The queries used for the tests were compliant with the standards set out by the TPC Benchmark™ DS (TPC-DS) specifications3 and included only minor query modifications as set out by section 4.2.3 of the TPC-DS specification document. For example, minor query modifications included vendor-specific syntax for date expressions. Also in the specification, some queries require row limits and, thus, vendor-specific syntax was used (e.g., TOP, FIRST, LIMIT, and so forth) as allowed by section 4.2.4 of the TPC-DS specification.

Although concurrency was not tested in the benchmark, Azure SQL Data Warehouse supports 128 concurrent queries. BigQuery supports a maximum concurrency of 50 per project, but this is not a hard limit. Many customers use hundreds or thousands of projects, so they get effective concurrency that is much higher. Snowflake’s maximum concurrency is difficult to calculate because it is a function of the number of queries, the submitted queries’ execution plan, the size of the warehouse, and the maximum number of multi-cluster settings. In our experience, we saw an X-Large (16 node) Snowflake warehouse run 6 concurrent simple scan queries (SELECT with a single column filter WHERE clause) before starting to queue. Your results may vary.

2 More can be learned about the TPC-DS benchmark at http://www.tpc.org/tpcds/.
3 The TPC Benchmark™ DS (TPC-DS) specification we used was found at http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.1.0.pdf.

3. Field Test Results

This section analyzes the query results from the fastest of the three runs of the GigaOm Analytic Field Test queries (derived from the TPC-DS) described above. The primary metric used was the aggregate total of the best execution times for each query. Three power runs were completed. Each of the 99 queries was executed three times in order (1, 2, 3) against each vendor cloud platform, and the fastest of the three times was used as the performance metric. These best times were then added together to gain the total aggregate execution time for the entire workload.

Selected Individual Query Results

Since the GigaOm Analytic Field Test included 103 TPC-DS derived queries, it would be too exhaustive to provide charts and analysis for every single one. GigaOm selected 10 queries to highlight that we determined to be good representatives of common data warehouse queries and use cases, irrespective of competitive performance. The section below discusses the queries and presents the results of the four platforms tested.

Long Running Queries

Query 14a

Query 14a is a lookup of sales by item, with breakouts of channels and brands. This information is valuable to marketing and sales leadership, and could be used behind interactive business intelligence (BI) dashboards driven by several parameters in the query, including date, brand, and category. If you have ever performed this SQL query in a modern organization, where there is a table for anything that can be determined to be discrete, you know the calculation is very complex, combining catalog, web, and store sales.

Query 14a is 1 of 2 queries in the GigaOm Analytic Field Test set that feature an intersect function and there are 2 intersects in the query. It is also one of the longer running queries in the set, so the performance is important.

Figure 1: Query 14a. GigaOm Analytic Field Test Execution Time

Query 80

Query 80 reports sales, net profit, and returns in all channels for a window of time for items with prices larger than a certain amount that are not promoted on television. Results are aggregated by channel and “means” within the channel (such as the store for store sales, the page of the catalog for catalog sales, and the actual web site for web sales). This is a helpful query for establishing the extended value of each channel.

Query 80 is complex to code and is also a bottom 10% query in terms of usual performance time.

Figure 2: Query 80. GigaOm Analytic Field Test Execution Time

Frequently Run Queries

Query 6

Query 6 returns high value and volume buyers by state, which is analogous to queries that are routinely performed in the enterprise today where support, service, and marketing functions are being tiered based on customer value. Also, Query 6 triangulates item and pricing data, so this query benefits pricing teams within marketing groups, targeting future pricing that will be attractive to customers in various states and regions.

Query 6 is one of the faster GigaOm Analytic Field Test performing queries, but that does not take away from its importance. Query 6 is a simplistic 5-table join, with one correlated subquery, but is of sufficient complexity that performance can start becoming an issue in an enterprise.

Figure 3: Query 6. GigaOm Analytic Field Test Execution Time

Query 29

Query 29 retrieves how many items were sold in stores, which were returned in a time window, and which were purchased through the catalog in a time window. It shows the quantity with results grouped by item and store. This query could drive sales BI reports and dashboards from mid-level district managers down to store managers to make predictions for future inventory levels, stocking, and purchasing needs.

In terms of complexity, Query 29 is a 6-table join with date joined into the core tables 3 times. It is a mid-tier performer, but it is really important to see how well the database performs the joins.

Figure 4: Query 29. GigaOm Analytic Field Test Execution Time

Query 30

Query 30 identifies customers who have returned a percentage more than the average for their state. Identifying outlying customers like this is important in data-driven organizations, who must utilize all data to get an advantage. Analyzing customers who exhibit outlier behavior, and their data, are precursors to machine learning algorithms to detect patterns including fraudulent returns or overly unsatisfactory products.

Query 30 has some complex subqueries, which are common in enterprise queries.

Figure 5: Query 30. GigaOm Analytic Field Test Execution Time

Query 39a

Query 39a calculates the coefficient of variation and mean of every item and warehouse for two consecutive months. This moving average inventory analysis would be very useful to supply chain managers, in coordination with suppliers and procurement teams, to ensure the proper balance of inventory flows.

Query 39a has a join, subquery, and case. Other than the calculations, it is a straightforward query, nicely representative of common enterprise queries.

Figure 6: Query 39a. GigaOm Analytic Field Test Execution Time

Query 44

Query 44 is looking for the best and worst performing items, a common query for an enterprise, but it is measured by net profit which adds complexity (as anyone who has ever attempted this calculation in a modern enterprise knows). These insights are critical for profit-and-loss financial analysts, as well as actuarial departments, who must forecast the best performing items and how current sales might impact the future bottom line and an upcoming stock market earnings report.

An enterprise would want to note the performance of nested subqueries, which this query features, and is why we find it is worth attention.

Figure 7: Query 44. GigaOm Analytic Field Test Execution Time

Query 47

Query 47 is looking for monthly sales that deviate by a certain percentage from the average monthly sales for the year, sorted by deviation and store. Performance management or performance-based incentive managers would use this information to track the changes over time, and store or sales managers might use it to measure the impact of new initiatives versus current efforts.

Query 47 is a trending and ranking query that is similar to what many enterprises are doing in determining points of attention that are based on business condition change. It is also a mid-tier query, where an enterprise would care about its performance.

Figure 8: Query 47. GigaOm Analytic Field Test Execution Time

Query 93

Query 93 looks at customer purchases with returns backed out, where the returns are looked up in ticketing tables. This is an operational query that could be used by return departments to track returned items.

Query 93 is a simple query to code and is a very common query run in enterprises today.

Figure 9: Query 93. GigaOm Analytic Field Test Execution Time

Query 94

Query 94 is calculating order counts. Calculating order counts for a specific use is usually more complex than a simple count. The count includes web sales, total shipping cost, and net profit in a time window for customers in a given state from a named website for non-returned orders shipped from more than one warehouse. For companies offering free shipping incentives on their e-commerce sites, this type of analysis would be valuable in triangulating pricing and sales data to ensure the free shipping offer is both cost effective and incentivizing for customers.

Figure 10: Query 94. GigaOm Analytic Field Test Execution Time

Aggregate Results

As mentioned, the best times of each platform’s three power runs were then added together to gain the total aggregate execution time for the entire workload. The following chart shows the overall performance of each platform for the given workload, in terms of total time it took to execute all 103 queries in the GigaOm Analytic Field Test.

Figure 11:GigaOm Field Test Total Aggregate Execution Time of All Queries

The next table presents the complete set of the best results of all query executions for each platform. The spark chart for each row provides a visual reference for the fastest and slowest times for each query. The following is a legend of the colors and headings used:

Figure 12: GigaOm Field Test Color Legend for Table 3

Table 3: GigaOm Field Test Results for All Queries

4. Price Per Performance

The price-performance metric is dollars per query-hour ($/query-hour). This is defined as the normalized cost of running the GigaOm Analytic Field Test workload on each of the cloud platforms. It was calculated by multiplying the best on-demand rate (expressed in dollars) offered by the cloud platform vendor (at the time of testing) times the number of computation nodes used in the cluster, and then dividing this amount by the aggregate total of the best execution times for each query (expressed in hours).

To determine pricing, each platform has different options, which include reserved instances, annual contracts, and prepay. However, reserved instance pricing can only be procured with annual commitments, and is most cost-effective when paid in full upfront. Long commitments are out of scope for this field test, so we chose the lowest on-demand rate. Buyers should evaluate all of the pricing options, not just the ones presented in this report.

For Azure SQL Data Warehouse, you pay for compute resources as a function of time. The hourly rate for SQL Data Warehouse varies slightly by region. We chose the lowest found hourly rate of $6.04 per node for the DW15000C service level.4 Also, we added the separate storage charge to store the 14TB of data (automatically compressed down from 30TB) at a rate of $0.19 per TB per hour.

For Amazon Redshift, you also pay for compute resources as a function of time. For price-performance, we used the lowest hourly rate we found for Redshift dc2.8xlarge instance type,  $4.80 per node.5

For Snowflake, you pay for compute resources as a function of time — just like SQL Data Warehouse and Redshift. However, with Snowflake, you choose the hourly rate based on the enterprise features you need. For the lowest level of support, the Standard rate is $2.00 per node, per hour.  Features like multi-cluster (automatic scale out of additional cluster), enterprise-level security, and more support, are $3.00 per node, per hour at the Enterprise level. There are higher levels. For price-performance, we used both Standard and Enterprise.6

With Google BigQuery, the default option is to pay for bytes processed. During a single run of the GigaOm Analytic Field Test derived queries, we processed roughly 113TB of data. If we used the on-demand pricing, the $5 per TB for BigQuery rate would have cost $564. The second option is to pay a flat rate cost-per-hour. If we used the cost-per-hour from BigQuery flat rate, which was $55 at the time of this report, the total workload, which ran for 10.4 hours, would have cost $570. Most likely, anyone using large scale data warehousing would be on the flat-rate. We show both options7

If you contiguously ran all 103 of these queries to completion of the set, the cost at an hourly basis is indicated in the chart below. Azure SQL Data Warehouse provided the best price-performance for the GigaOm analytic field test queries.

Table 4: GigaOm Field Test Price-Performance @30TB ($ per Query, per Hour) Comparison

SQL Data Warehouse Redshift Snowflake Standard Snowflake Enterprise BigQuery Flat Rate BigQuery Per Byte
Instance Type DW 15000C dc2.8xlarge 3X-Large 3X-Large Flat Rate Per Byte
Nodes 30 30 64 64 N/A N/A
Compute $/node/hour $ 6.04 $ 4.80 $ 2.00 $ 3.00
Compute $/hour $ 181.20 $ 144.00 $ 128.00 $ 192.00 $ 55.00 $ 5.00 / TB
$/hour storage $ 0.19
Total Storage TB 14 TB
Total Storage $/hour $ 2.66
Price Basis: Total Execution Time (Sum of Best Times) or TB processed 2,996 7,143 5,793 5,793 37,283 113 TB
Price-Performance $ 153.01 $ 285.73 $ 205.97 $ 308.96 $ 569.60 $ 1310.00

Figure 13: GigaOm Field test Price Performance Comparison Graph

4Azure SQL Data Warehouse pricing: https://azure.microsoft.com/en-us/pricing/details/sql-data-warehouse/gen2/
5Amazon Redshift pricing: https://aws.amazon.com/redshift/pricing/
6Snowflake pricing: https://www.snowflake.com/pricing/
7Google BigQuery pricing: https://cloud.google.com/bigquery/pricing

5. Summary

Cloud databases are a way for enterprises to avoid large capital expenditures, provision quickly, and provide performance at scale for advanced analytic queries. Relational databases with analytic capabilities continue to support the advanced analytic workloads of the organization with performance, scale, and concurrency. For the GigaOm Analytic Field Test, which contains a representative set of corporate-complex queries derived from the well-known TPC Benchmark™ DS (TPC-DS)8 standard, Azure SQL Data Warehouse consistently outperformed the competition.

Overall, the benchmark results were insightful in revealing query execution performance and some of the differentiators between Azure SQL Data Warehouse, Snowflake Data Warehouse, Amazon Redshift, and Google BigQuery. Azure SQL Data Warehouse query response times on the 30TB GigaOm Analytic Field Test data set were twice as fast as Snowflake, approximately two and a half times the performance of Redshift, and 12 times faster than BigQuery overall.

In terms of price per performance, Azure SQL Data Warehouse ran the GigaOm Analytic Field Test queries 30% cheaper than Snowflake in terms of cost per query per hour, and was 1.9 times more cost-effective in terms of cost per query, per hour than Redshift. Azure SQL Data Warehouse also ran the GigaOm Analytic Field Test queries 3.7 times cheaper than Google BigQuery in terms of price per query, per hour.

Price and performance are critical points of interest when it comes to selecting an analytics platform, because they ultimately impact total cost of ownership, value, and user satisfaction. Our analysis reveals Azure SQL Data Warehouse to be very powerful and comparative in value.

8This was NOT an official TPC Benchmark™ DS (TPC-DS) benchmark. More can be learned about the TPC-DS benchmark at http://www.tpc.org/tpcds/.

6. In Closing

Price-performance is important, but it is only one criterion in selecting a data warehouse platform. There are numerous other factors to consider including ease of administration, integration with existing software and systems, workload management, user interface, scalability, vendor support, and reliability, among others.

This price-performance test is a single  point-in-time check. It is our experience that performance changes over time and varies widely for different workloads. Also, a platform performance leader can hit up against the point of diminishing returns and viable contenders can quickly close the gap.

GigaOm runs all of its performance tests to strict ethical standards. The results of the report are the quantitative results of the application queries to the simulations described in the report. The report clearly defines the selected criteria and process used to establish the field test. The report also clearly states the data set sizes, the platforms, the queries, etc. used. The reader is left to determine for themselves how to utilize the information for their individual needs. The report does not make any claim regarding third-party certification, and presents the objective results received from the application of the process to the criteria as described in the report. The report strictly measures price-performance and does not purport to evaluate other factors that potential customers may find relevant when making a purchase decision.  

This is a sponsored report. Microsoft chose the test, the Azure configuration, and the platforms evaluated. GigaOm chose the most compatible configurations for the other tested platforms, configured and ran the queries. Choosing compatible configurations is subject to judgment. We have attempted to describe our decisions in this paper.

In this writeup, all the information necessary is included to replicate this test. You are encouraged to compile your own representative queries, data sets, data sizes, and compatible configurations and test for yourself.

7. About Microsoft

Microsoft offers Azure SQL Data Warehouse.

Learn more at https://azure.microsoft.com/en-us/services/sql-data-warehouse/.

8. About William McKnight

William McKnight is a former Fortune 50 technology executive and database engineer. An Ernst & Young Entrepreneur of the Year finalist and frequent best practices judge, he helps enterprise clients with action plans, architectures, strategies, and technology tools to manage information.

Currently, William is an analyst for GigaOm Research who takes corporate information and turns it into a bottom-line-enhancing asset. He has worked with Dong Energy, France Telecom, Pfizer, Samba Bank, ScotiaBank, Teva Pharmaceuticals, and Verizon, among many others. William focuses on delivering business value and solving business problems utilizing proven approaches in information management.

9. About GigaOm

GigaOm provides technical, operational, and business advice for IT’s strategic digital enterprise and business initiatives. Enterprise business leaders, CIOs, and technology organizations partner with GigaOm for practical, actionable, strategic, and visionary advice for modernizing and transforming their business. GigaOm’s advice empowers enterprises to successfully compete in an increasingly complicated business atmosphere that requires a solid understanding of constantly changing customer demands.

GigaOm works directly with enterprises both inside and outside of the IT organization to apply proven research and methodologies designed to avoid pitfalls and roadblocks while balancing risk and innovation. Research methodologies include but are not limited to adoption and benchmarking surveys, use cases, interviews, ROI/TCO, market landscapes, strategic trends, and technical benchmarks. Our analysts possess 20+ years of experience advising a spectrum of clients from early adopters to mainstream enterprises.

GigaOm’s perspective is that of the unbiased enterprise practitioner. Through this perspective, GigaOm connects with engaged and loyal subscribers on a deep and meaningful level.

10. Copyright

© Knowingly, Inc. 2019 "Cloud Data Warehouse Performance Testing" is a trademark of Knowingly, Inc. For permission to reproduce this report, please contact sales@gigaom.com.