Edit

.alter-merge table policy update command

Switch services using the Version drop-down list. Learn more about navigation.
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

Changes the table's update policy. The update policy simplifies the process of syncing and updating data between two tables. When new data is inserted into the source table, a transformation query runs over this data to modify and insert the data into the target table.

Note

  • The source table and the table for which the update policy is defined must be in the same database.
  • The update policy function schema and the target table schema must match in their column names, types, and order.

Permissions

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

Syntax

.alter-merge table [ DatabaseName .]TableName policy update ArrayOfPolicyObjects

Learn more about syntax conventions.

Parameters

Name Type Required Description
DatabaseName string The name of the database. When you run the command from the database context that contains the table to alter, DatabaseName isn't required.
TableName string ✔️ The name of the table. A wildcard, *, denotes all tables.
ArrayOfPolicyObjects string ✔️ A serialized array of policy objects. For more information, see update policy.

Returns

Returns a JSON representation of the policy.

Warning

If an update policy is already defined on the table, the PolicyObjects specified in the command are added to the array of PolicyObjects in the existing policy. Entries aren't deduplicated, so if the exact same PolicyObject already exists in the current update policy, another entry will be added, potentially causing duplicates during ingestion time.

Example

The following command changes the update policy for a table using multi-line string literals.

.alter-merge table MyDatabase.MyTable policy update
```
[
    {
        "IsEnabled": true,
        "Source": "MyTableX",
        "Query": "MyOtherTable",
        "IsTransactional": true,
        "PropagateIngestionProperties": false
    }
]
```

Example with an accelerated external table reference

The following example adds an update policy entry whose query joins data from an accelerated external table. Because the external table uses impersonation authentication, the policy must include a ManagedIdentity:

.alter-merge table MyDatabase.MyTargetTable policy update
```
[
    {
        "IsEnabled": true,
        "Source": "MySourceTable",
        "Query": "MySourceTable | join kind=leftouter (external_table('MyAcceleratedExternalTable')) on CommonKey | project-away CommonKey1",
        "IsTransactional": true,
        "PropagateIngestionProperties": false,
        "ManagedIdentity": "system"
    }
]
```

Example with an accelerated external table reference

The following example adds an update policy entry whose query joins data from an accelerated external table. In Fabric, the system automatically populates the OwnerPrincipalDetails property for authorization:

.alter-merge table MyDatabase.MyTargetTable policy update
```
[
    {
        "IsEnabled": true,
        "Source": "MySourceTable",
        "Query": "MySourceTable | join kind=leftouter (external_table('MyAcceleratedExternalTable')) on CommonKey | project-away CommonKey1",
        "IsTransactional": true,
        "PropagateIngestionProperties": false
    }
]
```

Note

When the policy is read back, the returned JSON may include a system-populated OwnerPrincipalDetails property. This property captures the identity of the user who sets or alters the update policy, and is used by the system for authorization when the query references external tables. The property is read-only and set automatically.

  • Last updated on