I have a rather complicated Athena query, which I would like to test on a local machine without connecting to Athena. I specified some mock data for testing purposes, and I was hoping that I could use something simple like SQLite to spin up a local database, populate it with mock data, run the tests and tear the database down.
The issue is that the SQLite dialect is just different enough for my Athena query to fail. What are good practices for this task? Should I connect to actual Athena and create mock data there? Are there any tools that can convert SQL queries between dialects?
Testing is being done with Python.
A possibility to achieve what you suggested it's via localstack.
Here an extensive guide that shown also how to create different table types with mocked data, and then connect to localstack via pyathena.
Anyhow Athena support in localstack seems to be in the Pro version (check the pricing page to have more insights).
If the data that you are dealing with is small enough, using the real athena service from AWS might be the best choice - if that's the case consider to use a separate environment.