Azure Data Explorer: Working with aggregation materialized views and historical data

HI

We have the following scenario

SourceTable => Deduplication Materialized View => Multiple Aggregation Materialized views based on the deduplicated view (Daily, Weekly, Monthly)

In the source table we can backfill historical data with LightIngest so that all data sharding is handled correctly, based on a custom datetime column in our data. This custom datetime column is also a group-by-key in our materialized views(Custom Datetime, bin(CustomDateTime, 1d, ...) .

However, when we create materialized views on top of the source data, the data sharding logic is lost.

This is now based on the materialized view ingestion time and not the original data sharding anymore.

We can make sure this is handled correctly using the backfill parameter when creating the materialized view, however this does not work when we add extra historical data to the source table when the view is already created (which is a valid scenario in our case)

What would be the best approach here?

• We are talking about tens of billions of records and we need query performance to be split second.
• We have a daily data ingestion that will always be based on ingestion time, so that is never an issue, but from time to time we need to add new historical data to the source table and this is where it becomes an issue due to the volume of data.

Is adding a Partitioning Policy to the materialized views so that data sharding is still handled correctly the best approach?

However, in the documentation I read the following:

https://learn.microsoft.com/en-us/kusto/management/materialized-views/materialized-view-policies?view=microsoft-fabric#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.

Is this also a valid scenario when the Custom Datetime column is necessary for the data sharding to organize itself on?

The following text also makes me believe this is the right approach

https://learn.microsoft.com/en-us/kusto/management/partitioning-policy?view=microsoft-fabric#supported-scenarios

Out-of-order data ingestion:

• Data ingested into a table might not be ordered and partitioned into extents (shards) according to a specific datetime column that represents the data creation time and is commonly used to filter data. This could be due to a backfill from heterogeneous source files that include datetime values over a large time span.
• In this case, set the uniform range datetime partition key to be the datetime column.
• If you need retention and caching policies to align with the datetime values in the column, instead of aligning with the time of ingestion, set the OverrideCreationTime property to true.

So I would say that we create partitioning policies for each of the materialized views,

• setting the the Custom DateTime to be the uniform range datetime partition key
• setting OverrideCreationTime to true

However, I do read

Only apply a uniform range datetime partition key on a datetime-typed column in a table when data ingested into the table is unlikely to be ordered according to this column.

• https://learn.microsoft.com/en-us/kusto/management/partitioning-policy?view=microsoft-fabric#uniform-range-datetime-partition-key

Does this mean that after we have backfilled and the partitioning has completed, we delete the partitioning policy again? (because we also have daily ingestion which will be automatically partitioned correctly)

So when an extra backfilling process starts

• The Partitioning policy kicks in and processes the data correctly according to the CustomDateTime column
• We update the merge policy with a lookback to hot cache, the merge policy does it's job and merges the extents. (and make sure the data in the MV is set to hot cache)

The partitioning policy would then look like

{
  "ColumnName": "CustomDateTime",
  "Kind": "UniformRange",
  "Properties": {
    "Reference": "2021-01-01T00:00:00",
    "RangeSize": "1.00:00:00",
    "OverrideCreationTime": true
  }
}

• We would then adjust the RangeSize depending on how the data is aggregated per view (1d, 7d, Monthly)

However, when we have backfilled the historical data, do we delete the partitioning policy again, just like we delete the merge policy again after backfilling? (as stated in the documentation)

• https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-historical?tabs=during-ingestion#ingest-historical-data
• https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-historical?tabs=post-ingestion#ingest-historical-data

I think this is the right approach, but trying to see if anybody else came across this scenario in a real-life situation.

Thanks in advance!