Deleted rows not removed from dataset with incremental refresh

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:

image

When we look at the data in the analysis we can see that the number of rows is unchanged (it should be 235,136)

image

A full refresh fixes this problem but I don’t understand why today’s deleted rows remain in the dataset

Hi,

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
(
    id     integer,
    device varchar,
    date   timestamp
);

1st Insert of data

insert into public.device_data
values (1,'A','2022-09-13'),
(2,'B','2022-09-13')
(3,'C','2022-09-13')

QuickSight Import into SPICE

Screenshot 2022-09-13 at 16.35.51

ID = ‘3’ was updated in database. Incremental refresh configured for 2 days on date

QuickSight Update

Screenshot 2022-09-13 at 16.39.12

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 )
Screenshot 2022-09-13 at 16.59.41

1 Like

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.

Screenshot 2022-09-13 at 23.19.57

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

Regards,
Koushik

1 Like

Yes, I’ve only seen this behavior when no rows are ingested (but there were deleted rows in that lookback window)