Materialized Views in Aws Redshift

Simple views:

Technically, the View of a table is a logical virtual copy of the table created by the “select query”, but the result is not stored anywhere in the disk. Whenever we need the data, we need to fire the query.
So, the user always gets the updated or latest data from the original tables.
The query expression is stored on the disk and not its result, so the query expression gets executed every time when the user tries to fetch the data from it so that the user will get the latest updated value every time.

Materialized views:

Materialized views are also the logical virtual copy of data−driven by the “select query”, but the result of the query will get stored in the table or disk.
The result of the query gets stored on the disk and hence the query expression does not get executed every time when user try to fetch the data so that user will not get the latest updated value if it get changed in database.

In this Data Analytics example we have a database (games_flattened_data_db) that contains two tables:

games_data: contains the results

players_data: contains the player data

The database is generated by an ETL job. The games_data table is made up of 2,000,000 records. The players_data table is made up of 1,000,000 records.

Let’s create a new schema from the data catalog. We will work on this schema.

Now let’s create a query to select all players with id “12345”.

Let’s look at the execution time.

Let’s create a materialized view.

We run the query using the materialized view.

Let’s look at the execution time.