How to have a real-time gold table in a KQL DB, referencing internal or external tables?

[JuanMahecha]

I have a DB KQL with the bronze table receiving data in real time with information such as IDs and values, I have some master tables located in a lakehouse, which I shorcut to the DB KQL, the idea is to create a gold table that with the help of the master tables expands the information from the sensors, I have tried to use update policies and materialized views but it tells me that I cannot refer to tables external, I then tried to make a copy of the master tables within the KQL DB to reference them as an internal table, but it tells me now that I can't create an update policy to a table that has the streaming ingestion policy enabled and that also references some table, and disabling that feature means losing the feature of having and visualizing data in real time or with low latency.

So I want to know how I can get that gold table that can do joins or other transformations between internal and external tables, and update in real time for visualization of quick insights, or if at least this is possible from the queryset, or if it's time to use another tool or method.

This is the code I'm trying to get to work:

.create-or-alter function with (docstring = "Función para limpiar y transformar datos de sensores") UpdateDataFromBronce() {

    BronceDataSensores

    | join kind=inner main_sensores_internal on SensorId

    | join kind=inner master_equipo_internal on EquipoId

    | join kind=inner master_area_internal on AreaId

    | where isnotnull(SensorId) and isnotnull(Descripcion)

    and isnotnull(TipoSensor) and isnotnull(value)

    and isnotnull(EquipoId) and isnotnull(NombreEquipo)

    and isnotnull(AreaId) and isnotnull(NombreArea)

    and isnotnull(EventProcessedUtcTime)

    | project

        SensorId,

        Valor = value,

        EquipoId,

        AreaId,

        Fecha_Hora = EventProcessedUtcTime

}

.alter table GoldDataSensores policy update

@'[{ "IsEnabled": true, "Source": "BronceDataSensores", "Query": "UpdateDataFromBronce()", "IsTransactional": true,  "PropagateIngestionProperties": false }]'

I appreciate your help very much

[JuanMahecha]

I appreciate your help very much, if you could please explain to me how to do a scheduled KQL query and a materialized work to know both options would be very useful to me.
Thanks a lot

[v-tsaipranay]

Hi @JuanMahecha ,

Thank you for reaching out to the Microsoft Fabric Community.

Currently, KQL DB does not support update policies or materialized views that reference external tables (e.g., Lakehouse shortcuts), as noted in this documentation.

Additionally, when streaming ingestion is enabled, update policies cannot reference other tables, per these restrictions. Due to these platform constraints, a gold table that joins streaming data with master tables in real time is not directly supported.

As a workaround, we recommend using a scheduled KQL query or materialized function to perform the enrichment at frequent intervals or leveraging Event stream with transformation nodes to join data before ingestion. If your use case is primarily analytical, modeling the joins in Power BI using Direct Lake can also provide near real-time insights without complex ingestion logic. We're happy to help further if you’d like assistance implementing any of these approaches.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[JuanMahecha]

@v-tsaipranay 
I can't find documentation about programmed KQL or materialized function, could you explain to me how this works and how to do it, thanks you

[v-tsaipranay]

Hi @JuanMahecha ,

Thank you for your follow-up.

To schedule KQL queries, you can use Microsoft Fabric Notebooks or Data Factory Pipelines. Notebooks allow you to write and schedule KQL queries to run at regular intervals, while Data Factory can orchestrate query execution automatically.

A materialized KQL function allows you to define reusable query logic (e.g., joins) without storing data. You can call this function whenever needed for on-demand enrichment.

For more on materialized functions, see Functions - Kusto | Microsoft Learn

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[JuanMahecha]

What about of Policy ingestionbatching? i can disable policy ingestionstreaming in bronce and activate ingestionbatching? In this way the bronze table would be updated in batch and by not having the streaming ingestion policy activated I could create the update policy that updates the data in gold with the transformations I want, this would work?

[v-tsaipranay · Accepted solution]

Hello @JuanMahecha ,

Yes, by disabling streaming ingestion and enabling batch ingestion on your bronze table, you will be able to create an update policy that transforms and updates data into the gold table. The use of batch ingestion removes the restrictions imposed by streaming ingestion, allowing you to reference external tables and apply the necessary transformations. To achieve this, you would first disable streaming ingestion on the bronze table, then enable batch ingestion. Afterward, you can create an update policy on the gold table that will perform the required transformations on the incoming data.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou