What are the differences between notebooks (written in SQL) and queries in Azure Databricks?

Question

What are the differences between notebooks (written in SQL) and queries in Azure Databricks?

I am particularly interested in what the differences are in terms of performance, billing and maintenance.

If there are no significant functional differences, is it more versatile to write all SQL in the Notebook instead of using the query feature?

Answer 1

Hi @Power BI Test User 2

Thank you for posting your query!

Notebooks in Azure Databricks and SQL queries both serve the purpose of querying data, but they differ in several aspects:

Performance:

• Notebooks typically run queries against compute clusters configured with Databricks Runtime, which can be optimized for interactive workloads. This allows users to see query results while transformations are processed.
• SQL queries, especially when run in SQL warehouses, are optimized for SQL execution and can scale automatically to match end-user demand. SQL warehouses manage SQL-optimized clusters and can offer better performance for SQL-specific workloads.

Billing:

• Databricks SQL workloads are charged according to the Standard Jobs Compute SKU. This means that running SQL queries in SQL warehouses may have different billing implications compared to running queries in notebooks, which utilize the Databricks Runtime.
• The cost-effectiveness of using notebooks versus SQL warehouses can vary based on the workload type and the compute resources used.

Maintenance:

• Notebooks allow for interactive development and testing, making them suitable for exploratory data analysis and iterative development. However, they may require more manual maintenance in terms of managing the execution environment.
• SQL warehouses, on the other hand, are managed services that automatically handle scaling and resource allocation, potentially reducing the maintenance burden on users.

Which is More Versatile?

• If you need complex workflows, data transformations, or multi-language support, Notebooks are better.
• If you only run ad-hoc queries and need a cost-effective, low-maintenance solution, Queries in SQL Editor are better.

For more details refer:

• Query data using SQL warehouses
• Databricks SQL release notes
• What is Databricks SQL?

Similar issues: https://learn.microsoft.com/en-us/answers/questions/668711/databricks-vs-serverless-notebook

https://learn.microsoft.com/en-us/answers/questions/461614/what-are-the-major-pros-and-cons-of-using-synapse

Hope this helps. Do let us know if you have any further queries.