Caching in Count Metrics

Speed up user experience and drive down compute costs with catalog-level caching.

Caching in Count Metrics is available at the workspace level, enabling query results to be stored and reused across multiple canvases. This reduces database query load, improves performance, and ensures faster data retrieval.

Enabling caching per-view

To enable caching for a view, simply provide the cache duration in the YAML file the view as in this example:

caching:
  duration: 3600 # seconds (1hr)
  schedule: 0 0 * * * # optional

source:
  connection: 7zoODN2N9UI
  query: SELECT * FROM `count-data`.demo_data.events

Caching configuration options

• duration – specifies how long source dependency results are cached (in seconds).

• schedule – a cron expression that defines the refresh schedule for cached data. This value may need to be enclosed in single quotation marks. (Optional)

DuckDB on the server

In order for caching to work, we run the query for the view on your database and cache this result on our servers. Any queries using this view will then be run outside your database by Count, either in DuckDB on the server or in your browser, and they will use the cached result (it may first need to regenerate the cache if it has expired).

Let's suppose someone opens a canvas with a visual that references a view with caching enabled. The following steps will occur:

Step 1

First, the view queries for your database are executed and the results are returned to your browser (if caching is enabled for the view, then cached results may be returned). Only the first 10,000 rows of results are returned.

Note - when running view queries on your database, they will have no row limit applied. If you would like to reduce the amount of data these queries return, add an explicit limit to the SQL definition in the view.

Step 2

Based on the results from step 1, Count decides how to execute the rest of the queries for the view.

Queries are executed with DuckDB on the server if:

• Any database queries returned more than 10,000 rows

• Your local DuckDB database is nearing its memory limit

Queries are executed in your browser if:

• All database queries returned fewer than 10,000 rows

• Your view includes Python dependencies

Step 3

Now that all of the view queries have been completed, the visual cell in the canvas will be executed. Count will apply the same logic to decide whether to execute the visual in your browser or on the server. Either way, it will not send a new query to your database if the view is using caching (until the cache expires).

Finally, suppose the visual is edited and a new query needs to be run. Rather than running steps 1 and 2 again, Count will re-use the results for any referenced views and only run the queries for the visual in step 3.