Query Comments and Per-user Caching

👤 This documentation is intended for Team Admins. Check with your Team Admin for additional access.

Overview

For each dashboard and chart load, and all SQL Lab queries, a user is executing a query to the backend connected datasource.

For some administrators, as an additional security precaution, it's important to track which user initiated any dashboard, chart, or SQL query within the database logs. Some teams may further utilize information on the user who initiated the query to in-flight filter the data the user has permission to access.

Query comments

Every query generated by Preset -- whether it's through SQL lab, a dashboard loading all charts, or a chart refresh -- has a comment prefixing the query with querying information. For example:

Preset offers a query_comments feature flag to add additional details in this comment prefix for the user email that initiated the query, and also the query source.

-- 6dcd92a04feb50f14bbcf07c661680ba
-- user: 
-- workspace_slug:
-- chart_id:
-- dashboard_id:
-- dataset_id:

The comment is dynamically built depending on the context and is added to the query so that it's available in your database's query logs. Below information is added when available:

• user: Provides the email address of the user that executed the query.
• workspace_slug: specifies the Workspace in which the query was initiated (specially useful in case a DB is connected using the same credentials on multiple Workspaces).
• chart_id: Specifies the Chart ID.
• dashboard_id: Specifies the Dashboard ID.
• dataset_id: Specifies the Dataset ID.

Use Case for Query Comments

Most teams considering query_comments are tracking who has been running which queries across Preset.

Some teams may use the user email in the query comment to manage data access. For instance, customers using an external security platform to manage all application's data access to databases can use the user's email in-flight of the query execution to determine what data a user should be filtered to view. This serves as an alternative to Row Level Security (RLS), which already provides data access management within Preset.

Another use-case is to troubleshoot complex queries that affect performance. The asset IDs should help you to identify the source for the query, to improve the dataset logic, chart configuration, etc.

Caching and Data Access

As part of Preset's performance settings, Preset caches all metadata generated for each chart, so that dashboard viewers don't need to wait for on-the-fly queries to complete. To do so, Preset stores the data generated on dashboard load when a user first views that dashboard and keeps the data available for 24 hours for any other users viewing the same dashboard with the same permissions.

If Row Level Security (RLS) is already set up, then additional filters are applied in the SQL query when a chart or dashboard is loaded. In order for subsequent users to view the data cached by the dashboard load from the first user, the underlying queries generated by Preset have to be identical for both users, including the Row Level Security rules applied.

Per-user Caching

Preset's default cache setting assumes that some users can have access to the same information on dashboard as the user who first viewed the dashboard. Some teams with more sensitive data policy or have external platforms that manage data access can consider per-user caching to protect data access policies set up outside of Preset.

Preset offers a per_user_caching feature flag that caches the data that is generated on a user-by-user basis. Each user would have their own set of cached metadata, so Preset will only show the cached version of a dashboard or a chart if the user had previously visited the same dashboard or chart within the last 24 hours (default settings).

One consideration for Per-user Caching is the impact on the database performance. Caching saves query execution on the connected database when Preset recognizes an identical query is being requested by the user viewing a chart or dashboard. Once Per-user Caching is enabled, one user's dashboard or chart access will not save the query executions for another user's dashboard or chart access.