I have a custom SQL dataset with SPICE storage which runs a complex and performance hungry query to retrieve statistics. The resulting rows have a date column. I would like to pass a date parameter into this query to optimize it’s performance when required, with a where clause.
On initial refresh, that parameter should be NULL, so that it retrieves all data.
And, on scheduled incremental refresh, I’m looking for a way to pass the window date, or the current date.
I just can’t find any documentation on how to pass a parameter like that with the refresh feature. Any help would be gladly appreciated.
Sorry, QuickSight currently does not support passing dynamic parameters directly in scheduled refreshes. For scheduled incremental refreshes, QuickSight does not allow dynamic parameters to be directly passed into the custom SQL for SPICE. However, you can try this work around by scheduling regular refreshes and using a custom SQL query where the parameter {{DateParameter}} is bound to the current date (or a window date you can define in your SQL logic). Custom query filters the data so that it only retrieves data after the specified interval. This ensures that during the incremental refresh, the dataset is only updated with new data that falls within the specified date window, improving performance.
Example:
SELECT *
FROM your_table
WHERE
your_table.date_column >= (CURRENT_DATE - INTERVAL '1' DAY)
Thank you for the response.
I still wonder how to write a single query that would also be able to retrieve all the data on initial refresh, let’s say dating back from 10 years independently of the current date.
I’ll see what I can do, however it would be very helpful if Quicksight could pass these values on refresh.
QuickSight incremental refresh retrieves only new data based on a specified date field. It optimizes performance by avoiding the reloading of the entire dataset, focusing solely on records that have been added or updated since the last refresh. This is especially useful for large datasets with continuous updates.
Please refer the below documentation this might be helpful for you.
The only question I have left: Is there any plan in the future to implement a feature that add parameters, like current date, to the scheduled refresh with custom SQL?
Otherwise, my refreshes are working, I’m simply refreshing everything.
Hi @Loupi,
While I am unaware of of the specified road map for new features, I can mark this as a feature request so that the support team can gain visibility.
You can always keep an eye on the ‘What’s New’ section to see new features being implemented!