Kusto - Materialized View on latest version of rows

Question

I have a Table called 'metadata', that contains a list of Parameter and ParamterValue that is partitioned by a TestId. Everytime a Test is changed, the Test will be reingested to Azure Data Explorer with a never Version.

My overall goal is to:

1. Define a Function (GetTestsFromSearch) that takes a Parameter (dynamic) of key value pairs, that lets me Query all Tests (Of latest version) for a match of the Key/Value pairs:

( {{"Search param1", "Search value1"},{"Search param2", "Search value2"}}

Example

GetTestsFromSearch({{"ProjectId", "SturnProject"},{"Product Name", "Nacelle "}})

Should return

TestId	Version
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	3
ea5b688c-b61f-4c5b-bb87-af2eac94d454	1

from the example metadata table below

2. Another Goal is to Create a Materialized View that contains only latest metadata for each Test (Explained below table)

Example of metadata table

TestId	TestName	Parameter	ParameterValue	Version
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	ProjectId	SturnProject	1
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Product Category	2MW	1
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Project Start Date	2022-02-03	1
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	ProjectId	SturnProject	2
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Product Category	2MW	2
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Project Start Date	2022-02-03	2
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	ProjectId	SturnProject	3
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Product Category	2MW	3
fc76aa10-5cf8-447e-95f6-3bd801ef2ed0	MyTest	Project Start Date	2022-02-03	3
ea5b688c-b61f-4c5b-bb87-af2eac94d454	MyTest	ProjectId	SturnProject	1
ea5b688c-b61f-4c5b-bb87-af2eac94d454	MyTest	Project State	Open	1
ea5b688c-b61f-4c5b-bb87-af2eac94d454	MyTest	Product Name	Nacelle	1

Over time there will be thousands of Tests in several different Versions, and hence I anticipate, that it would be a good idea to create a Materialized View, that only maintains the Latest Versions of each Test - I have tried to create the view as:

metadata
| summarize arg_max(Version,*) by TestId

But this only gives me one Parameter and Parameter Value for each TestId/Version, not the entire result set of the Test.

• Can anyone point me in the right direction for this materialized view?

I have included an example of a metadata table as DataTable, which can be used in kusto directly.

Metadata Table as DataTable

datatable (TestId: string, Name: string, Parameter: string, ParameterValue: string, Version: int) [
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Report, DMS number","1234-231",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project name","Thor3",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","GTRS reference","gtrs",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Product Category","2MW",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project number","TE-12321",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DUT responsible person","ANFRB3",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test execution person","ANFRB3",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project Manager","ANFRB3",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVPR, DMS number","1234-1234",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVRE, DMS number","1231-1213",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Start Date","2022-02-23T00:00:00.0000000Z",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Category","Verification safety",int(3),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","GTRS reference","gtrs",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project number","TE-12321",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DUT responsible person","ANFRB2",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test execution person","ANFRB2",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project Manager","ANFRB2",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVPR, DMS number","1234-1234",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVRE, DMS number","1231-1213",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Start Date","2022-02-23T00:00:00.0000000Z",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Category","Verification safety",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Product Category","2MW",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project name","Thor3",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Report, DMS number","1234-231",int(2),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Category","Verification safety",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project Manager","ANFRB",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","GTRS reference","gtrs",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Product Category","2MW",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project name","Thor3",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Project number","TE-12321",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DUT responsible person","ANFRB",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test execution person","ANFRB",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVPR, DMS number","1234-1234",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Report, DMS number","1234-231",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","DVRE, DMS number","1231-1213",int(1),
    "fc76aa10-5cf8-447e-95f6-3bd801ef2ed0","ANFRB-FILEVIEW-TEST","Test Start Date","2022-02-23T00:00:00.0000000Z",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","GTRS reference","gtrs232",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Product Category","4MW",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Project name","Myproject",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Project number","43324534",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","DUT responsible person","ANFRB",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test execution person","ANFRB",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Project Manager","ANFRB",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","DVPR, DMS number","435123454",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test Report, DMS number","123123123",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","DVRE, DMS number","12312312312",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test Start Date","2022-03-01T00:00:00.0000000Z",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test Category","Verification functionality",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test facility","CHE",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Test rig","rig23",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Sample ID","1",int(1),
    "ea5b688c-b61f-4c5b-bb87-af2eac94d454","ANFRB2-TEST","Link to test data","asdfsafdsdfa",int(1)
]

Thanks