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

[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

User Stories

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

Adnan Rahic

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

[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

[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

Artyom Keydunov

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

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.