
[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 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 and our contributions to the Cube community

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. As other members of the community were asking for it, we decided to [make a contribution](https://github.com/cube-js/cube.js/pull/3277), and our Helm Charts are available for everyone in the [example folder of Cube](https://github.com/cube-js/cube.js/tree/master/examples/helm-charts). This was the first major contribution from Gadsme to the Cube project, and contributing back if possible to open source community is a key part of our values.

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/multitenancy-setup), 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

Inspiring stories about companies building analytical solutions for end-users with Cube.

Company news and product release notes for prominent feature launches.

News & Product Updates

Community news, accomplishments, and programs.

Community Updates

Mastering Cube by digging into advanced concepts and dissecting core features.

Deep Dives

User Stories

Upcoming workshops and community calls with Cube creators and community members.

Events

Company news, product updates, and engineering deep dives.

All Things Cube

Query language for APIs with universal support in modern front-end frameworks.

GraphQL

Convenient APIs to access and consume analytics on the frontend.

APIs

Serverless data warehouse of Google Cloud Platform.

Petabyte-scale data warehouse of Amazon Web Services.

Amazon Redshift

Google BigQuery

Cloud-agnostic data platform supporting AWS, GCP, and Azure.

Snowflake

Popular cloud-based scalable data stores optimized for BI and analytics.

Cloud Data Warehouses (CDW)

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

Adnan Rahic

Platforms and tools for business intelligence bringing data insight to internal users.

BI & Data Exploration

Widespread relational and mostly row-based data stores.

Transactional Databases (OLTP)

Mostly column-based data stores optimized for business intelligence analysis.

Analytical Databases (OLAP)

Data stores optimized for processing timestamps with associated values.

Time Series Databases (TSDB)

Data stores built for stream processing and building real-time applications.

Streaming Databases (RTDB)

Key-value and document-based data stores.

NoSQL & Document Databases

Data processing tools used for copying data from one or more sources into a destination.

Extract, Load, Transform (ETL)

Architecturing data storage and solving common analytical tasks with SQL.

Data Modeling

Optimizing query latency and concurrency for better UX and lesser time-to-insight.

Performance

Data pipelines, data modeling, and BI tools to collect, store, and analyze data at scale.

Data Engineering

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!


Igor Lukanin

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

Source-available document-oriented data store.

MongoDB

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.js


[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.js, 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.js in 2019, they became early adopters of Cube.js soon after it was open sourced. You will find Cube.js in action today in this [RWA demo video](https://cotahealthcare.wistia.com/medias/1jl9z5dmz4).

![Sudhakar Velamoor's quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9f423675-6db9-435c-979a-b4987dd45fcc.png)

## How COTA is using Cube.js

Today, Cube.js is a foundational tool for COTA's products, with Cube.js [Data Schema](https://cube.dev/docs/getting-started-cubejs-schema) serving as the single source of truth for their data and Cube.js API powering their applications.

The COTA team uses Google Cloud Platform to host and run their applications, including the Cube.js 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.js deployment, the COTA team initially explored using Google Cloud Functions to run Cube.js in serverless mode, and they were among the first Cube.js users who were [interested in support](https://github.com/cube-js/cube.js/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.js while initially integrating Cube.js via its vanilla JavaScript library, which provided framework-agnostic methods to query Cube.js API. Today they use Angular 10 and leverage TypeScript support in Cube.js. 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.js 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/@cubejs-client-core#types-load-method-options), which synchronizes multiple concurrent requests performed by the Cube.js 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.js 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.js. 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.js 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/getting-started-cubejs-schema) 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.js, 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.js

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.js Developer Playground is used for creating the individual visualizations.

On the backend, the Cube.js API instance is hosted on a Google Kubernetes Engine (GKE) cluster. Cube.js 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.js and reducing the overhead on Cube.js 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.js rather than deal with third-party servers.

Other key benefits of Cube.js 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.js 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.js, Cyndx had a dedicated frontend engineer and analyst as well as a part-time backend engineer and designer. Cube.js 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.js Slack](https://slack.cube.dev/). 

The Cyndx team successfully went live with Cube.js in October 2020, and they are looking forward to several improvements in Cube.js 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.js 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.js 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.js 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.js backend and was an additional reason for selecting the tool.

## Interested in joining Cyndx'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 [Cube.js Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

How Cyndx enhanced its financial platform with Cube.js

Artyom Keydunov

Declarative component-based JavaScript library originally created by Facebook.

React

Web framework for React web applications by the Vercel team providing SSR & SSG.

Next.js

Approachable, performant, and versatile JavaScript framework.

Fully extensible web developer's platform.

Angular

Popular front-end frameworks for developing modern web applications.

Front-end Frameworks

This guide shows how to build a full-stack real-time dashboard with only open-source tools—from the database to the visualizations. You’ll learn how to set up a database, seed it with data, build an API endpoint on top of it, 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/big-data/2016/04/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 class="ui header"><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/big-data/2016/04/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.