Support per-query cache policies for file-based analytics queries

Open
  • Is something wrong?
#549 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 3 days

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
45/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Active
Tech stack
sql, typescript
Domain
backend-api-design, data

Research direction

Start by tracing the useAnalyticsQuery entry point and the analytics plugin configuration, then inspect how file-based queries are cached across the JSON and Arrow execution paths. Define how per-query disabled or TTL policies preserve existing defaults and execution isolation, and verify that disabled queries re-execute without nonce parameters.

Written by the indexing model from the issue text.

Description

Problem

AppKit's analytics plugin caches identical file-based SQL queries for one hour by default. Some queries are intentionally non-deterministic or calculate against current data and must execute every time the user submits them.

In AppKit 0.65.0, useAnalyticsQuery supports:

  • format
  • maxParametersSize
  • autoStart

There is no per-query cache option. Caching can be disabled globally with:

createApp({
  cache: {
    enabled: false,
  },
});

However, this disables caching for every AppKit plugin and analytics query in the application.

Example

-- config/queries/fresh_value.sql
SELECT current_timestamp() AS calculated_at

const { data } = useAnalyticsQuery('fresh_value');

After unmounting and remounting the component, the same cached timestamp is returned for up to an hour instead of executing the SQL again.

A more significant use case is a parameterized, non-deterministic table-valued function:

SELECT result_json
FROM IDENTIFIER(:functionName)(
  :input,
  :assumption
)

Submitting the same parameters again should deliberately recalculate against current source data.

Current workaround

We add a unique nonce to every submission:

-- @param queryNonce STRING
SELECT ...
WHERE length(:queryNonce) > 0

const [queryNonce] = useState(() => crypto.randomUUID());

const parameters = useMemo(
  () => ({
    input: sql.string(input),
    queryNonce: sql.string(queryNonce),
  }),
  [input, queryNonce]
);

This works because parameters are part of the cache key, but it pollutes the SQL contract and creates cache entries that can never be reused.

Requested capability

Please support a server-controlled cache policy for individual file-based queries.

For example, a query directive:

-- @cache disabled
SELECT ...

Or configurable TTL:

-- @cache ttl=60
SELECT ...

Alternatively, plugin configuration could define the policy:

analytics({
  queries: {
    fresh_value: {
      cache: false,
    },
    summary: {
      cache: { ttl: 60 },
    },
  },
});

A client-side option such as the following would also be useful, although a server-owned policy would prevent clients from bypassing caching for arbitrary expensive queries:

useAnalyticsQuery('fresh_value', parameters, {
  cache: 'no-store',
});

Expected behavior

  • Existing queries remain cached by default.
  • An individual query can disable caching or select a custom TTL.
  • The policy applies consistently to JSON and Arrow execution paths.
  • Service-principal and on-behalf-of-user execution retain their existing cache isolation.
  • Disabled queries execute against the warehouse on every request without requiring dummy parameters.
Dominant language
TypeScript
Stars
119
Forks
36
Avg merge
4d 16h
Merged PRs (30d)
37

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from databricks/appkit

All issues in databricks/appkit

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.