Use cases for a Data Store

Data Stores

Chartio’s Data Store feature is a widely used component of Chartio.

Creating a Data Store is similar to creating a chart. You use the same Data Pipeline to query and manipulate your data, but in the end the database is piped into another database table rather than a visualization. You specify a name for that table and what time a day you would like to repopulate it and Chartio will continually keep it up to date. You can then make other visualizations and variables off of this Data Store just like any other table.

These are the main use cases for a Data Store:

Your queries take a long time and you need a summary table.

It’s not uncommon for someone to be querying hundreds of billions of rows of data for several different charts on a dashboard, and multiple queries like that can be extremely taxing on your warehouse and can take a long time to complete. The best thing to do in cases like this is a create a pre-aggregated summary table. You can write your own scripts to create and manage these summary tables or you can use Chartio’s simple Data Stores.

You don’t want to repeat yourself.

You may find that many charts are relying on very similar or the same subquery; it’s also very common to have to frequently merge the same sets of data together as a basis for several different visualizations. Whatever the reason, if you are finding that you are having to repeat yourself a lot you should consider storing the results of that base query into a data source. From there, your charts can more easily and more responsively work off of that Data Store table and don’t have to include all the same building steps each time.

You want a simplified, cleaned data model.

Data Stores are commonly used to provide a simple, cleaned dataset for your team. Marketing for example, may have fairly complex schemas on many different sources in various stages of mess. They may find the raw version too complex or inconsistent to easily explore. You can simplify the process for them greatly by utilizing Data Stores with clean, aggregated, simplified tables for them.

Limitations of Data Stores

Currently, Data Stores only support up to one million rows of data. This is by design, as these are intended to be summary/aggregate tables which ideally do not get too big. They’re a helpful tool that cover about 80% of the use cases but definitely is not a full ETL solution; if you need larger aggregate tables you are entering into ETL land and need to perform those on your database or warehouse, here are some tips for doing so:

Summary tables and views

  • Data Stores are similar to summary tables that Chartio makes a bit easier. You can do them on your own database as well, if you’re working with a lot of data, or just want to have more control.
  • To do this, create VIEWS or persistent TABLES that simply store the results of your query. Each database deals with this in different ways and the documentation is widely available.
  • To do this, create VIEWS or persistent TABLES that simply store the results of your query. Each database deals with this in different ways and the documentation is widely available.

Extract, Transform, and Load (ETL) Tools

  • If you need help automating the creation of these tables, and especially if you data coming from multiple sources, you may want to utilize one of the many great ETL tools available such as Xplenty. They work similarly to the Chartio Data Pipeline but are designed for much larger sets of data and have more granular control over the timing and failover events of your operations.

Data Store Preview

If you have set your data store table limit to 1 million but are only seeing 1000 rows after running your data store, this is because the data store builder will always display the first 1000 rows of data as a preview.

When you query the data store with the 1 million row limit, you will be querying against all 1 million rows. The actual data store table will respect the limit you have set, but we do not display all 1 million rows for performance reasons.

Preview of Chartio chart creator