We have an incremental refresh set up. It goes back 1 day using the created at timestamp column (never modified)
There is a chance to have hard deletes on the underlying table within the 1 day period but the deleted rows remain in SPICE, even if it looks like the dataset refresh captures those changes:
When we look at the data in the analysis we can see that the number of rows is unchanged (it should be 235,136)
A full refresh fixes this problem but I don’t understand why today’s deleted rows remain in the dataset
Even though there are changes in the underlying data source , any data in SPICE which is not part of the defined lookback window will remain in SPICE . Check if the records which are in SPICE would fall out of the lookback window.
An example below (RDS PostgreSQL ) of incremental refresh when a record is deleted in database and is within the defined lookback window for SPICE :
create table if not exists device_data
1st Insert of data
insert into public.device_data
QuickSight Import into SPICE
ID = ‘3’ was updated in database. Incremental refresh configured for 2 days on date
Add a few more records, delete the record for id = 3 in the database and run an incremental refresh.
QuickSight after incremental refresh ( Note : 3 is not part of the SPICE dataset )
Thank you @Koushik_Muthanna . I know that’s the expected behavior but it’s not what I’m seeing. And I think it’s because the refresh is not getting any data at all. The only changes on the underlying table were deletes. No inserts or updates. So maybe there’s some optimization rule that doesn’t change the dataset if no new data is returned within the lookback window.
Will you be so kind to do that test? In the sample you gave you should skip inserting rows id 4 and 5. Only deleting 3 and refreshing. The loopback window should exclude rows is 1 and 2 in order to show ingested rows = 0
Since I had already deleted id 3 . I deleted records for id 4 and 5 and ran the incremental refresh.
Note : The ingestion is showing 2 records , since id 1 and 2 still are part of the lookback window.
Example in the documentation : Refreshing SPICE data - Amazon QuickSight
Yes, I’ve only seen this behavior when no rows are ingested (but there were deleted rows in that lookback window)