1.How does SPICE handle data retention when querying from Athena? If we need to maintain a rolling six-month dataset, do we have to manage data removal manually, or is there an automated way to handle this?
2.When a new month’s data is added, does SPICE overwrite the previous dataset, or does it append the data? How can we ensure that only relevant data is retained?
You would need to handle data retention/ new data with incremental refresh [ replied here : Setting data retention period on incremental refresh dataset? - #7 by Koushik_Muthanna ]
3.What is the impact of frequent data refreshes on performance and cost? Would a full refresh or incremental refresh be more efficient for our use case?
From a cost perspective, if we take Athena as example, then costs will be on Athena , performance from a dashboard view perspective is not going to be affected as you are viewing a snapshot based on SPICE. Once the refresh is complete, the latest snapshot on SPICE is visible. SPICE storage costs based on data ingestion.
4.How does SPICE compression work, and what factors influence the actual storage size compared to raw data in Athena?
5.How can we estimate the SPICE storage required before importing data, especially when working with consolidated billing data?
6.Since SPICE storage is billed based on allocated capacity, what strategies can we use to optimize usage and avoid unnecessary costs?
1/ Bring in required columns into SPICE [ If your business requirement needs 20 columns and your table has 100, don’t bring all the 100 ]
2/ Tips and tricks (Tips and tricks for high-performant dashboards in Amazon QuickSight | AWS Big Data Blog)
7.Is there a way to monitor and analyze SPICE usage trends over time to ensure we don’t exceed allocated capacity?
SPICE Usage Analytics (Dataset Sizes by User/Group/Namespace, Permissions, etc)
Kind regards,
Koushik