Optimize Dashboard Filter Performance via Precomputed/Extract-based Caching

[skalva200]

Context

In the current Apache Superset implementation, any alteration to dashboard filter values (e.g., column dropdowns, date range pickers) modifies the WHERE clause of the underlying chart queries. This forces Superset to regenerate the SQL, submit a completely new query to the target data source, and allocate a fresh cache key in Redis.

While this ensures data accuracy, it creates massive performance and infrastructure bottlenecks for large enterprise deployments utilizing engines like Presto, Hive, or Trino.

The Problem

1. Cache Key Explosion & Redundancy: Because minor changes to a WHERE clause generate unique SQL strings, Redis is flooded with duplicate/redundant cache entries for data that is fundamentally unchanged.
2. High Database Concurrency & Load: In dashboards containing dozens of charts, every single filter interaction triggers a wave of heavy queries to the data source. This significantly degrades database performance and introduces latency for end-users.
3. Missed Optimization Opportunities: If the baseline chart dataset is already sitting in the cache, Superset cannot currently apply runtime dashboard filters directly to that cached dataset; it must always round-trip back to the database.

Proposed Solution

We propose implementing a Data Extract and Advanced Caching Mechanism in Superset—conceptually similar to Tableau Data Extracts—to drastically improve dashboard interactivity and reduce database footprint.

Key Capabilities:

• Precomputed Chart Extracts: Query the source database to cache a broader dataset for the charts, serving subsequent filtered variations directly from this high-performance cache layer.
• Asynchronous/Offline Cache Refresh: Utilize background/scheduled Celery jobs to refresh these data extracts periodically or via data-change triggers.
• Cache-First Interactivity: Ensure charts dynamically render and filter based on the user's dashboard selections using the precomputed/cached data structure.
• On-Demand Cache Bypass: Provide explicit dashboard controls allowing users to fetch live data when real-time accuracy is required.

Implementation Strategy & Technical Breakdown

1. Cache Layer Integration (Redis)

• Introduce a specialized caching tier storing precomputed chart datasets.
• The cache can be keyed cleanly by Chart ID + Primary Dimensions, or pre-warmed using the most common filter combinations (e.g., country=Korea).

2. Offline Cache Refresh Job

• Implement scheduled background workers to pre-warm and refresh the cache during off-peak hours or upon ETL pipeline completion.
• Ensure the pre-warmed cache scope covers the most statistically frequent filter combinations used by business teams.

3. Chart Rendering & Fallback Logic

• Upon dashboard load, the rendering engine checks for available cached extracts.
  • Cache Hit: Filter logic is applied to the cached slice, rendering the chart instantly.
  • Cache Miss: Fallback gracefully by returning an empty state or prompting the user to query live data (unless an auto-fetch on miss policy is defined)

4. User Interface & Controls

To ensure data transparency, we propose adding the following UI components to the dashboard level:

• "Use Cached Data" vs. "Force Live Query" Toggle: A clear interactive switch for users.
• Cache TTL / Force Execution Dropdown: A control widget allowing power users to explicitly adjust the query's time-to-live or bypass the cache entirely for a session.

Business & Performance Benefits

• Drastic Reduction in DB Load: Eliminates thousands of repetitive, redundant queries hitting analytical data warehouses.
• Sub-Second Dashboard Interactivity: Serving filter adjustments directly out of an in-memory/Redis extract layer will drastically cut down dashboard rendering times.
• Resource Scalability: Allows concurrent usage of heavy dashboards by hundreds of business users without risking database timeouts or high cloud data-warehousing costs..