I can see that SQLAlchemy is doing requests like
SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
that show up in my query history in SnowflakeDB.
What is SQLAlchemy trying to achieve with those?
It's explained in the What's new in SQLAlchemy 1.3:
Once Python 3 was introduced, DBAPIs began to start supporting Unicode more fully, and more importantly, by default. However, the conditions under which a particular DBAPI would or would not return Unicode data from a result, as well as accept Python Unicode values as parameters, remained extremely complicated. This was the beginning of the obsolescence of the "convert_unicode" flags, because they were no longer sufficient as a means of ensuring that encode/decode was occurring only where needed and not where it wasn't needed. Instead, "convert_unicode" started to be automatically detected by dialects. Part of this can be seen in the "SELECT 'test plain returns'" and "SELECT 'test_unicode_returns'" SQL emitted by an engine the first time it connects; the dialect is testing that the current DBAPI with its current settings and backend database connection is returning Unicode by default or not.