Skip to main content

SQL Templating

Tutorial to Follow Along​

🌟 Jinja Templates​

When building dashboards or exploring data in Apache Superset, performance and dynamic interactivity are key. Superset supports Jinja templating, a powerful feature that allows you to write flexible, parameterized SQL queries. One particularly useful capability is using dynamic filters passed via the Superset UI — which can significantly improve performance and customization, especially in subqueries, complex joins, or conditional aggregations.

To enable sql templating add this to your superset_config.py filter

FEATURE_FLAGS = {
    #.....your existing feature flags,
    'ENABLE_TEMPLATE_PROCESSING': True,
}

💡 Where can you use this?​

Using filters dynamically in SQL isn't just about inserting values — it's about writing smarter queries. Here’s how dynamic filters help:

🧪 Example​

By pushing filters into inner queries or CTEs, you reduce the volume of data processed:

WITH filtered_data AS (
    SELECT 
        "STATE" AS ST,
        "CITY",
        "ARRIVAL_DELAY"
    FROM Flights 
    WHERE 1=1  
        --------------------- In clause -----------------------
        {% if filter_values('STATE') %}
            AND "STATE" IN {{ filter_values('STATE') | where_in }}
        {% endif %}
        --------------------- EQ (=) clause --------------------
        {% if filter_values('STATE') %}
            AND "STATE" = '{{ filter_values("STATE")[0] }}'
        {% endif %}
        -------------------- Date filter -----------------------
        {% if from_dttm is defined and  from_dttm is not none %}
            AND ds >= '{{ from_dttm }}'
        {% endif %}
        {% if to_dttm is defined and  to_dttm is not none %}
            AND ds <= '{{ to_dttm }}'
        {% endif %}
)
SELECT
    ST AS "STATES",
    "CITY",
    AVG("ARRIVAL_DELAY") AS avg_arrival_delay
FROM filtered_data
GROUP BY ST, "CITY"

This is much faster than applying filters on top of an unfiltered large dataset.

Another example can be dynamic filtering with condition from selected value as shown in the demo. This is used in where clause of chart to filter dynamically using some condition.

avg_arrival_delay >= {{ (filter_values('days')[0] if filter_values('days') else 0) | int }}

🔧 Macros – User Info​

Superset provides some built-in macros that you can use inside your SQL queries to get details about the current user who is viewing the chart or dashboard.

💡 Where can you use this?​

  1. To apply role-based filters
  2. To restrict data for specific users
  3. To track who is viewing the dashboard
  4. Or just to display logged-in user info on a chart
  5. Let me know if you want to see how to use this inside a WHERE clause or with filters!

🧪 Example​

SELECT 
    '{{ current_user_id() }}' AS CURRENT_USER,
    '{{ current_user_email() }}' AS USER_EMAIL,
    '{{ current_user_roles() | tojson }}' AS ROLES,
    '{{ current_user_roles()[0] }}' AS FIRST_ROLE
📌 What it does:​

current_user_id() → gives the ID of the logged-in user

current_user_email() → gives the user’s email ID

current_user_roles() → returns all roles assigned to the user (like Admin, Viewer)

current_user_roles()[0] → gives the first role from that list

url_param('custom_variable') → macro lets you define arbitrary URL parameters and reference them in your SQL code. Example

SELECT '{{ url_param("user") }}' as user