Athena query failed with the error message: Query exhausted resources at this scale factor

Sometimes the demands of a query exceed the resources available to the cluster running the query. When this happens, the query fails with the error Query exhausted resources at this scale factor.

Even if a query fails with an 'out of resource' error once, it might succeed when you run it again. Query execution is not deterministic. Factors such as how long it takes to load data and how intermediate datasets are distributed over the nodes can result in different resource usage. For example, imagine a query that joins two tables and has a heavy skew in the distribution of the values for the join condition. Such a query can succeed most of the time but occasionally fail when the most common values end up being processed by the same node.

To prevent your queries from exceeding available resources, use the performance tuning tips mentioned in this document [1]. In particular, for tips on how to optimize queries that exhaust the resources available, see [2].

Reference

1. https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html#performance-tuning-resource-limits
2. https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html#performance-tuning-query-optimization-techniques