That is a complex question, but I'll do my best to break it down.
Part of the beauty of Databricks is that it is a platform for your entire data community. In the old days there used to be differences between Scala, Python, SQL, R, Java. Nowadays, each language is well supported and for the most part there is parity and harmony across the API's.
Databricks is truly polyglot, so it's reasonable for some teams/individuals to use Scala, some to use SQL and some to use Python or R or Java. You could even use multiple languages in a single pipeline or notebook. That being said, it's often best for your team to standardize around a single language and style. This will make your code easier to reason about, test and write; improving overall developer Quality of Life.
Here are some additional things you should consider when choosing a language:
- What language(s) can your team(s) best support?
- What language(s) is my code base in today?
- How complex is the logic in my pipelines? Python and Scala are easier to write tests for.
- Are there any 3rd party libraries I need/want to use and support?
- Are there any external systems I need to integrate with that have better support in a particular language?
- Some code may be easier to express in Python or Scala over SQL, while other code may be easier to reason about and easier to write in SQL.
- Depending on your use case you will likely find more community support and reference code for one language or another. For more generic pipeline development and data management there will be more support in Python or SQL. It's important to note, any advantage here is being significantly flattened by AI coding assistants.
tldr; Use the language that works best for you and your data community. For me thats often python or SQL.
