What I personally like about SQL here is that you can start to think about your transformations in a purer "relational algebra" sense. You're able to declare the expected outcomes, and let the engine figure out the best way to handle that, as opposed to actually having to figure out how to join, aggregate, window, etc.

SQL transformations shine in environments where regular batching in short intervals is acceptable. Materialize and Flink are both super interesting for overcoming the batching shortcoming into realtime materialization via SQL.

Regarding the concerns about tests, it's true it's harder to do unit testing of SQL, but for me the tradeoff is it feels like there's a whole class of bug that's eliminated by focusing on the relational algebra. If you can avoid the category of work of defining the procedures to transform the data, and only define the expected outcome, things can be much simpler.