Amazon Redshift - Views: Simple, Materialized, Late-Binding

Image Source: Datanyze

Background

One of Redshift’s key features is the ability to create different types of Views.

Views are virtual tables that are based on the result of a SQL query.

In this post, I’ll do a basic walkthrough of the three common types of views available in Redshift, and when to use each type based on the use case.

Simple Views

Simple Views are the most basic type of view in Redshift.

They are created by defining a SQL query that selects data from one or more tables, and then executing that query to create a virtual table.

Simple views are useful for simplifying complex queries and making it easier to query data from multiple tables.

Here is an example of creating a Simple View:

CREATE VIEW simple_view AS
SELECT column1, column2, column3
FROM my_table
WHERE column1 = 'value';

In this example, we are creating a Simple View named simple_view that selects three columns from a table named my_table where the value in column1 is equal to 'value'.

Simple Views are ideal for use cases where the underlying data is relatively small, or where the view is only used infrequently. They are also useful for situations where the data is constantly changing, as they will always reflect the current state of the underlying data.

Materialized Views

Materialized Views are similar to Simple Views, but they store the results of the SQL query in a physical table rather than as a virtual table.

This means that the data is stored on disk and can be queried much faster than a Simple View, which must re-run the query each time it is queried.

Here is an example of creating a Materialized View:

CREATE MATERIALIZED VIEW materialized_view AS
SELECT column1, column2, column3
FROM my_table
WHERE column1 = 'value';

In this example, we are creating a Materialized View named materialized_view that selects three columns from a table named my_table where the value in column1 is equal to 'value'.

Materialized Views are ideal for use cases where the underlying data is very large, and where the view is frequently queried. They are also useful for situations where the data changes infrequently, as the view can be refreshed on a regular schedule to ensure that it always reflects the current state of the underlying data.

To refresh a Materialized View, you can use the REFRESH MATERIALIZED VIEW command:

REFRESH MATERIALIZED VIEW materialized_view;

Late-Binding Views

Late-Binding Views are a special type of view that is optimized for use with complex SQL queries that involve many tables and complex calculations.

They are created by defining a SQL query that includes placeholders for table and column names, and then executing that query with the specific table and column names when the view is queried.

Here is an example of creating a Late-Binding View:

CREATE VIEW late_binding_view AS
SELECT *
FROM {{table1}}
JOIN {{table2}} ON table1.column1 = table2.column1
WHERE table1.column2 = {{value}}
WITH NO SCHEMA BINDING;

This creates a Late-Binding View named late_binding_view that joins two tables and filters the results based on a specific value. Note that the placeholders {{table1}}, {{table2}}, and {{value}} are used in the SQL query, and must be replaced with actual values when the view is queried. Lastly, the WITH NO SCHEMA BINDING clause specifies that the view isn’t bound to the underlying database objects, such as tables and user-defined functions.

Late-Binding Views are ideal for use cases where the underlying data is complex and frequently changing. They are also useful for situations where you want to reuse a complex query across multiple tables, as you can simply substitute the appropriate table and column names when the view is queried.

Closing thoughts 👏

So pretty much, Redshift provides three main types of views - Simple , Materialized, and Late-Binding Views that are useful for various use cases.

Simple Views are ideal for small datasets and infrequent querying.

Materialized Views are ideal for large datasets and frequent querying.

Late-Binding Views are ideal for complex SQL queries that involve multiple tables and complex calculations.

Choosing the appropriate type of view can significantly improve the performance of your queries and make it easier to work with your data.

By understanding the differences between these view types and when to use each one, you can optimize your Redshift environment and get the most value from your data.