Any datasets managed in Whaly has several properties that manage its performance. You can materialize the datasets as table to make complex query run smoother (read about materialization) and you can also manage the way Whaly will update its cache when user query data on this particular dataset from the workbench. In order to better understand how cache is working on Whaly you can read more here.
We pride ourselves into providing a great experience to all data consumer in getting query result fast. Moreover, we don't want your datawarehouse bill to go through the roof when your adoption grows. That's why we give you means to control how your cache is working to make the best use of it.
In order to manage the cache strategy, please open any dataset on the workbench and click on Information, then you can find a Cache Strategy card with your card option. Please note that by default all dataset created from Whaly's sources will have the cache strategy set up to use the
_whaly_syncedcolumn that is automatically created. Otherwise datasets will be created with a
2 minutescache strategy.
Please note that each time you are changing the underlying SQL query or adding/editing new dimensions and metrics to exploration, this also reset the cache.
When this option is selected, every query done on this dataset from an exploration will be kept in cache during the period. If your underlying data change during this period, next queries will not update until the period is over.
Each time a query is played we will run the following query:
select max(COLUMN_NAME) from DATASET_SQL
Whenever the data changes, we will drop the cache and renew the query. This is useful when you are plugged directly to tables filled by your ETL as usually they give you insight on when was the last time a query was updated.
You can also run your own query to get access to your table metadata or more. This allow you to have more control on how to manage cache.
There are no straightforward way to remove the cache, but you can write a sql query that always return the current timestamp in order to break the cache for each query.