Caching Amazon Athena Query Results with DynamoDB and Managed Query Results
Amazon Athena on Apache Iceberg is a natural fit for analytics dashboards, but every query carries a fixed overhead of one to several seconds, even when the result is a single number. Dashboards auto-refresh and get opened by many users, so the same queries run again and again, paying that latency and the scan bill every time. Athena ships a built-in Query Result Reuse feature, but it refuses to run in a workgroup that uses Managed Query Results.
The exact error is easy to hit and hard to search for:
Managed Query Results is attractive because Athena handles result storage, encryption, and cleanup for you, with no result bucket to provision. Giving that up just to get result reuse is a bad trade. This article shows a caching approach that keeps Managed Query Results and is actually stronger than the native reuse feature.
Why the obvious options fall short
Native Query Result Reuse is time based. You set a MaxAgeInMinutes and Athena reuses a previous identical result within that window. That means you are either serving stale data or re-scanning after the window expires even though nothing changed. And, as shown above, it is mutually exclusive with Managed Query Results.
Materialized views do not exist in Athena. You can emulate them with CREATE TABLE AS SELECT into a summary table, but then you own a refresh schedule and a freshness tradeoff.
Caching the result yourself by writing the rows to S3 or DynamoDB works, but you inherit a result store: size limits, large-table handling, serialization, and cleanup. For a table widget that returns tens of thousands of rows, that store becomes the new bottleneck.
The key idea: cache the execution id, not the result
Managed Query Results already stores every result for 24 hours. And GetQueryResults reads a finished result by its QueryExecutionId without running anything. The API reference states it plainly:
This request does not execute the query but returns results.
So you do not need to store results at all. You cache a pointer: the QueryExecutionId of a previous identical run. On a cache hit, you return that old id and let the client read the result through the exact same GetQueryResults path it already uses. No new execution, no scan, no separate result store. Large results keep working because pagination is unchanged.
Exact invalidation with a per-table version in DynamoDB
Time based reuse is the weak part of the native feature. Replace it with a change signal.
Keep a small item per physical table in DynamoDB that records the last-change timestamp. Bump it on every write, after the write commits. When a query runs, take the maximum version across the tables it touches, hash the resolved query text, and store a pointer keyed by that hash.
- Cache hit: the stored version equals the current max version and the pointer is younger than the retention window. Return the cached execution id.
- Cache miss: run the query, then store
{ version, queryExecutionId }under the query hash.
This is stronger than time based reuse. As long as no touched table changed, the result is reused indefinitely. The moment a write bumps a table version, the next run is a clean miss. Always fresh, and reused as much as possible.
The core in Node.js
The write path bumps one tiny item per table. pk/sk model a single-table DynamoDB design.
const { PutCommand, GetCommand } = require('@aws-sdk/lib-dynamodb');
// Call this AFTER the write has committed to Iceberg.
async function bumpTableVersion(ddb, tableName, physicalTable) {
await ddb.send(new PutCommand({
TableName: tableName,
Item: {
pk: `queryVersion#${physicalTable}`,
sk: 'queryVersion',
version: Date.now()
}
}));
}
The read path computes the max version across the query's tables. A missing item means the table was never written, so a 0 sentinel keeps stable tables cacheable too.
async function maxTableVersion(ddb, tableName, physicalTables) {
const versions = await Promise.all(
physicalTables.map(async (t) => {
const { Item } = await ddb.send(new GetCommand({
TableName: tableName,
Key: { pk: `queryVersion#${t}`, sk: 'queryVersion' }
}));
return typeof Item?.version === 'number' ? Item.version : null;
})
);
const present = versions.filter((v) => typeof v === 'number');
return present.length ? Math.max(...present) : 0;
}
The decision function returns an execution id either way. The caller never needs to know whether it was a hit or a miss.
const crypto = require('crypto');
const { StartQueryExecutionCommand } = require('@aws-sdk/client-athena');
const RETENTION_MS = 23 * 60 * 60 * 1000; // stay under the 24h managed-results retention
async function startOrReuse(athena, ddb, tableName, opts) {
const { queryString, physicalTables, workGroup, database } = opts;
const version = await maxTableVersion(ddb, tableName, physicalTables);
const hash = crypto.createHash('sha256').update(queryString).digest('hex'); // fixed 64 chars
const { Item: cached } = await ddb.send(new GetCommand({
TableName: tableName,
Key: { pk: `queryCache#${hash}`, sk: 'queryCache' }
}));
if (cached && cached.version === version && (Date.now() - cached.cachedAt) < RETENTION_MS) {
return cached.queryExecutionId; // reuse: no execution, no scan
}
const { QueryExecutionId } = await athena.send(new StartQueryExecutionCommand({
QueryString: queryString,
WorkGroup: workGroup,
QueryExecutionContext: { Database: database, Catalog: 'AwsDataCatalog' }
// Managed Query Results: the workgroup owns the result location, no OutputLocation
}));
const now = Date.now();
await ddb.send(new PutCommand({
TableName: tableName,
Item: {
pk: `queryCache#${hash}`,
sk: 'queryCache',
version,
queryExecutionId: QueryExecutionId,
cachedAt: now,
ttl: Math.floor(now / 1000) + 24 * 60 * 60 // epoch seconds, DynamoDB TTL cleanup
}
}));
return QueryExecutionId;
}
Reading the rows is unchanged. The same paginated GetQueryResults call serves both a fresh and a reused execution id.
const { GetQueryResultsCommand } = require('@aws-sdk/client-athena');
async function* readRows(athena, queryExecutionId) {
let NextToken;
do {
const page = await athena.send(new GetQueryResultsCommand({
QueryExecutionId: queryExecutionId,
NextToken,
MaxResults: 1000
}));
yield page.ResultSet;
NextToken = page.NextToken;
} while (NextToken);
}
Details that decide whether this is correct
Bump after commit, not before. The version must mean "this data is visible in Iceberg". If you bump before the write commits, a concurrent miss can run a fresh query that does not see the new rows yet, then cache that stale result under the new version.
Cover every write path. The bump has to fire wherever a write commits: synchronous inserts, asynchronous inserts processed by a background worker, batch imports, and schema changes. A single forgotten path leaves a cache that never invalidates for that table.
A tiny pointer, not a store. The cache item is a hash, an id, and two timestamps. It never holds result data, so large tables and wide rows cost nothing extra. A DynamoDB TTL attribute deletes the pointer automatically after 24 hours, which matches the managed-results retention. Correctness does not depend on TTL timing, because the read side re-checks version and age anyway.
Skip volatile tables. Some tables change on almost every request, such as usage or event logs. Caching queries that touch them wins nothing and risks staleness, so exclude them from the version signal and never reuse when one is involved.
Keep the permission model intact. The execution id travels to the client, and result access is scoped at the workgroup level. Validate the caller's table permissions before handing out a cached id, exactly as you would before starting a fresh query. A cached id is only served to a user who is allowed to run that query in the first place.
Conclusion
Managed Query Results and result caching are not mutually exclusive once you stop thinking about caching rows. Athena already stores the result behind an execution id for 24 hours, so the only thing worth caching is that id, guarded by a per-table version in DynamoDB. The result is exact invalidation, indefinite reuse of unchanged queries, no separate result store, and near-instant dashboard reads that still bill only real executions.
This pattern powers the analytics dashboards at Pantarey, where the same queries are opened by many users.