I have a view say vw_A and my data source is redshift
When I run that view (select * from vw_A) on workbench/datagrip/local, it yields for ex. 100 rows.
When I run that same view on quicksight as spice data set, it yields 95 rows without errors/exceptions and rows skipped = 0
I even changed the view to a table say table_A
When I run that (select * from table_A) on workbench/datagrip/local, it yields for ex. 100 rows.
When I run that same query on quicksight as spice data set, it yields 95 rows.
I want the exact same number of rows to be pulled in quicksight.
Hi,
When you are running direct query against the Redshift view, results will include all the data rows available in that view however when you run the same query against the SPICE dataset based on that view, data available in SPICE is dependent on the when was that data uploaded in SPICE. SPICE supports the incremental data refresh. An incremental refresh queries only data defined by the dataset within a specified look-back window. It transfers all insertions, deletions, and modifications to the dataset, within that window’s timeframe, from its source to the dataset. You can choose your scheduling frequency such as Every 15 minutes/30 minutes/Hourly/Daily/Weekly/Monthly.
If your use case is to make sure QuickSight dashboard visuals reflects the same data as available in Redshift view then you want to create the dataset in direct query mode and not in SPICE. You can change the query mode - Direct Query or SPICE in dataset prep editor see the attached image
In case you wanted to still use SPICE dataset and schedule incremental refresh, refer this doc - Refreshing SPICE data - Amazon QuickSight
