# Quick Sight Incremental refresh with Athena **URL:** \ **Category:** Q&A\ **Tags:** Business-Intelligence-Engineer, quick-sight\ **Created:** [February 4, 2025, 10:16am UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907 "2025-02-04T10:16:14Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Mykhailo\_Sorochev](https://avatars.discourse-cdn.com/v4/letter/m/dc4da7/32.png) [@Mykhailo\_Sorochev](https://community.amazonquick.com/u/Mykhailo_Sorochev)\ **Post date:** [February 4, 2025, 10:16am UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907/1 "2025-02-04T10:16:14Z")
Hi Quick Sight Community, I’m working on setting up incremental refresh in Quick Sight using Athena, but I’m facing a performance issue where Athena scans all partitions instead of pruning them properly. My Setup: • My Athena table is partitioned by year, month, day. • Quick Sight incremental refresh is based on updated\_at. • Issue: When Quick Sight generates SQL, it only filters by updated\_at and does not apply partition filtering (year, month, day). • Result: Athena scans all partitions instead of just the relevant ones, making the queries slow and expensive. The question is: how can I connect updated\_at with partitioning to let Athena know to scan just limited partitions? ---
**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\ **Post date:** [February 4, 2025, 1:33pm UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907/2 "2025-02-04T13:33:17Z")
Hi @Mykhailo_Sorochev To optimize your Quick Sight incremental refresh with Athena and ensure partition pruning, follow these steps, 1. Modify your Quick Sight dataset’s custom SQL query to manually filter partitions based on updated\_at. 2. Enable [Partition Projection](https://docs.aws.amazon.com/athena/latest/ug/partition-projection-setting-up.html) (Athena Optimization). If your dataset is large, enabling partition projection in Athena speeds up performance by reducing metadata scans. 3. Run [EXPLAIN](https://docs.aws.amazon.com/athena/latest/ug/athena-explain-statement.html) SELECT in Athena to verify partitions are being used. ---
**Author:** ![Mykhailo\_Sorochev](https://avatars.discourse-cdn.com/v4/letter/m/dc4da7/32.png) [@Mykhailo\_Sorochev](https://community.amazonquick.com/u/Mykhailo_Sorochev)\ **Post date:** [February 4, 2025, 2:11pm UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907/3 "2025-02-04T14:11:21Z")
Thank you @Xclipse! So. if I update it and use the ‘manual’ incremental filtering, how it will work when I need a full refresh? As I understand, I can’t have different SQL for Incremental refresh and for a Full refresh, correct? Why do you need Incremental refresh option in UI in this case? Also, do you know if there is any option to update existing data in SPICE by row id number or something like that? ---
**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\ **Post date:** [February 4, 2025, 5:09pm UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907/4 "2025-02-04T17:09:24Z")
Hi @Mykhailo_Sorochev You’re absolutely right. Quick Sight does not allow different SQL queries for Incremental Refresh and Full Refresh in the UI. However, a possible approach is to create a view for the full load and later modify it to display only recent changes in the data based on your incremental refresh interval. The [Incremental Refresh](https://docs.aws.amazon.com/quicksight/latest/user/refreshing-imported-data.html) option in Quick Sight UI automatically generates SQL queries based on a timestamp column but may not efficiently handle partition pruning. If your data is not partitioned, Quick Sight default incremental refresh should work well. However, for partitioned datasets, using custom SQL is often necessary to ensure optimal performance and avoid scanning all partitions. No, Quick Sight does not support row-level updates in SPICE, similar to a database UPDATE statement. Instead, SPICE allows only Append (adding new rows) and Replace (performing a full dataset refresh). ---
**Author:** ![Mykhailo\_Sorochev](https://avatars.discourse-cdn.com/v4/letter/m/dc4da7/32.png) [@Mykhailo\_Sorochev](https://community.amazonquick.com/u/Mykhailo_Sorochev)\ **Post date:** [February 4, 2025, 5:21pm UTC](https://community.amazonquick.com/t/quicksight-incremental-refresh-with-athena/41907/5 "2025-02-04T17:21:00Z")
@Xclipce thank you! My test shows this: /\* Quick Sight d51933e8-e20b-4df2-b111-9578697e5721 \*/ SELECT “posted\_date”, “document\_type”, “document\_id”, “seller\_id”, “company\_id”, “marketplace\_id”, “inventory\_id”, “pnl\_class\_id”, “transaction\_type”, “customer\_order\_id”, “order\_item\_id”, “amount\_type”, “amount\_description”, “quantity”, “currency”, “amount”, “debit”, “credit”, “sku”, “fnsku”, “asin”, “fulfillment\_channel”, “sales\_channel”, “neon\_prt\_data\_type”, “neon\_prt\_company\_id”, “neon\_prt\_date\_month”, “date\_day”, “updated\_at” FROM (SELECT \*, CAST(concat(neon\_prt\_date\_month,‘-’,date\_day) as DATE) as updated\_at FROM “amzn\_transac\_journal”.“neon\_prt\_report\_type\_transaction\_journal” **where neon\_prt\_date\_month \>= ‘2025-01’** and neon\_prt\_data\_type =‘cogs’) AS “New custom SQL” **WHERE “updated\_at” \> from\_unixtime(cast(substr(cast(1738502510855 as varchar), 1, 10) AS bigint))** as you can see in my SQL I used partitioned column neon\_prt\_date\_month to cast as date. So, Quick Sight worked out correctly added the last Where statement and scanned just 2 days according partitions. I checked the amount of data scanned - it was resonably small comparing with the full SLQ. So, I am going to use it as a solution.