The most common use case for Athena I’ve come across is querying a data lake, as you mentioned. And, at least in my experience, I’ve not seen anybody using a third party UI tool for querying Athena, because as you mentioned, it’s only used for ad-hoc purposes during the development phase. More often than you think (or what I thought), people use an SDK (such as the one for Java or Python), to query Athena in some kind of data pipeline, or batch processing, mostly ETL.

Only in a couple of instances, according to what I’ve heard from other people, there are teams of people who only work with data via Athena for visualisations and stuff. In such cases, apparently, people use tools such as DBeaver for advanced features. Other than that (again, in my experience), I’ve not seen a lot of people using a third party tool just for querying Athena. It’s always just a part of a bigger flow or pipeline which is automated.

But, not underestimating the efficiency of Athena and partitioning the data in the data lake, Athena can return query results after querying tens GBs of data in seconds. Which is impressive. But even in such cases, it’s always part of some bigger setup and SDKs are used to run complex queries.

I hope I answered your question. Please let me know if I didn’t. I’ll try to put it in other words if possible. :)