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
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
ID = ‘3’ was updated in database. Incremental refresh configured for 2 days on date
QuickSight Update
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 )
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.
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.
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
