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..

[FrancescoCastaldi]

The challenge you describe is fundamental to SQL-based analytical engines (Trino, Presto, Hive) where query compilation and data scanning incur significant latency on every modified WHERE clause.

While Superset core does not currently feature an internal in-memory columnar query engine (like Tableau Data Engine / Hyper or DuckDB WASM in the browser), you can achieve extract-like performance and avoid query storms through several existing architectural capabilities:

1. Celery Cache Warmup with Pre-warmed Combinations

Superset provides a built-in background cache warming task located in superset/tasks/cache.py. You can schedule Celery beat workers to execute warm_up_cache on specific dashboards and filter tags during off-peak hours:

# In superset_config.py
from celery.schedules import crontab

CELERYBEAT_SCHEDULE = {
    "cache-warmup-hourly": {
        "task": "cache-warmup",
        "schedule": crontab(minute=0),  # hourly
        "kwargs": {
            "strategy_name": "DashboardTagsStrategy",
            "tags": ["warmup", "executive_kpis"],
        },
    },
}

This primes the Redis cache (DATA_CACHE_CONFIG) before users open the dashboard.

2. Intermediate Semantic Extract Acceleration (Trino / Iceberg / ClickHouse)

Rather than caching raw data in Redis key-value blobs and trying to filter JSON client-side, the scalable architecture for Presto/Trino consists of creating materialized extract tables or Iceberg views:

1. Schedule a nightly CTAS or Airflow job creating aggregated extracts.
2. Point your primary dashboard charts to this pre-aggregated dataset.
3. Configure CACHE_DEFAULT_TIMEOUT = 86400 on the dataset settings in Superset, allowing filter combinations within that pre-aggregated dataset to return in milliseconds.

3. Client-Side Cross-Filtering and In-Memory Filtering

For high-interactivity dashboards with moderately sized datasets (under 50,000 rows):

• Enable the feature flag DASHBOARD_CROSS_FILTERS: True.
• Use the Table chart or ECharts bar/pie charts as cross-filter emitters.
• Once the initial query resolves, cross-filtering applies in-memory in the browser without re-querying Trino or Redis for every selection change.

4. Jinja Macro Optimization with Cache Key Normalization

If specific filter values do not alter the broader dataset aggregation, you can use Jinja templating with cache_key_wrapper to avoid cache key explosion:

SELECT
  dimension,
  SUM(metric)
FROM my_extract_table
WHERE 1=1
{% if filter_values('region') %}
  AND region IN {{ filter_values('region') | where_in }}
{% endif %}
GROUP BY dimension

Combining these strategies provides sub-second interactive response times while shielding your data warehouse from redundant query loads.

[FrancescoCastaldi]

A quick syntax note regarding the Celery Beat configuration:
In kwargs, use "strategy_name": "dashboard_tags" (lowercase with underscore), as superset/tasks/cache.py matches the strategy by its name attribute rather than the Python class name. Passing "strategy_name": "dashboard_tags" ensures Celery dispatches the warmup tasks without triggering a strategy lookup error.