The real winner is going to be a framework that, during dev, transparently materializes CTEs to temporary tables so you can iterate on them like you’re saying, while continuing to harness SQL for the end product.
https://duckdb.org/docs/sql/query_syntax/with#cte-materializ...
Obviously, the materialization is gone after the query has ended, but still a very powerful and useful directive to add to some queries.
There are also a few DuckDB extensions for pipeline SQL languages.
https://duckdb.org/community_extensions/extensions/prql.html
https://duckdb.org/community_extensions/extensions/psql.html
And of course dbt-duckdb https://github.com/duckdb/dbt-duckdb