Amazon(AWS) Athena is a handy tool for digging into data stored in Amazon S3. When your data is split into partitions (like folders), writing smart queries can save time and money. This blog explains how to filter date-based partitions effectively, showing what works best for speed and efficiency.

What’s Partition Pruning?

Partition pruning is like telling Athena to only look in the right “folders” instead of searching everything. It’s a big deal for fast queries, so let’s see how to make it happen with date-based data.

Imagine we have a table called dumpmy_table partitioned by a column partition_0, which holds dates as strings in YYYY-MM-DD format (e.g., 2024-12-05).

---

Different Ways to Write Queries

Let’s compare some common query styles and see what’s fast or slow.

1. Picking Specific Dates with IN

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE partition_0 IN ('2024-12-05', '2024-12-06', '2024-12-07', '2024-12-08');

Why It’s Good:

• Athena only checks the listed dates (partition pruning works!).
• Perfect for grabbing a few random dates.

Downside:

• Gets messy if you need tons of dates.
• Not the best for a range like a whole week.

---

2. Converting Strings to Dates with CAST

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE CAST(partition_0 AS DATE) BETWEEN DATE '2024-12-05' AND DATE '2024-12-08';

Why Avoid It:

• Athena has to look at every partition first, then filter. No pruning = slow and expensive.

When It’s Okay:

• Only if your dates aren’t in YYYY-MM-DD format and need fixing.

---

3. Using >= and <= for Strings

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE partition_0 >= '2024-12-05' AND partition_0 <= '2024-12-08';

Why It’s Good:

• Since YYYY-MM-DD sorts nicely as a string, this works fast with pruning.

Downside:

• If your dates were in a weird format (like MM-DD-YYYY), this could mess up.

---

4. Using BETWEEN for a Date Range

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE partition_0 BETWEEN '2024-12-05' AND '2024-12-08';

Best Choice:

• Clean, easy to read, and fast with pruning.
• Great for a range like a few days or a week.

---

Splitting Partitions by Year, Month, and Day

Sometimes data is organized into partitions like year/month/day, with columns like:

• partition_year (e.g., 2024)
• partition_month (e.g., 12)
• partition_day (e.g., 05)

5. Targeting Specific Partitions

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE partition_year = '2024' 
  AND partition_month = '12' 
  AND partition_day BETWEEN '05' AND '08';

Why It’s Good:

• Super fast—Athena goes straight to the right partitions.
• Keeps data scanning low.

Downside:

• A bit more typing with multiple conditions.

---

6. Spanning Months or Years

SELECT * 
FROM "dumpmy_database"."dumpmy_table" 
WHERE (partition_year = '2024' AND partition_month = '12' AND partition_day BETWEEN '05' AND '31')
   OR (partition_year = '2025' AND partition_month = '01' AND partition_day BETWEEN '01' AND '08');

Why It’s Good:

• Handles bigger ranges (like across months) while staying fast with pruning.

Downside:

• Looks trickier as the range grows.

---

Quick Tips: Which Query to Pick?

What You Need	Best Query
A few random dates	IN (...)
A range of days	BETWEEN ...
Year/month/day partitions	Filter each column
Fast queries with pruning	BETWEEN or >= and <=
Avoid slow scans	No CAST(... AS DATE)

---

Top Advice

For quick and cheap queries in Athena:

• Use BETWEEN for date ranges.
• Use IN for specific dates.
• Filter year, month, and day separately if that’s how your data is split.
• Skip CAST(partition_0 AS DATE)—it slows things down.

With these tricks, your Athena queries will run faster and cost less. Happy analyzing!