Why materialized views are created in __databricks_internal ?

[AxelBrsn]

Hello, I have a question about why materialized views are created in "__databricks_internal" catalog?

We specified catalog and schemas in the DLT Pipeline.

[Kaniz_Fatma]

Hi @AxelBrsn, Let’s delve into materialized views and their purpose in the context of Databricks SQL.

Materialized views play a crucial role in optimizing query performance and reducing data processing costs. Here’s why they are created in the __databricks_internal catalog:

1. What Are Materialized Views?

   • Materialized views are Unity Catalog managed tables within Databricks SQL.
   • They allow users to precompute results based on the latest data in source tables.
   • Unlike traditional views, materialized views store the actual data, not just the query definition.
   • The results returned by materialized views reflect the state of data when the view was last refreshed, rather than updating results every time the view is queried.

2. Use Cases and Benefits:

   • Performance Optimization: Materialized views are especially useful for complex queries or aggregations that are performed frequently.
   • Cost Reduction: By precomputing slow queries and commonly used computations, materialized views reduce costs.
   • Query Latency Improvement: Storing pre-computed results avoids the need to repeatedly execute complex queries, resulting in faster response times.
   • Data Transformations: Materialized views simplify data transformations, such as cleaning, enriching, and denormalizing base tables.
   • Incremental Computation: In some cases, materialized views can incrementally compute changes from base tables, further reducing costs.

3. Delta Live Tables Integration:

   • When you create a materialized view in a Databricks SQL warehouse, a Delta Live Tables pipeline is automatically set up to process refreshes.
   • You can monitor refresh status through the Delta Live Tables UI, API, or CLI.

4. Requirements:

   • To create and refresh materialized views, you need a Unity Catalog-enabled Databricks SQL warehouse.
   • Ensure you’ve accepted the serverless terms of use, and your workspace is in a serverless-enabled region.

5. Limitations:

   • Be aware of limitations when using materialized views with Databricks SQL.

In summary, materialized views provide a powerful way to optimize data processing workloads, improve query performance, and enhance the end-user experience. They’re a valuable addition to your DLT Pipeline! 🚀

For more detailed information, you can refer to the official Databricks documentation on materialized views¹.