Table with frequent recent updates and infrequent old data updates

What’s the best strategy to incrementally update data in QuickSight when a partitioned table in Glue Data Catalog gets frequent report updates and infrequent updates to old reports (1+ year)?

If I setup look-back window to a few days, it will eventually going to miss those old report updates.
If look-back window is large, it will be deleting and updating large amounts of data that are unchanged

Also wanted to ask, how exactly QuickSight run these incremental updates? Is it something like
select * from original_table where updated_at > look-back-window-time or it is more sophisticated?

I can’t find good explanation what exactly it does expressed in SQL terminology.

Thank you
R

This YouTube video explains it. You can see the SQL query in the presenter’s spreadsheet.