I’m working with Amazon QuickSight in Direct Query mode against Aurora and I have a question related to performance and the use of parameters in custom queries within the dataset.
Context:
I have a QuickSight dataset built from multiple tables (7–8), each defined using custom SQL, and then joined together within the dataset.
There is a main table (e.g., “orders”) with a large volume of data (millions of rows).
I’m using embedded dashboards with:
Tag-based Row-Level Security (RLS), passing dynamic values through embedding.
Additional parameters, also passed via the URL (e.g., #p.region=EU&p.customer=123). These are currently not used, but they are defined in the JSON.
Problem:
When the data volume is small (a few hundred records), performance is acceptable.
When a user has access to a larger volume (tens of thousands of records), the dashboard takes significantly longer to load (60–90 seconds).
However, the equivalent query executed directly in Aurora runs in just a few seconds.
Based on our understanding, QuickSight executes the query without applying the RLS filter at the database level, and applies the filtering afterward, which could explain the performance issue.
Main question:
We understand that RLS tags cannot be directly used inside custom SQL, but in our case we are also passing parameters (p.region, p.customer, etc.) via the embedding URL.
Is it possible to use those parameters directly within the dataset’s custom SQL (e.g., in a WHERE clause) so that filtering happens at the database level (Aurora) instead of within QuickSight?
Additional questions:
Do these parameters need to be explicitly defined within the dataset in order to be used in custom SQL?
Are there any limitations when using parameters in custom queries with Direct Query?
What would be the best practice to ensure filtering happens as early as possible in this type of architecture?
Any guidance or experience with similar scenarios would be greatly appreciated.
If I am understanding you correctly, it is possible to have parameters within custom SQL, but the filtering will still be done within Quick sight rather than Aurora. If performance is your main concern, have you thought of creating your dataset within SPICE rather than direct query? Direct Query is really only used if you need real time information, but the caveat is that the dataset loads every time you access a dashboard. SPICE is considered as an in-memory storage option and allows incremental refresh up to every 15 minutes.
Hi! Yes, it’s mandatory to use a direct query because real-time data is required.
Regarding filtering the data in QuickSight instead of Aurora, that’s not a problem—perfect. The main question was whether a parameter can be used within a custom query to filter in the WHERE clause.
We’ve run some tests using parameters inside the custom SQL of the dataset (in the WHERE clause), and they do work in the sense that the filtering is pushed down to the database level (Aurora), which improves performance.
However, we’ve encountered an important side effect in the dashboard:
Dashboard filters (for example, the customer list) are no longer working correctly.
In some cases, filter controls appear empty or fail to load values.
From what we’ve investigated, this seems to be related to the use of parameters in the custom queries, since QuickSight limits the available value domain based on the parameter applied at the query level.
My understanding is that by applying the filter in the SQL query, QuickSight no longer has full visibility of the dataset, which impacts controls (especially dropdown filters that need to list distinct values).
Have you experienced similar issues when combining:
Parameters in custom SQL (Direct Query)
Dashboard filter controls
Are there any recommended best practices to avoid this problem? For example:
Using separate datasets (one filtered and one unfiltered for controls)
Using parameters only at the analysis level instead of in SQL
Any pattern to maintain performance without breaking filters
This behavior is expected. When a parameter is applied within a custom SQL WHERE clause, QuickSight only sees the filtered result set. As a result, dropdown controls may show limited values, appear empty, or fail to load if their value domain depends on that same filtered dataset.
Common patterns to use are:
Decoupled Datasets: Use a parameterized Direct Query dataset for visual performance, but a separate, lightweight lookup dataset for filter controls.
Parameter Mapping: Use dashboard parameters to pass selected values into the main SQL.
Analysis-Level Filters: Using standard filters ensures controls work naturally, though it may reduce SQL pushdown performance.
Try this out and let me know if this solves your issue!
Just checking back in since this thread hasn’t received a response in a while. Was JacobR’s reply helpful to you and/or were you able to find a solution yourself in the meantime? Please help the community by marking this answer as “Solution” or following up in general within the next 3 business days!
Is it possible to use those parameters directly within the dataset’s custom SQL (e.g., in a