Incremental rebuilds

The materialized view is a physical manifestation and summary of the original data in your table. If the source table, or multiple tables in the case of joins, changes, you likely want updates to the materialized view.

An update can occur as an incremental rebuild, or a full rebuild, of the materialized view. A full rebuild can be expensive. An incremental rebuild updates only the affected parts of the materialized view. An incremental rebuild improves the efficiency of maintaining the materialized view by decreasing rebuild step execution time. The incremental rebuild also relies on existing ACID guarantees of view tables.

The following types of incremental rebuilds occur automatically:
  • Record-based

    The delta is generated based on a snapshot of source tables taken at view creation and at rebuild time. Source tables must be full ACID (transactional) tables, which only Hive can write.

  • Partition-based

    The partitions that need rebuilding are identified, and then only those partitions are updated by an insert-overwrite. Source tables can be insert-only ACID (tranactional) tables, which Hive and Impala can write.

The materialized view must be transactional to qualify for an incremental rebuild. If the materialized view is not transactional, a full rebuild occurs.

Conditions for a record-based incremental rebuild

If compaction did not run on the source table after the last rebuild, a record-based incremental rebuild occurs under the following conditions:
  • The materialized view includes a count, sum, min, or max aggregation, and one of the source tables has an additional insert after the view is created.
  • The materialized view includes a sum aggregation, and one of the source tables has an additional insert or delete after the view is created.
  • You are using a Unified Analytics Virtual Warehouse with a Hive engine, which is the only Virtual Warehouse type that supports the incremental, records-based rebuild.
  • The materialized view is a full ACID table.

Conditions for a partition-based incremental rebuild

If compaction did not run on the source table after the last rebuild, a partitions-based incremental rebuild occurs under the following conditions:
  • The source table is an insert-only transactional tables.

  • The materialized view is an insert-only transactional table.

    If the materialized view is a full acid, a partition-based incremental rebuild is not attempted. Instead, a record-based incremental rebuild is attempted.

  • The materialized view definition includes any supported aggregation.
  • You are using a Unified Analytics Virtual Warehouse with the Hive engine.