Nested materialized views in Amazon Redshift allow you to create materialized views based on other materialized views. This capability enables a hierarchical structure of precomputed results, significantly enhancing query performance and data processing efficiency. With nested materialized views, you can build multi-layered data abstractions, creating increasingly complex and specialized views tailored to specific business needs. This layered approach offers several advantages:
You can implement nested materialized views using the CREATE MATERIALIZED VIEW statement, which allows referencing other materialized views in the definition. Common use cases include:
Architectural diagram depicting Amazon Redshift’s nested materialized view structure. Shows multiple base tables (orange) connecting to materialized views (red), with connections to a nested view layer and data sharing table (green). Includes integration points for users and QuickSight visualization.
To demonstrate how nested materialized views work in Amazon Redshift, we’ll use the TPC-DS dataset. We’ll create three queries using the STORE, STORE_SALES, CUSTOMER, and CUSTOMER_ADDRESS tables to simulate data warehouse reports. This example will illustrate how multiple reports can share result sets and how materialized views can improve both resource efficiency and query performance.
Let’s consider the following queries as dashboard queries:
SELECT cust.c_customer_id, cust.c_first_name, cust.c_last_name, sales.ss_item_sk, sales.ss_quantity, cust.c_current_addr_sk
FROM store_sales sales
INNER JOIN customer cust ON sales.ss_customer_sk = cust.c_customer_sk;
SELECT cust.c_customer_id, cust.c_first_name, cust.c_last_name, sales.ss_item_sk, sales.ss_quantity, cust.c_current_addr_sk, store.s_store_name
FROM store_sales sales
INNER JOIN customer cust ON sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN store store ON sales.ss_store_sk = store.s_store_sk;
SELECT cust.c_customer_id, cust.c_first_name, cust.c_last_name, sales.ss_item_sk, sales.ss_quantity, addr.ca_state
FROM store_sales sales
INNER JOIN customer cust ON sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN store store ON sales.ss_store_sk = store.s_store_sk
INNER JOIN customer_address addr ON cust.c_current_addr_sk = addr.ca_address_sk;
Notice that the join between STORE_SALES and CUSTOMER tables is present at all 3 queries (dashboards). The second query adds a join with STORE table and the third query is the second one with an extra join with CUSTOMER_ADDRESS table. This pattern is common in business intelligence scenarios. As mentioned earlier, using a materialized view can speed up queries because the result set is stored and ready to be delivered to the user, avoiding reprocessing of the same data. In cases like this, we can use nested materialized views to reuse already processed data.
When transforming our queries into a set of nested materialized views, the result would be as below:
CREATE MATERIALIZED VIEW StoreSalesCust as
SELECT cust.c_customer_id, cust.c_first_name, cust.c_last_name, sales.ss_item_sk, sales.ss_store_sk, sales.ss_quantity, cust.c_current_addr_sk
FROM store_sales sales
INNER JOIN customer cust ON sales.ss_customer_sk = cust.c_customer_sk;
CREATE MATERIALIZED VIEW StoreSalesCustStore as
SELECT storesalescust.c_customer_id, storesalescust.c_first_name, storesalescust.c_last_name, storesalescust.ss_item_sk, storesalescust.ss_quantity, storesalescust.c_current_addr_sk, store.s_store_name
FROM StoreSalesCust storesalescust
INNER JOIN store store ON storesalescust.ss_store_sk = store.s_store_sk;
CREATE MATERIALIZED VIEW StoreSalesCustAddress as
SELECT storesalescuststore.c_customer_id, storesalescuststore.c_first_name, storesalescuststore.c_last_name, storesalescuststore.ss_item_sk, storesalescuststore.ss_quantity, addr.ca_state
FROM StoreSalesCustStore storesalescuststore
INNER JOIN customer_address addr ON storesalescuststore.c_current_addr_sk = addr.ca_address_sk;
Nested materialized views can improve performance and resource efficiency by reusing initial view results, minimizing redundant joins, and working with smaller result sets. This creates a hierarchical structure where materialized views depend on one another. Due to these dependencies, you must refresh the views in a specific order.
With the new option “REFRESH MATERIALIZED VIEW mv_name CASCADE” you will be able to refresh the entire chain of dependencies for the materialized views you have. Note that in this example we are using the third materialized view, StoreSalesCustAddress, and this will refresh all 3 materialized views because they are dependent on each other.
If we use the second materialized view with the CASCADE option, we will refresh only the first and second materialized views, leaving the third unchanged. This may be useful when we need to keep some materialized views with less current data than others.
The SVL_MV_REFRESH_STATUS system view reveals the refresh sequence of materialized views. When triggering a cascade refresh on StoreSalesCustAddress, the system follows the dependency chain we established: StoreSalesCust refreshes first, followed by StoreSalesCustStore, and finally StoreSalesCustAddress. This demonstrates how the refresh operation respects the hierarchical structure of our materialized views.
Consider a dependency chain where StoreSalesCust (A) → StoreSalesCustStore (B) → StoreSalesCustAddress (C).
Complete the following steps to clean up your resources:
This post showed how to create nested Amazon Redshift materialized views and refresh the child materialized views using the new REFRESH CASCADE option. You can quickly build and maintain efficient data processing pipelines and seamlessly extend the low latency query execution benefits of materialized views to data analysis.
If you’re unfamiliar with materialized views but want to boost your workload performance, Amazon Redshift offers an Automated materialized view (auto-MV) feature. This intelligent system monitors your workload and automatically creates materialized views to enhance overall performance. For more detailed information on this feature, please refer to Automated materialized views.
Ritesh Kumar Sinha is an Analytics Specialist Solutions Architect based out of San Francisco. He has helped customers build scalable data warehousing and big data solutions for over 16 years. He loves to design and build efficient end-to-end solutions on AWS. In his spare time, he loves reading, walking, and doing yoga.
Raza Hafeez is a Senior Product Manager at Amazon Redshift. He has over 13 years of professional experience building and optimizing enterprise data warehouses and is passionate about enabling customers to realize the power of their data. He specializes in migrating enterprise data warehouses to AWS Modern Data Architecture.
Ricardo Serafim is a Senior Analytics Specialist Solutions Architect at AWS. He has been helping companies with Data Warehouse solutions since 2007.