An Overview of the materialized
view of Snowflake

Materialized overview

Optimizing query performance is Materialized View's (MV) main goal.

A pre-computed data set assembled from query results and information that is saved for later use is called a Materialized View. Because the data is pre-computed, querying a materialized view is faster than querying the view's underlying table. When a subject is asked frequently or is sufficiently complex, this exhibition difference can be significant. As a result, materialized views can speed up costly processes like projection, aggregation, and selection—especially when they are applied often to large data sets.

Because it has tangible information, the materialized perspective prefers to stay away from the ordinary view. While conventional wisdom makes references to its essential details. Here is a demonstration of the design for a materialized view (MV): The figure below shows how MV is built to query and link MV with the base table. You may think of a materialized view as an object that is different from a table, yet it consistently syncs with the data of its base table.

(a showcase of the below diagram)

The MV physical files are separate from the underlying table, much like a new table, as you are undoubtedly aware. To show how far away the MV is from its base table, the information stored in the MV ties the files to the source table.

Different versions of the object data are depicted in the above diagram, with MV data in the red bar and base table data in the green bar. The table version is represented by a single number below the green bar, while the MV version is represented by two numbers below the red bar. In this case, the first number denotes the MV version, and the second number in parenthesis is the base table version. For example, version 1 of MV is based on version 1 of its base table, as indicated by 1 (1) the first red MV version bar. Version 2 of MV is based on version 4 of its base table, as indicated by the second red MV version bar (version 4).

It should be noted that there is presently no direct way to query this MV. As can be observed, this is not an updated MV; there are still modifications to be done to version 5 and version 6. Furthermore, since neither the MV nor the updated MV data on the base table are refreshed, running a query on MV would yield inaccurate results. In an attempt to find a solution, we renamed the files as delta+ (a triangle shape with a plus sign, as shown in the figure above) and delta- (a triangle shape with a negative sign, as shown in the diagram above) respectively. Whereas the delta- represents the files that have been removed since the last MV refresh, the delta+ displays the files that have been added.

When performing an MV inquiry, we should concentrate on the following fundamental options at the greatest level of detail:

The last modified model query.

Including the three elements mentioned previously, a condensed query profile looks like this:

In addition to the DDL's CREATE statement, two DMLs may be used against an MV, respectively.

Refresh: Refreshing is done in the background as a Snowflake service.

Auto clustering: To enable auto clustering, add a clustering key to an MV (an additional Snowflake service that runs in the background).

It is alleged that the other two processes halt or restart an MV.

The base table's MV refresh is started by the compaction of the Stats File.

1- The number of files that have changed since the latest MV update on the base table.

2- The number of DML changes made to the base table since the previous refresh.

3- Compaction or defragmentation of statistics files in the background of Snowflake.