DEV Community

Cover image for Why is my Athena query failing or costing more after moving to a Medallion architecture?
Aniket Abhishek Soni
Aniket Abhishek Soni

Posted on AI-assisted

Why is my Athena query failing or costing more after moving to a Medallion architecture?

Many data engineers assume that simply organizing S3 prefixes into "Bronze," "Silver," and "Gold" folders is enough to ensure a performant and cost-effective Medallion architecture. In practice, this structural approach often leads to excessive S3 request costs, slow Athena query times, and massive metadata overhead, particularly when the underlying file formats and partitioning strategies are not aligned with how the AWS Glue Data Catalog tracks them.

This article provides a troubleshooting guide for engineers facing performance degradation or spiraling costs within an AWS-native Medallion pipeline. We cover identifying misaligned partitioning, excessive small files, and incorrect Iceberg table configurations. This scope assumes an architecture using Amazon S3 for storage, AWS Glue for the catalog and ETL, and Amazon Athena for ad-hoc analysis.

flowchart LR
    S3Raw["S3 (Bronze)"] --> GlueETL["AWS Glue ETL"]
    GlueETL --> S3Silver["S3 (Silver/Iceberg)"]
    S3Silver --> GlueCatalog["AWS Glue Data Catalog"]
    GlueCatalog --> Athena["Amazon Athena"]
    Athena --> S3Silver

Consider a hypothetical pipeline where a team has migrated their "Silver" layer to Apache Iceberg tables on S3. They find that queries against the events table are scanning significantly more data than expected, and simple SELECT statements are taking seconds longer than they did when using standard Parquet files in a Hive-style layout.

Symptoms

  • Query Latency: Queries against the Silver layer are slower than expected for the amount of data processed.
  • Cost Spikes: Athena "Data Scanned" metrics in AWS Cost Explorer are higher than the total size of the partitions being queried.
  • Metadata Overload: Athena query history shows high execution times spent in "Planning" or "Metadata initialization."
  • False Leads: The team might suspect network latency, complex joins, or the need for more expensive compute resources, when the issue is actually the way files are stored and indexed.

1. Excessive Small Files in the Silver Layer

A common mistake is treating the Silver layer as a landing zone where every incoming batch creates a new file. In Apache Iceberg, every file is an object in S3; having thousands of tiny files forces Athena to perform excessive S3 GET and LIST requests, which inflates cost and latency.

How to confirm:
Check the number of files per partition using the AWS CLI:

aws s3 ls s3://your-data-bucket/silver/events/ --recursive | wc -l
Enter fullscreen mode Exit fullscreen mode

If you see thousands of files for a single day's partition, your compaction strategy is failing.

How to fix:
Use the rewrite_data_files procedure in Athena to compact small files into larger ones (aim for 128MB to 512MB).

CALL system.rewrite_data_files('your_database', 'events');
Enter fullscreen mode Exit fullscreen mode

Verify the fix:
Re-run the aws s3 ls command or query the files metadata table to confirm a reduction in file count:

SELECT count(*) FROM "your_database"."events$files";
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Partition Pruning

If your Glue Data Catalog partition keys do not align with your query WHERE clauses, Athena will perform a full table scan. Even with Iceberg, partition evolution requires that your queries filter on the columns defined in the table schema.

How to confirm:
Check the query execution details in the Athena console. Look for "Data Scanned" vs. "Data Scanned per Partition." If the ratio is 1:1, you are scanning the entire dataset.

How to fix:
Explicitly filter by the partition key. If your table is partitioned by event_date, ensure your query includes it:

SELECT * FROM silver.events 
WHERE event_date >= '2023-10-01' AND event_date < '2023-10-02';
Enter fullscreen mode Exit fullscreen mode

If you are using hidden partitioning (e.g., partitioning by day(timestamp_column)), ensure your query uses the source timestamp column, not a derived string column.

Verify the fix:
Review the Athena query execution plan to see if "Partition Pruning" is listed as a filter step.

3. Missing or Stale Table Statistics

Athena’s query optimizer relies on statistics in the Glue Data Catalog (or Iceberg metadata) to decide how to join tables and how much data to scan. If these stats are missing, the optimizer may default to a sub-optimal execution plan.

How to confirm:
Run DESCRIBE FORMATTED on your table:

DESCRIBE FORMATTED your_database.events;
Enter fullscreen mode Exit fullscreen mode

Look for numFiles, totalSize, and numRows in the output. If these are null or outdated, the optimizer is flying blind.

How to fix:
For Iceberg tables, statistics are managed by the engine. You can update them using:

ANALYZE TABLE your_database.events COMPUTE STATISTICS;
Enter fullscreen mode Exit fullscreen mode

For non-Iceberg tables, you must run an AWS Glue Crawler or manually update statistics via the Glue API.

Verify the fix:
Run DESCRIBE FORMATTED again and verify that numRows and totalSize now reflect the current state of your data.


Preventing it

To keep a Medallion architecture performant, implement these systemic safeguards:

  1. Automate Maintenance: Do not rely on manual compaction. Use AWS Glue ETL jobs or AWS Step Functions to trigger rewrite_data_files on a schedule based on table growth.
  2. Use Iceberg for Silver/Gold: Avoid legacy Hive-style partitioning for Silver and Gold layers. Apache Iceberg handles partition evolution and metadata indexing far better than standard Parquet, which mitigates many "small file" problems at the ingestion layer.
  3. Strict Schema Evolution: Use the AWS Glue Schema Registry to ensure that incoming Bronze data matches the expected format. Drifting schemas often break partition logic, leading to empty results or massive scans of incorrectly formatted data.
  4. Monitor with Athena Query Metrics: Use the AthenaQueryExecution API to track DataScannedInBytes for your most frequent queries. Set up Amazon CloudWatch Alarms to alert when the 95th percentile of data scanned per query exceeds a specific threshold.

Clean up

If you created test tables or Glue databases to investigate these issues, ensure you clean them up to avoid storage costs:

  1. Drop the tables: DROP TABLE your_database.events;
  2. Delete the S3 data: aws s3 rm s3://your-data-bucket/silver/events/ --recursive
  3. Delete the Glue Database: aws glue delete-database --name your_database

By shifting the focus from "folder structure" to "file maintenance and metadata accuracy," you can stop treating the Medallion architecture as a static organization method and start treating it as a dynamic, tunable data system.

References

About the examples: automated checks confirmed that 1 AWS CLI command matches the AWS service API models (operation and parameter names). The examples have not been run end to end, so test them in a non-production environment before relying on them.

Cover photo by Elimende Inagella on Unsplash.


This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Top comments (0)