CODEWITHSUNDEEP
snowflake-cloud-data-platform
Marius
Marius

Reputation: 208

Snowflake Materialized View Not Updating

I have materialized views in Snowflake that is not refreshing. Below is a basic example of what I'm doing.

--Create table and insert two records
CREATE OR REPLACE TABLE T1 (ID INTEGER);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2);

--Create materialized view on table
CREATE OR REPLACE MATERIALIZED VIEW VW_T1 AS SELECT ID AS AVG_ID FROM T1;

--Insert two more records after creating the materialized view
INSERT INTO T1 VALUES (3);
INSERT INTO T1 VALUES (4);

-- Show metadata
SHOW MATERIALIZED VIEWS LIKE '%T1';

No matter how long I wait, the view does not seem to be updating. The row count is always 2. Behind_by always has a value.

What am i doing wrong. I have followed the troubleshooting in the Snowflake documentation, but no success. https://docs.snowflake.com/en/user-guide/views-materialized.html#troubleshooting

Marius

Upvotes: 2

Views: 5124

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10154

This is expected behaviour. Snowflake materialized views are different than materliazed views on other databases. Two important points:

1) Materialized views are automatically and transparently maintained by Snowflake.

2) Materialized views provide always current data. If a query is run before the materialized view is up-to-date, Snowflake either updates the materialized view or uses the up-to-date portions of the materialized view and retrieves any required newer data from the base table.

So you do not need to worry about the updates. It will be updated in the background time to time (based on some criteria such as DML size, DML count, time). You can see when it's updated if you check the "refreshed_on" column on the output of SHOW command.

---------- Extra info --------------

MV keeps the data on its own data files. The SHOW command shows "when the data is refreshed", "how many rows it contains" etc... Marius saw 2 rows, because the MV had 2 rows at that point. When Marius add more rows to the source table, MV will not copy them immediately. There are some thresholds, but if you try to read from MV, MV will read the delta from source table, and provide current data all the time. The users do not need to worry about the "behind_by", "refreshed_on" or "number of rows" (unless the lag is several days).

In summary, SHOW command and MV seem working as expected.

Upvotes: 3

Related Questions