Quick Sight Incremental Dataset Refresh duplicates rows instead of replacing old ones and appending new ones

Q&A
, ,
1

I’m trying to configure incremental refresh on my datasets. My objective is to replace any existing rows that are updated since last refresh and append new rows that got added for the 1st time. But as i observe, rows are just getting appended even if they were already present. Can someone help on this?

Now.. the full dataset refresh imports the entire table everytime, taking too much time to load. So, I thought incremental refresh won’t get the entire table on each refresh and gets only the updated records. 1st time i do a full refresh.

  1. If an already existing record gets updated in any column of its row, since last refresh, replace the old row with the new one.
  2. If it’s a new record which was not there since last refresh.. just append it.
    But for me, it’s just appending rows.

column : record_updated_at

window: 1 hour

2

Hello @vamseep
The issue you’re experiencing occurs because Amazon QuickSight’s incremental refresh doesn’t use a unique key (like ID) to identify and update existing rows.
When incremental refresh runs, it deletes and replaces data within the lookback window timeframe, but if rows were updated outside of the lookback window, they get appended as duplicates rather than replaced.

You can check the reference here:
[+] Duplicates rows are being created via Incremental loading on data set - #6 by Naveed

You can try changing the lookback window to a longer lookback window based on how far back your row updates can occur.

Or, you can set up calculated fields to Filter duplicates.

3

Hi @vamseep,

Just checking back in since this thread hasn’t received a response in a while. Was msnehali’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!

Thank you!

4

Hi @vamseep,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you!