Materialized views limitations and known issues

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

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 may 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 lookback (see supported properties), 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() and therefore can't be used in a materialized view with a lookback.
• The source table of a materialized view can't be a table with 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.

Warning

• A materialized view will be automatically disabled by the system if changes to the source table of the materialized view, or changes in data lead to incompatibility between the materialized view query and the expected materialized view's schema.
  • To avoid this error, the materialized view query must be deterministic. For example, the bag_unpack or pivot plugins result in a non-deterministic schema.
  • When using an arg_max(Timestamp, *) aggregation and when autoUpdateSchema is false, changes to the source table can also lead to schema mismatches.
    • Avoid this failure by defining the view query as arg_max(Timestamp, Column1, Column2, ...), or by using the autoUpdateSchema option.
• Using autoUpdateSchema may lead to irreversible data loss when columns in the source table are dropped.
• Monitor automatic disable of materialized views using the MaterializedViewResult metric.
• After fixing incompatibility issues, the view should be explicitly re-enabled using the enable materialized view command.

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 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 source Eventhouse to monitor shortcut database materialized views. For more details, 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.