Materialized views

• 1: Materialized views
• 2: Materialized views data purge
• 3: Materialized views limitations
• 4: Materialized views policies
• 5: Materialized views use cases
• 6: Monitor materialized views

1 - Materialized views

Materialized views expose an aggregation query over a source table, or over another materialized view.

Materialized views always return an up-to-date result of the aggregation query (always fresh). Querying a materialized view is more performant than running the aggregation directly over the source table.

Why use materialized views?

By investing resources (data storage, background CPU cycles) for materialized views of commonly used aggregations, you get the following benefits:

• Performance improvement: Querying a materialized view commonly performs better than querying the source table for the same aggregation function(s).

• Freshness: A materialized view query always returns the most up-to-date results, independent of when materialization last took place. The query combines the materialized part of the view with the records in the source table, which haven’t yet been materialized (the delta part), always providing the most up-to-date results.

• Cost reduction: Querying a materialized view consumes less resources than doing the aggregation over the source table. Retention policy of source table can be reduced if only aggregation is required. This setup reduces hot cache costs for the source table.

For example use cases, see Materialized view use cases.

How materialized views work

A materialized view is made of two components:

• A materialized part - a table holding aggregated records from the source table, which have already been processed. This table always holds a single record per the aggregation’s group-by combination.
• A delta - the newly ingested records in the source table that haven’t yet been processed.

Querying the materialized view combines the materialized part with the delta part, providing an up-to-date result of the aggregation query. The offline materialization process ingests new records from the delta to the materialized table, and updates existing records. If the intersection between the delta and the materialized part is large, and many records require updates, this might have a negative impact on the materialization process. See monitor materialized views on how to troubleshoot such situations.

Materialized views queries

There are 2 ways to query a materialized view:

• Query the entire view: when you query the materialized view by its name, similarly to querying a table, the materialized view query combines the materialized part of the view with the records in the source table that haven’t been materialized yet (the delta).

  • Querying the materialized view always returns the most up-to-date results, based on all records ingested to the source table. For more information about the materialized vs. non-materialized parts in materialized view, see how materialized views work.
  • This option might not perform best as it needs to materialize the delta part during query time. Performance in this case depends on the view’s age and the filters applied in the query. The materialized view query optimizer section includes possible ways to improve query performance when querying the entire view.

• Query the materialized part only: another way of querying the view is by using the materialized_view() function. This option supports querying only the materialized part of the view, while specifying the max latency the user is willing to tolerate.

  • This option isn’t guaranteed to return the most up-to-date records, but it should always be more performant than querying the entire view.
  • This function is useful for scenarios in which you’re willing to sacrifice some freshness for performance, for example for telemetry dashboards.

• Materialized views participate in cross-cluster or cross-database queries, but aren’t included in wildcard unions or searches.

  • The following examples all include materialized views by the name ViewName:

  cluster('cluster1').database('db').ViewName
  cluster('cluster1').database('*').ViewName
  database('*').ViewName
  database('DB*').ViewName
  database('*').materialized_view('ViewName')
  database('DB*').materialized_view('ViewName')
  

  • The following examples do not include records from materialized views:

  cluster('cluster1').database('db').*
  database('*').View*
  search in (*)
  search * 
  

• Materialized views participate in cross-Eventhouse or cross-database queries, but aren’t included in wildcard unions or searches.

  • The following examples all include materialized views by the name ViewName:

  cluster("<serviceURL>").database('db').ViewName
  cluster("<serviceURL>").database('*').ViewName
  database('*').ViewName
  database('DB*').ViewName
  database('*').materialized_view('ViewName')
  database('DB*').materialized_view('ViewName')
  

  • The following examples do not include records from materialized views:

  cluster("<serviceURL>").database('db').*
  database('*').View*
  search in (*)
  search * 
  

Materialized view query optimizer

When querying the entire view, the materialized part is combined with the delta during query time. This includes aggregating the delta and joining it with the materialized part.

• Querying the entire view performs better if the query includes filters on the group by keys of the materialized view query. See more tips about how to create your materialized view, based on your query pattern, in the .create materialized-view performance tips section.
• The query optimizer chooses summarize/join strategies that are expected to improve query performance. For example, the decision on whether to shuffle the query is based on number of records in delta part. The following client request properties provide some control over the optimizations applied. You can test these properties with your materialized view queries and evaluate their impact on queries performance.

ingestion_time() function in the context of materialized views

ingestion_time() function returns null values, when used in the context of a materialized view, if querying the entire view. When querying the materialized part of the view, the return value depends on the type of materialized view:

• In materialized views which include a single arg_max()/arg_min()/take_any() aggregation, the ingestion_time() is equal to the ingestion_time() of the corresponding record in the source table.
• In all other materialized views, the value of ingestion_time() is approximately the time of materialization (see how materialized views work).

Examples

1. Query the entire view. The most recent records in source table are included:

   ViewName
   

2. Query the materialized part of the view only, regardless of when it was last materialized.

   materialized_view("ViewName")
   

3. Query the entire view, and provide a “hint” to use shuffle strategy. The most recent records in source table are included:

   • Example #1: shuffle based on the Id column (similarly to using hint.shufflekey=Id):

   set materialized_view_shuffle = dynamic([{"Name" : "ViewName", "Keys" : [ "Id" ] }]);
   ViewName
   

   • Example #2: shuffle based on all keys (similarly to using hint.strategy=shuffle):

   set materialized_view_shuffle = dynamic([{"Name" : "ViewName" }]);
   ViewName
   

Performance considerations

The main contributors that can impact a materialized view health are:

• Cluster resources: Like any other process running on the cluster, materialized views consume resources (CPU, memory) from the cluster. If the cluster is overloaded, adding materialized views to it may cause a degradation in the cluster’s performance. Monitor your cluster’s health using cluster health metrics. Optimized autoscale currently doesn’t take materialized views health under consideration as part of autoscale rules.

  • The materialization process is limited by the amount of memory and CPU it can consume. These limits are defined, and can be changed, in the materialized views workload group.

• Overlap with materialized data: During materialization, all new records ingested to the source table since the last materialization (the delta) are processed and materialized into the view. The higher the intersection between new records and already materialized records is, the worse the performance of the materialized view will be. A materialized view works best if the number of records being updated (for example, in arg_max view) is a small subset of the source table. If all or most of the materialized view records need to be updated in every materialization cycle, then the materialized view might not perform well.

• Ingestion rate: There are no hard-coded limits on the data volume or ingestion rate in the source table of the materialized view. However, the recommended ingestion rate for materialized views is no more than 1-2GB/sec. Higher ingestion rates may still perform well. Performance depends on database size, available resources, and amount of intersection with existing data.

• Number of materialized views in cluster: The above considerations apply to each individual materialized view defined in the cluster. Each view consumes its own resources, and many views compete with each other on available resources. While there are no hard-coded limits to the number of materialized views in a cluster, the cluster may not be able to handle all materialized views, when there are many defined. The capacity policy can be adjusted if there is more than a single materialized view in the cluster. Increase the value of ClusterMinimumConcurrentOperations in the policy to run more materialized views concurrently.

• Materialized view definition: The materialized view definition must be defined according to query best practices for best query performance. For more information, see create command performance tips.

Materialized view over materialized view

A materialized view can be created over another materialized view if the source materialized view is a deduplication view. Specifically, the aggregation of the source materialized view must be take_any(*) in order to deduplicate source records. The second materialized view can use any supported aggregation functions. For specific information on how to create a materialized view over a materialized view, see .create materialized-view command.

Related content

• Materialized views policies
• Materialized views limitations and known issues
• Materialized views use cases
• Monitor materialized views
• .create materialized view
• .alter materialized-view
• {.disable | .enable} materialized-view

2 - Materialized views data purge

Data purge commands can be used to purge records from materialized views. The same guidelines for purging records from a table apply to materialized views purge.

The purge command only deletes records from the materialized part of the view (what is the materialized part?). Therefore, if the source table of the materialized view includes records to purge, these records may be returned from the materialized view query, even after purge completed successfully.

The recommended process for purging records from a materialized view is:

1. Purge the source table of the materialized view.
2. After the source table purge is completed successfully, purge the materialized view.

Limitations

The purge predicate of a materialized view purge can only reference the group by keys of the aggregation, or any column in a arg_max()/arg_min() /take_any() view. It cannot reference other aggregation functions result columns.

For example, for a materialized view MV, which is defined with the following aggregation function:

T | summarize count(), avg(Duration) by UserId

The following purge predicate isn’t valid, since it references the result of the avg() aggregation:

MV | where avg_Duration > 1h

Related content

• Materialized views

3 - 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

• Materialized views
• Materialized views use cases
• Monitor materialized views

4 - 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. The materialized view derives the database retention and caching policies by default. These policies can be changed using retention policy management commands or caching policy management 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 behavior occurs because the source table (delta part) also participates in the query.

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.

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

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 situation is common in multi-tenant solutions, where one of the materialized view’s group-by keys is the tenant’s identifier (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 increases the number of extents in the materialized view, and creates more “work” for the materialization process. For more information on the reason for this behavior, see the extents rebuild process mentioned in how materialized views work.

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, or when the row level security query references the group by keys of the materialized view aggregation.
• The policy is applied to the materialized part of the view only.
  • If the same row level security policy isn’t 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()/take_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 fails if there’s 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.

5 - Materialized views use cases

Materialized views expose an aggregation query over a source table or another materialized view. This article covers common and advanced use cases for materialized views.

Common use cases

The following are common scenarios that can be addressed by using a materialized view:

• Update data: Update data by returning the last record per entity using arg_max() (aggregation function). For example, create a view that only materializes records ingested from now on:

  .create materialized-view ArgMax on table T
  {
      T | summarize arg_max(Timestamp, *) by User
  }
  

• Reduce the resolution of data Reduce the resolution of data by calculating periodic statistics over the raw data. Use various aggregation functions by period of time. For example, maintain an up-to-date snapshot of distinct users per day:

  .create materialized-view UsersByDay on table T
  {
      T | summarize dcount(User) by bin(Timestamp, 1d)
  }
  

• Deduplicate records: Deduplicate records in a table using take_any() (aggregation function). For example, create a materialized view that deduplicates the source table based on the EventId column, using a lookback of 6 hours. Records are deduplicated against only records ingested 6 hours before current records.

  .create materialized-view with(lookback=6h) DeduplicatedTable on table T
  {
      T
      | summarize take_any(*) by EventId
  }
  

  [!NOTE] You can conceal the source table by creating a function with the same name as the table that references the materialized view instead. This pattern ensures that callers querying the table access the deduplicated materialized view because functions override tables with the same name. To avoid cyclic references in the view definition, use the table() function to reference the source table:

  .create materialized-view DeduplicatedTable on table T
  {
      table('T')
      | summarize take_any(*) by EventId
  }
  

For more examples, see the .create materialized-view command.

Advanced scenario

You can use a materialized view for create/update/delete event processing. For records with incomplete or outdated information in each column, a materialized view can provide the latest updates for each column, excluding entities that were deleted.

Consider the following input table named Events:

Input

Create a materialized view to get the latest update per column, using the arg_max() aggregation function:

.create materialized-view ItemHistory on table Events
{
    Events
    | extend Timestamp_col1 = iff(isnull(col1), datetime(1970-01-01), Timestamp),
                Timestamp_col2 = iff(isnull(col2), datetime(1970-01-01), Timestamp),
                Timestamp_col3 = iff(isnull(col3), datetime(1970-01-01), Timestamp)
    | summarize arg_max(Timestamp_col1, col1), arg_max(Timestamp_col2, col2), arg_max(Timestamp_col3, col3), arg_max(Timestamp, cud) by id
}

Output

You can create a stored function to further clean the results:

ItemHistory
| project Timestamp, cud, id, col1, col2, col3
| where cud != "D"
| project-away cud

Final Output

The latest update for each column for ID 1, since ID 2 was deleted.

Materialized views vs. update policies

Materialized views and update policies work differently and serve different use cases. Use the following guidelines to identify which one you should use:

• Materialized views are suitable for aggregations, while update policies aren’t. Update policies run separately for each ingestion batch, and therefore can only perform aggregations within the same ingestion batch. If you require an aggregation query, always use materialized views.

• Update policies are useful for data transformations, enrichments with dimension tables (usually using lookup operator) and other data manipulations that can run in the scope of a single ingestion.

• Update policies run during ingestion time. Data isn’t available for queries in the source table or the target table until all update policies run. Materialized views, on the other hand, aren’t part of the ingestion pipeline. The materialization process runs periodically in the background, post ingestion. Records in source table are available for queries before they’re materialized.

• Both update policies and materialized views can incorporate joins, but their effectiveness is limited to specific scenarios. Specifically, joins are suitable only when the data required for the join from both sides is accessible at the time of the update policy or materialization process. If matching entities are ingested when the update policy or materialization runs, there’s a risk of overlooking data. See more about dimension tables in materialized view query parameter and in fact and dimension tables.

Related content

• Materialized views overview
• Materialized views policies
• Materialized views limitations and known issues
• Monitor materialized views
• .create materialized view
• .alter materialized-view
• {.disable | .enable} materialized-view

6 - Monitor materialized views

Monitor the materialized view’s health in the following ways:

• Monitor materialized views metrics in the Azure portal with Azure Monitor. Use the materialized view age metric, MaterializedViewAgeSeconds, as the primary metric to monitor the freshness of the view.

• Monitor materialized view metrics in your Microsoft Fabric workspace. Use the materialized view age metric, MaterializedViewAgeSeconds as the primary metric to monitor the freshness of the view. For more information, see Enable monitoring in your workspace.

• Monitor the IsHealthy property using .show materialized-view.

• Check for failures using .show materialized-view failures.

Troubleshooting unhealthy materialized views

If the MaterializedViewAge metric constantly increases, and the MaterializedViewHealth metric shows that the view is unhealthy, follow these recommendations to identify the root cause:

• Check the number of materialized views on the cluster, and the current capacity for materialized views:

  .show capacity 
  | where Resource == "MaterializedView"
  | project Resource, Total, Consumed
  

  Output

  Resource	Total	Consumed
  MaterializedView	1	0

  • The number of materialized views that can run concurrently depends on the capacity shown in the Total column, while the Consumed column shows the number of materialized views currently running. You can use the Materialized views capacity policy to specify the minimum and maximum number of concurrent operations, overriding the system’s default concurrency level. The system determines the current concurrency, shown in Total, based on the cluster’s available resources. The following example overrides the system’s decision and changes the minimum concurrent operations from one to three:

  .alter-merge cluster policy capacity '{  "MaterializedViewsCapacity": { "ClusterMinimumConcurrentOperations": 3 } }'
  

  • If you explicitly change this policy, monitor the cluster’s health and ensure that other workloads aren’t affected by this change.

• Check if there are failures during the materialization process using .show materialized-view failures.

  • If the error is permanent, the system automatically disables the materialized view. To check if it’s disabled, use the .show materialized-view command and see if the value in the IsEnabled column is false. Then check the Journal for the disabled event with the .show journal command. An example of a permanent failure is a source table schema change that makes it incompatible with the materialized view. For more information, see .create materialized-view command.
  • If the failure is transient, the system automatically retries the operation. However, the failure can delay the materialization and increase the age of the materialized view. This type of failure occurs, for example, when hitting memory limits or with a query time-out. See the following recommendations for more ways to troubleshoot transient failures.

• Analyze the materialization process using the .show commands-and-queries command. Replace Databasename and ViewName to filter for a specific view:

  .show commands-and-queries 
  | where Database  == "DatabaseName" and ClientActivityId startswith "DN.MaterializedViews;ViewName;"
  

  • Check the memory consumption in the MemoryPeak column to identify any operations that failed due to hitting memory limits, such as, runaway queries. By default, the materialization process is limited to a 15-GB memory peak per node. If the queries or commands executed during the materialization process exceed this value, the materialization fails due to memory limits. To increase the memory peak per node, alter the $materialized-views workload group. The following example alters the materialized views workload group to use a maximum of 64-GB memory peak per node during materialization:

  .alter-merge workload_group ['$materialized-views'] ```
  {
    "RequestLimitsPolicy": {
      "MaxMemoryPerQueryPerNode": {
        "Value": 68719241216
      }
    }
  } 
  

  [!NOTE] MaxMemoryPerQueryPerNode can’t exceed 50% of the total memory available on each node.

  • Check if the materialization process is hitting cold cache. The following example shows cache statistics over the past day for the materialized view, ViewName:

  .show commands-and-queries 
  | where ClientActivityId startswith "DN.MaterializedViews;ViewName"
  | where StartedOn > ago(1d)
  | extend HotCacheHits = tolong(CacheStatistics.Shards.Hot.HitBytes), 
           HotCacheMisses = tolong(CacheStatistics.Shards.Hot.MissBytes), 
           HotCacheRetrieved = tolong(CacheStatistics.Shards.Hot.RetrieveBytes), 
           ColdCacheHits = tolong(CacheStatistics.Shards.Cold.HitBytes), 
           ColdCacheMisses = tolong(CacheStatistics.Shards.Cold.MissBytes), 
           ColdCacheRetrieved = tolong(CacheStatistics.Shards.Cold.RetrieveBytes)
  | summarize HotCacheHits = format_bytes(sum(HotCacheHits)), 
              HotCacheMisses = format_bytes(sum(HotCacheMisses)),
              HotCacheRetrieved = format_bytes(sum(HotCacheRetrieved)), 
              ColdCacheHits =format_bytes(sum(ColdCacheHits)), 
              ColdCacheMisses = format_bytes(sum(ColdCacheMisses)),
              ColdCacheRetrieved = format_bytes(sum(ColdCacheRetrieved))
  

  Output

  HotCacheHits	HotCacheMisses	HotCacheRetrieved	ColdCacheHits	ColdCacheMisses	ColdCacheRetrieved
  26 GB	0 Bytes	0 Bytes	1 GB	0 Bytes	866 MB

  * If the view isn’t fully in the hot cache, materialization can experience disk misses, significantly slowing down the process.
  
  * Increasing the caching policy for the materialized view helps avoid cache misses. For more information, see [hot and cold cache and caching policy](..//Management/Policies/Update/Update%20policy/Update%20policy.md) and [.alter materialized-view policy caching command](..//Management/Policies/Merge/.alter%20materialized-view%20policy%20merge%20command/.alter%20materialized-view%20policy%20merge%20command.md).  
  

  • Check if the materialization is scanning old records by checking the ScannedExtentsStatistics with the .show queries command. If the number of scanned extents is high and the MinDataScannedTime is old, the materialization cycle needs to scan all, or most, of the materialized part of the view. The scan is needed to find intersections with the delta. For more information about the delta and the materialized part, see How materialized views work. The following recommendations provide ways to reduce the amount of data scanned in materialized cycles by minimizing the intersection with the delta.

• If the materialization cycle scans a large amount of data, potentially including cold cache, consider making the following changes to the materialized view definition:

  • Include a datetime group-by key in the view definition. This can significantly reduce the amount of data scanned, as long as there is no late arriving data in this column. For more information, see Performance tips. You need to create a new materialized view since updates to group-by keys aren’t supported.
  • Use a lookback as part of the view definition. For more information, see .create materialized view supported properties.

• Check whether there’s enough ingestion capacity by verifying if either the MaterializedViewResult metric or IngestionUtilization metric show InsufficientCapacity values. You can increase ingestion capacity by scaling the available resources (preferred) or by altering the ingestion capacity policy.

• Check whether there’s enough ingestion capacity by verifying if the MaterializedViewResult metric shows InsufficientCapacity values. You can increase ingestion capacity by scaling the available resources.

• If the materialized view is still unhealthy, then the service doesn’t have sufficient capacity or resources to materialize all the data on time. Consider the following options:

  • Scale out the cluster by increasing the minimum instance count. Optimized autoscale doesn’t take materialized views into consideration and doesn’t scale out the cluster automatically if materialized views are unhealthy. You need to set the minimum instance count to provide the cluster with more resources to accommodate materialized views.
  • Scale out the Eventhouse to provide it with more resources to accommodate materialized views. For more information, see Enable minimum consumption.
  • Divide the materialized view into several smaller views, each covering a subset of the data. For instance, you can split them based on a high cardinality key from the materialized view’s group-by keys. All views are based on the same source table, and each view filters by SourceTable | where hash(key, number_of_views) == i, where i is part of the set {0,1,…,number_of_views-1}. Then, you can define a stored function that unions all the smaller materialized views. Use this function in queries to access the combined data.

  While splitting the view might increase CPU usage, it reduces the memory peak in materialization cycles. Reducing the memory peak can help if the single view is failing due to memory limits.

MaterializedViewResult metric

The MaterializedViewResult metric provides information about the result of a materialization cycle and can be used to identify issues in the materialized view health status. The metric includes the Database and MaterializedViewName and a Result dimension.

The Result dimension can have one of the following values:

• Success: The materialization completed successfully.

• SourceTableNotFound: The source table of the materialized view was dropped, so the materialized view is disabled automatically.

• SourceTableSchemaChange: The schema of the source table changed in a way that isn’t compatible with the materialized view definition. Since the materialized view query no longer matches the materialized view schema, the materialized view is disabled automatically.

• InsufficientCapacity: The instance doesn’t have sufficient capacity to materialize the materialized view, due to a lack of ingestion capacity. While insufficient capacity failures can be transient, if they reoccur often, try scaling out the instance or increasing the relevant capacity in the policy.

• InsufficientCapacity: The instance doesn’t have sufficient capacity to materialize the materialized view, due to a lack of ingestion capacity. While insufficient capacity failures can be transient, if they reoccur often, try scaling out the instance or increasing capacity. For more information, see Plan your capacity size.

• InsufficientResources: The database doesn’t have sufficient resources (CPU/memory) to materialize the materialized view. While insufficient resource errors might be transient, if they reoccur often, try scaling up or scaling out. For more ideas, see Troubleshooting unhealthy materialized views.

Materialized views in follower databases

Materialized views can be defined in follower databases. However, the monitoring of these materialized views should be based on the leader database, where the materialized view is defined. Specifically:

• Metrics related to materialized view execution (MaterializedViewResult, MaterializedViewExtentsRebuild) are only present in the leader database. Metrics related to monitoring (MaterializedViewAgeSeconds, MaterializedViewHealth, MaterializedViewRecordsInDelta) also appear in the follower databases.
• The .show materialized-view failures command only works in the leader database.

Track resource consumption

Materialized views resource consumption: the resources consumed by the materialized views materialization process can be tracked using the .show commands-and-queries command. Filter the records for a specific view using the following (replace DatabaseName and ViewName):

.show commands-and-queries 
| where Database  == "DatabaseName" and ClientActivityId startswith "DN.MaterializedViews;ViewName;"

Related content

• Materialized views
• Materialized views use cases