https://community.databricks.com/t5/technical-blog/best-practices-for-building-the-presentation-layer-in-a/ba-p/117425 <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="shyam_rao_0-1746119066033.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/16406iA757713E3A6425AB/image-size/large?v=v2&amp;px=999" role="button" title="shyam_rao_0-1746119066033.png" alt="shyam_rao_0-1746119066033.png" /></span></SPAN></P> <P><FONT size="5"><SPAN><FONT face="times new roman,times"><FONT face="tahoma,arial,helvetica,sans-serif"><FONT face="arial,helvetica,sans-serif" size="4">The</FONT>&nbsp;</FONT></FONT></SPAN></FONT><FONT size="4"><SPAN><STRONG>Presentation Layer</STRONG> is the final stage in the Lakehouse data architecture. This layer contains curated, user-friendly data for business analysts, decision-makers, and BI applications. The data is generally organized into star schemas, aggregated summary tables, business views, and APIs, enabling integration with BI tools such as <A href="https://www.databricks.com/product/business-intelligence" target="_blank" rel="noopener">Databricks AI/BI</A>, Tableau, and Power BI for interactive analysis and reporting.</SPAN></FONT></P> <H3><SPAN>Key Features</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>User-Centric</STRONG><SPAN>: Tailored for analysts and decision-makers, providing clean and accessible data.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Abstracts Complexity</STRONG><SPAN>: Simplifies raw data into enriched, pre-aggregated views, encapsulating business logic.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Supports Reporting</STRONG><SPAN>: Optimizes for fast querying, visualizations, and calculated measures.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Secure</STRONG><SPAN>: Enables governed, masked, and role-based access to sensitive data.</SPAN></FONT></LI> </UL> <H3><SPAN>Best Practices</SPAN></H3> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Simplify Data</STRONG><SPAN>: Use business-friendly schemas with clear relationships.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Optimize Performance</STRONG><SPAN>: Precompute aggregations and materialize views for efficiency.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Enforce Security</STRONG><SPAN>: Implement role-based access control, data masking, and audit trails.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Monitor Usage</STRONG><SPAN>: Track query performance and usage patterns for optimization.</SPAN></FONT></LI> </OL> <P><FONT size="4"><SPAN>The Presentation Layer bridges technical complexity and business needs, ensuring </SPAN><STRONG>fast, secure, and actionable data</STRONG><SPAN> for decision-making.</SPAN></FONT></P> <H3><SPAN>How Databricks Fits into the Presentation Layer</SPAN></H3> <P><FONT size="4"><SPAN>Databricks has several tools to implement the presentation layer:</SPAN></FONT></P> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Delta Universal Format</STRONG><SPAN>: Store pre-aggregated or denormalized data for BI consumption.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Materialized Views</STRONG><SPAN>: Precompute results of complex queries and aggregations for optimized performance.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Databricks SQL</STRONG><SPAN>: Create queries, dashboards, and visualizations directly in Databricks.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Integration with BI Tools</STRONG><SPAN>: Connect Databricks directly with tools like Power BI, Tableau, or Looker for seamless visualization and reporting.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Unity Catalog (UC)</STRONG><SPAN>:&nbsp; Unified data and AI governance for enhanced security and compliance.</SPAN></FONT></LI> </OL> <P>&nbsp;</P> <H1><FONT color="#000000"><SPAN>How do I build my presentation layer?</SPAN></FONT></H1> <P><FONT size="4"><SPAN>The presentation layer comprises queryable objects such as </SPAN><STRONG>tables</STRONG><SPAN>, </SPAN><STRONG>materialized views,</STRONG><SPAN> and </SPAN><STRONG>views</STRONG><SPAN>.</SPAN></FONT></P> <H2><FONT color="#003366"><U>Materialized Views</U></FONT></H2> <H4>What Are Materialized Views in Databricks?</H4> <P><FONT size="4"><SPAN>In Databricks SQL, materialized views are UC-managed tables that allow users to precompute results based on the latest version of data in source tables. CREATE and REFRESH operations on materialized views are powered by serverless DLT pipelines.</SPAN></FONT></P> <H4>Why Use Materialized Views?</H4> <H5><FONT size="3">Performance Optimization</FONT></H5> <UL> <LI><FONT size="4"><STRONG>Reduced Query Time</STRONG><SPAN>: Materialized views precompute and store the results of complex or computationally expensive queries. When users query the materialized view, they access the precomputed data, leading to faster query responses.</SPAN></FONT></LI> <LI><FONT size="4"><STRONG>Efficient Aggregations</STRONG><SPAN>: Instead of recalculating aggregations (for example, sums, averages) each time, a materialized view can store these precomputed results. For repetitive queries, materialized views reduce compute overhead and optimize resources.</SPAN></FONT></LI> </UL> <H5><FONT size="3"><SPAN>Simplified Querying</SPAN></FONT></H5> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Abstracts Complexity</STRONG><SPAN>: Users can query the materialized view without worrying about the underlying data's complexity or transformations. Transformations like joins, aggregations, and filters are encapsulated in the view.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Business-Friendly Data Models</STRONG><SPAN>: Materialized views can expose preprocessed data tailored for business use cases.</SPAN></FONT></LI> </UL> <H5><FONT size="3"><SPAN>Incremental Updates</SPAN></FONT></H5> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Many queries can be incrementally refreshed when running updates on materialized views using serverless pipelines. Incremental refreshes save compute costs by detecting changes in the data sources used to define the materialized view and incrementally computing the result.</SPAN></FONT></LI> </UL> <H5><FONT size="3"><SPAN>Compatibility with BI Tools</SPAN></FONT></H5> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Materialized views integrate well with BI tools (for example, Power BI, Tableau). They offer pre-aggregated or preprocessed datasets that speed up dashboard loading times.</SPAN></FONT></LI> </UL> <H5><FONT size="3"><SPAN>Cost Savings</SPAN></FONT></H5> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Lower Compute Costs</STRONG><SPAN>: Precomputing results in materialized views reduces the need for repetitive expensive computations, saving on Databricks compute costs.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Resource Efficiency</STRONG><SPAN>: The cluster resources can be allocated more efficiently because fewer resources are needed to serve end-user queries.</SPAN></FONT></LI> </UL> <H4>Limitations of Materialized Views</H4> <UL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Not real-time: Materialized views are not updated in real-time. The frequency of updates depends on your refresh schedule.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Storage-overhead: Since materialized views store pre-computed data, they consume additional storage.</SPAN></FONT></LI> </UL> <H4>How to Create and Refresh Materialized Views in Databricks</H4> <H5><FONT size="3"><SPAN>Create a Materialized View (</SPAN><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view.html" target="_blank" rel="noopener"><SPAN>AWS</SPAN></A><SPAN>|</SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view" target="_blank" rel="noopener"><SPAN>Azure</SPAN></A><SPAN>|</SPAN><A href="https://docs.databricks.com/gcp/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view" target="_blank" rel="noopener"><SPAN>GCP)</SPAN></A></FONT></H5> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Write a query for your materialized view (for example, aggregations, joins).</SPAN></FONT> <PRE><FONT size="4">CREATE MATERIALIZED VIEW sales_by_region_mv</FONT><BR /><FONT size="4">AS</FONT><BR /><FONT size="4"><SPAN>SELECT region, customer, item, SUM(sales) AS total_sales</SPAN></FONT><BR /><FONT size="4"><SPAN>FROM transactions</SPAN></FONT><BR /><FONT size="4"><SPAN>GROUP BY region</SPAN></FONT><BR /><FONT size="4"><SPAN>CLUSTER BY AUTO;</SPAN></FONT></PRE> </LI> </OL> <P class="lia-indent-padding-left-30px"><FONT size="4"><SPAN>Note: </SPAN><A href="https://www.databricks.com/resources/demos/tours/governance-uc/row-and-column-level-security-with-unity-catalog" target="_blank" rel="noopener"><SPAN>Row-Level Security and Column-Level Masking</SPAN></A><SPAN> can be defined on MVs.</SPAN></FONT></P> <H5><FONT size="3"><SPAN>Refresh a Materialized View (</SPAN><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-refresh-full.html" target="_blank" rel="noopener"><SPAN>AWS</SPAN></A><SPAN>|</SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-refresh-full" target="_blank" rel="noopener"><SPAN>Azure</SPAN></A><SPAN>|</SPAN><A href="https://docs.databricks.com/gcp/en/sql/language-manual/sql-ref-syntax-ddl-refresh-full" target="_blank" rel="noopener"><SPAN>GCP)</SPAN></A></FONT></H5> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Refresh the materialized view periodically using a Databricks Workflows Job:</SPAN></FONT> <PRE><FONT size="4"><SPAN>REFRESH MATERIALIZED VIEW sales_by_region_mv;</SPAN></FONT></PRE> </LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Configure the schedule with the SCHEDULE clause during CREATE or ALTER</SPAN></FONT> <PRE><FONT size="4"><SPAN>... SCHEDULE CRON '0 0 0 * * ? * ...<BR />... SCHEDULE EVERY 2 HOURS ...</SPAN></FONT></PRE> </LI> </OL> <P class="lia-indent-padding-left-30px"><FONT size="4"><SPAN>Note: Refresh can be performed in either </SPAN><STRONG>SYNC</STRONG><SPAN> or </SPAN><STRONG>ASYNC</STRONG><SPAN> mode.</SPAN></FONT></P> <H5><FONT size="3"><SPAN>Monitoring Maintenance Activities</SPAN></FONT></H5> <PRE class="lia-indent-padding-left-30px"><FONT size="4"><SPAN>SELECT</SPAN></FONT><BR /><FONT size="4"><SPAN>&nbsp;</SPAN><SPAN>*</SPAN></FONT><BR /><FONT size="4"><SPAN>FROM</SPAN></FONT><BR /><FONT size="4"><SPAN>&nbsp;event_log(TABLE(catalog.schema.table))</SPAN></FONT><BR /><FONT size="4"><SPAN>WHERE</SPAN><SPAN> event_type</SPAN> <SPAN>in</SPAN> <SPAN>(</SPAN><SPAN>'maintenance_progress', 'background_operation')</SPAN></FONT><BR /><FONT size="4"><SPAN>ORDER BY</SPAN></FONT><BR /><FONT size="4"><SPAN>&nbsp;timestamp </SPAN><SPAN>desc</SPAN><SPAN>;</SPAN></FONT></PRE> <H4><SPAN>Improving Performance Using Materialized Views</SPAN></H4> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Use Liquid Clustering to optimize the data layout and maximize query performance. Take advantage of </SPAN><STRONG>Predictive Optimization</STRONG> <SPAN>(</SPAN><A href="https://docs.databricks.com/aws/en/optimizations/predictive-optimization" target="_blank" rel="noopener"><SPAN>AWS</SPAN></A><SPAN>|</SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/optimizations/predictive-optimization" target="_blank" rel="noopener"><SPAN>Azure</SPAN></A><SPAN>|</SPAN><A href="https://docs.databricks.com/gcp/en/optimizations/predictive-optimization" target="_blank" rel="noopener"><SPAN>GCP</SPAN></A><SPAN>) </SPAN><SPAN>and Auto Clustering.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>Creating a wide table, for example, including attributes from a dimension, can result in better data skipping based on column statistics.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><SPAN>To generate optimized plans, specify constraints such as </SPAN><A href="https://www.databricks.com/blog/primary-key-and-foreign-key-constraints-are-ga-and-now-enable-faster-queries" target="_blank" rel="noopener"><SPAN>PRIMARY KEY..RELY</SPAN></A><SPAN> and column constraints such as NOT NULL.</SPAN></FONT></LI> </OL> <H2><FONT color="#003366"><U>Views</U></FONT></H2> <P><FONT size="4"><SPAN>Enhance your dashboarding and reporting processes by creating and exposing views on top of dimension and fact tables. </SPAN><STRONG>Create a view on a table or a materialized view</STRONG><SPAN>.</SPAN></FONT></P> <H4><SPAN>Views can be used to:</SPAN></H4> <OL> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Tailor the presentation</STRONG><SPAN>: Expose only essential attributes (for example, primary address).</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Simplify attribute representation</STRONG><SPAN>: ARRAY and STRUCT (and </SPAN><A href="https://www.databricks.com/blog/introducing-open-variant-data-type-delta-lake-and-apache-spark" target="_blank" rel="noopener"><SPAN>VARIANT</SPAN></A><SPAN>) types might not be compatible with all BI tools.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Create role-playing dimensions</STRONG><SPAN>: For example, shipping facility vs. receiving facility.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Use consistent formulas</STRONG><SPAN>: Encapsulate business logic.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Make extensible</STRONG><SPAN>: Easily include new calculated measures based on user request.</SPAN></FONT></LI> <LI style="font-weight: 400;" aria-level="1"><FONT size="4"><STRONG>Focus on relevant data</STRONG><SPAN>: Use filters to include only the necessary data. For instance, exclude soft-deleted records or outdated versions based on your specific use case.</SPAN></FONT></LI> </OL> <H4><SPAN>Agile Data Modeling with Views</SPAN></H4> <P><FONT size="4"><SPAN>Views are beneficial in an agile environment, where business users frequently request new metrics. A well-designed Lakehouse architecture enables simple modifications, such as adding calculated columns in views. You also have the freedom to change the data representation in the backing table or materialized view. I have benefited from this design on multiple occasions.</SPAN></FONT></P> <H3><STRONG>Query Optimization Tip</STRONG></H3> <P><FONT size="4"><SPAN>When querying data, retrieve only the columns essential for analysis to optimize performance using predictive I/O and reduce fetch times.</SPAN></FONT></P> <P>&nbsp;</P> <H1><FONT color="#000000"><SPAN>Conclusion</SPAN></FONT></H1> <P><FONT size="4"><SPAN>By following these guidelines, you can effectively design and optimize your Star Schema (or other models such as Business Views) in Databricks SQL, leveraging advanced features and best practices to enable high performance and scalability. </SPAN><A href="https://www.databricks.com/product/databricks-sql" target="_blank" rel="noopener"><SPAN>Get Started!</SPAN></A></FONT></P> Thu, 08 May 2025 12:59:09 GMT shyam_rao 2025-05-08T12:59:09Z https://community.databricks.com/t5/technical-blog/best-practices-for-building-the-presentation-layer-in-a/ba-p/117425 <P><FONT size="4"><SPAN>The <STRONG>Presentation Layer</STRONG> is the final stage in the Lakehouse data architecture. This layer contains curated, user-friendly data for business analysts, decision-makers, and BI applications. The data is generally organized into star schemas, aggregated summary tables, business views, and APIs, enabling integration with BI tools such as <A href="https://www.databricks.com/product/business-intelligence" target="_blank" rel="noopener">Databricks AI/BI</A>, Tableau, and Power BI for interactive analysis and reporting.</SPAN></FONT></P> Thu, 08 May 2025 12:59:09 GMT https://community.databricks.com/t5/technical-blog/best-practices-for-building-the-presentation-layer-in-a/ba-p/117425 shyam_rao 2025-05-08T12:59:09Z https://community.databricks.com/t5/technical-blog/best-practices-for-building-the-presentation-layer-in-a/bc-p/118516#M632 <P>Great summary of the core principles and tools for building an effective Lakehouse presentation layer. The emphasis on user-centric design, performance optimization, and strong security is spot on-especially as organizations increasingly rely on BI and AI to drive decisions. Materialized views and business-friendly schemas are key to bridging the gap between raw data and actionable insights, ensuring that analysts and decision-makers can trust and easily consume the data.</P> <P>One point that resonates is the focus on incremental updates and monitoring. These practices not only reduce costs but also make the architecture agile, allowing teams to adapt quickly to changing business needs. And, as you note, integrating with leading BI tools and leveraging Databricks SQL and Unity Catalog for governance ensures that data is both accessible and secure.</P> <P>Finally, the reminder to keep data models simple, monitor usage, and optimize for query performance is a must for any modern data team. This layer truly is the bridge between technical complexity and business value-well captured!</P> <P>Great Job Shyam!</P> <P>Cheers, Lou.</P> Thu, 08 May 2025 14:20:55 GMT https://community.databricks.com/t5/technical-blog/best-practices-for-building-the-presentation-layer-in-a/bc-p/118516#M632 Louis_Frolio 2025-05-08T14:20:55Z