Incremental Materialized View: Advanced

What is an Incremental Materialized View?

When a Materialized View is refreshed, the entire query is re-executed and all data is fetched again from the source. With large data volumes, this puts a heavy load on the system. The Incremental Materialized View is designed to solve this problem. It works as follows:

1. The user specifies the source table, the Incremental Key, and the Unique Key.

   1. Incremental Key (required): Each refresh records the maximum value of this column, and queries the data source for rows greater than (>) that maximum to fetch only the new data.
   2. Unique Key (optional): Used to merge data during subsequent incremental refreshes. If not specified, updates (updated rows) from the data source are ignored, and new data from the source is only appended to the Materialized View.

2. On the first fetch, just like a regular Materialized View, the entire dataset is copied, and the maximum value of the incremental key column is recorded.

   We fetch the users table, using id as the unique key and updated_at as the incremental key.

   

3. From the second refresh onward, updates are incremental.

   In the previous refresh, the recorded maximum of update_at was 2021/1/5. We fetch rows later than 2021/1/5 from the source and merge them into the existing data using id. Rows whose id does not yet exist are inserted as new rows.

info

Note: Canner Enterprise currently supports only Append and Update changes; Delete changes are not yet supported.