Urgent question about Policy update

[JuanMahecha]

Hi, I'm trying to implement the medallion architecture in RT, so I was thinking I'd have one KQL DB for bronze, one for silver, and one for gold.

The question is how I can adjust the update policy to bring the bronze transformed data to the silver table that is in its own DB.

Currently it did so as follows:

.alter table SilverDataSensores policy update

@'[{"IsEnabled": true, "Source": "Bronce2DataSensores", "Query": "UpdateGoldTable()", "IsTransactional": true}]'

So as I mention I want to have a different KQL DB for each layer, how can I reference that SilverDataSensors is in another DB, or in its defense that Soruce and Query are from the bronze DB?

Thanks 😄

[datacoffee · Accepted solution]

Hi again

You can surely read from a different database. if you want to write to a different database, then it is not pissible unless you use an Eventstream or a pipeline.
The KQL functions are built to work locally and with ability to read remote.

I hope you find a solution

[svelde · Accepted solution]

Hello @JuanMahecha ,

For table update policies, check the Azure Data Explorer documentation.

Check the note:

The source and target table must be in the same database.

But, if we check this documentation it says:

The update policy must be configured with a managed identity in the following scenario: When the update policy query references tables in other databases

As seen here, using a Managed Identity or a User Identity, the function can read changed in another source database to forward rows in a table living in the same database as the target table lives in.

[datacoffee · Accepted solution]

Hi again

You can surely read from a different database. if you want to write to a different database, then it is not pissible unless you use an Eventstream or a pipeline.
The KQL functions are built to work locally and with ability to read remote.

I hope you find a solution

[datacoffee]

Hi

you can alter/create the fundction you are using for the update policy to use cross database query.

there is a guide on learn right here

https://learn.microsoft.com/en-us/kusto/query/cross-cluster-or-database-queries?view=microsoft-fabri...

😊

[JuanMahecha]

Específicamente quiero que la tabla de destino esté en una base de datos y la tabla de origen esté en otra, esto para no tener en la misma base de datos una mezcla de tablas de bronce y otras en oro, pero leyendo la documentación menciona que esto no es posible, por lo que no se me ocurre qué hacer.
I'm trying to make the policy from the gold queryset, taking the source table as a table that receives data at the same time as the bronze table so that it works as a trigger nothing more, in the transform function I try to call the bronze table like this(database("TestDB"). Bronze2DataSensors), the only thing is that it asks me to configure the policy with managed identity and I'm looking at how to do it and thus test if this works