I second the recommendation for dbt. Especially if you're following an ELT architecture where you load your data into a data warehouse and then want to transform it. However dbt is more useful for transforming existing data, or aggregating data in batches. It's not a tool for generating SQL expressions on-the-fly like your library would allow you to do.

Most difficulties in data processing arise from the need to process multiple tables. It is highly difficult in SQL but it can be also difficult in pandas especially for complex analytical queries. Indeed, in pandas you still have to use the same join and groupby operations as in SQL.

An alternative to SQL, join-groupby, and map-reduce is developed in the Prosto library:

- https://github.com/prostodata/prosto Functions matter! No join-groupby, No map-reduce.

It is a layer over pandas and its main distinguishing feature is that it relies on functions and function operations for data processing as opposed to using only sets and set operations in SQL and other set-oriented approaches

I created something similar recently: https://docs.racket-lang.org/plisqin/index.html. At its core, it is also a library for composing fragments of SQL, but it has some novel (as far as I know) ideas. The most notable is that joins are values (or "expressions", if you prefer) that can be returned from a procedure like any other value. I was hoping that the world would realize "that's obviously how query builders should work" and copy the approach when starting new projects, but that hasn't happened.

https://github.com/machow/siuba

One thing I was wondering about the gnarly ast stuff you mention, what about operator overloading? E.g. Q("Select a from" + subquery + "where a < 1")