Optimize Athena partition deletions for insert_overwrite strategy

[juhoautio-rovio]

resolves #1125

docs: "N/A"

Problem

The dbt-athena adapter currently deletes metadata and files of overlapping partitions by making individual API calls for each partition.

This naive approach results in extraneous API calls for incremental models that process more than a few partitions. For example, a model with 150 partitions requires 300+ API calls (150+ to Glue, 150+ to S3), causing partition deletion to dominate the total execution time. In real-world testing, clean_up_partitions consumed 13.5 minutes out of a 15-minute total runtime, making partition deletion the primary performance bottleneck.

Solution

Significantly improves performance of insert_overwrite incremental materializations by optimizing S3 and Glue API usage when deleting partitions.

Key Changes

1. Batch S3 deletions
   • New bulk_delete_from_s3() method batches up to 1000 objects per API call
2. Batch Glue partition deletions
   • Uses batch_delete_partition API (25 partitions per call)

Performance Impact

API call reduction - for example, if there are 150 partitions to handle:

• Before: 150 S3 calls + 150 Glue calls = 300 API calls
• After: ~1 S3 call + ~6 Glue calls = ~7 API calls (~98% reduction)

Real-world execution time (150-partition incremental model):

• Before: 13m 30s in clean_up_partitions
• After: 1m 33s in clean_up_partitions
• Result: 10x faster partition deletion

Testing

• Unit test with 1100 partitions verifying batch processing logic
• Integration test with 150 partitions verifying expression chunking

Manual validation

Tested in a dbt project by patching dbt-athena adapter in the project venv and running:

dbt run -s my_daily_incremental_model --vars '{"start_date": "2024-01-10", "end_date": "2025-05-11"}'

• Before patching: total 15m 0s, clean_up_partitions 13 m 30s
• After: total 3m 33s, clean_up_partitions 1m 33s

In other words, the clean_up_partitions part was ~10x faster.

Question about a slow integration test

The new test_partition_chunking.py takes approximately 2+ minutes to run. Is that too much?

Reason for the long duration is creating and processing 150 partitions with real AWS API calls. The Glue GetPartitions API has a 2048 character limit for the Expression parameter. This test creates enough partitions to trigger code path that splits to have more than a single chunk. There's no way to trigger that scenario without creating many partitions or modifying the runtime code.

Some options to consider:

1. Keep the test as is for maximum confidence, despite the long runtime.
2. Patch the constant GET_PARTITIONS_API_EXPRESSION_MAX_LENGTH (default 2048) to use a smaller value during the test, so that a minimal number of partitions are needed to trigger chunking.
3. Make the GET_PARTITIONS_API_EXPRESSION_MAX_LENGTH configurable and set a smaller value during the test. This would not be needed in actual usage though, so it would be adding noise to the set of available configurations.
4. Remove this test. Cover the chunking logic only in unit test instead of an integration test.

Future Improvements

Consider parallelizing S3 API calls for additional performance gains.

Checklist

• I have read the contributing guide and understand what's expected of me
• I have run this code in development and it appears to resolve the stated issue
• This PR includes tests, or tests are not required/relevant for this PR
• This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX
  • The clean_up_partitions signature change from str to Union[str, List[str]] was specified by @colin-rogers-dbt in [Feature] Optimize Athena insert overwrite deletions by using batch operations #1125 (comment)
  • New bulk_delete_from_s3() method is marked @available for potential future use; the annotation can be removed if not desired for the public API

[github-actions]

Thank you for your pull request! We could not find a changelog entry for this change in the dbt-athena package. For details on how to document a change, see the Contributing Guide.

[Copilot]

Pull request overview

This PR optimizes insert_overwrite incremental runs in the dbt-athena adapter by reducing the number of AWS API calls required to delete overlapping partitions (batching both Glue partition deletions and S3 object deletions), addressing performance bottlenecks described in issue #1125.

Changes:

• Update the incremental helper macro to call adapter.clean_up_partitions() once with a list of partition predicates (instead of one call per partition).
• Enhance AthenaAdapter.clean_up_partitions() to chunk Glue GetPartitions expressions and to batch S3 + Glue deletions.
• Add unit + functional tests to validate batching/chunking behavior, plus a new chunk_iterable() utility.

Reviewed changes

Copilot reviewed 6 out of 6 changed files in this pull request and generated 4 comments.

Show a summary per file

File	Description
dbt-athena/src/dbt/adapters/athena/impl.py	Implements chunked partition fetching, batch Glue deletions, and bulk S3 deletions.
dbt-athena/src/dbt/adapters/athena/utils.py	Adds chunk_iterable() to chunk generator/iterable inputs.
dbt-athena/src/dbt/include/athena/macros/materializations/models/incremental/helpers.sql	Switches partition cleanup from per-partition calls to a single batched call.
dbt-athena/tests/unit/test_adapter.py	Adds/updates unit tests for list-input compatibility, large-partition chunking, and S3 delete error handling.
dbt-athena/tests/functional/adapter/test_partition_chunking.py	Adds an integration test to exercise Glue expression-length chunking in a real run.

---

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

[juhoautio-rovio]

Addressed the copilot review comments (with relevant test cases added).

[Copilot]

Pull request overview

This PR optimizes insert_overwrite incremental partition cleanup for the dbt-athena adapter by batching S3 object deletions and Glue partition deletions to dramatically reduce API calls and runtime (resolves #1125).

Changes:

• Update clean_up_partitions to accept Union[str, List[str]] and chunk Glue GetPartitions expressions to respect the 2048-character limit.
• Add batched S3 deletion via bulk_delete_from_s3() (up to 1000 objects per call) and batched Glue partition deletion (25 per call).
• Add unit + functional tests covering batching, chunking, multi-bucket deletes, and error surfacing.

Reviewed changes

Copilot reviewed 6 out of 6 changed files in this pull request and generated 5 comments.

Show a summary per file

File	Description
dbt-athena/src/dbt/adapters/athena/impl.py	Implements chunked partition retrieval plus batched S3/Glue deletions; adds bulk S3 delete API.
dbt-athena/src/dbt/adapters/athena/utils.py	Adds chunk_iterable() to chunk generators/iterables for streaming processing.
dbt-athena/src/dbt/include/athena/macros/materializations/models/incremental/helpers.sql	Switches macro to call clean_up_partitions once with a list of partition predicates.
dbt-athena/tests/unit/test_adapter.py	Updates/extends unit coverage for list input, chunking at scale, S3/Glue error handling, and multi-bucket deletes.
dbt-athena/tests/functional/adapter/test_partition_chunking.py	Adds functional coverage to validate expression chunking with real AWS API constraints.
dbt-athena/.changes/unreleased/Under the Hood-20260223-225659.yaml	Adds changelog entry for the optimization.

---

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

[colin-k-rogers]

On testing options: I don't think we need a specific integration test for chunking, ideally this is validated as part of a test of insert_overwrite

One question on the order of operations here but this looks just about good to ship

[juhoautio-rovio]

On testing options: I don't think we need a specific integration test for chunking, ideally this is validated as part of a test of insert_overwrite

Did you mean modifying some of the existing integration tests (tests/functional/adapter/something) or just making sure that unit tests have the desired coverage?

If you would still like to cover this in an integration test (some existing one, right?), then would you like to optimize the speed by mocking (as proposed in PR desc)? I suppose an extra 2 minutes is too much for this :)

[juhoautio-rovio]

I checked for different options for testing. Maybe this could help with the decision:

What the new functional test covers that unit tests don't: Unit tests mock AWS responses and verify chunking logic in isolation. The functional test is the only place that verifies chunking integrates correctly
end-to-end — that after real Glue API calls with expression chunking, the resulting data has no duplicates or missing rows.

Existing insert_overwrite functional tests: test_unique_tmp_table_suffix.py is the closest match — it uses insert_overwrite with partitioned_by=['date_column'] and runs incrementally multiple times, but
only writes one partition per run (a single date value). test_partitions.py has 212 records across many partitions but never does an incremental insert_overwrite run. None of the existing tests exercise the
expression-length chunking path.

Folding chunking into the existing test: GET_PARTITIONS_API_EXPRESSION_MAX_LENGTH = 2048 is a class constant on AthenaAdapter. Monkeypatching it to e.g. 60 would force chunking with
just 3–4 partitions (each (date_column='2023-01-01') expression is ~28 chars). test_unique_tmp_table_suffix.py could be extended to write a few dates instead of one, monkeypatch the limit, and assert no
duplicates — covering the chunking path without 150 partitions. The test would become marginally slower: Athena query startup and result polling dominate the runtime, so a handful of extra partitions adds
negligible time compared to the current test_partition_chunking.py which takes 2+ minutes purely due to creating 150 partitions. The same optimization for test duration can be of course applied also in the test_partition_chunking.py itself, if you would like to keep these tests more isolated and case specific.

[colin-k-rogers]

Folding chunking into the existing test: GET_PARTITIONS_API_EXPRESSION_MAX_LENGTH = 2048 is a class constant on AthenaAdapter. Monkeypatching it to e.g. 60 would force chunking with
just 3–4 partitions (each (date_column='2023-01-01') expression is ~28 chars). test_unique_tmp_table_suffix.py could be extended to write a few dates instead of one, monkeypatch the limit, and assert no

@juhoautio-rovio if we can do this that would be great