Hello,
When trying to validate a dashboard, I noticed a discrepancy of counts in the dashboard vs what I am seeing in Redshift. Currently the dataset is set to SPICE and pulling in 179,578 rows.
When I run the comparable query in Redshift I get 175,778.
If I switch the dataset from SPICE to direct query, the dashboard pulls the correct number of 175,778.
I’ve tried doing a new refresh on the dataset once it is in SPICE, but I still end up with the extra 3800 records.
There are four datasets attached to this dashboard, at the suggestion of another thread, I put all of them into SPICE, and still no luck.
Ensure that the Redshift data source used in QuickSight matches the one you’re querying directly. For example, if you have separate instances for Dev and Prod, double-check that you’re comparing data from the correct environment.
Double-check the custom SQL queries or any filters applied to the datasets in SPICE versus Redshift. Minor differences in query syntax or missing filters could cause a variation in the data being fetched.
Go to your dataset in QuickSight and do a full refresh rather than relying on an incremental one.
If you are using incremental refresh for SPICE, some changes to the data may not have been captured correctly. You might want to force a full refresh rather than incremental, to ensure consistency.
If the problem persists, try creating a new dataset from scratch, importing the same data into SPICE, and see if the row count discrepancy still exists. This can sometimes solve issues related to caching or SPICE dataset corruption.
I’ve verified the redshift datasource and Quicksight datasource are pointed to the exact same enviornment/view
The dataset in QS is only ‘select * from view’. I even went so far as to disable every filter I had in QS just to make sure there wasn’t a filter unaccounted for
Tried a full refresh a number of times, to no avail
Created a new dataset from scratch, and still no luck
After some further digging, it looks like some rows have differing statuses that contribute to the count in question. one id has a value in column A, and in redshift when I query that record, it is null. Odd that a full refresh doesn’t seem to fix that.
Would turning the view into a materialized view be beneficial?
It sounds like you’ve already done extensive troubleshooting to ensure both the data source and datasets match, which helps narrow down potential causes. The fact that you’re seeing differing values between Redshift and SPICE particularly with some rows having different statuses or values indicates it could be a data synchronization or caching issue. Here are a few ideas, including the potential benefits of using a materialized view.
Materialized view can be beneficial in scenarios like this, as materialized views store the result set physically, thereby ensuring more consistency between SPICE and the source view in Redshift.
Materialized views can improve performance, particularly when using complex queries. They offer faster retrieval since the data is precomputed and stored, and this could also help make the SPICE refresh more fast and reliable.
Ensure that no data updates are happening during the time you’re refreshing the SPICE dataset. If records are being modified while SPICE is pulling data, you may encounter inconsistent snapshots.
Given the amount of troubleshooting you’ve already conducted, AWS Support may be able to offer insights or identify any underlying issues specific to your environment.
If the issue is still not resolved. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!
Since we have not heard back from you, I’ll go ahead and close this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.