One Hybrid table with multiple sources - KQL & BigQuery

[ramiouaness]

Hello,

Let me start by saying that I'm putting this question under Power BI Service Forum, but I'm not sure if it's the right place for it.

I have a requirement where data needs to be read in real time. I have two sources available for this data:
- A BigQuery table that is refreshed daily with data from yesterday.

- An API that can provide the data in real-time but is limited to 1 hour interval (Last 1 Hour).

I already built an Event Stream that has a Python notebook source that gets the data from the API. This Python code runs every 5 seconds and gets the data from the last 4 seconds. The event stream then pushes the data into a KQL database. This currently works great!

What I want to do now is the following: Import historical data until yesterday from Bigquery, and Direct Query today's data from the real time KQL database. I want to have both results under the same table in my Power BI dataset to combine them with dimentional data and create some sort of a centralized datamart for reporting purposes.

My question is the following: Is it possible to either:
- Use Hybrid Tables (Dataflows) to Incrementally Refresh my data from BigQuery until yesterday and the rest get it from KQL in Real-Time?
- Or combine/append the data from these both sources while keeping the mixed direct query/import aspect of it?

The first scenario would be preferably for maintainability reasons.

If neither one of these scenarios is possible, what architecture/tips would you recommend to both have real-time data from an API and historical data from BigQuery and be able to combine them into one table/dataflow to be used in a centralized dataset?

In advance, thank you very much for any help.

[aj1973]

Hi @ramiouaness 

Both options can be achieved in Power BI

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

[ramiouaness]

Hello Amine,

Thank you for the reply.

However, your link explains the composite model where we would have two queries, one that's in direct query and the other in import mode. What I need is for the same table to be hybrid. Meaning that the same table would take today's data in direct query from KQL and older data up til yesterday from BigQuery.

[aj1973]

What do you mean by same Table?

You can create 2 Dataset from each source and then combine the 2 into 1 or you can merge the 2 queries in the composite dataset

[ramiouaness]

I am talking about Hybrid Tables in Power BI. One table with its historical partitions imported and its current partition in Direct Query. My question is about the possibility to point the Direct Query partition to another source that is different from the imported historical partitions.

Merging queries in Power Query where one is in DQ and the other is in Import mode is not supported because merge operations from my understanding are not supported in DQ mode.

[aj1973]

No, the Hybrid Tables can only be created out of a composite model located in a workspace with premium capacity. 

It is well explained in the link you sent me.