Edit

Share via

.alter-merge table policy mirroring command

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

Changes the tables's mirroring policy. The mirroring policy creates a logical copy of tables in your database in delta parquet format and allows you to partition your files to improve query speed. Each partition is represented as a separate column using the PartitionName listed in the Partitions list. This means there are more columns in the target than in your source table.

Syntax

(.alter | .alter-merge) table TableName policy mirroring [partition by (Partitions)] dataformat = parquet [with ( propertyName = propertyValue [, ...])]

Learn more about syntax conventions.

Parameters

Name Type Required Description
TableName string ✔️ A table name that adheres to the Entity names rules.
Partitions string A comma-separated list of columns used to divide the data into smaller partitions. See Partitions formatting.
propertyName, propertyValue string A comma-separated list of key-value property pairs. See supported properties.

Partitions formatting

The partitions list is any combination of partition columns, specified using one of the forms shown in the following table.

Partition Type Syntax Notes
String column value PartitionName : string = ColumnName
Truncated datetime column (value) PartitionName : datetime = (startofyear | startofmonth | startofweek | startofday) ( ColumnName ) See documentation on startofyear, startofmonth, startofweek, or startofday functions.
Truncated Datetime Column Value = bin ( ColumnName , TimeSpan ) Read more about the bin function.

Note

Each partition is represented as a separate column using the PartitionName listed in the Partitions list. PartitionName must be a case insensitive unique string, both among other partition names and the column names of the mirrored table.

Supported properties

Name Type Description
IsEnabled bool A Boolean value that determines whether the mirroring policy is enabled. Default value is true. When the mirroring policy is disabled and set to false, the underlying mirroring data is soft-deleted and retained in the database.
TargetLatencyInMinutes int The write operation delay in minutes. By default, the write operation can take up to 3 hours or until there's 256 MB of data available. You can adjust the delay to a value between 5 minutes and 3 hours.
Backfill bool When set to true, mirroring starts from the EffectiveDateTime. If EffectiveDateTime isn't specified, all of the table data is mirrored. Default value is false.
EffectiveDateTime datetime Relevant when Backfill is set to true. If provided, mirroring starts from the specified value.

Note

When the mirroring policy is enabled, tables can't be renamed.

Examples

.alter table policy mirroring

In the following example, a table called myTable is mirrored. The data is partitioned first by name and then by date.

.alter table myTable policy mirroring
  partition by (Name: string=Name, Date: datetime= startofday(timestamp))
  dataformat=parquet
  with
  (IsEnabled=true, Backfill=true, EffectiveDateTime=datetime(2025-01-01), TargetLatencyInMinutes=180)

Related content

  • Last updated on