Do you know how this chain is being executed by the underlying DB-API?

I'm not sure if you're asking about how we run it, or how the underlying DB-API in google-cloud-bigquery does it (?). Assuming the former, we open a connection object and a cursor object and cursor.execute the series of statements.

Perhaps this would be fixed by changing the executemany() function to issue the statements in one batch?
I think that executemany is a way to run the same SQL statement multiple times with different parameters (?) https://googleapis.dev/python/bigquery/latest/dbapi.html#google.cloud.bigquery.dbapi.Cursor.executemany

Now I'm realizing the issue may be in google-cloud-bigquery, do you think I should open an issue there?

Yes, https://github.com/googleapis/python-bigquery would be the appropriate location for this issue.

Are you calling execute once? Or once per statement?

If once per statement, are there any transaction semantics you're using? The BigQuery API doesn't hold onto cursor/connection state, so we'll need some kind of additional information in order for the client to batch these up into a single request.

We're calling execute for each statement, most other DB-API driver's execute assumes a single statement. All other DB-API drivers we use in Apache Superset respond well to this approach

so we'll need some kind of additional information in order for the client to batch these up into a single request

Wondering what that may be. I saw the notion of job_id reading some source code in google-cloud-bigquery, but unclear as to how to get this to work.

I opened an issue on python-bigquery with more details, and really appreciate all the help so far!

Updating this issue from our discussion on the DB-API. Currently we workaround this by batching all statements in to a single query request. This triggers the BigQuery "scripting" path.

The ideal solution is for the BigQuery backend to support true sessions for scripting. If/when they add support for sessions, we can make the update to the DB-API and test it with this SQLAlchemy connector.

Sorry to drag up an old issue, but this is where I was looking to figure out how to write such a query using bigquery sqlalchemy.

I'm assuming the SET/DECLARE statements need to be created using text(), but is there a good way to combine these SET/DECLAREs with a selectable at the end without compiling the selectable?

Relatedly, is there a way to do a CREATE TEMP TABLE foo AS () and input a selectable?

Happy to file a new issue along the lines of Document multi-statement query usage if that's easier!