Do you know how good is the SparkSQL optimization in e.g. Databricks? Conversely I have also seen SQL used in overcomplicated ways to replace a more general pro

Do you know how good is the SparkSQL optimization in e.g. Databricks? Conversely I have also seen SQL used in overcomplicated ways to replace a more general programming language. When scripting in SQL I have seen that in certain SQL flavors there is no good way to split a large file into k smaller ones for a fixed k without creating a new column 1-<number of records>, selecting k new tables modulo the number in the new column, then writing the k files. The way this is implemented is usually naive so the performance is piss poor due to writing and subsequently reading so much data. If this candidate was very good at Scala, assuming they were a Spark programmer, they could probably do lots of dataframe operations that were as good or better than SQL commands regarding performance no?