.create-or-alter materialized-view

Article
03/19/2025

Applies to: ✅ Azure Data Explorer

Creates a materialized view or alters an existing materialized view.

Permissions

You must have at least Materialized View Admin permissions to run this command.

Syntax

.create-or-alter materialized-view [ with (PropertyName = PropertyValue,...)] MaterializedViewName on table SourceTableName { Query }

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
PropertyName, PropertyValue	`string`		List of properties in the form of name and value pairs, from the list of supported properties.
MaterializedViewName	`string`	✔️	Name of the materialized view. The view name can't conflict with table or function names in the same database and must adhere to the identifier naming rules.
SourceTableName	`string`	✔️	Name of source table on which the view is defined.
Query	`string`	✔️	Query definition of the materialized view.

Supported properties

New table

If the table is new, the following properties are supported in the with(PropertyName = PropertyValue ). All properties are optional.

Name	Type	Description
backfill	`bool`	Whether to create the view based on all records currently in `SourceTable` (`true`), or to create it from now on (`false`). Default is `false`. For more information, see Backfill a materialized view.
effectiveDateTime	`datetime`	Relevant only when you're using `backfill`. If it's set, creation backfills only with records ingested after the datetime. `backfill` must also be set to `true`. This property expects a datetime literal; for example, `effectiveDateTime=datetime(2019-05-01)`.
updateExtentsCreationTime	`bool`	Relevant only when you're using `backfill`. If it's set to `true`, Extent Creation time is assigned based on the datetime group-by key during the backfill process. For more information, see Backfill a materialized view.
lookback	`timespan`	Valid only for `arg_max`/`arg_min`/`take_any` materialized views. It limits the period of time in which duplicates are expected. For example, if a lookback of 6 hours is specified on an `arg_max` view, the deduplication between newly ingested records and existing ones will take into consideration only records that were ingested up to 6 hours ago. Lookback is relative to ingestion_time(). If the materialized view query does not preserve the `ingestion_time()` value, lookback cannot be defined on the view. See materialized views limitations and known issues. Defining the lookback period incorrectly might lead to duplicates in the materialized view. For example, if a record for a specific key is ingested 10 hours after a record for the same key was ingested, and the lookback is set to 6 hours, that key will be a duplicate in the view. The lookback period is applied during both materialization time and query time.
autoUpdateSchema	`bool`	Whether to automatically update the view on source table changes. Default is `false`. This option is valid only for views of type `arg_max(Timestamp, )`/`arg_min(Timestamp, )`/`take_any()` (only when the column's argument is ``). If this option is set to `true`, changes to the source table will be automatically reflected in the materialized view.
dimensionTables	array	A dynamic argument that includes an array of dimension tables in the view. See Query parameter.
folder	`string`	The materialized view's folder.
docString	`string`	A string that documents the materialized view.
allowMaterializedViewsWithoutRowLevelSecurity	`bool`	Allows creating a materialized view over a table with row level security policy enabled.

Existing table

If the table already exists, only the following subset of properties are supported in the with(PropertyName = PropertyValue ). All properties are optional.

Name	Type	Description
lookback	`timespan`	The time span that limits the period during which duplicates or updates are expected. For more information, see Lookback period.
lookback_column	`string`	A `string` column in the view that serves as the reference for the lookback period. If the `lookback_column` isn't defined, the lookback period is calculated based on ingestion_time(). If a `lookback_column` is already defined, its value can't be modified. For more information, see Lookback period.
autoUpdateSchema	`bool`	Whether to automatically update the view on source table changes. Default is `false`. This option is valid only for views of type `arg_max(Timestamp, )`/`arg_min(Timestamp, )`/`take_any()` (only when the column's argument is ``). If this option is set to `true`, changes to the source table are automatically reflected in the materialized view.
--	--	--
folder	`string`	The materialized view's folder.
docString	`string`	A string that documents the materialized view.

Returns

Output schema:

Name	Type	Description
Name	`string`	Name of the materialized view.
SourceTable	`string`	Name of source table on which the view is defined.
Query	`string`	Query definition of the materialized view.
MaterializedTo	`datetime`	Maximum materialized ingestion_time() timestamp in source table. For more information, see how materialized views work.
LastRun	`datetime`	Last time materialization was run.
LastRunResult	`string`	Result of last run. Returns `Completed` for successful runs, otherwise `Failed`.
IsHealthy	`bool`	`true` when view is considered healthy, `false` otherwise. View is considered healthy if it was successfully materialized up to the last hour (`MaterializedTo` is greater than `ago(1h)`).
IsEnabled	`bool`	`true` when view is enabled (see Disable or enable materialized view).
Folder	`string`	Folder under which the materialized view is created.
DocString	`string`	Description assigned to the materialized view.
AutoUpdateSchema	`bool`	Whether the view is enabled for auto updates.
EffectiveDateTime	`datetime`	Effective date time of the view, determined during creation time (see `.create materialized-view`).
Lookback	`timespan`	Time span limiting the period of time in which duplicates are expected.

Examples

The examples in this section show how to use the syntax to help you get started.

Create or alter a materialized view

The following example creates a new materialized view or alters an existing one called ArgMax that's based on table T. It contains the most recent record based on the Timestamp column, for each user from table T.

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

Output

Name	SourceTable	Query	MaterializedTo	LastRun	LastRunResult	IsHealthy	IsEnabled	Folder	DocString	AutoUpdateSchema	EffectiveDateTime	Lookback
ArgMax	T	T \| summarize arg_max(Timestamp, *) by User	2023-02-26T16:40:03.3345704Z	2023-02-26T16:44:15.9033667Z	Completed	true	true			false	2023-02-23T14:01:42.5172342Z

Remarks

If the materialized view doesn't exist, this command behaves just like .create materialized-view.

For more information, see the Query parameter and Properties sections.

Limitations

Changes not supported:
- Changes to the materialized view group by expressions.
- Changing column type.
- Renaming columns. For example, altering a view of T | summarize count() by Id to T | summarize Count=count() by Id drops column count_ and creates a new column Count, which initially contains nulls only.
Impact on existing data:
- Altering the materialized view has no impact on existing data.
- Adding filters to the query applies only to newly ingested records, and doesn't change records that have already been materialized.
  - New columns receive nulls for all existing records until records ingested after the alter command modify the null values.
  - For example: A view of T | summarize count() by bin(Timestamp, 1d) is altered to T | summarize count(), sum(Value) by bin(Timestamp, 1d). For a particular Timestamp=T for which records have already been processed before altering the view, the sum column contains partial data. This view only includes records processed after the alter execution.