The incremental refresh is working correctly .
Here is a post where I have explained it a bit more in detail ( Data synced in SPICE memory using greatest function though incremental refresh duplicates old data instead of overriding it for latest updates - #6 by Koushik_Muthanna ) .
Youtube vide on how it work : https://www.youtube.com/watch?v=jl_91ahT-P8
source table on 5/21/2024 midnight:
id last_update_date
111 5/20/2024 00:00:00
222 5/20/2024 00:00:00
333 5/20/2024 00:00:00
source table on 5/22/2024 midnight(111,222 got update):
id last_update_date
111 5/21/2024 00:00:00
222 5/21/2024 00:00:00
333 5/20/2024 00:00:00
Based on incremental refresh
|Id |last_update_date|Snapshot|
|111|5/20/2024|5/21/24 0:00:00|
|222|5/20/2024|5/21/24 0:00:00|
|333|5/20/2024|5/21/24 0:00:00|
|111|5/21/2024|5/22/24 0:00:00|
|222|5/21/2024|5/22/24 0:00:00|
|333|5/22/2024|5/23/24 0:00:00|
On 22nd when the incremental refresh was executed , with a lookback window of 1 day , it will get any records after 21st . 333 5/20/2024 00:00:00 is outside of this window .
Kind regards,
Koushik