Analytical workloads exhibit substantial semantic repetition, yet most production caches key entries by SQL surface form (text or AST), fragmenting reuse across BI tools, notebooks, and NL interfaces. We introduce a safety-first middleware cache for dashboard-style OLAP over star schemas that canonicalizes both SQL and NL into a unified key space-the OLAP Intent Signature-capturing measures, grouping levels, filters, and time windows. Reuse requires exact intent matches under strict schema validation and confidence-gated NL acceptance; two correctness-preserving derivations (roll-up, filter-down) extend coverage without approximate matching. Across TPC-DS, SSB, and NYC TLC (1,395 queries), we achieve 82% hit rate versus 28% (text) and 56% (AST) with zero false hits; derivations double hit rate on hierarchical queries.
https://binds.ch (L. Bindschaedler)
CEUR Workshop
ISSN1613-0073
on hierarchical workloads. Although NL canonicalization accuracy degrades under ambiguity (44%
adversarial, 51% BIRD [
This paper makes the following contributions: • An OLAP Intent Signature that canonicalizes heterogeneous SQL and NL into a unified cache key space, enabling cross-client reuse without requiring a single semantic API. • Safety-first reuse with schema validation and confidence gating, with quantified NL failure modes (44% adversarial accuracy) showing why safety mechanisms are essential. • Safe derivations (roll-up, filter-down) that extend reuse without approximate matching.
Most production query caches key entries by SQL surface form (normalized text or AST-derived
representations) [
This section describes a middleware cache that answers OLAP-style aggregation queries. It maps each request (SQL or NL) to a structured OLAP Intent Signature, the cache key. The design is correctness-first: reuse requires exact intent equality with strict validation. Extensions beyond exact matches are limited to correctness-preserving transformations under stated assumptions.
We focus on a high-value class of dashboard queries, specifically aggregations over a star or snowflake
schema [
Scope Coverage. In TPC-DS, only 14% of queries qualify (the rest use window functions, CTEs, or set operations); SSB and NYC TLC are 100% covered. Queries outside the scope of supported operations bypass the cache and execute directly on the backend.
To make equivalence checkable, we assume that join semantics are dictated by the schema: given the fact table and referenced dimension attributes, there is a unique join path.
Terminology. We adopt standard OLAP terminology [
Figure 1 illustrates the system architecture. The middleware operates as an intermediary between clients, such as BI tools, notebooks, and NL interfaces, and the backend OLAP engine.
For each request, the following steps are taken: (1) canonicalize the request into an intent signature, (2) validate the signature against the schema and safety rules, (3) look up the signature hash in the cache, (4) on a miss, execute on the backend and store the result under the signature. Because canonicalization NL
Canonicalizer
Validator
Hash bypass miss
Cache Backend
hit populate
Result execute and validation are distinct steps, reuse decisions are auditable: the signature is an explicit contract that determines whether a cached result may be returned.
We define a canonical signature as a JSON object containing all semantics that can afect the numerical output of the query. The components of the signature include: • Measures: aggregation function and base expression (for example SUM(f_sales)), including
DISTINCT flags. • Grouping levels: list of hierarchy levels in GROUP BY, sorted alphabetically to a canonical order (for example geo.region, time.month). SQL GROUP BY order is semantically irrelevant; we canonicalize to ensure equivalent queries produce identical signatures. • Filters: a set of normalized predicates over non-temporal dimensions and facts, including canonical literal formats. • Time window: explicit start and end boundaries on the time dimension, normalized to a canonical representation. We separate time windows from general filters for two reasons: (i) temporal predicates require special canonicalization (resolving “last quarter” to concrete dates, handling timezone normalization), and (ii) time boundaries are critical for cache invalidation—when new data arrives, entries with open-ended time windows (“last 30 days”) must be refreshed while closed windows (“Q1 2024”) remain valid. • Post-aggregation operators: HAVING, ORDER BY, LIMIT. • Metric identity (optional): a metric identifier from a governed layer, if available. • Scope (optional): for multi-tenant deployments, a tenant or user identifier ensures cache isolation across security boundaries.
The optional fields are relevant for governed or multi-tenant deployments; our evaluation uses ungoverned single-tenant schemas.
We serialize the signature into a canonical JSON string (with sorted keys and normalized lists) and compute a hash (SHA-256) to obtain a fixed-length cache key. This ensures diferent surface forms map to the same signature (Figure 2). Join paths are implicit, determined by the schema’s foreign keys, so signatures omit them; queries with ambiguous joins (role-playing dimensions, self-joins) bypass caching.
NL: “Show total revenue by region for electronics in Q1 2024” SQL: SELECT r.region_name,
SUM(s.amount) AS revenue FROM sales s
JOIN regions r ON s.region_id = r.id
JOIN products p ON s.product_id = p.id WHERE p.category = 'electronics'
AND s.sale_date >= '2024-01-01'
AND s.sale_date < '2024-04-01' GROUP BY r.region_name { } OLAP Intent Signature: "measures": [{"agg": "SUM",
"expr": "sales.amount"}], "levels": ["regions.region_name"], "filters": [{"col": "products.category",
"op": "=", "val": "electronics"}], "time_window": { "start": "2024-01-01", "end": "2024-04-01"}
SQL requests are parsed into an AST and normalized deterministically (identifier resolution, predicate ordering, literal canonicalization). NL requests are mapped to signatures using an LLM constrained to produce strict JSON; the LLM also returns a confidence score (0–1) used for safety gating. Lowconfidence signatures bypass caching to avoid incorrect reuse.
Before reuse, we validate that all referenced measures/dimensions exist, time windows resolve to concrete boundaries, and join paths are unambiguous. Validation failures bypass the cache and execute on the backend, prioritizing misses over incorrect reuse.
Exact intent matching is the default reuse mode. We also support two safe derivations: Roll-Up from Finer-Grained Cache. If a cached entry has identical measures and filters but finer grouping levels, we can re-aggregate the cached result. Roll-up is permitted only for composable aggregations (SUM, COUNT, MIN, MAX); it is rejected for AVG, COUNT DISTINCT, or ratios. Filter-Down from Cached Supersets. If a cached entry uses a superset filter and contains the attributes needed to apply a tighter filter, we filter the cached result. Derivations are disabled when ORDER BY or LIMIT is present.
Both derivations have explicit preconditions that prevent incorrect reuse. Note that drill-down (finer ← coarser) is not supported: query-level caching lacks the detailed data needed to derive finer-grained results from coarser aggregates.
NL canonicalization can produce schema-valid but semantically incorrect signatures. We control NL reuse via layered policies: (1) schema validation for structural correctness, (2) confidence-gated reuse bypassing low-confidence signatures, and (3) heuristic checks rejecting common ambiguity patterns (unresolved relative time, underspecified spatial terms). These layers prioritize misses over false hits.
We implement the middleware as a Python prototype with three components. The canonicalizer parses SQL via sqlglot and transforms it into intent signatures through deterministic AST normalization; NL requests use an LLM endpoint emitting schema-constrained JSON with confidence scores. The validator checks that referenced measures/dimensions exist and join paths are unambiguous; failures bypass the cache. The cache store uses Parquet files indexed by signature hash with a SQLite metadata index for derivation candidate lookup. NL string-to-signature mappings are memoized to avoid repeat LLM calls.
We evaluate on three OLAP workloads: TPC-DS [
Table 1 reports hit rates and reduction factors. LLMSigCache achieves the highest hit rate on all three workloads (82% average), outperforming TextCache (28.2%) and ASTCache (55.6%).
Within LLMSigCache, NL reuse arises from both NL-to-NL hits (paraphrases of the same intent) and cross-surface hits (NL matching SQL-populated entries or vice versa). Cross-surface sharing accounts for 3–34% of NL cache hits; most NL benefit comes from unifying paraphrases under intent keys.
All methods produce zero false hits on controlled workloads. NL accuracy drops to 44% on
adversarial queries and 51% on BIRD [
NL Semantic Accuracy under Ambiguity. NL canonicalization accuracy degrades under realistic
ambiguity. On 63 adversarial queries (Table 2), accuracy is 44.4%; on 150 human-authored BIRD [
SQL cache lookup adds 9–16 ms median overhead (Table 4a). NL canonicalization costs ∼1.3 s on first occurrence but is memoized for repeats. Dashboard-like query orderings (Sequential, Zipf) maintain high hit rates even at 10–25% cache capacity (Table 4b).
We construct an SSB hierarchical workload where queries drill through time, geography, and product hierarchies. TPC-DS and NYC TLC lack systematic hierarchy traversal, so derivations provide limited benefit on those workloads. Without derivations, hit rate is 37%; enabling roll-up and filter-down raises it to 80% with zero false hits.
Cache Invalidation. Our evaluation uses static snapshots, but production systems must link entries
to data freshness [
Deployment Configurations. The precision-coverage trade-of is tunable: conservative (threshold 0.7, all heuristics) yields 71.4% precision at 22.2% coverage; balanced (0.5, time+spatial) reaches 72.0% at 39.7%; disabling safety drops to 48.3% precision with 30 wrong results.
Limitations. Key limitations include: (1) queries with window functions, set operations, or CTEs
bypass the cache; in TPC-DS, only 14% of queries qualify, though dashboard-oriented workloads
(SSB, NYC TLC) are fully covered; (2) NL canonicalization accuracy drops on ambiguous inputs (44%
adversarial, 51% BIRD [
Classic semantic caching [
We introduced a safety-first semantic cache for dashboard-style OLAP that canonicalizes SQL and NL into a unified key space, the OLAP Intent Signature. Evaluation shows large hit-rate and backend-savings gains over text and AST caching, while safety mechanisms prevent incorrect reuse despite NL errors. Key directions for future work include robust cache invalidation under data updates, stronger schema grounding for NL canonicalization, and extending input modalities to MDX.
The LLMs evaluated in this work (GPT-4o-mini, Claude-3.5-haiku) are components of the proposed system and were not used to prepare the manuscript. The author used Claude (Anthropic) and Grammarly for writing style, grammar, spelling, and formatting, and OpenAI Deep Research for citation management. The author reviewed and edited all outputs and takes full responsibility for the content.