warm_up_cache API doesn't apply filters when the dataset uses the filter_values Jinja macro

[pandax08]

Hi everyone,

I'm trying to pre-warm the cache for a dashboard connected to AWS Athena using a Python script that calls the /api/v1/dataset/warm_up_cache endpoint. My datasets are virtual and use Jinja templates in the SQL to apply filters dynamically.

The issue I'm facing is that the filters I send via the extra_filters parameter in the API call are not being applied to the final query that runs on Athena. After several tests, I have confirmed that the filter_values('my_column') macro always returns an empty list ([]) when the query is triggered from the context of the warm_up_cache API. This causes the {% if filter_values(...) %} conditions to always evaluate to false, and therefore, the WHERE clauses are never added to the query.

Environment Details:

• Database: AWS Athena
• Datasets: Virtual, with SQL parameterized using Jinja.
• API Endpoint: /api/v1/dataset/warm_up_cache

Example SQL Query in the Dataset

The structure of my filters in the dataset's SQL is as follows:

SELECT
    -- my columns
FROM
    my_table
WHERE
    1 = 1
{% if filter_values('my_filter_column_1') %}
    AND column_1 IN {{ filter_values('my_filter_column_1', remove_filter=true) | where_in }}
{% endif %}
{% if filter_values('my_filter_column_2') %}
    AND column_2 IN {{ filter_values('my_filter_column_2', remove_filter=true) | where_in }}
{% endif %}

API Request Body Example

Here is an example of the JSON body I'm sending in the PUT request to the /api/v1/dataset/warm_up_cache endpoint. The extra_filters parameter is a JSON string as required.

{
    "dashboard_id": 15,
    "db_name": "AWS Athena",
    "table_name": "my_virtual_dataset",
    "extra_filters": "[{\\"col\\": \\"my_filter_column_1\\", \\"op\\": \\"in\\", \\"val\\": [\\"value_a\\"]}, {\\"col\\": \\"my_filter_column_2\\", \\"op\\": \\"in\\", \\"val\\": [\\"value_b\\"]}]"
}

Observed Behavior

When my script calls the API with the body above, the query that reaches Athena does not have the filters applied:

-- Query that actually runs on Athena
SELECT
    -- my columns
FROM
    my_table
WHERE
    1 = 1
-- The AND clause for the filters is missing

Expected Behavior

I would expect the query executed on Athena to include the filters sent from the API, like this:

-- Query that should run on Athena
SELECT
    -- my columns
FROM
    my_table
WHERE
    1 = 1
    AND column_1 IN ('value_a')
    AND column_2 IN ('value_b')

Diagnosis

To confirm the problem, I ran a test by replacing the dataset's query with SELECT '{{ filter_values("my_filter_column_1") }}'. The result executed on Athena was SELECT '[]', which proves that the macro is not receiving the values from the API's context.

Has anyone else experienced this behavior? Is there a recommended configuration or method for filter_values to work correctly with the warm_up_cache API?

Thank you very much in advance!

[dosubot[bot]]

Hi @pandax08! I'm Dosu and I’m helping the superset team.

This is a known limitation in the current implementation of the /api/v1/dataset/warm_up_cache endpoint. The extra_filters you send are only merged into the chart's form_data—and thus made available to the filter_values Jinja macro—if a dashboard_id is provided. If dashboard_id is missing or not handled as expected, extra_filters are ignored, which causes filter_values to always return an empty list in your virtual dataset SQL. This explains why your filters are not applied during cache warming, even though they work in other contexts.

This behavior is likely an unintentional bug or omission in the cache warming logic. The code path for warm_up_cache only attaches extra_filters to form_data when dashboard_id is present, and does not merge them otherwise, so the Jinja context never sees your filters. No recent pull requests address this issue yet, so it's not fixed in the latest versions [source].

As a workaround, always provide a valid dashboard_id in your API call, and ensure that the charts you want to warm up are associated with that dashboard. This should allow extra_filters to be merged and passed to filter_values as expected. If you need to warm up cache for charts not tied to a dashboard, you may need to manually trigger cache warming for each chart with the appropriate form_data, or consider patching the logic to always merge extra_filters regardless of dashboard context.

Let me know if you need more details on the workaround or want to discuss alternative strategies!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[pandax08]

@dosu,

I'd like to clarify that in my script, I am already providing a valid dashboard_id with every call to the /api/v1/dataset/warm_up_cache endpoint. The ID is obtained dynamically by first calling the /api/v1/dashboard/ endpoint to get the list of available dashboards and then using that ID in the subsequent requests.

Despite providing the dashboard_id as suggested, the filter_values macro still returns an empty list in the Jinja context. This seems to confirm that, at least in our environment, the issue persists even when following the recommended workaround.

[dosubot[bot]]

You're right—providing dashboard_id should, in theory, merge extra_filters into form_data so that filter_values works in your Jinja SQL, but there are edge cases in the code that can break this. For legacy (non-query_context) charts, extra_filters are merged into form_data and processed by filter_values, but for query_context-based charts, extra_filters are not merged the same way and may not reach the Jinja context, which could explain why your workaround isn't working for all chart types reference.

Other possible causes include: race conditions (if get_form_data is called before extra_filters are set), unexpected merges if the DB slice's form_data already contains extra_filters, or differences in structure between dashboard and API-supplied extra_filters reference.

As a practical workaround, you can use an if/else block in your SQL to provide a default value when filter_values returns empty, ensuring your query always returns results. If you need cache warmup to work for all chart types, you may need to patch the logic to always merge extra_filters into form_data, or restructure your filters to avoid relying on filter_values in this context.

Let me know if you want help identifying chart types or validating form_data structure for your setup.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}