[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query

The error you're experiencing, "[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query," is likely related to the size and complexity of your query, particularly the large CASE statement with approximately 700 WHEN clauses.

This type of error often occurs when Athena's query engine encounters difficulties processing extremely large or complex queries. In your case, the sheer number of WHEN clauses in the CASE statement is probably overwhelming Athena's internal processing capabilities.

The fact that the query succeeds when you reduce the number of WHEN clauses supports this conclusion. Athena has certain limitations and performance thresholds, and your query may be exceeding these limits.

To resolve this issue, consider the following approaches:

1. Break down the large CASE statement into smaller, more manageable pieces. You could potentially split the logic across multiple queries or use temporary tables to store intermediate results.

2. Optimize your query structure. Look for ways to simplify the logic or use alternative query patterns that might be more efficient for Athena to process.

3. If possible, consider preprocessing some of the data or moving some of the complex logic to a different stage of your data pipeline, reducing the workload on Athena.

4. Review Athena's performance tuning guidelines to see if there are other optimizations you can apply to your query or data structure.

If the issue persists after attempting these optimizations, you may need to contact AWS support for further assistance, as there could be underlying system limitations or configuration issues specific to your Athena setup.

Remember, while Athena is powerful, it does have limits, and extremely complex queries can sometimes push beyond these boundaries, resulting in internal errors like the one you're experiencing.
Sources
Troubleshoot issues in Athena - Amazon Athena
[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query. | AWS re:Post
Amazon Athena experienced an internal error while executing a query : Query Id: c133a2c6-c3bd-4917-9e45-ff7c17c575c8 | AWS re:Post