Caching in Count Metrics

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, configure caching settings within each view by:

• Updating the connection to null

• Referencing the database table in the dependencies section

The schema panel on the right-hand side provides details on available caching options, allowing for flexible control over query execution and data freshness.

Caching configuration options

• caching – defines caching settings for source dependencies attached to a connection.

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

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

Refer to the example below for implementation details. Proper caching configuration ensures efficient query execution while maintaining data accuracy and consistency.

No Caching Enabled

With caching enabled

YAML caching example

caching:
  duration: 360000
  schedule: 0 0 * * *    

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

DuckDB on the server

When your views contain local queries (i.e. with connection: null) , Count will decide how to execute these queries automatically, either in DuckDB on the server or in your browser (just like other cells in the canvas).

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

Step 1

First, the non-DuckDB queries for the view 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 on the server if:

• Any non-DuckDB queries returned more than 10,000 rows

• Your local DuckDB database is nearing its memory limit

Queries are executed in your browser if:

• All non-DuckDB queries returned fewer than 10,000 rows

• Your view includes Python dependencies

• You have not enabled caching for this view

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 local queries.

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.