Hi Community!
I recently created an Athena DB that points to an S3 bucket, which is partitioned by date in this format: 2026-08-13 00:00:00. In QuickSight, my dataset uses this partition_date as the incremental refresh date, using a window of two days. Each incremental refresh does ingest two days worth of data into the dataset. However, when checking my costs in AWS, i realized it was doing a full scan of the data each refresh. Checking the query history, I found no WHERE clause was being added to the query, resulting in this full scan. I tried using this community post’s solution by changing my QuickSight dataset query to:
SELECT
*,
CAST(partition_date AS TIMESTAMP(3)) AS date_incremental
FROM “dataDB”.“tableX”
And configuring the incremental refresh to use date_incremental. Still no WHERE Clause added, this is what came up on Athena’s history for a scheduled incremental refresh:
UNLOAD (/* QuickSight 6c63ca06-3653-487b-bf32-e063d3532d7d */
SELECT column1,…, CAST(boolean_col1 AS INTEGER) AS boolean_col1, CAST(boolean_col2 AS INTEGER) AS boolean_col2, CAST(partition_date AS TIMESTAMP(3)) AS partition_date, CAST(date_incremental AS TIMESTAMP(3)) AS date_incremental
FROM (SELECT
*,
CAST(partition_date AS TIMESTAMP(3)) AS date_incremental
FROM “dataDB”.“tableX”) AS “New custom SQL”) TO ‘s3://bucket/’ WITH (format = ‘PARQUET’,compression = ‘ZSTD’)
Why is the WHERE Clause failing? Could it have anything to do with my boolean columns having to be cast to integer? Why is my partition_date being cast to timestamp when it is already a timestamp in my table schema? Why does it recast date_incremental?
I’d appreciate any help or suggestions!
Hi @pedro_marro and welcome to the community!
As per my understanding, quick sight’s incremental refresh is designed to append a WHERE clause to filter at the source, and in the thread you linked it did exactly that. The user there used their partition column inside the custom SQL to create the date field (e.g. CAST(partition_column AS DATE) AS updated_at), set that as the incremental refresh column, and quick sight appended a WHERE “updated_at” > from_unixtime(…) that Athena was able to push down to the partition. So the question is why it’s not doing that in your case.
A couple things worth checking. First, is partition_date actually defined as a partition column in your Glue/Athena table? You can verify with SHOW CREATE TABLE “dataDB”.“tableX”. If it’s just a regular column that holds dates but isn’t in the PARTITIONED BY clause, I don’t believe Athena will prune regardless of any WHERE clause. Second, I’m curiuos if the subquery wrapping is the issue. From your athena history I can see quick sight nests your custom SQL as FROM (…) AS “New custom SQL”, and would need to add the WHERE outside that wrapper. Athena might not be able to push that predicate back through the subquery to reach the partition. It might be worth trying partition_date directly as your incremental column without the CAST, since it’s already a timestamp. Fewer layers between the partition key and the filter gives Athena a better shot at pruning.
Hope this helps!
Hi @WLS-Cesar thanks for your tips.
Yes, my SHOW CREATE TABLE query has this part in the definition:
PARTITIONED BY (
partition_date timestamp)
So it should be used for pruning.
I tried a couple of strategies before using date_incremental. First, my partition_date was just a DATE column (2026-08-13); I imported the whole table without using a custom query in the dataset, but when checking the incremental refresh queries it ran something like this:
UNLOAD(SELECT …, CAST(“partition_date” AS TIMESTAMP(3)) AS “partition_date”
FROM “AwsDataCatalog”.“dataDB”.“tableX”) TO …
So I thought maybe it was strict with the date format it wanted, so I redid all my partitions on my historical data to have the extra 00:00:00 and created my table again with partition_date being a timestamp instead of a regular date column. The result was basically the same, it was still forcing a CAST() on partition_date and not adding a WHERE Clause. That’s when I found the other post I linked and it’s the last thing I tried.
After this I haven’t tried anything else because I bugged out my SPICE capacity by creating and deleting the dataset too many times and now it says I’m over my capacity 
, so also waiting on support for that to be resolved.
Should I do a custom query casting all the columns to a format QuickSight actually likes (booleans to integer and dates to timestamp(3)), so it doesn’t have to do any casting itself in the incremental query?
Hi @pedro_marro,
Thanks for the detailed follow-up and for confirming that partition_date is in your PARTITIONED BY clause. That rules out the most common cause. To answer your question, the pre-casting approach is worth trying. If quick sight is forcing its own CAST on every column (booleans to integer, dates to timestamp(3)), it’s possible that’s interfering with how it generates the WHERE clause. Doing all the casting yourself in the custom SQL so quick sight has nothing left to transform is a reasonable next step given everything else you’ve tried. I can’t say for sure it will fix it, but the logic tracks.
I know you’re currently blocked on the SPICE capacity issue. Once that’s resolved and you’re able to test, let us know what you find. If the pre-cast approach still doesn’t produce the WHERE clause, I’d suggest including everything you’ve shared here (the Athena query output, the PARTITIONED BY confirmation, and the different approaches you’ve tried) in a support ticket(Case management - AWS Support) since at that point it would need someone on the backend to explain why the WHERE isn’t being generated.