Zero-downtime partition swaps on Athena: build in a new prefix, flip with SET LOCATION

In part 1
I made partition rebuilds idempotent with delete prefix → INSERT INTO →
add partition. In the comments, Karan Pulyala
pointed out two weak spots. This post fixes the first one, hands-on and
from scratch:

delete-then-INSERT leaves a window where Athena readers see an empty
partition, and if the INSERT fails, the partition stays empty until
the next run.

Thanks, Karan. Here is the full pattern, with a demo you can run in your
own account and tear down afterwards.

1. The problem: the empty window

With delete-then-insert, the partition goes through three states:

• Between the delete and the end of the insert, every query on that partition returns zero rows. A dashboard refreshing at that moment shows a hole.
• If the insert fails, nothing restores the old data. The partition stays empty until somebody reruns the job.

Both come from the same design choice: we mutate the data the readers
are using.

2. The fix: never touch the live data

A Hive-style table in the Glue Data Catalog does not store data, it
stores pointers: each partition has a LOCATION. Athena reads
whatever that location points to. So instead of rewriting the location,
write somewhere new and move the pointer:

1. Build the new data in a fresh prefix, for example day=2026-11-01/run=20261102T140501Z-1a2b3c/. Nobody reads it yet.
2. Swap the partition to the new prefix with one metadata change: ALTER TABLE ... PARTITION (...) SET LOCATION.
3. Clean up old runs later, keeping the previous one.

If step 1 fails, the catalog still points to the old run, so readers
never notice. The half-written prefix is deleted by the next run.

3. Hands-on: the demo from scratch

The demo creates a Glue database, a source table and a target table,
both partitioned by day, and rebuilds one target partition from the
source.

What you need: Python 3.10+, boto3, pandas and pyarrow, AWS
credentials, an existing S3 bucket, and an Athena workgroup with a
query result location. All names come from environment variables:

export DEMO_BUCKET=my-sandbox-bucket export DEMO_PREFIX=partition-swap-demo export DEMO_DATABASE=partition_swap_demo export ATHENA_WORKGROUP=primary python demo.py setup # database, tables, 100 source rows python demo.py swap # build run 1 and point the partition at it python demo.py count # -> 100 python demo.py change # add 20 rows to the source python demo.py swap # build run 2, swap, keep run 1 python demo.py count # -> 120 python demo.py rollback # point back to run 1 python demo.py count # -> 100 python demo.py teardown # drop everything the demo created 

The full code is in the repository that goes with this post. The
interesting part is three steps in one method.

Step 1: build with UNLOAD into a new prefix

INSERT INTO always writes under the table's own partition path, so we
cannot point it to a run= prefix. UNLOAD
can: it writes the result of any SELECT to the S3 location you choose,
in Parquet.

new_location = self._settings.uri( partition.table, partition.path, f"{_RUN_KEY}={new_run_id()}", ) # 1. Build. A failure here leaves the current partition untouched. self._athena.run( f"UNLOAD ({select_sql}) TO '{new_location}' " f"WITH (format = 'PARQUET', compression = 'SNAPPY')" ) 

Two details:

• UNLOAD refuses to write to a location that already has data. A unique run id per execution (UTC timestamp plus a short random suffix) guarantees the prefix is empty, and the timestamp keeps runs sortable.
• Leave the partition column out of the SELECT. Its value comes from the catalog, not from the files.

Step 2: swap the pointer

def _point_to(self, partition: PartitionRef, location: str, exists: bool) -> None: table = f"{self._settings.database}.{partition.table}" if exists: sql = ( f"ALTER TABLE {table} PARTITION ({partition.spec}) " f"SET LOCATION '{location}'" ) else: sql = ( f"ALTER TABLE {table} ADD IF NOT EXISTS " f"PARTITION ({partition.spec}) LOCATION '{location}'" ) self._athena.run(sql) 

The current location comes from the Glue GetPartition API. If the
partition does not exist yet, the first run adds it instead of moving it.

Step 3: clean up, but not too early

# 3. Clean up. Keep the previous run for rollback and in-flight queries. self._delete_runs_except(partition, keep={new_location, old_location}) 

Why keep the previous run instead of deleting it right away?

• A query that started before the swap may still be reading the old files. Deleting them immediately can break it.
• Rollback becomes free. If run 2 turns out to be wrong, pointing the partition back to run 1 is the same one-line SET LOCATION.

Every other run= prefix is deleted, including runs whose build failed
before the swap, so storage does not grow forever.

def rollback(self, partition: PartitionRef) -> str: """Point the partition back to the newest run older than the current one.""" current = self.current_location(partition) older = [run for run in self._run_locations(partition) if run < current] if not older: raise RuntimeError(f"No previous run to roll back to for {partition}") previous = older[-1] self._point_to(partition, previous, exists=True) return previous 

4. Combining it with part 1

The swap replaces the "delete prefix, INSERT INTO" steps from part 1.
The content signatures stay as they were: compute the expected
signature, skip the partition if it did not change, otherwise build and
swap, and write the signature after the swap succeeded.

5. When to use Apache Iceberg instead

If you are starting a new table, consider
Apache Iceberg tables in Athena.
Every Iceberg write produces a new snapshot with snapshot isolation, and
MERGE INTO updates and inserts in a single commit. You get this
behavior without managing prefixes yourself.

The pattern in this post is for the many existing Hive-style Parquet
tables you cannot migrate today. It needs nothing beyond S3, the Glue
Data Catalog and Athena.

6. Pitfalls

Wrap-up

• Don't mutate the data your readers are using.
• Build each run in its own prefix with UNLOAD.
• Swap with one SET LOCATION; a failed build never empties a partition.
• Keep the previous run: in-flight queries survive and rollback is free.
• For new tables, look at Iceberg first.

Next week, part 3: the second point from Karan's comment, making
content signatures stable across dtypes, nulls and number formatting.

Have you hit the empty-partition window in production? How did you
handle it?