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)

I am observing similar behaviour where even if deleted records are within lookback window and no ingested records then , deleted records are not propogated to dataset even when dataset size in refresh table gives less numbers.
Those(deleted records within lookback window) are propogated when new records are ingested

1 Like

Hi,

1st run : Record ingestion ( 2 rows )
Below gif where i tested record deletion .
2nd run: Record deletion ( 1 row ) ( No new record ingestion )

The deleted record does not exist anymore after the incremental refresh.

QS_Incremental_Refresh

How incremental refresh works : 2022 QuickSight Learning Series: Keep your data fresh with incremental SPICE refresh - YouTube

If you are still facing an issue, I would suggest that you log a ticket so the technical team can debug the issue.
If you have access to the AWS console of the AWS account where you’re using QuickSight, please see these instructions on how to raise a support case.
If you do not have access, you’d need to ask whoever manages your AWS account for you to raise a support ticket.

Regards,
Koushik

1 Like

Hi @Koushik_Muthanna,

We faced the same issue recently.

A row was deleted in the underlying datasource and the data being queried was part of the loop back window. However, when the incremental refresh ran it did not remove the record from the QuickSight dataset.

We will raise this as an issue.

Regards,
Giri

1 Like