Cannot delete from S3 table bucket (via Athena) - GENERIC_INTERNAL_ERROR

1. Check Glue Table Metadata

• Go to the Glue Console → Databases → your table.
• Look closely at column types, partition keys, and table properties.
• If anything looks off (like missing types or broken partition info), it can trigger this error.

2. Recreate the Table Manually

• If you're able to read the raw files (you mentioned DuckDB works), consider re-creating the table using a fresh CREATE EXTERNAL TABLE statement pointing to the same S3 location.

3. Query Specific Files

• You can try this to isolate if a particular file is breaking it:

SELECT * FROM your_table WHERE "$path" = 's3://your-bucket/path/to/file.parquet'

4. MSCK REPAIR TABLE

• If it's a partitioned table, run: ---> MSCK REPAIR TABLE your_table;
• This helps refresh the metadata and often fixes weird Glue desync issues.

Since you're using aws s3tables create-table and see no table in Glue:

1. Try Re-Creating the Table Using DDL (instead of CLI-generated JSON) Try manually creating the table with CREATE TABLE in Athena or using the Iceberg connector

2. Check Raw S3 Data Manually If DuckDB can read it, try to download and inspect the first few files using Python or aws s3 cp: aws s3 cp s3://your-bucket/path/file.parquet . Then validate the schema with parquet-tools schema or DuckDB’s CLI: SELECT * FROM 'file.parquet' LIMIT 10; Look for null values in numeric fields.

3. Use $path Filtering in Athena to Isolate the Failing File If your Iceberg or S3 table supports it: SELECT * FROM default.simple WHERE "$path" = 's3://your-bucket/path/to/specific/file.parquet'; This helps identify which file is problematic, especially useful for large datasets.

4. Enable Glue Catalog Integration (Optional) Even though you’re using s3tables, consider registering the table in Glue. This gives Athena more context: aws glue create-table --database-name mydb --table-input file://table-def.json Or use MSCK REPAIR TABLE if the table is already in Glue and partitioned.

The error you're encountering when trying to delete data from an S3 table via Athena is likely due to a compatibility issue or missing permissions.

S3 Tables is a relatively new feature, and there are a few things to check:

1. Make sure you're using the latest version of the AWS CLI. S3 Tables were recently announced, and older CLI versions don't support them properly.

2. Check that you have the necessary permissions. S3 Tables integrate with AWS Lake Formation for access control, which requires specific permissions to be granted even if you're an administrator in the AWS account.

3. Verify that you're using the correct catalog when querying. S3 Tables use a separate catalog from the default AWS Glue Data Catalog, and you may need to specify the S3 Tables catalog explicitly in your Athena query.

4. Ensure you've registered the S3 table bucket catalog as a data source in Athena.

If you need to delete the table entirely rather than just the data within it, you should use the AWS CLI with the s3tables commands:

aws s3tables delete-table \
--table-bucket-arn arn:aws:s3tables:region:account-id:bucket/your-table-bucket-name \
--namespace your_namespace \
--name your_table_name

Replace the placeholders with your specific information. If you need to delete the entire table bucket, you'll first need to delete all tables and namespaces within it, and then use the delete-table-bucket command.
Sources
S3tables deletion | AWS re:Post
How to Fix "The bucket that you tried to delete is not empty" Error When Deleting an S3 Table Bucket | AWS re:Post
S3 Tables - Unable to query using Athena | AWS re:Post