I am currently working with two SPICE datasets in QuickSight. The first dataset acts as the “base” measure for my report, which is quite large and contains approximately 20 million rows. The second dataset, on the other hand, contains delta corrections that are uploaded by users to the report, and it’s quite small with only a few thousand rows.
My initial approach was to combine these two datasets into one and perform an incremental update. However, I’ve noticed that QuickSight’s incremental update doesn’t delete rows from SPICE that no longer exist in the source table. Therefore, the only other option I see is to execute a full refresh. But this is not ideal for me as it’s quite time-consuming, taking several minutes to complete.
Ideally, I would like to union the two datasets in QuickSight and only carry out a full refresh on the smaller delta dataset. Is there a way to achieve this? Any suggestions or insights would be greatly appreciated.
Let me try to address the delete rows not reflecting in incremental refresh issue. Incremental refresh is fully dependent on lookback window, so any changes made beyond the lookback window does not get synced to the dataset. For your use case if you have a definition similar to previous XX days in time modifications in data is expected. These XX days value should be the lookback window size.
Once this is fixed, you may not need to do a full refresh on the base table.
Reference to incremental refresh configuration : Here
Additionally , You can setup 2 refresh schedule for the “base” measure dataset, 1/ could be hourly incremental refresh and 2/ weekly full refresh during offline hours to sync SPICE dataset to its baseline source.
Second table which is more of Slowly changing dimension - it can be set to full refresh.
Here is additional demo i would suggest to look at to understand how incremental and full refresh together can help on base measure table.
if this helps resolve your query please mark it as “Solution”
Thanks - This was also my conclusion - I have setup a frequent incremental refresh schedule for catching the deltas. And a daily doing a full refresh to make sure that data is complete and in synched - just in case deltas are missed in the incremental update.