Benefits of Databricks Views vs Tables

[raghunathr]

Do we have any explicit benefits with Databricks Views when the view going to be a simple select of table?

Does it improve performance by using views over tables?

Giving access to views vs Tables?

[Tharun-Kumar]

@raghunathr 

If the view is a simple select of a table, then there would not be any benefits. But if it's a join of multiple tables, then we have significant benefits. You can also use Views to restrict the columns of a table or records of a table by adding a filter condition. In these cases you get better access control.

Below are few of the benefits:

1. Simplify Access: Databricks Views can simplify access to data by providing a simplified view of a more complex dataset. This can make it easier to work with tables without having to remember all the complex joins and transformations. You can simply refer to the view in your code.

2. Query Optimization: Databricks Views have query optimization built in, which means that the queries executed on the view may run faster than on the original table. This is because the system can optimize the query execution plan based on the view definition.

3. Security: Databricks Views can be used to control access to data. You can grant access to the view and not the underlying tables. This can be particularly useful when you want to limit the amount of data that users can see or manage sensitive data.

[Kaniz]

Hi @raghunathr, The benefits of Databricks Views vs Tables are: 

Benefits of Databricks Views:
• Views allow you to break down large or complex queries into smaller, more manageable queries.
• You can validate intermediate results using expectations.
• Views reduce storage and compute costs and do not require the materialization of query results.
• Views can be queried from any part of the Databricks product, assuming you have permission.

Benefits of Databricks Tables:
• Tables are materialized and can be viewed and queried outside of the pipeline; using tables during development can help validate the correctness of computations.
• Tables offer additional control of their materialization, such as partitioning to speed up queries.
• Tables can be used when multiple downstream queries consume the table.
• Tables can be used when other pipelines, jobs, or queries consume the table.

Sources:
- https://docs.databricks.com/lakehouse/data-objects.html#views
- https://docs.databricks.com/lakehouse/data-objects.html#tables-vs-views

[youssefmrini]

There can be several benefits to using Databricks views, even when the view is a simple select of a table:

1. Improved query readability and maintainability:

   By encapsulating queries in views, you can simplify complex queries, making them more readable and easier to maintain over time. Views can also help abstract application logic from the underlying database schema and provide a simple interface for querying data.

2. Improved security and access controls:

   Views can be used to control access to sensitive data by only exposing select information to specific users or groups. By granting users permissions to views instead of tables, you can help ensure that they only have access to the data they need to see.

3. Improved performance:

   Views can improve query performance by optimizing query execution and reducing the amount of data that needs to be read. In some cases, views may also be used to pre-aggregate data to improve performance.

However, it's important to note that there can be performance considerations when using views. In certain situations, accessing data through a view can be slower than accessing the underlying table directly. This may be the case if the view includes complex joins or subqueries that require additional processing time.

Overall, views can be a useful tool for improving query readability, security, and access controls, and in certain situations, they can also improve performance. However, it's important to carefully consider whether a view is the appropriate solution for your specific data needs, and to test the performance impact of using views on your queries.