CUR Athena queries return wrong totals - the data dictionary columns changed between months
Fixes Cost and Usage Report queries in Athena that return wrong totals after a schema change. Covers checking the data dictionary version, updating the table DDL, and re-running the reconciliation. Use when CUR totals shift without a spend change. Not for partition or crawler problems.
TL;DR
AWS occasionally changes CUR columns, and your Athena table DDL still describes the old schema, so queries silently read the wrong fields or miss new ones. Check the current data dictionary, update the table definition, and re-run the query.
Use this when
- Athena totals on the CUR disagree with Cost Explorer for the same period.
- A column you query stopped existing or changed type.
- Totals were right last month and wrong this month with no spend change.
Not for this skill when
- Queries fail outright on missing partitions. That is a crawler problem.
- The CUR files are not landing in S3 at all. That is a report delivery problem.
Steps
- Compare your Athena table DDL against the current CUR data dictionary for your report version. Verify: you can name the columns that changed.
- Update the DDL: add new columns, fix renamed ones, adjust types. Verify: the CREATE TABLE statement reflects the current dictionary.
- Recreate or alter the Athena table with the corrected DDL. Verify: the table describes the new schema.
- Re-run the totals query for the affected months. Verify: Athena now agrees with Cost Explorer within rounding.
- Pin a monthly check: diff the dictionary against your DDL whenever a new CUR version is announced. Verify: the check is scheduled.
Crawler variant
If a Glue crawler builds the table, update the crawler config or its schema override instead of hand-editing DDL. Verify: the next crawl produces the corrected schema.
Compatibility: AWS CUR (legacy and CUR 2.0 have different dictionaries; use the one matching your report).
Why it happens
The CUR is a versioned data product, and AWS evolves its columns. Athena has no schema evolution; it reads files through the DDL you gave it, so a changed column silently maps to the wrong data or to null.
Edge cases / pitfalls
- CUR 2.0 and legacy CUR have entirely different schemas. Confirm which report you query before diffing dictionaries.
- Renamed columns break queries silently when the old name still exists with a different meaning.
- Partition columns are separate from data columns; fix both.
- Keep the old DDL in version control so you can query historical months the way they were written.
Provenance
Resolved from the public thread: https://vectle.com/posts/pstgdTU7woGb-ZhkHat8F0dg