Materialized views limitations

This article describes materialized views limitations.

The materialized view source

  • The source table of a materialized view:
    • Must be a table into which data is directly ingested, using an update policy, or ingest from query commands.
      • Using move extents or replace extents from other tables to the source table of the materialized view is only supported if using setNewIngestionTime property as part of the move extents command (refer to .move extents and .replace extents commands for more details).
      • Moving extents to the source table of a materialized view, while not using setNewIngestionTime can cause the move to fail with one of the following errors:
        • Cannot drop/move extents from/to table 'TableName' since Materialized View 'ViewName' is currently processing some of these extents.
        • Cannot move extents to 'TableName' since materialized view 'ViewName' will not process these extents (can lead to data loss in the materialized view).
  • The source table of a materialized view must have IngestionTime policy enabled. This policy is enabled by default.
  • If the materialized view uses a default lookback, the ingestion_time() must be preserved in the materialized view’s query. Operators such as mv-expand or pivot plugin don’t preserve the ingestion_time(), so they can’t be used in a materialized view with a lookback. For more information, see Lookback period.
  • The source table of a materialized view can’t be a table with a restricted view access policy.
  • A materialized view can’t be created on top of another materialized view, unless the first materialized view is of type take_any(*) aggregation. See materialized view over materialized view.
  • Materialized views can’t be defined over external tables.

Impact of records ingested to or dropped from the source table

  • A materialized view only processes new records ingested into the source table. Records that are removed from the source table, either by running data purge/soft delete/drop extents, or due to retention policy or any other reason, have no impact on the materialized view.
  • The materialized view has its own retention policy, which is independent of the retention policy of the source table. The materialized view might include records that aren’t present in the source table.

Follower databases

  • Materialized views can’t be created in follower databases. Follower databases are read-only and materialized views require write operations.
  • Materialized views can’t be created in database shortcuts. Database shortcuts are read-only and materialized views require write operations.
  • Materialized views that are defined on leader databases can be queried from their followers, like any other table in the leader.
  • Use the leader cluster to monitor follower database materialized views. For more information, see Materialized views in follower databases.
  • Use the source Eventhouse to monitor shortcut database materialized views. For more information, see Monitor materialized views.

Other

  • Cursor functions can’t be used on top of materialized views.
  • Continuous export from a materialized view isn’t supported.

Related content