ADF pipeline must be used to write up to 500k records per day to Azure SQL DB. I used some straightforward calculations as part of the data transformation process that a SQL Stored Procedure activity can carry out. Additionally, I've noticed that Databricks Notebooks are frequently used, particularly because of their future scalability. However, there are overhead activities such as managing authentication, moving files to a new location after transformation, etc., so I want to avoid over-engineering unless absolutely necessary. For about 50k records, the SQL Stored Proc I tested performed admirably (not yet tested with higher volumes).
However, I'd still like to hear a general opinion about which of the two options is better, especially from knowledgeable Azure or data engineers.