SQL has type safety. It's primary purpose is to have typed schemas. I can't imagine why you would say this, other than some pedantic reason. SQL implementations like Postgres will happily throw errors when your types are off.
Testability - you use a general purpose language to execute SQL. Again, I don't know what you mean.
Composability - I suppose, but remember SQL is a language to retrieve data. I reuse fragments everywhere in a general purpose language.
The query planner optimizes it. Why would you want a compiler to optimize SQL? The nature of your data affects how it is optimized! The declarative statement to retrieve data must be interpreted based upon the nature of that data. You can't pre-optimize without knowing something about your data, in which case, you are basically storing some of the information outside of the database.
I don't understand why anyone would prefer SQL to that for anything beyond a simple SQL query. And it's not just my opinion: industry at large uses Spark for production with complex queries. SQL is for analysts.
SQL is just a DSL; it is not the only or primary API for Spark, and there's nothing magical about it. If you ditch it you can get your type safety, composability, and testability back, like so:
See those case classes that neatly encapsulate business objects? Add to that functional transforms that concisely express typical operations like filtering, mapping, and so on, you get something that is simply superior to SQL.
Any ORM provides the same features for any SQL database. There is nothing special going on here. If perhaps the database autogenerated a bunch of classes, maybe that’s interesting? I think some projects have introspected a database and created all the boilerplate language classes before.
There is nothing magical about wrapping database objects in language classes. This has been happening forever.