> stricter type systems ... the practices of Python or Scala
I do understand what you are talking about. But I really think you and the OP are both complaining about the wrong problem.
SQL doesn't require bad practices, doesn't inherently harm composability (the way the OP was referring), and don't inherently harm verification. Instead, it has stronger support for many of those than the languages you want to replace it with.
The problems you are talking about are very real. But they do not come from the language. (SQL does bring a few problems by itself, but they are much more subtle than those.)
At least BigQuery does a fair bit of typechecking, and gives error messages in a way that's to the par of application programming (e.g. not letting you pass a timestamp to a DATE function and stating that there's no matching signature).
But a tool that doesn't "require" bad practices but doesn't require good practices either makes your work harder in the long run.
Tooling is poor, the best IDE-similes you got until recently were of the type that connects to a live environment but doesn't tie to your codebase, and encourages you to put your code directly on the database rather than version control, the problems of developing with a REPL and little in the way to mitigate them. I'm talking of course of the problem of having view and function definitions live in the database with no tools to statically navigate the code.
Testing used to be completely hand rolled if anyone bothered with it at all.
That was until now, that data pipeline orchestration tools exist and let you navigate the pipeline as a dependency graph, a marked improvement, but until dbt's Python version is ready for production, we're talking here of a graph of Jinja templates and YAML definitions, with modest support for unit testing.
Dataform is a bit better but virtually unknown and was greatly hindered by the Google acquisition.
Functions have always been clunky and still are.
RDDs and then, to a lesser extent, Dataframes offered a much stronger programming model, but they were still subject to a lack of programming discipline from data engineers in many shops. The results of that, however, are on a different scale with undisciplined SQL programming, and it's downright hard to be disciplined when using it.
The trend to move from ETL to ELT I feel shouldn't have been unquestioningly transitioned to untyped Dataframes and then SQL.
The language itself has some issues, like no attention at all paid into modularity and reusability; the old-language distinction between functions and data; lack of expressivity of the the type system (well, not compared with Python and Scala, but just ADTs would already bring a huge gain); and the complex limitations on the symbol literal vs. evaluated use that forces people into metaprograming every time they need to decide on a table at runtime.
The first one would limit the use of widespread best-practices, but in practice it's not the bottleneck, because every SQL-based tooling already creates strictly more constraining issues.
I do understand what you are talking about. But I really think you and the OP are both complaining about the wrong problem.
SQL doesn't require bad practices, doesn't inherently harm composability (the way the OP was referring), and don't inherently harm verification. Instead, it has stronger support for many of those than the languages you want to replace it with.
The problems you are talking about are very real. But they do not come from the language. (SQL does bring a few problems by itself, but they are much more subtle than those.)