Encoding policy and materialized views

Question

Hello ADX experts.

What happens if the source table of a materialized view has an encoding policy set for one or more columns? Is the encoding policy preserved in the MV, or is it just ignored?

I have the following staging table defined:

.create table MetricStaging (
    MetricId: string,
    Timestamp: datetime,
    Value: real,
    Details: string
)

.alter column MetricStaging.MetricId policy encoding type = "Identifier"
.alter column MetricStaging.Details policy encoding type = "BigObject"

I use Identifier to maximize query performance, and BigObject to reduce storage size, as mentioned here.

I also have this MV for deduplication:

.create materialized-view Metrics on table MetricStaging
{
    MetricStaging
    | summarize take_any(*) by MetricId, Timestamp
}

After some trial and error, I discovered that prefixing the MV name with MV does work:

.alter column _MV_Metrics.MetricId policy encoding type = "Identifier"

Questions:

1. Is this officially supported? I can't deduce that this is a valid command from the documentation. It feels like a hack.
2. Does it actually change how the data is stored in the MV, or is it just ignored?
3. Would you recommend using a non-default encoding policy in my case?

Answer 1

When it comes to materialized views (MVs) in Azure Data Explorer (ADX), the encoding policy of the source table columns is not preserved in the MV. The encoding policies applied to the source table columns are ignored when the data is stored in the materialized view. Instead, you can set the encoding policy for the MV columns separately, which allows you to optimize the storage and query performance as needed.

Regarding your specific questions:

1. Is this officially supported? Yes, altering the encoding policy for the MV columns is supported, and it is a standard practice to optimize performance. Prefixing the MV name with "MV_" is not a hack; it is a way to differentiate the MV from other tables and ensure clarity in your schema.
2. Does it actually change how the data is stored in the MV, or is it just ignored? Setting the encoding policy for the MV does change how the data is stored in the MV. The encoding policy you apply will be effective for the data in the MV.
3. Would you recommend using a non-default encoding policy in my case? Yes, using a non-default encoding policy can be beneficial depending on your use case. For example, using "Identifier" for high cardinality columns can improve query performance, while "BigObject" can help reduce storage size for large text fields. It is advisable to evaluate your specific data access patterns and storage requirements before finalizing the encoding policies.

---

References:

• Schema optimization best practices
• Eventhouse and KQL database - Git integration