Cache

Cache allows you to keep a full copy of a data stream, so that tiles load fast and the results come back complete, even when the live source is slow or rate limited.

Caches are ideal for data streams you rely on often, that are slow to return data, or that return large or paged datasets. Data is synced into the cache with a frequency that you choose (from every 5 minutes up to 24 hours), and tiles can then query the cached data instead of hitting the live data stream each time.

How caching works

Only raw data is cached. Filters, grouping, sorting, and formatting are not included in the cache, but are applied by the tile so it continues to display exactly as configured. This means the cache can be used by multiple tiles in different ways.

Enable a cache

1. Configure a tile with the data you want to cache.
   
   Note

   You may like to Clone the tile before creating the cache so you can compare the cache tile with the original.

2. In the data preview pane, click Enable cache
   (or Switch to cache if one already exists). Do this, for example, when the data stream shows a rate-limited message.
   
   
   
   
   
   Note

   See Why is the Enable cache button unavailable?

3. Optional: rename the cache using the Cache name field, for example "Cache". The data is synced and stored in a table, and the table name is made up of the data source, data stream, and cache name.
   

   You can rename a cache, but this doesn't change the table name after the table has been created.

4. Set the Sync frequency (how often the cache should sync the data). Options range from every 5 minutes up to the default of 24 hours. Sync more often if you need more up-to-date data.
5. Click Enable.
   The cache immediately triggers its first sync. Once complete, the data appears automatically on the tile you created it from, and you can continue to edit the tile as needed.
6. Click Save to keep the tile using the cache.

If the data source reports it is rate limited, SquaredUp keeps retrying in the background until it has the full dataset. The next time you open the dashboard, the data should be ready instead of showing the rate limit error again. Other errors and timeouts are not retried until the next sync.

To monitor progress, see Check the sync status.

Use an existing cache

You can also select an existing cache directly when creating a new tile:

1. Add a data tile to a dashboard.
2. Choose the data source you used to create the cache (for example, Azure).
3. Toggle from Direct to Cache to see all the caches available for that data source.
   You'll see the available cached data streams and their status.
   
   
   If you'd like to create a new cache, rather than use an existing one, click + Add cache.
   Note

   In Direct mode, a data stream that already has one or more caches shows a badge, for example 1 cache or 2 caches. This helps you spot which data streams you can already use a cache for.

4. Select the cached data stream.
5. Click Done.
6. The Parameters tab will default to showing All data, while the Advanced query option allows you to use SQL for more complex queries. See Using Advanced query.
7. Continue to configure the tile as normal.

Parameters > Advanced query

The Advanced query option is great for more complex queries, such as filtering and aggregations.

1. When a tile is using a cache, you can use the Parameters tab to change from All data to Advanced query.
2. Write your SQL in the editor.
3. Click Apply, then Done.

You can reference the cache's table by its table name (shown in Settings > Data > Caches > Edit cache).

Common things to do here might be to return a simple COUNT() aggregation, or to use a {{}} to filter by timeframe.

This option queries the cache for this data stream only. To query across multiple caches, use the Advanced Query data stream on the Cache data source.

Only SELECT and WITH (CTE) statements are allowed. DDL (CREATE, DROP, ALTER), DML (INSERT, UPDATE, DELETE), and access to database metadata or credentials are all blocked by server-side validation.

A simple example

SELECT host_name, status, last_seen
FROM azure_active_hosts
WHERE status = 'critical'
ORDER BY last_seen DESC

This queries a cache called azure_active_hosts, filters to critical status, and sorts by most recently seen.

Access control and security

Admin users can see all caches and edit any cache in Settings > Data > Caches. Other users can manage a cache only from the tile editor, and only if they have full access to its data source.

A cache is treated as belonging to the data source from which it was created. In the tile editor:

• Users with full access to the data source (Full Control or when the data source does not restrict access) can use, add, edit and delete a cache in that data source within the tile editor (Data Stream > Cache > Edit cache ).
• Users with Link to Workspace or no access to the data source can only use the cache. They cannot add, edit or delete a cache, because their access to the data source does not allow it.
• This is separate from users' access to the workspace in which the data source is linked. For example, if they are only a Viewer of that workspace, they won't be able to edit a tile.

Each cache maps to a single table in the tenant's warehouse schema.

Cache data source

The Cache data source provides the following data streams:

• Advanced Query - A free-form SQL editor that isn't bound to any single table. You can write arbitrary SELECT queries across any cached tables, join between them, use CTEs, etc. This is the power-user interface.
• Cache Overview - Displays metadata about all cached tables - display name, table name, row counts, storage size, last sync time, etc. Useful for monitoring cache health.

Using the Advanced Query data stream

The Advanced Query data stream is great for more complex queries, such as filtering, joining multiple caches, aggregations, or the SQL patterns described below.

1. Add a data tile to a dashboard.
2. Select Browse all data sources.
3. Select the Cache data source and click Next.
4. Select the Advanced Query data stream and click Done.
5. Click the Parameters tab.
6. Write your SQL in the editor.
7. Click Apply, then Done.

You can reference any cached table by its table name (shown in Settings > Data > Caches > Edit cache).

Common things to do here might be to return a simple COUNT() aggregation, or to use a {{}} to filter by timeframe. The query runs against all tables in the tenant's schema, so you can JOIN between caches, use UNION, CTEs, subqueries.

Only SELECT and WITH (CTE) statements are allowed. DDL (CREATE, DROP, ALTER), DML (INSERT, UPDATE, DELETE), and access to database metadata or credentials are all blocked by server-side validation.

Join two caches

SELECT
    h.host_name,
    h.status,
    i.incident_id,
    i.severity,
    i.created_at
FROM azure_active_hosts h
JOIN service_now_open_incidents i ON i.host_name = h.host_name
WHERE h.status IN ('critical', 'warning')
ORDER BY i.severity, h.host_name

This joins a hosts cache with an incidents cache to show which unhealthy hosts have open incidents.

Cache Overview data stream

Use the Cache Overview data stream to create a data tile using the table visualization. This will show information about all the caches in the tenant, including name, row count and size. If the Total Rows, Effective Rows, or Table Size do not show a value, then that cache has either not finished its first sync or isn't working properly.

1. Add a data tile to a dashboard.
2. Select Browse all data sources.
3. Select the Cache data source and click Next.
4. Select the Cache Overview data stream.
5. Click Done.
6. Under Select a visualization, click Table.
7. Click Save.

You could also add a State column and use a value expression to determine the state which you then show using the Blocks visualization. For example, if row_count has a value, show success, otherwise show error:

{{ $['row_count'] > 0 ? 'success' : 'error' }}

Cache settings

Only admin users can see Settings > Data > Caches, where they can view all caches and edit any cache. Non-admin users with Full Control of the data source can manage its caches from the tile editor by clicking Edit cache on the Data Stream tab of the tile editor.

See Caches settings.

Troubleshooting

Check the sync status

To check the sync status of a cache:

• Admin users: go to Settings > Data > Caches and check the Sync Status column.
• Other users: open a tile that uses the same data source in the tile editor, click the Data Stream tab and click Cache. The status is shown under the cache name.

See Sync Status.

If the status is Warning or Failed, hover over it to see the reason, and check below:

Warning: Data could not be retrieved for N objects

The sync completed, but the data stream returned an error for some of the objects in scope. The cache holds data for the objects that succeeded, and the data for the failed objects isn't shown until a later sync retrieves it.

This is usually caused by requests timing out, or by errors from the source such as missing permissions or objects that no longer exist. Only rate limits are retried during a sync. Other errors wait until the next sync.

Watch out for data streams that query each object separately. A cache scoped to many objects makes many requests on every sync, which is slower and more likely to be rate limited. Scope the cache to the objects you need.

To resolve it:

1. An admin user can go to Settings > Data > Caches > Edit cache and click Sync. If the cache shows Ready, the problem was temporary.
2. If the warning repeats, check the data stream in Direct mode, scoped to one of the affected objects.
3. Reduce the number of objects in scope or sync less often, and check the data source credentials can access all the objects.

If the data stream times out for a single object in Direct mode, a cache won't fix it.

Cache sync failed

The first sync for this cache failed. Check the cache settings are correct, then run it again. See Check the sync status.

Data stream not found

If a tile using a cache says it cannot find the data stream, look at Settings > Data > Caches to check that the data stream or its data source hasn't been deleted or disabled.

Syncing cache

The tile shows Syncing cache while the first sync runs. This can take a few minutes for large data streams. Your data appears automatically when it's ready, so there's no need to refresh.

The first sync of a large or heavily rate-limited dataset can take a long time. Choose a sync frequency that leaves enough time for each sync to finish.

To check the progress, see Check the sync status.

Frequently asked questions

Why isn't all my data shown?

1. Check the sync status of the cache. See Check the sync status. If the source fails partway through a paged sync, the objects affected can end up with partial data for that sync.
2. Check the Timeframe:
   • In Settings > Data > Caches, click Edit next to the relevant cache. The Cache Name column shows each cache's name.
   • Click the Timeframe tab to check the timeframe set for the cache. Change it if required.
   • If you change the timeframe or any other cache setting, click Save and sync.
3. Check for missing rows. Some APIs omit rows or publish data late. Compare the Rows count in Settings > Data > Caches > Edit cache with the source before relying on a new cache.

What happens to the cached data if I delete a cache?

When you delete a cache, the table and all its data (including all partitions) are permanently deleted from the warehouse, and any tiles using the cache will stop working immediately.

See Access control and security.

Why is the Enable cache button unavailable?

It is not possible to use a cache with more than one data source, with multiple datasets, from the output of a SQL analytics query, or for SquaredUp data sources (SquaredUp and Rollup). The data from SquaredUp data sources already lives in SquaredUp's database and is retrieved efficiently, so caching is not available. You also can't enable a cache if you don't have permission to cache the data source, the tile isn't configured yet, or the tile is already using a cache.

Can I query across multiple cache tables in a single query?

Yes, use the Advanced Query data stream on the Cache data source. It gives you full SQL access to all tables in your tenant's schema, so you can JOIN, use UNION, CTEs, subqueries, etc.

Can I use dashboard variables with a cache?

A cache always stores the full data set from the data stream, regardless of the dashboard variable selection. For example, if a variable has 12 objects and only 1 is selected, all 12 are cached. The selection is applied when the tile queries the cache.

To link a cached data stream to a variable, edit the query using Parameters > Advanced query (see Using Advanced query). You will need to use an expression (mustache syntax), but the actual SQL will be specific to your data.

For dashboard variables, your expression should refer to variable1.

Does this single data value = this single-select variable value?

property = '{{ variable1.property }}'

This is a simple example, for a single-select variable, where you can use an equals comparison.

SELECT
  *
FROM
  "github_open_prs"
WHERE
  '{{ variable1[0].rawId}}' = repo_id

Does this single data value appear in this collection of variable values?

list_contains(['{{ variable1.map(v => v.property).join("','") }}'], property)

A multi-select variable where you want to compare a value in the dataset with a collection of values (1, many, all, etc.) coming from the variable.

SELECT
  *
FROM
  "github_open_prs"
WHERE
  list_contains(['{{ variable1.map(v => v.rawId).join("','") }}'], repo_id)

-- 3 objects: list_contains(['243313865','243060753','858611084'], '858611084') -> TRUE
-- 2 objects: list_contains(['243313865','243060753'], '858611084') -> FALSE
-- 1 objects: list_contains(['858611084'], '858611084') -> TRUE

Does this array data value overlap with this collection of variable values?

list_has_any(['{{ variable1.map(v => v.property).join("','") }}'],property::JSON::VARCHAR[])

When the property column in the data is a string, but it looks like an array, for example:
["property/1234"]

SELECT
    "label",
    SUM("value") AS "total_value"
FROM
  "google_analytics_ga4_service_account_all"
WHERE
    list_has_any(
      ['{{ variable1.map(v => v.sourceId).join("','") }}'],
      property::JSON::VARCHAR[]
    )
GROUP BY
    "label"
ORDER BY
    "total_value" DESC

Does this single data value appear in this selection of variable values?

property IN ('{{ variable1.map(v => v.displayName).join("','") }}')

A variable where you want to check whether a value in the dataset is one of the values selected in the variable. This works whether one or many objects are selected.

What happens if the source data stream returns an error during a sync?

If the whole sync fails, the cache keeps the data from the last successful sync and shows Failed. If only some objects fail, the sync completes with a Warning and the cache holds data for the objects that succeeded. See Warning: Data could not be retrieved for N objects. To check the status of a cache, see Check the sync status.

Is there a size limit on query results?

Yes, responses are capped at approximately 5 MB. If results exceed this, they are automatically truncated and a truncated flag is returned with the response. Truncation is calculated dynamically based on the table size. It will always return the maximum possible rows.

If your query returns too much data, the response will be truncated. Use LIMIT, WHERE clauses, or aggregations to keep result sets manageable. A query also times out after about 25 seconds.

What happens to cache data if the data source is removed?

This is a known issue. Currently the cached data is not automatically removed when the data source is deleted.

Does a cache respect the timeframe configured on the source data stream?

Yes, the timeframe specified in the cache is passed to the source data stream on each sync.

Reference

Table naming

The table name is generated from the cache name when the cache is created, so pick a cache name you'll be happy typing in SQL queries. The table name isn't shown until the cache exists. To see it, go to Settings > Data > Caches and click Edit next to the cache.

You can rename a cache, but this doesn't change the table name after the table has been created.

The cache name must be 2 to 24 characters and include at least one letter or number.

The table name is built from the data source, data stream and cache name, in lowercase, with other characters replaced by underscores. It is shortened to 63 characters if needed (the cache name is never shortened) and must be unique in your organization.

So a cache called "Cache" for "Pull Requests" from the "GitHub" data source will result in github_pull_requests_cache.

Was this article helpful?