## Introduction
Often at the beginning of an analytical application's lifecycle–when there is a smaller dataset that queries execute over–the application works well and delivers responses within acceptable thresholds. As the size of the dataset and number of users grow, the time-to-response from a user's perspective can often suffer quite heavily. This is true of both application and purpose-built cloud data warehousing solutions.

This leaves us with a chicken-and-egg problem. Application databases can deliver low-latency responses with small-to-large datasets, but struggle with massive analytical datasets. Cloud data warehousing solutions usually make no guarantees except to deliver a response, which means latency can vary wildly on a query-to-query basis.

Cube Cloud optimizes query performance from redundant queries with in-memory caching, queue management, and configurable pre-aggregation caching in Cube Store. In this blog, we’ll provide an overview of Cube Cloud’s caching capabilities and load testing to compare Cube Store to a cloud data warehouse.

## Caching in Cube Cloud
Cube Cloud provides a two-level caching system. The first level is an [in-memory cache](https://cube.dev/docs/product/caching#in-memory-cache "in-memory cache") and is active by default. Cube Cloud's in-memory cache acts as a buffer for your database when there's a burst of requests hitting the same data from multiple concurrent users. The second level of caching is called [pre-aggregations](https://cube.dev/docs/product/caching#pre-aggregations "pre-aggregations"), and requires explicit configuration to activate. 

Cube Cloud caches the results of executed queries using in-memory cache. The cache key is a generated SQL statement with any existing query-dependent pre-aggregations. Upon receiving an incoming request, Cube Cloud first checks the cache using this key. If nothing is found in the cache, the query is executed in the database and the result set is returned as well as updating the cache.

If an existing value is present in the cache and the ```refresh_key``` value for the query hasn't changed, the cached value will be returned. Otherwise, the SQL query will be executed against either the pre-aggregations storage or the source database to populate the cache with the results and return them.

Pre-aggregations are defined in the data model and stored in a layer of the aggregated data built and refreshed by Cube Cloud. You can learn more about defining pre-aggregations in [data modeling reference](https://cube.dev/docs/reference/data-model/pre-aggregations "data modeling reference"). It can dramatically improve the query performance and provide a higher concurrency. Pre-aggregations are designed to provide the right balance between time to insight and querying performance.

![](https://ucarecdn.com/4151b6b0-2136-488e-9bfb-3403a0ce5f60/)

## Load Testing
### Objective
The following tests measure query performance and peak Requests Per Second (RPS) across distinct scenarios.

### Hypothesis 
Cube Store can deliver better performance than a cloud data warehouse in a real-world analytics use case.

### Methodology
Load tests were structured into three scenarios using a similar size/cost configuration and <a href="https://www.tpc.org/tpch/" target="_blank" rel="noopener">TPC-H</a> with a scaling factor of 100. The testing framework parameters operate in terms of virtual users (VU): 1 VU is 1 concurrent request. The test duration was set to 10 seconds. The parameters of the test were done purposefully so that both components could achieve a 100% success rate, instead of overwhelming the components to failure. 

Each load test is designed to evaluate the platforms' performance under different conditions:
1. __Run Case 1:__ This scenario involved running identical queries against both Cube Store and the cloud data warehouse to gauge baseline performance metrics.
2. __Run Case 2:__ Here, we introduced a measure, a dimension, and a random filter into the queries, simulating a more realistic analytical workload.
3. __Run Case 3:__ This scenario added complexity by incorporating random dates and granularity alongside a measure and a dimension, reflecting common real-world analytics use cases.

| Cube Cloud Configuration    | Cloud Data Warehouse Configuration     |
| ---------- | ---------- |
|  Tier: Enterprise with Dedicated Infrastructure. | Cluster size: Medium 
API instances: 10 (min and max range set to 10).
Cube Store workers: 8 | 

__NOTE:__ When conducting small and quick tests on query speeds and then trying to extrapolate those results to real-world use cases, the methodology usually fails to take into account query variability and the multiple layers of caching a modern data stack has. It is important to test with your data and workloads. Your results may vary.

## Results
### Run Case 1
In this scenario, where identical queries were executed on both Cube Store and the cloud data warehouse, we observed the following results:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 766       | 691       |
| Peak RPS       | 46.67       | 46       |
| p(95) ms       | 253       | 303       |

![](https://ucarecdn.com/02ca1190-d182-4493-8e1c-5d08211d2867/)

![](https://ucarecdn.com/78da4f06-36b4-44b7-b2e3-ad8ee8f35932/)

The first run case is similar to a very rigid and unrealistic test. In both tests, we are just measuring the performance of Cube Store and the cloud data warehouse first caching layers. Both perform similarly and even in a real-world use case where the same query is executed multiple times by different actors, the real performance would be affected not by the query itself but by the size of the payload the caching layer is serving.

### Run Case 2
Introducing a measure, a dimension, and a random filter provided deeper insights into the platforms' performance under a more realistic workload:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 814       | 731       |
| Peak RPS       | 47       | 46.67       |
| p(95) ms       | 226       | 290       |

![](https://ucarecdn.com/ae58c0f9-b5d9-4782-b3ee-061d38fb41eb/)

![](https://ucarecdn.com/1205e2f0-8c85-487d-bcf4-6864b8323a13/)

The second run case tests a scenario where every query is different from each other, but the possible set of queries being executed is small. This means that the first few queries will be slow, but as the cloud data warehouse caching layers are warmed up, the subsequent queries are as fast as possible. Cube Cloud’s caching layer is already warmed up since it’s serving queries from pre-aggregations; that’s the reason it can achieve maximum performance since the beginning of the test.

### Run Case 3
The inclusion of random dates and granularity alongside other query elements further tested the platforms' capabilities:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 880       | 85       |
| Peak RPS       | 46.67       | 5.67       |
| p(95) ms       | 250       | 4201       |

![](https://ucarecdn.com/67ea8fd3-198b-48de-ae0a-2206241f0359/)

![](https://ucarecdn.com/917fdb7d-5cdd-4a89-a94e-6dee649566f7/)

The third run case is a more realistic scenario. All queries are different, so all queries get executed by the respective query engine. In this scenario, Cube Store's speed is more noticeable, and for the cloud data warehouse to achieve the same performance, it would require using a much bigger cluster size.

## Conclusion
Cube Cloud’s caching capabilities prove to be a powerful solution for improving query performance. With its in-memory caching, queue management, and configurable pre-aggregation caching in Cube Store, Cube Cloud addresses the latency issues that often arise as dataset and user numbers grow. This is particularly beneficial since application databases tend to struggle with massive analytical datasets, and cloud data warehousing solutions can be inconsistent in their response times. Load testing Cube Cloud’s caching capabilities has demonstrated its effectiveness compared to cloud data warehouse solutions.

Nurzhan Ospanov

All Things Cube

Comparative Analysis of Load Tests on Cube Store and Cloud Data Warehouse

Today, I am proud to announce our integration between Cube and the powerful database platform, [SingleStore](https://www.singlestore.com/). At Cube we are committed to empowering businesses of all shapes and sizes with the tools they need to thrive in today's fast-paced digital landscape. By partnering with SingleStore, we are giving customers one more choice in their data warehouse platform. Our joint customers today are building data-driven applications that combine the developer productivity benefits and API management capabilities of Cube with the exceptional performance characteristics of SingleStore to delight their customers.

In addition to all of the features of Cube Cloud that you know and love, integrating with SingleStore provides some of the following benefits:
- Unify analytical and transactional workloads in a single data platform for transacting, analyzing, and contextualizing data in real-time
- Horizontal scaling capability with a distributed architecture to allow users to scale SingleStore to meet the evolving demands of their workloads
- Access to real-time insights provided their high performance and unified architecture

Cube Cloud users can now connect to SingleStore, define their data models and security models in a code-first experience, and then deliver that data to their downstream applications via our REST, GraphQL and SQL APIs. SingleStore is available as a data source now in Cube Cloud and we encourage you to take a look at it. Connect all of your business intelligence tools, custom data-driven applications and embedded analytics use cases, as well as your AI use cases to SingleStore via Cube and improve your developer efficiency and metric consistency.

[Documentation for integrating SingleStore with Cube Cloud](https://cube.dev/docs/product/configuration/data-sources/singlestore) is available.

If you have any questions about Cube Cloud, or SingleStore please reach out via the [contact us form](https://cube.dev/contact), or your Cube Cloud account executive.


Brian Bickell

The Cube x SingleStore Integration

Announcing Support for SingleStore in Cube Cloud

## Utilize the power and scalability of Snowflake.

We’ve long seen that the data experiences landscape has been very diverse. From dozens of general-purpose BIs to hundreds of vertical purpose-built analytical solutions, data consumers have many, many options to visualize and consume data.

The evolution of front-end technologies made it possible to embed quality analytics experiences directly into many software products, further accelerating the proliferation of data products and experiences. 

Cube works on top of Snowflake with direct queries and doesn’t require you to run extracts hence your data always remains in Snowflake. 

## What is a Semantic Layer?

A semantic layer is a middleware between your cloud data warehouse, such as Snowflake, and downstream applications. It abstracts raw data and presents it in a meaningful way to the end-user and serves as a contextual filter between the data warehouse and the various analytical tools that businesses use to slice, dice, and analyze their data.

![](https://ucarecdn.com/8d945f29-e9eb-4e7f-9e9e-29ae7074e195/)

## Why Cube and Snowflake?

**Future-proof data stack**. Snowflake established itself as the center of gravity in a **modern data stack**, a new approach to data engineering that enables teams to pursue higher-value activities. 

**Modern data stack** follows several foundational principles and architecture designs: 
* Using a cloud data warehouse like Snowflake as a central data platform
* [ELT vs. ETL](https://www.snowflake.com/guides/etl-vs-elt)
* Applying software engineering best practices to data management 

Cube embraces software engineering best practices and allows data engineers to [manage data model in code](https://cube.dev/docs/product/getting-started/cloud/create-data-model). This enables leveraging the version control systems to manage data model evolution safely and facilitate collaboration and code reviews. It also makes it possible to have a semantic layer as a part of the CI/CD process and have isolated environments for development, staging, and production.

**Utilize the power and scalability of Snowflake**. Cube works on top of Snowflake with direct queries and doesn’t require you to run extracts hence your data always remains in Snowflake. Cube pushes compute down to your Snowflake instance, fully utilizing the power and scalability of your cloud data warehouse. You can optionally create [pre-aggregations](https://cube.dev/docs/product/caching/getting-started-pre-aggregations) in Cube and enable aggregate awareness to not query your Snowflake instance with small repeated queries but rather update aggregates in the background.

## A step-by-step guide to building semantic layer with Cube and Snowflake

In the below section, we’ll cover a quickstart step-by-step tutorial on how to start with Cube and Snowflake. Cube Cloud is free to start, and you can sign up anytime. No credit card or calls with salespeople are required!

You can find a detailed getting started guide in Cube documentation. 

### Create Snowflake and Cube accounts
First, create Cube Cloud and Snowflake accounts.

If you don’t have a dataset that you would like to use, you can follow [this guide](https://cube.dev/docs/product/getting-started/cloud/load-data) to create a test dataset in your Snowflake instance.

### Connect Snowflake to Cube

Please follow these steps to create your first deployment in Cube Cloud and connect it to your Snowflake instance.

1. After you [sign in to your Cube Cloud account](https://cubecloud.dev/auth), click “Create Deployment”.

2. Give the deployment a name, select the cloud provider and region of your choice, and click “Next”.

3. Next, click Create to create a new project from scratch.
4. Select Snowflake from the list of supported data sources and enter your Snowflake credentials to connect to it. 
5. Once Snowflake is connected, Cube can generate a basic data model from your data warehouse schema, which helps you get started with data modeling faster. We'll inspect these generated files in the next section and start changing them.

### Create your data model
Cube follows a dataset-oriented data modeling approach, which is inspired by and expands upon dimensional modeling. 

When building a [data mode](https://cube.dev/docs/product/getting-started/cloud/create-data-model) in Cube, you work with two dataset-centric objects: cubes and views. Cubes usually represent business entities such as customers, line items, and orders. In cubes, you define all the calculations within the measures and dimensions of these entities. 

To begin building your data model, click on “Enter Development Mode” in Cube Cloud. This will take you to your personal developer space.

Once you are in developer mode, navigate to the “Data Model” and click on any file located in the left sidebar to open it.

You can follow [this detailed guide](https://cube.dev/docs/product/getting-started/cloud/create-data-model) on how to make changes to your data model and build new measures and dimensions.

When you are ready to test updates to your data model, you can navigate to [Cube’s Playground](https://cube.dev/docs/product/workspace/playground). The Playground is a web-based tool that allows you to query your data without connecting any tools or writing any code.

### Query your data
You can query Cube using a BI or visualization tool through the Cube SQL API. For a better experience, we recommend mapping the BI's data model to Cube's semantic layer. This can be done automatically with Semantic Layer Sync or manually.

**Semantic Layer Sync**
[Semantic Layer Sync](https://cube.dev/docs/product/getting-started/cloud/query-from-bi#semantic-layer-sync) will synchronize all public cubes and views with connected BI tools.
1. Create a new sync by navigating to the “Semantic Layer Sync” tab on the “BI Integrations” page and clicking “+ Create Sync”. 
2. Follow the steps in the wizard to create a sync
3. Replace the fields for user, password, and URL with your BI tool credentials, then click on “Save All”. You can now go to the “BI Integrations” page and trigger the synchronization of your newly created semantic layer.
4. After running the sync, navigate to your BI tool instance. You should see the orders_view dataset that was created in the tool. Cube automatically maps all metrics and dimensions in the BI tool to measures and dimensions in the Cube data model.

**Manual setup**
Alternatively, you can connect to Cube and create all the mappings manually. 
1. You can find the credentials to connect to Cube on the “BI Integrations” page under the “SQL API Connection” tab.
2. After connecting, create a new dataset in the BI tool and select "orders_view" as a table.
3. Now you can map the BI tool metrics and columns to Cube's measures and dimensions.

That was a high level preview. You can create both you Snowflake and Cube instance for free to try it out in your own environment.

### ...and you are ready to go...

The combination of Cube and Snowflake help to untangle the complexities of data modeling and empowers organizations to streamline their analytics efforts. Through step-by-step guidance, the blog has illuminated the path to creating a powerful semantic layer, enabling users to harness the true potential of their data and drive insightful decision-making. 

### More resources

Recorded workshop [Getting Started with Cube on Snowflake](https://cube.dev/events/unlock-data-cube-snowflake)

Detail on [connecting to Snowflake](https://cube.dev/docs/product/configuration/data-sources/snowflake)


Artyom Keydunov

Deep Dives

Getting Started with a Snowflake Semantic Layer 

Bartosz Mikulski

Firebolt

Lightning-fast analytics experiences for modern data engineering and dev teams.

Building terabyte-scale data apps with Firebolt and Cube

ClickHouse

Use ClickHouse with Cube to add semantic and caching layers to your data pipeline.

ClickHouse scalability and power for building data-intensive applications

About 6 months ago, we witnessed what Andy Pavlo [wittily characterised](https://twitter.com/andy_pavlo/status/1459251061085118465) as an "old school database benchmark gang war". The timeline of events goes like this:

* [Databricks](https://databricks.com), a data lakehouse company founded by the creators of Apache Spark, published a [blog post](https://databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html) claiming that it set a new data warehousing performance record in 100 TB [TPC-DS benchmark](https://www.tpc.org/tpcds/). It was also mentioned that Databricks was 2.7x faster and 12x better in terms of price performance compared to Snowflake.
* [Snowflake](https://snowflake.com), a data warehousing company founded by ex-Oracle and ex-VectorWise experts, responded with a [blog post](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) that critically reviewed Databricks' findings, reported different results for the same benchmark, and claimed comparable price/performance to Databricks.
* After two days, Databricks followed up with another [blog post](https://databricks.com/blog/2021/11/15/snowflake-claims-similar-price-performance-to-databricks-but-not-so-fast.html) that confirmed that they stand by the initially reported results.

While this back-and-forth between respected data vendors was informative and amusing, its collateral damage was even more substantial. Databricks shared that they have [eliminated the anti-competitive *DeWitt clause*](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html) from their service terms. Snowflake followed up with a [similar update](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) to their acceptable use policy.

But what is the *DeWitt clause*?

More importantly, why would you need to know if a DeWitt clause is included in terms of service in case you'd like to do and publish a performance benchmark? Let's explore.

## Backstory of the DeWitt Clause

In 1982, at the very inception of relational databases, [David DeWitt](https://www.linkedin.com/in/david-dewitt-30189b50/), a researcher at the Department of Computer Sciences at the University of Wisconsin-Madison, was working on measuring database performance. His team wrote the Wisconsin Benchmark, tested a number of databases, and [published the results](https://pages.cs.wisc.edu/~dewitt/includes/benchmarking/vldb83.pdf).

Apparently, some folks were displeased with the results. [Michael Stonebraker](https://en.wikipedia.org/wiki/Michael_Stonebraker), a researcher at the UC Berkeley and co-creator of Ingres, called DeWitt and expressed how upset he was. However, they remained friends anyway as DeWitt recounts: in his [talk on YouTube](https://www.youtube.com/watch?v=-TIUGC4X2q8&t=420s):

<iframe width="800" height="500" src="https://www.youtube.com/embed/-TIUGC4X2q8?start=420" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

Oracle also didn’t perform very well and reacted hastily. [According to DeWitt](https://www.eweek.com/development/db-test-pioneer-makes-history/), Oracle CEO Larry Ellison was so displeased that he called the department chair and insisted, "You have to fire this guy.” Oracle also inserted a clause in their terms of use that boiled down to the fact that one can’t publish benchmarks without getting an explicit approval from Oracle.

## DeWitt Clause vs. DeWitt Embrace Clause

Nowadays, the DeWitt clause is a common provision in end-user license agreements for proprietary software that prevents from publishing information about the software without an explicit approval from the vendor. Over time, it have become widespread in the software industry and its implications are drastic:

* People avoid publishing benchmarks about software products with the DeWitt clause.
* Moreover, some won’t even bother doing a benchmark knowing they won’t be able to publish the results.

There are debates whether DeWitt clauses are [moral or legal](https://www.dwheeler.com/essays/dewitt-clause.html) because of the implications above as well as the overall corrosive effect they have on the industry. Once one supplier adds a DeWitt clause, the others can feel that they are at a disadvantage without one. We're lucky it can also work the other way round, like in the Databricks and Snowflake case. However, open source software (OSS) licenses don’t and can't have DeWitt clauses, meaning that the OSS can be legally critiqued, but not their proprietary competitors.

At the same time, some vendors do quite the opposite to the inclusion of a DeWitt clause. Instead, they change their license to the DeWitt Embrace Clause meaning that if you disclose benchmark results for them, you automatically allow them to do the same with you. In a way, DeWitt Embrace Clause is like copyleft for the DeWitt Clause. 

## Can You Benchmark %DATABASE%?

Now, do you wonder whether some vendor has or hasn't a DeWitt clause?

Shortly after the Databricks and Snowflake debate, [Mark Callaghan](https://twitter.com/MarkCallaghanDB), an ex-MongoDB and ex-Rockset database expert, checked if some database and public cloud vendors have DeWitt clauses in their terms of use.

In this blog post, I try to extend [his research](http://smalldatum.blogspot.com/2021/11/dewitt-clause-vs-public-cloud.html) and maintain an ever-green list of database and similar vendors with regard to the DeWitt clause. (If you spot any inaccuracy, which is totally possible, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post.)

*Last updated: June 2, 2022.*

### 😇 Open-source vendors (without the DeWitt clause)

Open source licenses usually grant users permission to [use open source software for any purpose](https://opensource.org/docs/osd). So, by definition, open source software can't contain DeWitt clauses (because if it did, it wouldn't be open source).

Yes, you can benchmark any of these tools:

* Apache [Drill](https://github.com/apache/drill), [Druid](https://github.com/apache/druid/), [Flink](https://github.com/apache/flink), [Hive](https://github.com/apache/hive), [Kafka](https://kafka.apache.org), [Spark](https://github.com/apache/spark)
* [appwrite](https://github.com/appwrite/appwrite)
* [ClickHouse](https://github.com/ClickHouse/ClickHouse) (they even maintain a [list of benchmarks](https://github.com/ClickHouse/ClickHouse/issues/22398) on GitHub)
* [CockroachDB](https://github.com/cockroachdb/cockroach) (licensed under BSL, not [OSI-approved](https://opensource.org/licenses/alphabetical))
* [Dremio](https://github.com/dremio/dremio-oss)
* [Elasticsearch](https://github.com/elastic/elasticsearch) (licensed under SSPL, not OSI-approved)
* [ksqlDB](https://ksqldb.io)
* [Materialize](https://github.com/MaterializeInc/materialize) (licensed under BSL, not OSI-approved)
* [MongoDB](https://github.com/mongodb/mongo) (licensed under SSPL, not OSI-approved)
* [PostgreSQL](https://www.postgresql.org)
* [Presto](https://github.com/prestodb/presto)
* [Redpanda](https://github.com/redpanda-data/redpanda) (licensed under BSL, not OSI-approved)
* [QuestDB](https://github.com/questdb/questdb)
* [TimescaleDB](https://github.com/timescale/timescaledb)
* [Trino](https://github.com/trinodb/trino)

Also, some open-source vendors collaboratively maintain benchmarking suites such as [Time Series Benchmark Suite](https://github.com/timescale/tsbs) to help choose the best tools for particular workloads.

### 😇 Vendors without the DeWitt clause

Cloud vendors can restrict you from benchmarking their services but sometimes they choose not to have the DeWitt clause.

Yes, you can benchmark any of these services:

* [Ahana Cloud](https://ahana.io/terms-of-service/)
* [Altinity Cloud](https://altinity.com/legal/)
* [appwrite Cloud](https://appwrite.io) (not launched to GA yet)
* [ClickHouse Cloud](https://clickhouse.com/cloud/) (not launched to GA yet)
* [Confluent Cloud](https://assets.confluent.io/m/103033bdcb9b3fcd/original/20220201-Confluent_Cloud_Services_Agreement.pdf)
* [Firebolt](https://d7umqicpi7263.cloudfront.net/eula/product/28a79f78-8008-4c02-a971-54ef6d8ed904/898eb1bc-7c2a-4829-b479-67f5a18a51ff.pdf)
* [Materialize Cloud](https://materialize.com/terms-and-conditions/)
* [MongoDB Cloud](https://www.mongodb.com/cloud-terms-and-conditions)
* [QuestDB Cloud](https://questdb.io/terms/) (not launched to GA yet)
* [Redpanda Cloud](https://redpanda.com/cloud/)
* [Snowflake](https://www.snowflake.com/legal/acceptable-use-policy/)
* [Starburst Galaxy](https://www.starburst.io/terms/galaxy-terms-of-service/)
* [Supabase](https://supabase.com/docs/company/terms)
* [Timescale Cloud](https://www.timescale.com/legal/timescale-cloud-terms-of-service)

However, many of these cloud vendors disallow *abusive benchmarking* in their acceptable use policies. So be careful and please don't get banned.

### 🙃 Vendors with the DeWitt Embrace clause

Some cloud vendors permit you to benchmark their service but require reciprocity: you must make the benchmark reproducible and allow benchmarking of your own service or tool in response.

Yes, you can benchmark any of these services and probably get away with it:

* [Amazon Web Services](https://aws.amazon.com/service-terms/), including [Athena](https://aws.amazon.com/athena/), [Aurora](https://aws.amazon.com/rds/aurora/), and [Redshift](https://aws.amazon.com/redshift/). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [CockroachDB Serverless](https://www.cockroachlabs.com/acceptable-use-policy/). *You can only disclose benchmarking tests if you (i) provide us the necessary information to replicate the tests, (ii) disclose the methodology for the benchmarking tests along with the results, and (iii) allow us to run our own benchmarking tests against your products and services at our discretion.*
* [Databricks](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Platform Services and may disclose the results of the Benchmark other than for Beta Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Platform Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [SingleStore](https://www.singlestore.com/blog/eliminating-the-dewitt-clause-for-greater-transparency-in-benchmarking/). *Customer may perform industry standard benchmarks, comparative tests or evaluations (each, a "Performance Report") of the Managed Service. If Customer performs or discloses, or directs or permits any third party to perform or disclose, any Performance Report of any of the Managed Service, Customer (i) will include in any disclosure, and will disclose to SingleStore all information necessary to replicate such Performance Report and the data from the Performance Report, and (il) agree that SingleStore may perform and disclose the results of the Performance Report of Customer's products or services, irrespective of any restrictions on Performance Report.*

### 🤬 Vendors with the DeWitt clause

These vendors chose to restrict benchmarks with DeWitt clauses.

No, you can't benchmark any of these tools and get away with it:

* [Cloudera Data Platform](https://www.cloudera.com/legal/terms-and-conditions/cloudera-online-services-terms.html). *Restrictions. Customer may not: [...] access or use the Services for purposes of monitoring availability, performance or functionality of the Services, or for any benchmarking or competitive purposes.*
* [Decodable](https://www.decodable.co/legal/tos). *Restrictions. Customer may not, and may not cause or permit others to: [...] disclose results of any benchmark tests or performance tests of the Decodable services hereunder without Decodable’s prior written consent.*
* [Dremio Cloud](https://www.dremio.com/legal/terms-of-service/). *Restrictions. Customer shall not, and shall not cause or allow any Authorized User or third party to: [...] except with Dremio’s prior written permission, publish any performance or benchmark tests or analysis relating to Dremio Cloud.*
* [Elastic Cloud](https://www.elastic.co/agreements/global/cloud-services-monthly). *Restrictions [...] You shall not: [...] access or use any Cloud Service for purposes of monitoring its availability, performance or functionality, or for any other benchmarking or competitive purposes, including, without limitation, for the purpose of designing and/or developing any competitive services.*
* [Google Cloud Platform](https://cloud.google.com/terms/service-terms), including [BigQuery](https://cloud.google.com/bigquery) & [Firebase](https://firebase.google.com). *Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if it (a) obtains Google's prior written consent, (b) provides Google all necessary information to replicate the Tests, and (c) allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test of the Services or (ii) disclose the results of any such Test.*
* [InfluxDB Cloud](https://www.influxdata.com/legal/terms-of-use/). *Restrictions. Customer will not: [...] publish or provide any benchmark, comparison or performance test results.*
* [MariaDB SkySQL](https://legal.mariadb.com/agreements/skysql/SkySQL_Terms_of_Service_V2_2020-12-16.pdf) and [MariaDB Xpand](https://legal.mariadb.com/agreements/xpand/xpand-license-terms-v2-2021-02-24.pdf). *Benchmarking. Customer may perform benchmarks or comparative tests or evaluations (each, a "Benchmark") of the SkySQL Services. However, Customer must obtain MariaDB's prior written approval to disclose to a third party the results of any Benchmark of the SkySQL Services.*
* [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/). *BENCHMARK TESTING. You must obtain Microsoft’s prior written approval to disclose to a third party the results of any benchmark test of the software.*
* [Oracle](https://www.oracle.com/us/corporate/contracts/lic-online-toma-ph-de-eng-2817945.pdf). *RESTRICTIONS. [...] You may not: [...] disclose results of any Program benchmark tests without Oracle's prior written consent.*
* [PlanetScale](https://planetscale.com/legal/aup). *Customer will not, and will ensure that its End Users do not: [...] access any portion of the Product for the purpose of building a similar or competitive product or service, or monitor the Product for any benchmarking or competitive purpose.*

### 😶 Vendors for which the author didn't find the data

I wasn't able to identify where a few vendors stand with regard to the DeWitt clause:

* [MySQL HeatWave](https://www.mysql.com/cloud/)
* [Yellowbrick Data](https://www.yellowbrick.com/)

If you can provide any pointers, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post. Also, please reach out if you'd like more databases to be featured here.

## What about Cube?

[Cube](https://cube.dev) is an open-source [headless BI](https://cube.dev/blog/headless-bi) platform that [integrates with a lot of data sources](https://cube.dev/docs/config/databases): cloud data warehouses, query engines, and databases. Many of them are designed for low latency and some of them can provide high concurrency. However, sometimes they don't; you can read our [blog post](https://cube.dev/blog/data-warehouse-performance-and-how-cube-can-help) about that.

In any case, Cube will have no problem delivering your data, with sub-second latency and high concurrency, to any [data consumer](https://cube.dev/docs/config/downstream) out there: BI tools, data notebooks, front-end apps, etc. Thanks to [Cube Store](https://cube.dev/blog/introducing-cubestore), Cube's REST, GraphQL, and SQL APIs are able to respond to any request within 200-300 milliseconds and allow for 100+ concurrent requests—and that can scale.

Please explore how Cube integrates with [databases and other tools](https://cube.dev/blog/category/data) in the data space in our blog. Also, don't hesitate to try [Cube Cloud](https://cubecloud.dev/auth/signup), join our large [Slack community](http://slack.cube.dev), and give Cube a [star on GitHub](https://github.com/cube-js/cube.js) 🌟

Igor Lukanin

Performance

All database vendors who will either praise or punish you for doing a benchmark as well as an entertaining digression into the recent history of computer science

DeWitt clause, or Can you benchmark a database and get away with it

[KazanExpress](https://kazanexpress.ru/) is a marketplace in Russia with free one-day delivery, regardless of the order value and region. We provide over three million products in over 20 product categories including clothing, electronics, and groceries.

Our marketplace consists of 4 offerings:

1. **B2C** — everything related to the happiness of shoppers and the process of buying goods in the marketplace. We have more than a million visitors daily, and we are loved by more than four million shoppers.
2. **B2B** — focuses on the experience of the seller and the process of selling goods in the marketplace. In recent years, more than 10,000 sellers became our partners and we were able to greatly improve their business.
3. **WMS** — a warehouse management system that is responsible for a key part of our business — efficient packaging and fast delivery that allows us to deliver orders to more than 115 cities in Russia.
4. **ERP** — a single system for automating and managing internal business processes of the marketplace. It helps build simple and effective links between different departments.

We were looking for an analytics solution—both for us and our sellers using the B2B product of KazanExpress—that can provide insight into our performance against our business goals. For KazanExpress, this includes how quickly new sellers can onboard to our marketplace, the effectiveness of existing sellers, our revenue stream, and the number of purchases on KazanExpress.  

Our sellers needed insight on things like types of customer search queries, demands for product categories, product revenue forecast to help plan product availability in the warehouse. In addition, they also needed to understand what impact activities such as price changes, promotions, adding new SKUs, and even changing product photos had on their revenue.

## Why we chose Cube at KazanExpress

Our analytics tool needed to be capable of doing aggregations from several types of databases (e.g. ClickHouse, Postgres, etc.) and easy to integrate with the KazanExpress infrastructure. We looked at three options and you'll see the pros and cons for each below:

<table>
  <thead>
    <tr>
      <th width="15%"></th>
      <th width="42.5%">Pros</th>
      <th width="42.5%">Cons</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td><b>Yandex DataLens</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Predefined set of chart types, customizable even by non-technical people</li>
          <li>Support for materialization and caching</li>
          <li>Supported by Yandex</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>No pre-aggregations mechanism</li>
          <li>Large materialized views are not possible <b>(a major roadblock)</b></li>
          <li>Row-level security (RLS) doesn't support integration with 3rd party applications incl. our security layer <b>(a major roadblock)</b></li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>In-house solution with ETL and ClickHouse data marts</b></td>
      <td valign="top">
        <ul>
          <li>We have full control of the architecture and security layer</li>
          <li>Easy to integrate with our existing infrastructure</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>
            Huge amount of developer resources required to implement
            ClickHouse data marts, interaction with security middleware, etc.
          </li>
          <li>frontend developers required to build charts</li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>Cube</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Ability to build OLAP cubes on top of the raw data layer without data volume limitations</li>
          <li>Simple API and integrations with popular frontend charting libraries</li>
          <li>Support for <a href="https://cube.dev/docs/schema/reference/pre-aggregations">pre-aggregations</a></li>
          <li>Support for <a href="https://cube.dev/docs/product/configuration/advanced/multitenancy">multitenancy</a></li>
          <li>Support for <a href="https://cube.dev/docs/security#custom-authentication">custom authentication</a></li>
          <li>Row-level Security (RLS) and <a href="https://cube.dev/docs/security#generating-json-web-tokens-jwt">JSON Web Tokens (JWT)</a></li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>frontend developers required to build charts</li>
          <li>
            Lack of infrastructure tools in Cube (e.g.
            instrumentation/monitoring data, official Helm chart/Kubernetes
            deployment guide)
          </li>
        </ul>
      </td>
    </tr>
  </tbody>
</table>

Obviously, Cube had a lot of pros and we decided to pursue the Cube route in July 2021. Below you will see one of our seller’s analytics dashboards that are powered by Cube.

![Seller analytics dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9ed4d541-db24-4fde-9950-1f35959ffbfd.png)

## Implementing Cube at KazanExpress and our workflow

Our setup follows the recommended Cube deployment architecture that consists of Cube API, Cube Refresh Worker, external Redis for caching, and Cube Store cluster for working with pre-aggregations. Currently, Cube is connected to three Postgres databases and ClickHouse and you can see our architecture diagram below.

![Architecture Diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9c78020d-6f04-4857-ab22-49bbc0b88914.png)

In the Cube API, we implemented integration with our internal authorization provider to grant access to data in Cube using the same token used throughout our application.

Currently, we have 3 staging deployments in our K8s cluster. We templated the Cube platform using the Helm chart to simplify the deployment process and make it reproducible/extendable/observable. Here are our staging deployments:

- **Feature.** A feature deployment like in Git flow. This is the playground where we can make any experiments with Cube or develop new features. And at the same time, we can test visualization on the frontend.
- **Development.** A place where all approved features are located and we might build releases with required load-testing, regression testing, etc.
- **Production.** A working copy of the platform that we are confident with and available to our sellers.

Each deployment has a full copy of the Cube platform that consists of Cube API, Cube Refresh Worker, Cube Store with its own bucket on S3, and Redis for caching. This workflow gives us the capability to test our hypotheses easily and instantly deliver features to our customers.

We had three people (a frontend developer, a backend developer, and a designer) that were involved in the implementation of Cube at KazanExpress that took about 3 1/2 weeks. We also implemented Cube in three phases:

- **Phase 1.** Deployment of the Cube API into our Kubernetes cluster.
- **Phase 2.** Setting up the Cube API with a refresh worker and pre-aggregations.
- **Phase 3.** Setting up Cube Store.

## Looking ahead

We look forward to continuing to expand our analytics offering with Cube to our sellers. This includes adding more Postgres data sources and providing anonymous statistics on shoppers' search queries, buyers' demographics/interests, product category information on impressions/views/sellers, etc.

## Interested in joining KazanExpress' success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.

Oleg Taizov

User Stories

How KazanExpress is using Cube for analytics in their marketplace offerings

[Gadsme](https://www.gadsme.com/) (literally Ads in Game) was founded in 2019 to provide an advertising platform designed for the gaming and e-sports industries. We are engaged in the serving of non-intrusive advertising embedded within scenes of video games.

Gadsme work with major video game studios and serve millions of ads every day. We keep innovating to bring new brand experiences to this area.

![Gadsme stock photo](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/66b39395-d91d-469c-a29d-7af29f21f708.jpeg)

## How Gadsme started using Cube

The Gadsme team first heard of Cube in 2019 after reading a post on Hacker News. At the time, we were focusing more on developing our cross-platform advertising SDK. When it came to building our [real-time analytics](https://cube.dev/use-cases/real-time-analytics) solution, Cube was the perfect tool to ease iterations around how data insights can be exposed efficiently to our clients. Now, all our dashboards (e.g., for ad requests, impression, fill rate, [eCPM](https://en.wikipedia.org/wiki/Cost_per_mille#Effective_cost_per_mille), etc.) and reporting API use Cube under the hood.

As an AdTech company, Gadsme collects millions of events every day. All those events are stored in BigQuery. With Cube and its built-in pre-aggregations feature, we were able to expose meaningful data with speed and maintain complete control of analytics costs.

## How Cube was implemented at Gadsme 

Our team used pre-aggregations since day one. We were first using Postgres as an external [pre-aggregations database](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage) and now we have been using Cube Store in production since July 2021. This last move to Cube Store brought a noticeable speed-up in the loading times of data and graphs for our client dashboards.

We run Cube on Kubernetes, and we spent some time improving the deployment automation of Cube using Helm Charts.

We also use GraphQL to build all our APIs. We decided not to expose Cube directly and preferred building a GraphQL proxy on top of Cube's [REST API](https://cube.dev/docs/rest-api) (see our architecture diagram below).

![Architecture Diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/63a6a07f-4d68-4925-a0d9-1c1df168e888.png)

In November, we started a new [contribution](https://github.com/cube-js/cube.js/pull/3555) to the Cube project to bring the first official support of a GraphQL API. This new API is available since Cube [v0.28.58](https://github.com/cube-js/cube.js/releases/tag/v0.28.58) and supports the following features:

* API autogeneration based on Cube definitions
* Directives support
* Compatibility with the authorization layer (e.g., [JSON Web Tokens](https://cube.dev/docs/security/context), [queryRewrite](https://cube.dev/docs/config#query-rewrite), [multitenancy](https://cube.dev/docs/product/configuration/advanced/multitenancy), etc.)

We also had the opportunity to discuss our GraphQL contribution during the [November community call](https://www.youtube.com/watch?v=EQ5Wl4yn71c&list=PLtdXl_QTQjpb1dHZCM09qKTsgvgqjSvc9), and we’d welcome your feedback on our contributions not only for GraphQL, but also for Helm Charts. 

## Interested in joining Gadsme's success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community. 

Luc Vauvillier

How Gadsme is using Cube for analytics on their in-game advertising platform

Adnan Rahic

Building a ClickHouse Visualization with Altinity and Cube

Cloud Data Warehouses (CDW)

Performance capabilities of data warehouses and how Cube can help

BigQuery is one of the most well-known data warehouses and it's great at handling large datasets.
However, due to its distributed architecture, it will never give you a sub-second response, on any dataset.

For application developers, building an app directly on top of BigQuery means having substantial wait time
on dashboards and charts, especially if they're dynamic (where users can select different date ranges or
change filters). Most certainly, we can tolerate that for internal apps, but not for the public-facing
apps that we ship to customers.

Anyway, we still want to leverage BigQuery’s cheap data storage and the ability to process large datasets,
while not giving up on the performance. BigQuery will act as a single source of truth and store the raw
data, however, we'll need to add an acceleration layer on top of it. This layer will be based on
[Cube.js](http://github.com/cube-js/cube.js) and we'll store only small, aggregated tables (so-called
"external rollups" or "pre-aggregations") in [Cube Store](https://cube.dev/blog/introducing-cubestore/)
which will provide us with desired sub-second responses. We'll also build an example front-end app with
a dashboard:

[![front-end app](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9d97fff1-aa38-492f-81bf-e2ee83b8c0f4.png)](https://external-rollups-demo.cube.dev)

You can [check out the demo](https://external-rollups-demo.cube.dev) and the
[source code on GitHub](https://github.com/cube-js/cube.js/tree/master/examples/external-rollups).
Make sure to play with date range and switchers: dynamic dashboards benefit the most from the external
rollups (pre-aggregations).

Cube.js provides external rollups (pre-aggregations) as a part of its
[two-level caching system](https://cube.dev/docs/caching). The schema below shows how Cube.js and
Cube Store interact with BigQuery: the raw data is pre-aggregated asynchronously and put in Cube Store;
when a query comes to the API, it will be served from pre-aggregations, without requests to BigQuery.

![schema](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4f6d753e-77d2-4d46-964f-8d2512ff9a18.png)

To use the acceleration layer, we need to configure Cube.js to connect to BigQuery and specify the
pre-aggregations that we want to build. If you are new to Cube.js, I recommend checking
[this 101-style tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/)
first and then coming back here. We are going to use the public Hacker News dataset in BigQuery for our app.

First, please make sure you have [Docker](https://www.docker.com/get-started) installed on your machine.
It's recommended to run Cube.js with [Docker](https://cube.dev/docs/getting-started/docker).

Second, let's create a new folder for your Cube.js app and navigate to it:

```bash
mkdir external-rollups
cd external-rollups
```

Then, create a new `docker-compose.yml` file with Cube.js configuration. We'll use environment variables
from the `.env` file and instruct Cube.js to connect to BigQuery:

```bash
cat > docker-compose.yml << EOL

version: '2.2'

services:
  cube:
    image: cubejs/cube:latest
    ports:
      - 4000:4000
      - 3000:3000
    env_file: .env
    volumes:
      - .:/cube/conf
EOL
```

In the `.env` file we'll set credentials for BigQuery. You can learn more how to obtain them in the
[docs](https://cube.dev/docs/config/databases/google-bigquery). Note that Cube.js should have a write
access to a schema in BigQuery where pre-aggregations will be temporarily stored before being loaded
into Cube Store.

```bash
cat > .env << EOL

CUBEJS_DB_BQ_CREDENTIALS=<JSON_KEY_FILE_IN_BASE64>
CUBEJS_DB_BQ_PROJECT_ID=<PROJECT_ID>
CUBEJS_DB_TYPE=bigquery
CUBEJS_PREAGGREGATIONS_SCHEMA=<WRITABLE_SCHEMA_NAME>
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_API_SECRET=SECRET
CUBEJS_DEV_MODE=true
EOL
```

That is all we need to let Cube.js connect to BigQuery. Also note that Cube Store is readily available for
your development purposes when the `CUBEJS_DEV_MODE` environment variable is set to `true`. Learn more
about [running Cube Store in production](https://cube.dev/docs/caching/running-in-production) in the docs.

Also, we're ready to create our first Cube.js data schema file. Cube.js uses the data schema to generate
SQL code which will be executed in the database. Let's create the `schema/Stories.js` file with the
following contents:

```javascript
cube(`Stories`, {
  sql: `select * from \`fh-bigquery.hackernews.full_partitioned\` WHERE type = 'story'`,

  measures: {
    count: {
      type: `count`,
    }
  },

  dimensions: {
    category: {
      type: `string`,
      case: {
        when: [
          { sql: `STARTS_WITH(title, "Show HN")`, label: `Show HN` },
          { sql: `STARTS_WITH(title, "Ask HN")`, label: `Ask HN` }
        ],
        else: { label: `Other` }
      }
    },

    time: {
      sql: `timestamp`,
      type: `time`
    }
  }
});
```

Now we're ready to run Cube.js:

```bash
docker compose up
```

Then, let's navigate to the [Developer Playground](https://cube.dev/docs/dev-tools/dev-playground),
a convenient web-based tool that helps explore the data, at [localhost:4000](http://localhost:4000).
You can select the `Stories Count` measure and the `Category` dimension there, alongside a time dimension
named `Stories Time` for `All time` by `Month`, to build a chart as shown below:

![Developer Playground](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ddf4eb46-3985-4a84-8491-da91261ce138.png)

If we inspect generated SQL by clicking on the `SQL` button, we’ll see the following:

```sql
SELECT
  CASE
    WHEN STARTS_WITH(title, "Show HN") THEN 'Show HN'
    WHEN STARTS_WITH(title, "Ask HN") THEN 'Ask HN'
    ELSE 'Other'
  END `stories__category`,
  DATETIME_TRUNC(DATETIME(`stories`.timestamp, 'UTC'), MONTH) `stories__time_month`,
  count(*) `stories__count`
FROM
  (
    select
      *
    from
      `fh-bigquery.hackernews.full_partitioned`
    WHERE
      type = 'story'
  ) AS `stories`
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000
```

This SQL shows us that this query runs against the raw data inside BigQuery. Now, let’s make it run
against the pre-aggregated table inside Cube Store. To do that, we are going to define a pre-aggregation.
Usually, it is done inside the same cube, but for the sake of the tutorial, so we can compare the
performance with and without pre-aggregation, let’s create a new cube. We can do it inside the same file.

Add the following code to the `schema/Stories.js` file to declare a pre-aggregation and specify which
measures and dimensions to include into it.

```javascript
cube(`StoriesPreAgg`, {
  extends: Stories,

  preAggregations: {
    main: {
      measures: [ count ],
      dimensions: [ category ],
      granularity: `month`,
      timeDimension: time
    }
  }
});
```

Now, go to the Developer Playground, refresh the page, and select the same measures and dimensions as
before: count, category, and time grouped by month, but from the `Stories Pre Agg` cube.
When running this query the first time, Cube.js will generate a pre-aggregation and load it into
Cube Store. All subsequent requests will go directly to Cube Store. You can inspect the generated SQL
and it should look like the following:

```sql
SELECT
  `stories_pre_agg__category` `stories_pre_agg__category`,
  `stories_pre_agg__time_month` `stories_pre_agg__time_month`,
  sum(`stories_pre_agg__count`) `stories_pre_agg__count`
FROM
  stb_pre_aggregations.stories_pre_agg_main
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000
```
As you can see, query goes to the `stb_pre_aggregations.stories_pre_agg_main` table inside
Cube Store. You can play around with filters to see the performance boost of the accelerated query
compared to the raw one.

You can also check this [demo dashboard with multiple charts](https://external-rollups-demo.cube.dev)
and compare its performance with and without pre-aggregations. The source code of the example dashboard is
[available on GitHub](https://github.com/cube-js/cube.js/tree/master/examples/external-rollups).

Happy hacking!

Google BigQuery

External Rollups: Using Cube Store as an Acceleration Layer for BigQuery

Based in Malaysia, [FeedMe](https://feedmepos.com/) was founded in 2019 and provides a Point of Sale (POS) system for the food and beverage industry across South East Asia. In addition to POS equipment that you see in restaurants, FeedMe provides reporting to their customers. The reporting ranges from sales reports, marketing analytics, inventory management, and others.  

FeedMe wanted to provide a robust reporting system that allowed users to generate their own custom reports. However, their previous solution that was based on SQL queries and JavaScript to process data across MongoDB, CouchDB, and BigQuery was becoming increasingly difficult to maintain and scale. FeedMe uses CouchDB in their client POS systems that can work either online or offline. CouchDB data is then fed into the BigQuery data lake for reporting.  FeedMe’s backend systems are written in JavaScript, so they wanted to use JavaScript to process data from MongoDB and BigQuery.  

## Taking a phased approach to deploy Cube.js

The FeedMe team found Cube.js through Google searches as they were looking for a solution that can help them develop a data schema standard.  They soon learned that Cube.js could help simplify their data processing and decided to take a phased approach to deploy Cube.js. 

In the first phase, they only focused on migrating part of their data schema using Cube.js’ [Dynamic Schema Creation](https://cube.dev/docs/schema/dynamic-schema-creation#top). They found this process to be relatively straightforward and spent about two weeks to complete this first phase.

In the next phase, the team spent another three weeks migrating the rest of their data schema and then implementing more advanced features such as [pre-aggregations](https://cube.dev/docs/schema/reference/pre-aggregations) and [Cube Store](https://cube.dev/docs/deployment/overview#cube-store). The FeedMe team liked the more flexible caching mechanism in Cube.js since they can customize [refreshKey](https://cube.dev/docs/schema/reference/pre-aggregations#refresh-key) and [rollup](https://cube.dev/docs/schema/reference/pre-aggregations#parameters-type-rollup) definitions. With Cube.js pre-aggregations and Cube Store, FeedMe’s BigQuery cost is down by about ⅔. (Before Cube.js, they could only rely on BigQuery’s built-in cache.)

Also, without pre-aggregations, it could take several minutes for their customers to query several months of sales data which wasn’t ideal.  When the data is cached and pre-aggregated, a similar query would take less than 10 seconds. 

## How FeedMe is using Cube.js

At FeedMe, Cube.js is deployed via Docker along with Kubernetes, and they use [Chart.js](https://awesome.cube.dev/tools/chartjs) for visualizations. You will find more details in the architectural diagram below.

![Architecture Diagram Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cd35818c-31b5-4fcf-9e69-866de2f670b1.png)

Cube.js has been in production at FeedMe since May 2021, and below is a sample screenshot of a sales dashboard. 

![Sales Dashboard Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/469f653f-9dcf-432c-b628-54adbf313764.png)

The following screenshot shows a dashboard called the daily Close Up summary.

![Close Up Summary Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cdfbbc45-fdaf-466d-aa39-e27314be13f1.png)

## Working with the Cube.js community

The FeedMe team used the [Cube.js documentation](https://cube.dev/docs/) as an onboarding tool to get familiar with Cube.js and its features in the early days. They also liked the collaborative nature of the Cube.js community, especially on [Slack](https://slack.cube.dev/), as they were navigating different features such as [rollupJoin](https://cube.dev/docs/schema/reference/pre-aggregations#rollup-join), pre-aggregations, and Cube Store. 

They also provided feedback to the Cube Dev team when they discovered an [issue](https://github.com/cube-js/cube.js/issues/3081) with pre-aggregations. Enhancements to the rollupJoin and pre-aggregations will further streamline how the FeedMe team provides custom reports for their users while maintaining their BigQuery costs. 


## Interested in joining FeedMe's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

Ray Paik

How FeedMe used Cube.js to redesign their reporting system

Core to our business at [Simon Data](https://www.simondata.com/) is a data platform that enables our clients to access, operationalize, and centralize their marketing data.  The ultimate goal of Simon Data is to give marketers an intuitive, non-technical platform in which to manage sophisticated data operations required to drive great customer marketing outcomes.

![Simon Data Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a4a475ea-dcc6-4fea-8386-6ae3843c8ed8.png)

The warehouse is powered by a complex, multi-tenant environment comprising datasets of varying lineages, schemas, and business purposes. Some datasets are common across client accounts — such as data we produce or import through marketing channel integrations — while other datasets are client-specific. While this delivers significant leverage for our customers to orchestrate bespoke end-user impressions, it also introduces a brain-bending taxonomical challenge to the engineers looking to drive analytical insights across the entire system.

Since the product’s creation, the platform has seen several analytics products built into it. Early last year, we organized a focused team to consolidate them on a standard foundation. At that same time, we also wanted to rethink how we integrated analytics in our application to streamline the development and deployment processes.

We knew the best path forward was to build a framework that could be used both now and in the future for analytics product development on any part of our core platform. To pull this off, we had some goals for the system.

We wanted a seamless development experience, support for querying arbitrary data, fluency in managing various schemas, and an ability to rapidly prototype and develop the user experience. Equally important was the ability to transparently present the queries and transformations used to produce results to facilitate QA by multiple stakeholders.

## What our users wanted

When we talked with users and business stakeholders, we discovered several pain points:

- Users complained about difficulty configuring custom reports and limited dashboarding capabilities.
- Developers spent too much time maintaining multiple different and complex implementations of analytics products.
- Salespeople wanted an easy-to-use and effective out-of-the-box reporting dashboard to present to prospective clients.
- Client Solutions Managers wanted more transparency in how the system calculated results.
- Product managers and engineers were concerned about the level of effort required to build new analytics products.

## Enter Cube.js

We started with a holistic review of the analytics products we already had to gather requirements for the new reporting suite and took a close look at how clients were (or were not) using them. Alongside that, we also did a systematic review of technologies we could use to build a new reporting platform.

We looked at many commercial products and open-source projects and narrowed the list down to a handful of contenders, including [Apache Kylin](http://kylin.apache.org/) and Druid from the open-source space and Looker from the enterprise. Cube.js quickly rose to the top of that list.

What we liked most about Cube.js (no other offering provided all of these):

- It was designed for precisely the problem we were trying to solve, which was embedding analytics into an existing application, and it fits nicely into our existing technology stack.
- First-class support for the tools we use: Snowflake on the backend and React/Recharts on the frontend.
- Pluggable authentication and schema configuration hooks we could use to integrate with the rest of our platform. Specifically, we use [driverFactory](https://cube.dev/docs/config#options-reference-driver-factory) to provide credentials for Snowflake and MySQL (for external pre-aggregations).
- Out-of-the-box support for deploying as a serverless application on AWS Lambda with API Gateway, ElastiCache, and Aurora/MySQL.
- A fantastic developer sandbox app, [Developer Playground](https://cube.dev/docs/dev-tools/dev-playground), which provides interactive query development, troubleshooting/debugging, the export of queries, and even ready-made Recharts components.
- [Multiple layers of caching](https://cube.dev/docs/caching) to provide the performance necessary for interactive data visualization in our application.
- An integrated framework for building frontend data components leveraging our massive data warehouse, one that was lightweight and enabled rapid iteration without a lot of boilerplate, nor the need to wire together lots of different libraries.

## Where we had to invest in Cube.js

Marketers interact with Simon Data through our easy-to-use web application. We needed a way to query data from multiple independent data warehouses, each with its different schema, and present the results in a common interface. This was a challenge on two fronts: development and deployment.

First, we had to invest in integrating Cube.js with our infrastructure to provide credentials and schema configuration for each individual client's data warehouse. Cube.js provided hooks we could use, so this was reasonably straightforward.

However, we needed to give developers a way to run Cube.js to mirror the production environment while being able to rapidly iterate on schema, query, and frontend component development. This meant building a common application bootstrap and configuration for Cube.js that we could use to launch it in dev, staging, and production environments.

One neat trick we pulled off here was getting Cube.js to seamlessly serve both its playground application and API queries from our web app when running in development mode. This is important for us to be able to run everything (i.e. both Cube.js and Simon Data web app) on our laptops and have the web app be able to send queries to the Cube.js Playground running locally.

Another challenge we faced was safely and securely deploying configurations to production without impacting what was already running. We achieved this by making schemas immutable.

When we want to deploy a new schema or patch an existing one, we create a whole new schema or increment the current schema with the changes. Once we have the latest schema working in our development environment, we can test it out in the staging environment and deploy it to production. We can do all of this in the background, so we only switch the production web application over to the new schema once we have complete confidence in it (see Figures 2 & 3 below).

Our repeatable deploy process sets up the new schema in Snowflake and Cube.js simultaneously, which gives strong consistency between the Snowflake and Cube.js configurations.

We have defined schemas as a set of Snowflake views and Cube.js cubes that are developed and deployed together. We create a schema by writing view templates that map onto the data in Simon’s warehouse designed for the given customer to provide a standard interface to create the cubes on top of (see Figure 1 below). Our developers write these view and cube templates using [Jinja](https://palletsprojects.com/p/jinja/) from their IDEs and can rapidly iterate on them locally using our end-to-end development environment integration. Once the schema is complete and committed, our deployer tool dynamically renders the templates by filling in variables specific to each customer’s datasets. This pattern was the key to building repeatable schemas and deploying them consistently in our complex multi-tenant environment.

Finally, we created a comprehensive, automated end-to-end integration test suite (written in Python) that exercises each cube across all schemas that are live in Cube.js. This serves two important purposes:

1. It validates our production environment.
2. It keeps the Cube.js pre-aggregations database up to date ahead of user interaction.

The integration test runs periodically and also after the deployment of any new schemas. Once the test passes, we can safely switch the web application to the new schema with confidence.

### How Cube.js is deployed at Simon Data

![Figire 1](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/329b1deb-0db8-4c31-a91a-8aa93d4c817c.png)
**Figure 1: Schema is the unit of deployment**

- Each schema gets deployed into both Cube.js and Snowflake at the same time.
- A schema is composed of one or more sources. Each source defines a particular data source.
- Sources are where developers work. During development, the Cube, View, and Table definitions are created as a single unit (a source) that powers one or more in-app features.
- In a source, each Cube (Cube.js config files) has a dependent View (with a SQL query, which the Cube is defined on top of), and each View has one or more dependent Tables (with a name and list of columns that are verified in validation).
- Tables represent the upstream data in the data warehouse that we want to bring into the web application with an in-app reporting feature.

![Figure 2](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/fc652e61-0640-463f-b8ed-0b7d0375b12d.png)
**Figure 2: Deployment process**

- Create a new schema in the Cube.js Playground.
- Deploy the schema into Snowflake & Cube.js.
- Switch the Simon Web App to use the new schema.

![Figure 3](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3d5dc184-927a-4216-9bef-345227ed7b99.png)
**Figure 3: Schema migration**

We can develop a new schema and deploy it in the background, then switch the Simon web app over when ready.

## How are we better of now?

We delivered three major wins to our clients and business stakeholders with this platform:

**Speed.** We can develop and ship analytics products without dealing with any net new infrastructure development going forward. Our efforts are focused on what matters: the data and the UI. Integrating the Cube.js Playground into our development stack enables us to rapidly build a schema and query in Cube.js and then export that for implementation in the front end, all of which a developer can quickly do on a laptop.

**Flexibility.** We have a framework that enables us to seamlessly query any data in Snowflake and present it in the web application. The real win here is that we won't have to choose between maintainability and time to market to get this flexibility -- we can have both.

**Stability (Antifragility).** When we're ready to ship a new product feature, we can deploy it with the confidence that we will not introduce regression on existing functionality because our schemas are immutable. We can also roll out new features to client accounts in a controlled manner, one by one or in batches, and even keep the changes hidden to perform production validation before they are released to end users.

## Results and future plans

We’ve already built net-new analytics products on this platform that solve fundamental client pain points, proving that the platform works. We’re also starting to realize the velocity gains when iterating on these products to add functionality and refine the user experience.

Now we’re starting to look at consolidating some of the earlier analytics products onto the new framework. Finally, our business stakeholders now have transparency into the calculations that power analytics products in the application, giving them confidence in the data and helping them more easily answer client questions.

## Interested in joining Simon Data's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

Louis Zuckerman

How Simon Data addressed their runaway analytics complexity with Cube

[COTA](https://cotahealthcare.com/) was founded in 2011, and they combine oncology expertise with advanced technology and analytics to organize real-world medical treatment data to guide cancer research and care.

COTA builds solutions to better support oncologists' clinical decision-making and researchers' development of new drugs and therapies. One such product is the Real World Analytics (RWA) solution that helps clinicians and researchers make sense of fragmented and often incomplete electronic health records (EHR) data and provide insight into the patient population, treatment patterns, disease outcomes, etc.

COTA has access to millions of electronic oncology patient records with a vast amount of associated data unmatched in the oncology healthcare industry. During COTA's recent [Series&nbsp;D funding](https://cotahealthcare.com/cota-secures-34m-in-growth-capital-and-triples-data-access/) announcement, they shared the expansion of their data access by more than 300%.

Data analytics is thus critical for COTA, and they strive to apply innovative ways to improve the technology used in the healthcare space. The COTA team wanted to replace off-the-shelf solutions like Qlik and Tableau, which require heavy customization and specialty configuration knowledge with a more developer-friendly ecosystem.

With Cube, they found that match and leveraged its reference architecture to save development cycles that previously involved writing custom queries, custom data munging, and processing. When the COTA team discovered Cube in 2019, they became early adopters of Cube soon after it was open sourced. You will find Cube in action today in this [RWA demo video](https://cotahealthcare.wistia.com/medias/1jl9z5dmz4).

<Quote authorName="Sudhakar Velamoor" authorAvatar="https://ucarecdn.com/e33ab3da-98bd-405f-b326-f0348bddd439/" authorRole="VP, Engineering, COTA">The Cube platform reduces our development time significantly and integrates easily with data from various sources. The Cube team is collaborative and quick to respond to our requests and their open source ethos keeps things transparent.</Quote>

## How COTA is using Cube

Today, Cube is a foundational tool for COTA's products, with Cube [data model](hhttps://cube.dev/docs/guides/recipes/code-reusability/schema-generation) serving as the single source of truth for their data and Cube API powering their applications.

The COTA team uses Google Cloud Platform to host and run their applications, including the Cube deployment. Their deep, longitudinal datasets feature upwards of 150 tables from structured and unstructured sources, which come together in their data products to form a cohesive, comprehensive patient journey.

While assessing possible architecture options for the Cube deployment, the COTA team initially explored using Google Cloud Functions to run Cube in serverless mode, and they were among the first Cube users who were [interested in support](https://github.com/cube-js/cube/issues/87) for Google Cloud Functions. However, they ultimately decided to use microservices hosted on Google Compute Engine.

![COTA architecture](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1f3fad8d-f65c-4659-a679-ef0edbf3c7f1.png)

The COTA team uses Angular on the front-end of their solutions. They have been an early proponent of [Angular support](https://cube.dev/docs/frontend-introduction/angular) in Cube while initially integrating Cube via its vanilla JavaScript library, which provided framework-agnostic methods to query Cube API. Today they use Angular 10 and leverage TypeScript support in Cube. They also use the [Plotly](https://plotly.com/javascript/) charting library wrapped with the [angular-plotly.js](https://github.com/plotly/angular-plotly.js) component for data visualization in their applications.

Since multiple queries are often run against Cube API by users while navigating the applications, the COTA team wanted to ensure that queries can be canceled to conserve resources. For example, when a user queries multiple times, they want to cancel the ongoing queries and only run the current (or the latest) query. To achieve that, they used [mutex support](https://cube.dev/docs/reference/frontend/cubejs-client-core), which synchronizes multiple concurrent requests performed by the Cube API via `mutexKey` and `mutexObj`:

```jsx
let resultSet = this.cubejsClient.load({
    dimensions,
    measures,
    filters,
    timeDimensions,
    limit,
    order,
  }, {
    mutexKey,
    mutexObj: this.mutexObject // mutexObject is initialised as {}
  });
```

[In-memory cache and pre-aggregations](https://cube.dev/docs/caching) in Cube also helped the COTA team achieve desired performance since the same table can be viewed in different ways based on applications. They are happy with the performance so far; however, they also experienced that pre-aggregations is likely one of the more complicated features in Cube. Ensuring they use all pre-aggregations features correctly required a fair amount of effort and community support on [Slack](https://slack.cube.dev).

The COTA team also employs [dynamic schema generation](https://cube.dev/docs/schema/dynamic-schema-creation) with zero-setup `originalSql` pre-aggregations to materialize results for every `CustomerID` and `ProductID` pair of dimensions. This approach significantly reduces the response time for their per-customer or per-product queries:

```jsx
asyncModule(async () => {
  const types = ['CustomerID', 'OrderDetail.ProductID'];

  cube(`GeneticTest`, {
    sql: `SELECT * FROM Order`,

    measures: {
      count: { sql: `orderid`, type: `countDistinct` }
    },
    dimensions: {
      orderId: { sql: `orderid`, type: `number` }
    },
    ...(types
      .map(type => ({
        [${getDimensionName(marker)}]: {
          sql: JSON_VALUE(${CUBE}.Order, `$.${type}`),
          type: `string`
        },
      }))
      .reduce((a, b) => Object.assign(a, b))
    ),
    preAggregations: {
      main: { type: `originalSql` }
    }
  })
});
```

The COTA team also provided early feedback on the [advanced boolean logic](https://cube.dev/docs/query-format#boolean-logical-operators), as this feature made it easier for the team to read and write code. Before the advanced boolean logic, it wasn't easy to do complex nested queries, so they used multiple queries by getting an inner query result and computing another query on that result set. With the advanced boolean logic, they took advantage of the [Angular-QueryBuilder](https://zebzhao.github.io/Angular-QueryBuilder/) to create complex logic to get a result with a single API call.

## Deploying Cube.js at COTA

For the Cube.js implementation at COTA, there were 4 people involved: a product manager, a data analyst, and two software engineers. The release of the minimum viable product (MVP) for their RWA product took about 6-8 months.

As the COTA team was working on the Cube.js implementation, they took advantage of community support and insights on [Slack](https://app.slack.com/client/TC0C1HURK/CC0403RRR) and the [community forum](https://forum.cube.dev/). They also appreciated the ability to file bugs and feature requests on [GitHub](https://github.com/cube-js/cube.js/issues). More recently, they learned from feature demo discussions during [Monthly Community Calls](https://www.youtube.com/playlist?list=PLtdXl_QTQjpb1dHZCM09qKTsgvgqjSvc9) on ways to fine-tune their Cube.js implementations (e.g., optimizing `countDistinct` pre-aggregations).

## Future plans

As areas for improvement in Cube.js, the COTA team would like to see more out-of-the-box Angular support from Cube.js to help with the speed of integration at COTA. For example, implementing `AngularHttpTransport` would help them leverage features like `HttpInterceptor` to simplify canceling the requests.

![Shivam Mathura's quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9e94f574-fa44-44ff-b9fe-636fd6c0e8a1.png)

Internally, the COTA team also wants to migrate Cube.js to containerized [Docker deployments](https://cube.dev/blog/cubejs-loves-docker/) to align with the rest of their infrastructure at the company.

## Interested in joining COTA's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

How COTA uses Cube to aid the development of new drugs and therapies

In this tutorial we'll explore how to build an analytical application on top of Google BigQuery, a serverless data warehouse, and use a few public datasets to visualize the impact of the COVID-19 pandemic on people's lives.

## What is Google BigQuery?

Before you can ask, here's the application we're going to build:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rlibk2mzd3x3ai34i7uh.png)

*And not only for the United States but for every country.*

Now to BigQuery! [BigQuery](https://cloud.google.com/bigquery) is a serverless big data warehouse available as a part of Google Cloud Platform. It's highly scalable, meaning that it can process tiny datasets as well as petabytes of data in seconds, using more cloud capacity as needed. (However, due to BigQuery's distributed architecture, you can't possibly expect it to have a sub-second query response time.)

BigQuery has a gentle learning curve, in part due to its excellent support for SQL, although (big surprise!) we won't be writing SQL queries in this tutorial.

BigQuery also has a free usage tier: you get up to 1 TB of processed data per month and some free credits to spend on Google Cloud during the first 90 days. You can probably guess that BigQuery is billed by the amount of processed data.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dtip1f1mcth7svxvv7v2.png)

*BigQuery web console in Google Cloud, with the most important information being: "Query complete (2.3 sec elapsed, 2.1 GB processed)."*

So, let's see what datasets are waiting to be explored! 🦠

## What are BigQuery Public Datasets?

[BigQuery public datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset) are made available without any restrictions to all Google Cloud users. Google pays for the storage of these datasets. You can use them to learn how to work with BigQuery or even build your application on top of them, exactly as we're going to do.

You could query them just if they were your own. However, always pay attention to the license and other relevant info, like update frequency and last update date. Unfortunately, some datasets are really outdated:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v7gvj282p12yfvzhz6tf.png)

So, what to expect? Some of these [212 public datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset) are quite interesting:

* [28 datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset,category:climate) about climate, including the [Real-time Air Quality](https://console.cloud.google.com/marketplace/product/openaq/real-time-air-quality) dataset
* [24 datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset,category:developer-tools) related to developer tools, including [GitHub Activity Data](https://console.cloud.google.com/marketplace/product/github/github-repos)
* [33 datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset,category:encyclopedic) marked encyclopedic, including [Hacker News](https://console.cloud.google.com/marketplace/product/y-combinator/hacker-news) dataset
* and [33 datasets](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset,category:covid19) for COVID-19 research — let's talk about them!

**COVID-19 Government Response Tracker.** This [dataset](https://console.cloud.google.com/marketplace/product/university-of-oxford/covid19_govt_policy) is maintained by the University of Oxford Blavatnik School of Government. It tracks policy responses to COVID-19 from governments around the world. Basically, all lockdowns, curfews, and workplace closures worldwide are registered in this dataset.

**Google Community Mobility Reports.** This [dataset](https://console.cloud.google.com/marketplace/product/bigquery-public-datasets/covid19_google_mobility) is maintained by Google. It provides insights into what has changed in people's habits and behavior in response to policies aimed at combating COVID-19. It reports movement trends over time by geography, across different retail and recreation categories, groceries and pharmacies, parks, transit stations, workplaces, and residential.

We can use both datasets to visualize and correlate the time measures against COVID-19 with changes in social mobility. Here's how it might look like:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/amefzcyiqamzfleopqw8.png)

Want to try it? Here's the [live demo](https://bigquery-public-datasets-demo.cube.dev/) you can use right away. Also, the full source code is on [GitHub](https://github.com/cube-js/cube.js/tree/master/examples/bigquery-public-datasets).

For that, we need to create an analytical API over BigQuery and a web application talking to that API. So, let's get hacking! 🦠

## How to Create an Analytical API

Why do we need an API in the first place?

The most obvious reason is that BigQuery can't provide a sub-second query response time, meaning that an application that talks directly to BigQuery will have a suboptimal user experience. Also, BigQuery bills you by the amount of transferred data, so if you have a popular app, you might suddenly know about that from a billing alert.

Also, direct interaction with BigQuery means that you'll need to write SQL queries. There's nothing wrong with SQL; it's a great domain-specific language, but having SQL queries all over your codebase smells like a leaky abstraction — your application layers will know about column names and data types in your database.

**So, what are we going to do? In this tutorial, we'll use Cube.**

[Cube](https://cube.dev) is an open-source analytical API platform, and it allows you to create an API over any database, BigQuery included.

Cube provides an abstraction called a "semantic layer," or a "data schema," which encapsulates database-specific things, generates SQL queries for you, and lets you use high-level, domain-specific identifiers to work with data.

Also, Cube has a built-in caching layer that provides predictable, low-latency response query times. It means that an API built with Cube is a perfect middleware between your database and your analytical app.

Let's try it in action.

**The first step is to create a new Cube project.** Here, I assume that you already have [Node.js](https://nodejs.org/en/) installed on your machine. Note that you can also [use Docker](https://cube.dev/docs/getting-started-docker) to run Cube. Run in your console:

```bash
npx cubejs-cli create bigquery-public-datasets -d bigquery
```

Now you have your new Cube project in the `bigquery-public-datasets` folder containing a few files. Let's navigate to this folder.

**The second step is to add BigQuery and Google Cloud credentials to the `.env` file.** Cube will pick up its configuration options from this file. Make sure your `.env` file looks like this:

```
# Cube environment variables: https://cube.dev/docs/reference/environment-variables

CUBEJS_DB_TYPE=bigquery
CUBEJS_DB_BQ_PROJECT_ID=your-project-id
CUBEJS_DB_BQ_KEY_FILE=./your-key-file-name.json

CUBEJS_DEV_MODE=true
CUBEJS_API_SECRET=SECRET
```

Here's what all these options mean and how to fill them:
* Obviously, `CUBEJS_DB_TYPE` says we'll be connecting to BigQuery.
* `CUBEJS_DB_BQ_PROJECT_ID` should be set to the identifier of your project in Google Cloud. First, go to the [web console](https://console.cloud.google.com) of Google Cloud. Create an account if you don't have one. Then go to the [new project creation page](https://console.cloud.google.com/projectcreate) and create one. Your project identifier is just below the name text field:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oysxbawk9kq7li6uz5c8.png)

* `CUBEJS_DB_BQ_KEY_FILE` should be set to the key file name for your Google Cloud user that will connect to BigQuery. It's better to use a service account, a special kind of Google Cloud account with restricted access. Go to the [service account creation page](https://console.cloud.google.com/iam-admin/serviceaccounts/create) and create one. On the second step, you'll be asked to specify the roles for this service account. The only roles needed for read-only access to public datasets are `BigQuery Data Viewer` and `BigQuery Job User`. After the user is created, you need to add a new authentication key — use the `...` button on the right to manage the keys for this account and add a new one of JSON type. The key file will be automatically downloaded to your machine. Please put it in the `bigquery-public-datasets` folder and update your `.env` file with its name.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/125qlk9meqvecw9jw34w.png)

* The rest of the options configure Cube and have nothing to do with BigQuery. Save your `.env` file.

**The third step is to start Cube.** Run in your console:

```bash
npm run dev
```

And that's it! Here's what you should see:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5rdos5okrrindw23m5di.png)

Great, the API is up and running. Let's describe our data! 🦠

## How to Define a Data Schema

Before we can explore the data, we need to describe it with a data schema. The [data schema](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) is a high-level domain-specific description of your data. It allows you to skip writing SQL queries and rely on Cube query generation engine.

Create two schema files with the following contents: take `schema/Measures.js` from [this file](https://github.com/cube-js/cube.js/blob/master/examples/bigquery-public-datasets/schema/Measures.js), and `schema/Mobility.js` from [that file](https://github.com/cube-js/cube.js/blob/master/examples/bigquery-public-datasets/schema/Mobility.js). Here is a redacted version of the first file with a few interesting things:

```js
cube(`Measures`, {
  sql: '
    SELECT *
    FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker`
  ',

  measures: {
    confirmed_cases: {
      sql: `confirmed_cases`,
      type: `max`
    },

    cancelPublicEvents: {
      sql: `CAST(cancel_public_events AS NUMERIC)`,
      type: `max`
    },
  },

  dimensions: {
    country: {
      sql: `country_name`,
      type: `string`
    },

    date: {
      sql: `TIMESTAMP(${Measures}.date)`,
      type: `time`
    },
  },
});
```

Note that:
* in this data schema, you describe an analytical `cube`
* it contains the data retrieved via a straightforward `sql` query
* you can define `measures`, i.e., numerical values to be calculated
* measures are calculated using various functions, such as `max`
* you can define `dimensions`, i.e., attributes for which the measures are calculated
* dimensions can have different data types, such as `string` or `time`
* in measure and dimension definitions, you can use BigQuery functions, e.g., `CAST(... AS NUMERIC)` or `TIMESTAMP`

And here's a part of another file:

```js
cube(`Mobility`, {
  sql: '
    SELECT *
    FROM `bigquery-public-data.covid19_google_mobility.mobility_report`
  ',

  measures: {

  },

  dimensions: {

  },

  joins: {
    Measures: {
      sql: `${Measures}.country_name = ${Mobility}.country_region AND
            ${Measures}.date = ${Mobility}.date`,
      relationship: `hasOne`,
    }
  }
});
```

Here you can see that our two cubes, based on different tables from different BigQuery datasets, are joined together with `join`, where a join condition is provided as an SQL statement. Cube takes care of the rest.

Now we have the data schema in place, and we can explore the data! 🦠

## How to Explore the Data

As the console output suggests, let's navigate to [localhost:4000](http://localhost:4000) and behold Cube Developer Playground. It provides a lot of features, but we have a clear path to follow. Let's build a query.

To do so, go to the "Build" tab, click "+ Measure," and select a measure. For example, select `Measures Confirmed Cases`. As you can see, the `Measures Date` time dimension has been automatically selected, and the chart below displays the count of confirmed COVID-19 cases over time. You can even "+ Filter" by `Measures Country`, use the "equals" condition, and put your own country's name into the filter field. Looks interesting, right?

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pagu3j1xrea3i0ix1ofh.png)

Definitely feel free to experiment and try your own queries, measures, dimensions, time dimensions, granularities, and filters.

After that, let's move on and build an analytical app! 🦠

## How to Build an Analytical App

It's worth noting that Cube Developer Playground has one more feature to explore.

If you go to the "Dashboard App" tab, you'll be able to generate the code for a front-end application with a dashboard. There're various templates for different frameworks (React and Angular included) and charting libraries there. Still, you can always choose to "create your own," and if you choose a "dynamic" template, you'll be able to compose queries and add charts just like you did.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/43ljijihw21cpknz4i22.png)

However, we'll choose a much simpler way to go from zero to a full-fledged analytical app — we'll grab the code from GitHub:

* first, download this [dashboard-app.zip](https://github.com/cube-js/cube.js/blob/master/examples/bigquery-public-datasets/dashboard-app.zip) file
* unzip it to your `bigquery-public-datasets` folder
* run `yarn` and `yarn start` (or `npm install` and `npm start`)

You should be all set! Navigate to [localhost:3000](http://localhost:3000) and have a look at this app:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h2ee2ag8q2elit2yis75.png)

Choose your country and take your time to explore the impact of COVID-19 and how mitigation measures correlate with social mobility.

**Let's take Israel.** You can clearly see three waves and the "easing" effect of "stay at home" requirements — after they are introduced, every wave spreads with lesser speed.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wts71vfluxnwvuhza7u9.png)

**Let's take Germany.** You can see how Germans interact with the rules: after the first "stay at home" requirements are lifted, park activity grows, and after the second "stay at home" requirements are introduced, parks instantly become deserted.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e63mtd6ocea1f3u7rq0q.png)

**Let's take Singapore.** Obviously enough, you can see Singapore doing a great job containing the virus. The third wave is nearly unexistent.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cn7fc0ww08xraetp9ikn.png)

**What are your own insights? Please share them in the comments!**

And now, let's explore a few crucial parts of this app to understand better how it works and, more specifically, how it retrieves data from Cube API.

First, as you can see from `package.json`, it's obviously a React app created with the `create-react-app` utility. It has an `index.js` as an entry point and the `App` root component.

Second, it references `@cubejs-client/core` and `@cubejs-client/react` packages as dependencies. Here's what you can see in the `api.js` file:

```js
// Let's use Cube client library to talk to Cube API
import cubejs from '@cubejs-client/core'

// API URL and authentication token are stored in .env file
const cubejsApi = cubejs(
    process.env.REACT_APP_CUBEJS_TOKEN,
    { apiUrl: `${process.env.REACT_APP_API_URL}/cubejs-api/v1` }
);

// The simplest Cube query possible:
// "Hey, Cube, give us a list of all countries."
const countriesQuery = {
    dimensions: [ 'Mobility.country' ]
}

export function loadCountries(callback) {
    // cubejsApi.load returns a promise.
    // Once it's resolved, we can get the result.
    // We can even transform it with tablePivot() or chartPivot()
    cubejsApi
        .load(countriesQuery)
        .then(result => {
            const countries = result
                .tablePivot()
                .map(row => row['Mobility.country'])

            callback(countries)
        })
}
```

Believe it or not, that's the bare minimum we should know about working with Cube REST API in the front-end apps. You import a client library, you compose your query as a JSON object, you load the result asynchronously, and you do whatever you want with the data.

In this application, the data is visualized with Chart.js, a great data visualization library. However, you can choose any library you're familiar with. And maybe your app will look even better than this one:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0inga4y2cruq5dvv4qno.png)

And that's all, folks! 🦠 I hope you liked this tutorial 🤗

Here's just a few things you can do in the end:
* go to the [Cube repo](https://github.com/cube-js/cube.js/) on GitHub and give it a star ⭐️
* share a link to this tutorial on social media or with a friend 🙋‍♀️
* share your insights about the impact of COVID-19 in Cube [community Slack](https://slack.cube.dev)

BigQuery Public Datasets for COVID-19 Impact Research

ClickHouse Dashboard: Analytics Tutorial

[Cyndx](https://cyndx.com) tools serve some of the largest financial services companies worldwide to help them identify emerging companies, growth opportunities, and other investment opportunities. Along with their AI-driven algorithms, Cyndx uses data from over 12 million companies and more than 1 million acquisitions, capital raises, investments, and investors data in their database to provide predictive analytics and help its clients identify target lists in a fraction of the time of traditional workflows.

Recently, Cyndx decided to expand its product and develop functionality to explore the data analytics that would allow its end users to dig deeper than their existing platform. The Cyndx team evaluated several commercial business intelligence (BI) products in the past. Still, most of the solutions required a lot of custom work for frontend/design and integrating with their AI. After one of the Cyndx analysts discovered Cube, they put together a demo within a day that looked like the Cyndx platform.

![cyndx-quote (1).png](https://media.graphcms.com/6jZT0zArTT22BxUhhm8t)

## How Cyndx is using Cube

On its frontend, Cyndx uses the React framework with movable/resizable components. They also use visualization components from [Nivo](https://nivo.rocks/), a data visualization library built on top of [D3.js](https://d3js.org). An extended version of the Cube Developer Playground is used for creating the individual visualizations.

On the backend, the Cube API instance is hosted on a Google Kubernetes Engine (GKE) cluster. Cube is making calls to Cyndx BigQuery data warehouse. For performance, BigQuery caching has been key, but they also do database clustering and partition keys.

Further, they are leveraging a custom backend which is used to store permissions and custom dashboard configurations on a per user basis. The permissions gate access to the dashboards while the dashboard configurations are retrieved on page load to give a user a personalized experience. 

## Testing at Cyndx

To ensure the quality of the data being released, Cyndx runs numerous tests both on the frontend visualizations and on the backend data. Tools such as [Datadog](https://www.datadoghq.com) are used for browser testing to ensure functionality like drill-downs/links are working and to monitor long-running queries that may take too long. 

Additionally, Cyndx also have around 1,000 tests on data itself using [dbt](https://www.getdbt.com) before data gets promoted to production. The Cyndx team has also been active in the dbt community to help build the underlying data structure going into Cube and reducing the overhead on Cube by standardizing what tables look like.

## Self-hosting and custom data visualizations

For Cyndx, it was essential to be able to manage their own security, and the Cyndx team liked the fact that they can self-host Cube rather than deal with third-party servers.

Other key benefits of Cube were the ability to integrate directly with the React framework and using Cyndx's own CSS so that new features such as plotting looked like the rest of the Cyndx platform. The screenshots below show the Cyndx platform (with mock data) where Cube is used.

![Untitled-5.png](https://media.graphcms.com/RdtYBZHrQwaxFtNqFJx8)

![Untitled-6.png](https://media.graphcms.com/nNNs7cERSB2C4J0fVrqM)

## Cyndx team and plans

To implement Cube, Cyndx had a dedicated frontend engineer and analyst as well as a part-time backend engineer and designer. Cube Developer Playground has been instrumental for the team and is on everyone's computer. The team was also able to get responsive community support from [Cube. Slack](https://slack.cube.dev/). 

The Cyndx team successfully went live with Cube in October 2020, and they are looking forward to several improvements in Cube that can help enhance their platform. For example, they default to quarterly (vs. year-over-year) views at Cyndx, so a better way to address granularity in Cube would be beneficial. They would also like to see more custom functions such as "calculated fields" and enhancements in pulling in data from REST APIs (e.g., Salesforce API).

## Future: Self-serve analytics

In the future, Cyndx is planning on rolling out self-serve analytics to its end-users. Clients have expressed a strong interest in digging deeper and customizing the analytics for their organizations. Many investors focus on specific regions and/or specific segments of the market, and they would like the analytics to reflect what is relevant for them. This type of customization is not possible without self-serve analytics as it is not possible to enumerate all of the use cases that Cyndx's end-users may be looking for.

This personalization of the analytics will be done through a multi-phase project in which Cyndx first extends the Cube Developer Playground so that it looks and feels like their web app. The ability to make such customizations was a crucial component in their selection of Cube for their backend modeling tool. Many other enterprise offerings for analytics will allow customizing the visuals, but the user interface for building out visuals was often not editable.

Through the use of the customized playground, the end-users will be able to develop dashboards customized to both the user's individual needs and jointly edit dashboards necessary for their organization's workflows. This type of permission logic is easily extensible on top of Cube backend and was an additional reason for selecting the tool.

## Interested in joining Cyndx's success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Cube Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.

How Cyndx enhanced its financial platform with Cube

React

This guide shows how to build a full-stack real-time dashboard with only open-source tools. You’ll learn how to set up a database, seed it with data, build an API endpoint, and then load and update charts on the frontend via WebSockets in real time.

Real-Time Dashboard with Open-Source Tools

Many of our customers ask us which data warehousing option is cheaper: BigQuery or Redshift? TL;DR: Redshift is cheaper as soon as you don’t leverage multi-stage querying and don’t care a lot about SQL query structure. BigQuery can be much more cost effective if you structure your data warehouse querying very well and split it into stages.

In this blog post, we’re going to break down BigQuery vs Redshift pricing structures and see how they work in detail.

<h1>Redshift pricing</h1>
Redshift pricing is pretty simple to understand. They charge as usual for AWS: per machine, per hour. The cheapest Redshift cluster you can spin up will cost you $0.25 per hour, or about $180 per month. It’ll contain one dc2.large node in the US region. AWS constantly updates prices so please <a href="https://aws.amazon.com/redshift/pricing/" target="_blank" rel="noopener">check their site</a> for up-to-date information.

Storage is bound to computing power for Redshift, unlike EC2 deployments. This means Redshift pricing will depend on your data size. Per TB pricing is $0.425 / TB / hour for HDD storage and $1.5625 / TB / hour for SSD storage.

So the cheapest pricing is $306 / TB / mo. This is on-demand pricing and as usual, AWS provides significant discounts if you pay upfront. Also, there’s separate Redshift Spectrum pricing, which is out of scope for this post.

<h1>BigQuery pricing</h1>

BigQuery pricing is much more complicated compared to Redshift. BigQuery charges separately for storage at $20 / TB / month and $5 / TB processed in query. Ingestion into a BigQuery warehouse is usually free of charge, but this is not the case for data streaming. You can <a href="https://cloud.google.com/bigquery/pricing">check updated BigQuery pricing</a>.

Bytes processed in query, also known as bytes billed, are pretty tricky to understand and this usually makes up about 95% of your BigQuery costs. Let’s review some simple SQL queries to see how BigQuery calculates it.

<h1>Bytes billed explained</h1>

BigQuery uses columnar storage, and bills are based on scanned data within columns and not within rows. We’ll review a simple count query on the sample <a href="https://cloud.google.com/bigquery/public-data/hacker-news" target="_blank" rel="noopener">Hacker News BigQuery data set</a>, which is publicly available.

```
SELECT count(*) FROM `bigquery-public-data`.hacker_news.full
```

It will cost you $0 because no columns are processed in this query.

Let’s introduce a column here:

```
SELECT max(timestamp) FROM `bigquery-public-data`.hacker_news.full
```

This query will cost you 130MB. Here and below we’re using bytes billed cost for the sake of simplicity. This price is calculated as total `timestamp` column size. A data type for the `timestamp` column is `TIMESTAMP`, which is 8 bytes. So there’re about 16M timestamps stored in this column, which correlates with 17M of rows in this table.

Note that the `timestamp` column is not stored for every row due to the nature of <a href="https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format" target="_blank" rel="noopener">BigQuery’s Capacitor storage format</a>.

A much more interesting use case is filtering. Look at the billing for this query: 

```
SELECT max(timestamp) FROM `bigquery-public-data`.hacker_news.full WHERE timestamp BETWEEN '2015-01-01' AND '2015-02-01'
```

You may expect that such a query allows you to reduce the costs, but actually it doesn’t. It’ll be the same 130MB. When a column is involved in a query, BigQuery calculates data as a whole column scan. This happens even if BigQuery doesn’t scan this data due to internal storage optimizations. In this case, querying performance and pricing aren’t aligned.

<aside><h2><a href="https://statsbot.co/blog/modern-data-warehouse/">Choosing Between Modern Data Warehouses</a></h2></aside>

<p>So how can query costs be reduced for such a filtering query? This is where BigQuery sharding and partitioning come into play. BigQuery sharding is implemented as <a href="https://cloud.google.com/bigquery/docs/querying-wildcard-tables" target="_blank" rel="noopener">wildcard table querying</a>. It works as a UNION ALL operation on a scale. You can query at once <a href="https://cloud.google.com/bigquery/quotas" target="_blank" rel="noopener">up to 1000 tables</a> with a specified suffix. This approach is fast as well as cost effective.</p>

Here is an example query on a <a href="https://cloud.google.com/bigquery/public-data/noaa-gsod" target="_blank" rel="noopener">NOAA Global Surface Summary of the Day Weather Data dataset</a>:

```
SELECT max(temp) FROM `bigquery-public-data`.noaa_gsod.`gsod*` WHERE _TABLE_SUFFIX BETWEEN '2010' AND '2018'
```

Unlike in the previous example, costs will be reduced to the size of columns of tables starting with `gsod2010` up to `gsod2018`. It’ll be 262MB instead of 1.11GB in the case where all tables are scanned. BigQuery partitioning works in a pretty same way but it’s not separated unlike sharding tables.

<h1>Leveraging multi-stage querying</h1>

Partitioning and sharding are not the only way to reduce your BigQuery costs. From our perspective, they are not the main one as well. Let’s consider an upgraded version of the previous example.

```
SELECT CONCAT(year, '-', mo, '-', da), max(temp)
FROM `bigquery-public-data`.noaa_gsod.`gsod*`
WHERE CONCAT(year, '-', mo, '-', da) BETWEEN '2010-01-01' AND '2018-01-01' GROUP BY 1
```

It’ll cost you 3.04GB. If you use a table suffix filter instead of a date filter, you can reduce costs to 720MB per query. In such types of queries, the date filter is usually variable, which leads to a lot of queries. Although the table suffix filter allows you to reduce costs dramatically, they will still be high if you query too often. The typical solution here is to introduce a roll up table first and then query it.

At the moment, BigQuery supports the CREATE TABLE AS statement in beta mode, as well as it supports load jobs, which you can use to create a table from an SQL query.

So, for our query we can introduce the following roll up table:

```
SELECT CONCAT(year, '-', mo, '-', da) as date, max(temp) as max_temp
FROM `bigquery-public-data`.noaa_gsod.`gsod*`
GROUP BY 1
```

And to query it we can run something like:

```
SELECT date, max(max_temp)
FROM rollups.max_temp_rollup
WHERE date BETWEEN '2010-01-01' AND '2018-01-01' GROUP BY 1
```

The first query costs us 3.04GB, which is obviously more than in the case of table suffix filtering. On the other hand, the second query is 634KB, which is at least 1000 times less than suffix filtering. So even if you use this query often, it won’t significantly affect your bill.

<h1>Conclusion</h1>

As you see, there is no absolute winner in the Bigquery vs Redshift comparison. On-demand Redshift may cost you $10 / TB / day and up no matter how intensive the workload is. Meanwhile, BigQuery will allow you to query only about 10-50 queries per 1TB of data stored for that price per day. At the same time, we saw a lot of BigQuery deployments, which are at least 4 times cheaper than Redshift due to multi-stage querying.

This means that if you are not cautious about queries issued to BigQuery, it’ll become much more expensive than the Redshift option pretty fast.

Pavel Tiunov

How to optimize your query costs

BigQuery vs Redshift: Pricing Strategy

<span css="font-weight: 400;">While developing <a href="https://github.com/statsbotco/cube.js">an open source analytics framework Cube.js</a>, we've worked with many data warehouses. When our customers ask us what the best data warehouse is for their growing company, we consider the answer based on their specific needs. Usually, they need nearly real-time data for a low price without the need to maintain data warehouse infrastructure. In this case, we advise them to use modern data warehouses such as </span><b>Redshift, BigQuery, or Snowflake</b><span css="font-weight: 400;">.</span>

<span css="font-weight: 400;">Most of the modern data warehouse solutions are designed to work with raw data. It allows to re-transform data on the fly without a need to re-ingest your data stored in a warehouse.</span>

<span css="font-weight: 400;">In this post, we would like to dig deeper into the factors to consider while choosing a data warehouse. Here they are:</span>

<ul>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Volume of data</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Dedicated human resources for the support and maintenance</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Scalability: horizontally vs. vertically</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Pricing models</span></li>
</ul>

<h1><b>Data Volume</b></h1>

<span css="font-weight: 400;">You need to know the estimates of the volume of data that you will be dealing with. If it’s a clear cut scenario that you are working with datasets ranging in the hundreds of TBs or in petabytes, then it is strongly advised to go for non-relational databases. The architecture of such databases that supports working with huge data sets is ingrained in their DNA.</span>

<span css="font-weight: 400;">On the other hand, many relational databases have really great time-proven query optimizers. You can consider them as an option for an analytics warehouse as soon as your dataset fits into one single node.</span>

<span css="font-weight: 400;">Let’s look at some mathematics relevant to dataset size:</span>

<aside><h2><a href="https://statsbot.co/blog/postgres-to-bigquery-etl/">Loading Terabytes of Data from Postgres into BigQuery</a></h2></aside>

<ul>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">The Postgres, MySQL, MSSQL, and many other RDBMS sweet spot is up to 1TB of data involved in analytics. If this size is exceeded, you may experience degraded performance. </span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Amazon Redshift, Google BigQuery, Snowflake, and Hadoop-based solutions support a dataset size up to multiple petabytes in an optimal manner. </span></li>
</ul>

<h1><b>On-premises vs Cloud</b></h1>

<span css="font-weight: 400;">Another important aspect to evaluate is whether you have any dedicated resources for the maintenance, support, and fixes for your database, if any. This facet plays an important role in the comparison.</span>

> <i><span css="font-weight: 400;">If you have dedicated resources for the support and maintenance, you have earned yourself a lot more options in choosing the database. </span></i>

<span css="font-weight: 400;">You can opt for creating your own Big Data warehousing options based on something like Hadoop or Greenplum. These systems </span><b>do require significant setup, maintenance engineering resources, and</b> <b>skilled personnel.</b>

<span css="font-weight: 400;">But in the case that you don’t have any dedicated resources for maintenance then you limit your options a bit. We advise going for a modern data warehouse solution like Redshift, BigQuery, or Snowflake. As an administrator or user, you don’t need to worry about deploying, hosting, resizing VMs, handling replication, or encryption. You can start using it by issuing SQL commands.</span>

<h1><b>Scalability</b></h1>

<span css="font-weight: 400;">When you start working with a database, you expect it to be scalable enough to support your further growth. Broadly, database scalability can be achieved in two ways, </span><b>horizontally or vertically</b><span css="font-weight: 400;">. </span>

> <i><span css="font-weight: 400;">Horizontal scalability refers to the addition of more machines, whereas vertical scalability means the addition of resources into a single node to increase its capability.</span></i>

<b>Redshift</b><span css="font-weight: 400;"> provides easy scalable options. With a few mouse clicks, you can increase the number of nodes and configure them to meet your needs. Redshift scales very well until you hit about 100TB of data that is processed at once in a query. Computing capacity of your Redshift cluster will always rely on a count of nodes in your cluster, unlike some other data warehouse options.</span>

<span css="font-weight: 400;">It’s a point where such solutions as </span><b>BigQuery</b><span css="font-weight: 400;"> come into play. There’s virtually no cluster capacity as BigQuery can allocate up to </span><a href="https://cloud.google.com/bigquery/quotas#queries"><span css="font-weight: 400;">2000 slots</span></a><span css="font-weight: 400;">, which is the equivalence of nodes in Redshift. Also, due to this multi-tenancy strategy, even when customers’ concurrency demands grow, BigQuery scales seamlessly with those demands and can go over this limitation of 2000 slots if required.</span>

<span css="font-weight: 400;">BigQuery relies on Colossus, which is Google’s latest generation distributed file system. Colossus allows BigQuery users to scale to dozens of petabytes in storage seamlessly, without paying the penalty of attaching much more expensive computing resources.</span>

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/etl-vs-elt/">ETL vs ELT: Considering the Advancement of Data Warehouses</a></h2></aside>

<b>Snowflake</b><span css="font-weight: 400;"> is built on Amazon S3 cloud storage and its storage layer holds all the diverse data, tables, and query results. Because this storage layer is engineered to scale completely independent of computing resources, it ensures that maximum scalability can be achieved effortlessly for big data warehousing and analytics. </span>

<span css="font-weight: 400;">In addition to this, Snowflake offers multiple virtual warehouses, at nearly any scale and concurrency, which can simultaneously operate on the same data while fully enforcing global system-wide transactional integrity and keeping it scalable. </span>

<h1><b>Pricing</b></h1>

<span css="font-weight: 400;">If you go with the self-hosted option like Hadoop, your pricing will consist mostly of VM or hardware bills. AWS provides an EMR solution which is a </span><a href="https://aws.amazon.com/emr/pricing/"><span css="font-weight: 400;">cost efficient</span></a><span css="font-weight: 400;"> option to consider when going with Hadoop stuff. </span>

<span css="font-weight: 400;">Drilling down further into Redshift vs BigQuery vs Snowflake, all of them offer on-demand pricing, but each one comes with their own unique pricing model flavor. </span>

<b>Amazon Redshift</b><span css="font-weight: 400;"> offers three pricing models:</span>

<ul>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">On-Demand Pricing: no upfront commitments and cost, you simply pay an hourly rate depending upon the types and number of nodes in your cluster. Here, one important factor often ignored is that the rates do vary depending upon the region. The rates cover both computation and data storage.</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Spectrum Pricing: you merely pay for the bytes scanned while querying against Amazon S3.</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Reserved Instance Pricing: if you are sure that you will be running on Redshift for at least a few years, then you can save up to 75% over on-demand rates by opting for reserved instance pricing.</span></li>
</ul>

<b>Google BigQuery</b><span css="font-weight: 400;"> offers scalable, flexible pricing options and charges for data storage, streaming inserts, and for querying data, but loading and exporting data are free of charge. The pricing strategy of BigQuery is quite unique as it is </span><a href="https://cloud.google.com/bigquery/pricing"><span css="font-weight: 400;">based on a rate per GB for storage and a bytes scanned rate for queries</span></a><span css="font-weight: 400;">. Also, it offers cost control mechanisms that enable you to cap your daily costs to an amount that you choose. It offers a long-term pricing model as well. </span>

<b>Snowflake</b><span css="font-weight: 400;"> offers </span><a href="https://www.snowflake.net/product/pricing/"><span css="font-weight: 400;">on-demand pricing</span></a><span css="font-weight: 400;">, which is similar to BigQuery and Redshift Spectrum. Unlike BigQuery, computational usage is billed on a per-second basis rather than bytes scanned, with a minimum of 60 seconds. Snowflake decouples data storage from computation, and hence the billing is individual for both of them. </span>

<span css="font-weight: 400;">The storage rate starts at around $40/TB/month for the standard edition and remains the same for other editions. On the other hand, for compute the rate starts at $2.00 per hour for standard and goes up to $4.00 per hour for the enterprise edition.</span>

<h1><b>Conclusion</b></h1>

<span css="font-weight: 400;">General advice for choosing a data warehouse, which we usually provide to our customers, is the following:</span>

<ol>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Use index optimized RDBMS such as Postgres, MySQL, or MSSQL when data volumes are much less than 1TB of data in total and much less than 500M rows per analyzed table, and the whole database can fit into a single node.</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">Use modern data warehouses like Redshift, BigQuery, or Snowflake when your data volume is between 1TB and 100TB. Also consider Hadoop with Hive, Spark SQL, or Impala as a solution if you have access to this expertise and you can allocate dedicated human resources to support that.</span></li>
 	<li css="font-weight: 400;"><span css="font-weight: 400;">When your data volume is over 100TB use BigQuery, Snowflake, Redshift Spectrum, or the self-hosted Hadoop equivalent solution.</span></li>
</ol>

Essential factors to consider when building your own data warehouse

Choosing Between Modern Data Warehouses

Despite the fact that an ETL task is pretty challenging when it comes to loading Big Data, there’s still the scenario in which you can load terabytes of data from Postgres into BigQuery relatively easy and very efficiently. This is the case when you have a lot of immutable data distributed in tables by some timestamp. For example, a `transactions` table with a `created_at` timestamp column. BigQuery and Postgres have great tools in order to do this pretty fast and conveniently.

<h1>Preparing Postgres Tables</h1>

<p>BigQuery <a href="https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format" target="_blank" rel="noopener">Capacitor</a> storage format, as many other Big Data formats, is optimized for a one-time write of an entire table. To distribute data between tables, BigQuery heavily relies on the <a href="https://cloud.google.com/bigquery/docs/querying-wildcard-tables" target="_blank" rel="noopener">wild card tables</a> pattern. Typical usage is to create tables with names suffixed by some field value. Usually, date suffixes are used for this purpose. For example, the table `transactions20180301` can contain transactions created on March 1, 2018. This approach allows for both the creation of very efficient storage on the BigQuery side, and for ease of loading the data source on the storage side.</p>

In order to implement this loading approach we need to ensure we have the right indexes in the Postgres database before we start loading processes from Postgres into BigQuery. In order to create this, you can use psql to connect to your database. Let’s create an index for a `created_at` timestamp in the `transactions` table. Assuming we’re dealing with an ubuntu machine that hosts the db instance, it can be as simple as:

```
$ sudo su - postgres
$ psql yourdbname

yourdbname=>create index transactions_created_at on transactions (created_at);
```

Index creation can take a while if you have a lot of data. You can track process execution using `top` utility. You should repeat index creation for each table you’re going to load into BigQuery.

<h1>Loading Data into BigQuery</h1>

To load data into BigQuery we’re going to use BigQuery CLI, which is a very versatile tool. You can install it using <a href="https://cloud.google.com/sdk/docs/#deb" target="_blank" rel="noopener">these</a> instructions. As we’re on Linux, we’ll be using bash script in order to perform all the work. I assume BigQuery CLI is installed and authorized.

Let’s create `bigquery-upload.sh` and add the following function in order to upload a single day from a specific table:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};
```

This function has three arguments: table, columns for selection, and date to upload. As you can see, it uses the `\copy` operation to download a CSV from Postgres and then compresses it. BigQuery docs says loading of a compressed CSV is slower than uncompressed, but uploading uncompressed data almost always seems slower.

You can call this function as simply as adding a line at the end of the script:

```
upload_day 'transactions' '*' '2018-03-01'
```

This will create the `transactions20180301` table with one day of data. To automate the uploading process we can introduce another function:

```
function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}
```

This `upload_table` function has 4 arguments: table name, columns for select, start dates, and end dates.

You can make this script parametrized by adding `upload_table "$1" '*' "$2" "$3"` as the last line so that entire script will be:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};

function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}

upload_table "$1" '*' "$2" "$3"
```

Don’t forget to `$ chmod +x bigquery-upload.sh` your script. After it you can run it as

```
$ ./bigquery-upload.sh "transactions" "2017-01-01" "2018-03-01"
```

The great thing about this script of loading data from Postgres into BigQuery is you can use it to upload just a single day and it can be put in the `/etc/cron.daily` or `/etc/cron.hourly` directory so that you can get your data in nearly real time. The `--replace` option script can be run more than once for the same day to replace data as it is refreshed.

If all is done right, it should load your terabyte of data in a day or so. Please do not hesitate to <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=postgres_to_bigquery">contact us</a> and we would love to advice you.