Quicksight Incremental Refresh for Modified records?

Question

Hi, is there a way to setup Quicksights incremental refresh to update “modified” records?

I have a large database of sales records and want “near real time” metrics. Right now it is scheduled to refresh each hour, but would like to get that down to 15 minutes. Thought I could setup incremental refresh for this, but my problem is a row may not be modified for a year and I want to capture that change when it does happen.

Any way to achieve this with Quicksight (Direct query is too slow for my calculations).

Answer

Hi,

with incremental refresh you can define a date column (last_updated for example) if you have and set up a look-back window (i.e. 30 min), that will re-read only the data changed in the last 30 minute.

So if you have a row that was older than 30m min but updated in the window it will be captured and added, the caveat is that when Quicksight tries to remove all the record in the incremental windows it will not find the older record, and you will end up with some duplicates.

Still, it is possible to avoid issue for the visuals by adding a calculated measure in Quicksight using the runningcount function partitioned by the unique key of the record and ordered by the last modified date descending (the one used in the incremental refresh) , then set a filter on that calculated columns that is equal to 1 and apply the filter to all visuals . The filter will keep only the most recent value making the dashboard correct.

The duplicates can be removed with a full refresh, and you can also set up an alert to know whenever a duplicate is added in the Spice dataset, so that a full refresh could be submitted.

Hope this helps

Quicksight Incremental Refresh for Modified records?

Relevant content