A Base Directory Mismatch Between Amazon S3 Tables and Snowflake CLD

Original Japanese article: Amazon S3 Tables×Snowflake CLDでbase directory不整合が発生した話

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

I had a setup where an Iceberg table on S3 Tables was being referenced from Snowflake through a Catalog-Linked Database (CLD). While running it, I hit an interesting error, so I'm sharing the investigation process here.

While looking at Data Preview for a table in Snowsight, I noticed the following error:

The parquet file 'data/xxxx/xxxx/xxxx/xxxxxxxx/target_date_month=2026-07/00000-xx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx-0-00001.parquet'
for table 'SAMPLE_DB."gold"."fact_daily_sample_metric"' was inaccessible.

Since it was complaining about being unable to access a parquet file, I ran ALTER ICEBERG TABLE ... REFRESH just to check the state, and got a completely different error this time:

Statement 1 Failed
One of the specified Iceberg metadata files does not conform to the required directory hierarchy.
All files must reside as a strict subpath under the defined base directory.
Current base directory: s3://aaaaaaaa-1111-4aaa-9999-aaaaaaaaaaaa--table-s3/
Conflicting file path: s3://bbbbbbbb-2222-4bbb-8888-bbbbbbbbbbbb--table-s3/metadata/00004-....metadata.json

This looked nothing like the first error, but once I dug in, both turned out to come from the same root cause.

Spoiler: the cause wasn't some other table's files leaking in. It was that the same table had been DROPed and re-CREATEd in a short window, and the old warehouse location ended up conflicting with the new one. It looks at first glance like "some other table's files got mixed in," but the mismatch was entirely self-contained within this one table.

Tracking down the cause took three stages of investigation: verifying metadata via Athena, checking S3 Tables' spec against the official AWS docs, and pinning down the exact events in CloudTrail. This article is a record of that process.

Auto-Sync Behavior of the Catalog-Linked Database

This setup uses the Catalog-Linked Database (CLD) I covered in an earlier article.

Connecting Amazon S3 Tables with Snowflake

CLD links a namespace (database) to a Glue Catalog, in this case backed by S3 Tables. As I confirmed in that earlier article, when a table is created or changed on the AWS side, it's automatically reflected on the Snowflake side without writing any DDL. That's a real operational win (you don't have to run CREATE ICEBERG TABLE for every single table), but this same "auto-follow" mechanism is also the precondition for the trouble in this article.

Specifically, CLD doesn't just sync the list of tables and their schemas (columns) under a namespace. It also automatically syncs each table's current metadata pointer (table name → location of the latest metadata.json). The "base directory" that caused today's problem is part of that synced state on the Snowflake side.

For what it's worth, the feature itself is genuinely convenient, and I plan to keep using it.

Test Environment

• Catalog integration: Snowflake Catalog-Linked Database (CATALOG_SOURCE = ICEBERG_REST, connecting to S3 Tables via the Glue Iceberg REST endpoint)
• S3 Tables table bucket: sample-s3-table-gold (anonymized)
• Target table: fact_daily_sample_metric (anonymized)
• Write job: Glue (Spark), DDL operations via Athena

Looking at the Error Message

To recap, the error is essentially saying "you tried to reference a file outside the path registered as the base directory."

Current base directory: s3://aaaaaaaa-1111-4aaa-9999-aaaaaaaaaaaa--table-s3/
Conflicting file path: s3://bbbbbbbb-2222-4bbb-8888-bbbbbbbbbbbb--table-s3/metadata/00004-....metadata.json

Both S3 paths point to different UUIDs under a --table-s3 suffix. The first thing I needed to figure out was whether these were genuinely two different tables, or whether this was all happening within a single table.

Verifying the Facts via Athena

Checking the current data file paths with $files

I used Iceberg's hidden metadata table $files to check which data file paths the table was currently referencing.

SELECT file_path
FROM "gold"."fact_daily_sample_metric$files";

Every path that came back was under bbbbbbbb-.... Not a single file referenced aaaaaaaa-....

Well, that tracks.

Going back through history with $snapshots

Next, I checked the past snapshot history using $snapshots.

SELECT snapshot_id, committed_at, manifest_list, summary
FROM "gold"."fact_daily_sample_metric$snapshots"
ORDER BY committed_at ASC;

Even going all the way back to the oldest snapshot, manifest_list was consistently under bbbbbbbb-.... So this wasn't a case of "contamination partway through": as far back as I could check, it had consistently referenced bbbbbbbb-....

Again, that tracks too.

At this point, all I'd confirmed was that "aaaaaaaa-... doesn't currently exist as an actual entity in the Glue Catalog." I still had no idea why it was showing up in the error message at all.

Checking S3 Tables' Spec in the Official Docs

At this point I decided to step back and check the spec for S3 Tables itself. I referenced the following official docs:

• https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-tables.html
• https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucketnamingrules.html

What I confirmed here is that S3 Tables automatically generates a unique warehouse location dedicated to each table, every time a table is created.

When you create a table, Amazon S3 automatically generates a warehouse location for that table, a unique S3 location that stores the objects associated with it.

It's also explicitly stated that the --table-s3 suffix is a bucket naming convention reserved exclusively for S3 Tables.

In other words: if you DROP a table and then CREATE it again under the same name, a brand-new warehouse location with a new UUID gets assigned. This felt like a strong candidate for the identity of the two different UUIDs in the error message.

Confirming It With CloudTrail

To test the hypothesis, I searched CloudTrail for this specific table's CreateTable and DeleteTable events. Filtering on those two event names, I was able to reconstruct the table's complete history.

The table's complete history

Time	Event	Entity UUID
Day 1, 11:53:37	Initial CreateTable (S3 Tables)	aaaaaaaa-... (anonymized)
Day 1, 11:54:13	CreateTable completed (Glue side)	Same as above
Day 3, 07:23:47	DeleteTable (Glue + S3 Tables, via Athena)	aaaaaaaa-... deleted
Day 3, 07:24:08	CreateTable again (Glue + S3 Tables, same actor)	bbbbbbbb-... created

The gap between the DROP completing (07:23:47) and the re-CREATE completing (07:24:08) was exactly 21 seconds. And these two UUIDs are literally the "base directory" (aaaaaaaa-...) and "conflicting file path" (bbbbbbbb-...) from the error message. So this table was created once on Day 1, then DROPed and re-CREATEd on Day 3, and in that process, its physical entity (the warehouse location) was swapped out entirely.

What this points to is a working hypothesis: even though the table had been recreated, Snowflake's side kept referencing the old location.

Why Did CLD Keep Referencing the Old Location?

Everything from here on is speculation, built from what I could confirm plus what's documented officially by Snowflake.

According to Snowflake's official docs (Use a catalog-linked database for Apache Iceberg tables), CLD polls the remote catalog on a 30-second interval by default (SYNC_INTERVAL_SECONDS).

More importantly, CLD actually has two distinct kinds of sync going on:

• Table discovery (SYNC_INTERVAL_SECONDS, default 30s): detects tables added to or removed from the namespace
• Table metadata refresh (REFRESH_INTERVAL_SECONDS, default 30s): updates each table's snapshot pointer (the location of the latest metadata.json)

There's also a documented mechanism for detecting table-uuid mismatches during automatic refresh: if the UUID of the external table doesn't match the UUID CLD has on record, the refresh fails and Snowflake drops that table from the CLD (without touching the remote side). So there is, on paper, a mechanism in place to detect that a table's underlying entity has been swapped out.

In this case, I think a timing conflict like this may have occurred:

1. Day 3, 07:23:47: the old table (aaaaaaaa-...) gets DROPed
2. 21 seconds later, at 07:24:08: a new table with the same name (bbbbbbbb-...) is re-CREATEd
3. In between, CLD's table discovery hadn't yet detected that "the table disappeared" before a same-named new table showed up
4. As a result, this got processed not as "table deleted → re-created" but as "existing table updated," leaving the old base directory info in place

The mechanism for detecting a table-uuid mismatch and dropping the table from CLD is documented, and I actually confirmed this detection error firing directly, in the reproduction experiment described below. That said, I never observed the table actually getting dropped afterward (more on that below). My best explanation right now is that because the DROP-to-re-CREATE interval (21 seconds) was shorter than both sync intervals (30 seconds), this mechanism didn't get a chance to fire before the change was processed as an "update."

That said, CLD's internal implementation (exactly when discovery vs. refresh run, and which one wins a race) isn't fully documented, so this remains a hypothesis built from observed facts plus documentation, not something I can call confirmed.

Reproduction Experiment

To test the hypothesis, I tried reproducing the same behavior on a small test table.

-- 1. Create a test table
CREATE TABLE gold.test_dropcreate_repro (
    id INT,
    name STRING,
    created_at TIMESTAMP
);

-- 2. Insert one row just to confirm things are working
INSERT INTO gold.test_dropcreate_repro
VALUES (1, 'test_row', current_timestamp);

-- 3. DROP
DROP TABLE gold.test_dropcreate_repro;

-- 4. Re-CREATE
CREATE TABLE gold.test_dropcreate_repro (
    id INT,
    name STRING,
    created_at TIMESTAMP
);

-- 5. Insert a row again
INSERT INTO gold.test_dropcreate_repro
VALUES (1, 'test_row', current_timestamp);

I ran this three times total, and the same mismatch reproduced twice out of three.

Trial	DROP-to-re-CREATE interval	Result	Errors observed
1	(short)	Not reproduced	None (data displayed normally)
2	(short)	Reproduced	"0 Rows" in Data Preview / base directory error on REFRESH
3	(short)	Reproduced	Error Code 091391 (UUID mismatch) in Data Preview / base directory error on REFRESH

Trial 1: Not reproduced

The first time I tried this sequence, nothing happened. Data Preview showed the data normally in Snowsight, and ALTER ICEBERG TABLE ... REFRESH didn't throw anything either.

Trial 2: Reproduced (data disappears)

Running the same sequence again, with a different DROP-to-CREATE interval, Data Preview showed "0 Rows": the data had disappeared.

Running ALTER ICEBERG TABLE ... REFRESH at that point produced the following error:

One of the specified Iceberg metadata files does not conform to the required directory hierarchy.
All files must reside as a strict subpath under the defined base directory.
Current base directory: s3://cccccccc-3333-4ccc-7777-cccccccccccc--table-s3/.
Conflicting file path: s3://dddddddd-4444-4ddd-6666-dddddddddddd--table-s3/metadata/00001-....metadata.json.

This matched the pattern from the production incident exactly: base directory was still pointing at the old table's warehouse location (cccccccc-...), while trying to reference the new table's metadata (dddddddd-...), and failing.

Trial 3: Reproduced (UUID mismatch error + base directory error)

Running the same sequence one more time, opening Data Preview in Snowsight this time produced a slightly different error.

Error Code: 091391
Message: SQL compilation error: The UUID of Iceberg table 'test_dropcreate_repro2' does not match
the table UUID in metadata file 's3://eeeeeeee-5555-4eee-9999-eeeeeeeeeeee--table-s3/metadata/00001-....metadata.json'.

On top of that, running ALTER ICEBERG TABLE ... REFRESH against this same table produced the same "base directory mismatch" error as Trial 2:

One of the specified Iceberg metadata files does not conform to the required directory hierarchy.
All files must reside as a strict subpath under the defined base directory.
Current base directory: s3://ffffffff-6666-4fff-8888-ffffffffffff--table-s3/.
Conflicting file path: s3://gggggggg-7777-4ggg-5555-gggggggggggg--table-s3/metadata/00001-....metadata.json.

So in Trial 3, both the "UUID mismatch error (091391)" and the "base directory mismatch error" showed up on the same table. I think this is direct evidence of the table-uuid mismatch detection mechanism from the docs actually firing. The two errors look different on the surface, but they appear to share the same root cause: the old table's warehouse location still being held as the base directory.

The docs state that when this mismatch is detected, the refresh fails and Snowflake drops the table from CLD. In my reproduction, though, the table was never actually dropped from CLD after this error occurred. It just stayed there, displayed, with the error persisting. This matches exactly what happened in production too: the table itself continued to exist in CLD the whole time, with the error simply recurring. I wasn't able to determine, within what I observed, exactly when or under what conditions the documented auto-drop behavior actually kicks in.

This reproduction experiment (3 trials, 2 reproductions) ended up directly supporting the hypothesis laid out above.

• The condition described in the hypothesis, "CLD's discovery hadn't detected the DROP before the same-named table showed up," turned out, through the reproduction experiment, to not really be about the absolute length of the interval (whether it's 21 seconds or 30 seconds), but about the relative timing of whether a poll happens to land between the DROP and the CREATE.
• The one out of three trials that didn't reproduce is likely explained by a CLD poll happening to land between the DROP and the CREATE, correctly detecting the deletion. This looks like classic race-condition behavior: the same steps don't necessarily give you the same result every time.
• The table-uuid mismatch detection mechanism does genuinely exist, and depending on conditions, it can surface as this detection error (Error Code 091391).

So "leave a sufficiently long gap" as a mitigation is valid in the sense that a longer gap increases the odds of at least one poll landing in that window, but there's no absolute safe threshold, and it's more accurate to think of it as a way to reduce the probability of hitting this rather than eliminating it.

How I Recovered

Since this was a CLD setup, rather than manually recreating the individual table on the Snowflake side, I fixed it from the AWS side (S3 Tables/Glue Catalog). Specifically: DROP the table, wait significantly longer than CLD's sync interval (30 seconds), then re-CREATE it. I never explicitly ran ALTER ICEBERG TABLE or CREATE OR REPLACE ICEBERG TABLE from the Snowflake side.

Since CLD polls the namespace state periodically and reflects it automatically, as long as I left enough of a gap between the DROP and re-CREATE on the AWS side, Snowflake picked up the new table's entity on its own and settled back into a normal state. This doesn't prove that "a short interval was the cause," but it did serve as one supporting data point for the hypothesis (see the reproduction experiment above for more).

Things to Keep in Mind Going Forward

• DROPing and re-CREATEing a table from the catalog side carries a real risk of this kind of mismatch, caused by the combination of S3 Tables regenerating the physical location and CLD's sync timing.
• The reproduction experiment backed up the hypothesis: the real issue isn't the absolute length of the DROP-to-re-CREATE interval, but whether CLD's polling manages to detect the DROP before the re-CREATE completes.
• Because of that, "leave a sufficient gap" helps in the sense that a longer gap raises the odds of a poll landing in between, but it's not a guaranteed-safe threshold, and that's worth keeping in mind.
• Periodically checking $snapshots and $files to confirm consistency between a table's actual entity and what's registered in the catalog should make it faster to isolate this kind of issue if it comes up.

Conclusion

In this article, I walked through investigating a base directory mismatch I hit while referencing an S3 Tables table through Snowflake CLD, starting with metadata verification in Athena, moving to spec-checking against the official AWS docs, and finishing with pinning down the exact events in CloudTrail.

To summarize:

• The two different UUIDs in the error message weren't from two different tables. They were the "old location" and "new location" of the same table, from a single DROP and re-CREATE.
• I confirmed in the docs that S3 Tables generates a new, unique warehouse location every time a table is (re-)created.
• CloudTrail's actual events confirmed the gap between the DROP completing and the re-CREATE completing was 21 seconds.
• Snowflake CLD has two distinct kinds of sync, table discovery (SYNC_INTERVAL_SECONDS) and table metadata refresh (REFRESH_INTERVAL_SECONDS), both defaulting to 30 seconds. There's also a documented protection mechanism that detects table-uuid mismatches and drops the table from CLD.
• My working theory is that this 21-second DROP-to-re-CREATE interval, being shorter than both default sync intervals (30 seconds), meant table discovery hadn't yet detected the deletion by the time the same-named table reappeared, leaving the old location info in place.
• A reproduction experiment on a small test table (3 trials, 2 reproductions) directly backed up that hypothesis. The real issue isn't the absolute length of the interval, it's the relative timing of whether CLD's polling detects the DROP before the re-CREATE completes. This remains a hypothesis, but reproducing the same error multiple times gives it strong empirical support.

When you stack an external catalog integration (CLD) on top of an open format like S3 Tables, where a table's physical location can shift dynamically, there's still a black box in how the two catalogs stay in sync. Even a seemingly simple operation like DROP-and-re-CREATE can trigger an unexpected mismatch in a setup like this, which is worth keeping in mind operationally.

To be clear: this is something I observed in my own environment (Snowflake CLD + S3 Tables), and I'm not claiming it as a confirmed Snowflake bug.

I hope this is useful to anyone running S3 Tables together with Snowflake CLD.