Dataflow :: How does Power BI know what to push to server?

[pbi_throw_away]

Trying to understand how dataflows really work. It seems magical that it pulls the data from the database. Since I am a skeptic, I am trying to learn how dataflows work underneath the hood. 

How does Power BI / Dataflow know that a record has been added or updated and to push that to Power BI Server?

Thanks

[ichavarria]

Hi @pbi_throw_away,

Power BI dataflows allow you to build and manage data pipelines for your Power BI reports, and they can automatically refresh your data on a schedule. Under the hood, dataflows use a combination of technologies to fetch data from various sources, transform it, and store it in a standardized format for consumption by Power BI reports.

When you create a dataflow, you define its data source, transformations, and output. Power BI uses a connector to fetch data from the source, and it applies the transformations you specify to the data before storing it in the output destination. This output destination can be a folder in Azure Data Lake Storage Gen2 or a Power BI dataset.

When data is added or updated in the source system, Power BI dataflows can use one of two mechanisms to detect these changes and refresh the data:

1. Incremental refresh: This feature enables you to only refresh the data that has changed since the last refresh. When you set up incremental refresh, you specify a column that contains a date or timestamp, and Power BI dataflows use this column to track changes in the source data. During each refresh, Power BI dataflows only fetches the new or updated data, applies the transformations, and appends it to the existing output.

2. Scheduled refresh: If you don't configure incremental refresh, Power BI dataflows refreshes all data during each refresh. You can schedule a refresh of your dataflows to occur automatically on a regular basis, such as every day or every week. When the scheduled refresh occurs, Power BI dataflows fetches all the data from the source, applies the transformations, and stores the output in the destination.

In summary, Power BI dataflows use connectors to fetch data from various sources, apply transformations to the data, and store it in a standardized format. They use either incremental or scheduled refresh to detect changes in the source data and refresh the output accordingly.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

[pbi_throw_away]

@ichavarria Thank you. One follow-up question then:

"When the scheduled refresh occurs, Power BI dataflows fetches all the data from the source, applies the transformations, and stores the output in the destination."

Would I think of this as a truncate and a full reload? So if there are 100,000,000 records, each scheduled refresh would truncate and reload alllllll of those records?

[ichavarria]

Hi @pbi_throw_away,

Do not take my word for Microsoft documentation haha, but this is my understanding yes. I have seen this behavior for multiple data sources and it happens during the "evaluation" part. If you go to the data base, you should see the query trigerring. 

I would suggest you implement Using incremental refresh with dataflows - Power Query | Microsoft Learn. That way you can avoid it from happening. 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly