
<ul class="anchorlinks"><li><a href="#what">What is a materialized view</a></li><li><a href="#why">Why use a materialized view</a></li><li><a href="#how">How to create it</a></li><li><a href="#experiment">Experiment to win with materialized views</a></li></ul>



<p><em>We live in a world of data: there’s more of it than ever before, in a ceaselessly expanding array of forms and locations. </em><strong><em>Dealing with Data</em></strong><em> is your window into the ways Data Teams are tackling the challenges of this new world to help their companies and their customers thrive.</em></p>



<p>Data warehouses have changed the way the world deals with data. They are already deployed at countless companies across every industry and the market for these storage and computing solutions could top out at over <a href="https://www.sdcexec.com/warehousing/news/21090469/data-warehousing-market-to-exceed-30-billion-by-2025" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">$30 billion by 2025</a>. This is all a far cry from the time when storing information was the biggest challenge an organization had to overcome. Organizations did endless analysis around what machines to purchase and how to best maintain them, then struggled with hiring professionals to handle their installation and upkeep.&nbsp;</p>



<p>Today, companies can buy virtually limitless storage and computing power, without bothering with the server machines themselves. Modern cloud data engineers will never see the machines where their data and sometimes their entire code lives; their challenges now center around connecting data in the cloud with other systems to <a href="https://www.sisense.com/blog/r-vs-python-whats-the-best-language-for-natural-language-processing/" target="_blank" rel="noreferrer noopener" aria-label="generate insights from those massive stores of data (opens in a new tab)">generate insights from those massive stores of data</a>. Those insights inform business decisions, so data teams need to ensure they get into the hands of colleagues and end-user customers. Thankfully there are a variety of capabilities to increase the effectiveness and efficiency of such work.</p>



<p>Materialized views are one way to make gleaning insights and performing advanced analyses simpler, faster, and often cheaper, in an era where cloud-as-a-service is the dominant model. This article only scratches the surface of the ways materialized views of all kinds, from SQL server materialized views and Postgres materialized views to a wide array of other systems are changing analytics and data.</p>



<figure class="wp-block-image size-large"><a href="https://www.sisense.com/product/data-teams/" target="_blank" rel="noreferrer noopener"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/packages-CTA-banners_Cloud-Data-Teams.jpg" alt="packages-CTA-banners_Cloud-Data-Teams" class="wp-image-92288"/></a></figure>



<h2 class="wp-block-heading" id="what">What is a materialized view, anyway?</h2>



<p>In simplest terms, a materialized view can be thought of as the results of a query saved as a table. The name “materialized view” can be a bit confusing: The difference between a view and a materialized view is that, in a database or data warehouse, a view refers to a virtualized table representing the results of a query. A materialized view differs in that it is stored as a physical table in the database (taking up disk space in the server), where the information underlying the query can be updated as needed.&nbsp;</p>



<p>This concreteness also helps simplify more <a href="https://www.sisense.com/blog/how-to-aggregate-global-data-from-the-coronavirus-outbreak/" target="_blank" rel="noreferrer noopener" aria-label="complex data prep (opens in a new tab)">complex data prep</a> by taking the results of a query you might run multiple times (at the visualization layer) and keeping it at the ready, instead of creating it anew every time you need it. Materialized views not only improve BI performance but also allow you to consolidate business logic and maintain a source of truth.</p>



<h2 class="wp-block-heading" id="why">Why use a materialized view?</h2>



<p>In many data warehouses, users are charged per query: either through the volume of computing resources consumed which takes away from other queries, compute time, or storage scanned. If you have a huge dataset, you don’t want to run an oft-used query (one that’s referred to by multiple other charts and dashboards) over and over again. It’s much more efficient and cost-effective to prepare and aggregate the data once, through a materialized view, and query against that dataset. We tend to consider materialized views another tool at our disposal for performance boosts, with improvements akin to those we might see with setting <a href="https://www.sisense.com/blog/when-and-how-to-use-surrogate-keys/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">primary keys</a> or indexes.</p>



<p>For instance, since the materialized view lives in your data warehouse, data engineers and other cloud data experts can use SQL to clean and transform the data in-warehouse, instead of transforming it as it’s being presented in a visual layer or ingested by other analytic apps.&nbsp;</p>



<p>Beyond analytics, data scientists are often the benefactors of materialized views when building advanced statistical models to answer predictive questions, using this cleaned, prepared data for their models. Typically we see these teams use their more native languages like Python and R to clean the data, but with that being handled in a data warehouse, they can pull directly from the materialized views. These <a href="https://www.sisense.com/blog/naive-bayes-sentiment-analysis-in-python-after-preparing-data-using-sql/" target="_blank" rel="noreferrer noopener" aria-label="advanced languages (opens in a new tab)">advanced languages</a> are an organization’s gateway to machine learning applications like sentiment analysis on text (consumer reviews, social media, etc.) and other more complex tasks. With all the data prep already done in the data warehouse, there’s no need to clean the data, and these tools can be utilized for what they do best.</p>



<figure class="wp-block-image size-large"><a href="https://www.sisense.com/whitepapers/live-data-analytics-and-high-performance-data-warehouses/?gated" target="_blank" rel="noreferrer noopener"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/sisense-guide-Getting-the-Most-Out-of-Your-Digital-Transformation-Analytics-and-High-Performance-Data-Warehouses-20191203-bl-01-banner.jpg" alt="" class="wp-image-84422"/></a></figure>



<h2 class="wp-block-heading" id="how">How to create a materialized view</h2>



<p>Creating a materialized view varies from system to system, but usually involves some version of the “CREATE” command, often “CREATE MATERIALIZED VIEW.” Simple, right? From there, just insert the underlying query and pull in your results. The system will create and store the database object, refreshing the data on the schedule you dictate.</p>



<p>But what does that look like? While different databases have different <a href="https://www.sisense.com/blog/stronger-sql-and-more-essential-skills-for-data-teams-in-quarantine/" target="_blank" rel="noreferrer noopener" aria-label="syntax for SQL (opens in a new tab)">syntax for SQL</a>, here is some sample code you can use to get you started with building your own materialized view from your SalesForce data.</p>



<h3 class="wp-block-heading">Building your SQL server materialized view</h3>



<p>To build a materialized view in SQL, start with this code.</p>



<pre class="wp-block-code"><code>CREATE MATERIALIZED VIEW sales_reporting.account_roll_up AS 
select
  account.id
  , account.name
  , account.monthly_revenue * 12 as annual_revenue
  , count(opportunity.id) as total_opps
  , sum(opportunity.amount) as total_opp_revenue
  , avg(opportunity.amount) as average_opp_revenue
from
  account
  join opportunity on
    account.id = opportunity.account_id
group by
  account.id
  , account.name
  , account.mrr
[;]</code></pre>



<h2 class="wp-block-heading" id="experiment">Experiment to win with materialized views</h2>



<p>Organizations of all kinds are dealing with larger and larger datasets. Materialized views are a way to improve efficiency and cost-effectiveness when dealing with immense amounts of data and can also be useful for advanced analytics use cases. Whatever <a rel="noreferrer noopener" aria-label="data warehouse (opens in a new tab)" href="https://www.sisense.com/blog/snow-everything-about-your-warehouse/" target="_blank">data warehouse</a> you’re using and whatever you’re using it for, chances are there’s a place you can use a materialized view. Try it for yourself and see the improvements they offer.</p>



<figure class="wp-block-image size-large"><a href="https://www.sisense.com/product/data-teams/" target="_blank" rel="noreferrer noopener"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/packages-CTA-banners_Cloud-Data-Teams.jpg" alt="packages-CTA-banners_Cloud-Data-Teams" class="wp-image-92288"/></a></figure>



<p><em><strong>Chris Meier</strong> is a Manager of Analytics Engineering for Sisense and boasts 8 years in the data and analytics field, having worked at Ernst &amp; Young and Soldsie. He’s passionate about building modern data stacks that unlock transformational insights for businesses.</em></p>


A Guide to Materialized Views

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article