Materialized views policies

This article includes information about policies that can be set on materialized views.

Retention and caching policy

A materialized view has a retention policy and caching policy, like any Azure Data Explorer table. The materialized view derives the database retention and caching policies by default. These policies can be changed using retention policy control commands or caching policy control commands.

Both policies are applied on the materialized part of the materialized view only. For an explanation of the differences between the materialized part and delta part, see how materialized views work. For example, if the caching policy of a materialized view is set to 7d, but the caching policy of its source table is set to 0d, there may still be disk misses when querying the materialized view. This will happen because the source table (delta part) participates in the query as well.

The retention policy of the materialized view is unrelated to the retention policy of the source table. Retention policy of source table can be shorter than the retention policy of the materialized view, if source records are required for a shorter period. We recommend a minimum retention policy of at least few days, and recoverability set to true on the source table. This setting allows for fast recovery for errors and for diagnostic purposes.


Zero retention policy on the source table isn't supported.

The retention and caching policies both depend on Extent Creation time. The extent creation time for a materialized view is determined by the last update for a record.


The materialization process attempts to minimize the amount of updates to the materialized part of the view. In cases where a record doesn't have to be updated in the view, it won't be updated. For example, when the materialized view is an any(*) aggregation, new records of same group-by keys won't be re-ingested into the view, and therefore the retention policy would be by earliest record ingested.

Partitioning policy

A partitioning policy can be applied on a materialized view. We recommend configuring a partitioning policy on a materialized view only when most or all of the view queries filter by one of the materialized view's group-by keys. This is common in multi-tenant solutions, where one of the materialized view's group-by keys is the tenant's identifer (for example, tenantId, customerId). For more information, see the first use case described in the partitioning policy supported scenarios page.

For the commands to alter a materialized view's partitioning policy, see partitioning policy commands.

Adding a partitioning policy on a materialized view will increase the number of extents in the materialized view, and will create more "work" for the materialization process. For more information on why this happens, see the extents rebuild process mentioned in how materialized views work). In EngineV3 clusters, this process is much more efficient than in V2. Therefore, we recommend to only add a partitioning policy on a materialized view only if the cluster is a V3 cluster.

Row level security policy

A row level security can be applied on a materialized view, with several limitations:

  • The policy can be applied only to materialized views with arg_max()/arg_min()/take_any() aggregation functions.
  • The policy is applied to the materialized part of the view only.
    • If the same row level security policy is not defined on the source table of the materialized view, then querying the materialized view may return records that should be hidden by the policy. This happens because querying the materialized view queries the source table as well.
    • We recommend defining the same row level security policy both on the source table and the materialized view if the view is an arg_max() or arg_min()/any().
  • When defining a row level security policy on the source table of an arg_max() or arg_min()/take_any() materialized view, the command will fail if there is no row level security policy defined on the materialized view itself. The purpose of the failure is to alert the user of a potential data leak, since the materialized view may expose information. To mitigate this error, do one of the following actions:
    • Define the row level security policy over the materialized view.
    • Choose to ignore the error by adding allowMaterializedViewsWithoutRowLevelSecurity property to the alter policy command. For example:
    .alter table SourceTable policy row_level_security enable with (allowMaterializedViewsWithoutRowLevelSecurity=true) "RLS_function"

For commands for configuring a row level security policy on a materialized view, see row_level_security policy commands.