how to avoid Power BI incremental refresh duplicated queries in BigQuery?

[VitorAzevedo]

I implemented a incremental refresh in a report that gets a lot of data from Google BigQuery. The issue is that each incremental refresh makes duplicated queries, and one of them is pretty expensive.

As Chris Webb's described in his article, power BI makes two queries in order to import data from a SQL database; one that returns a limited number of rows just to discover the table schema and another to get the actual data.

In many SQL servers, this only compromise performance, but in BigQuery, where the charge is made for the amount of data processed, regardless of the number of resulting rows, this first query is costing us a lot, because query folding is not taking place.

here I have some experiments with smaller datasets to show what is happening:

Notice that the "discovery query" above is processing 121.66 MB to return just one row, while the "data query" below, where the query folding is taking place, is processing just 7.35 MB.

I have implemented Chris solution modifying my M code, but BigQuery are still receiving duplicated queries. Is there any way to avoid the "discovery query"??

[DavidPROVOST]

Hi Vitor, we've had the same issue with discovery queries being issued to big query, even with the "new" Bigquery native connector. Have you found an elegant workaround ?

[VitorAzevedo · Accepted solution]

Guys, it turns out that its not possible to avoid those duplicate queries, but there is a way to clusterize BigQuery tables to reduce the amount of processed data!

For more details check this post: https://stackoverflow.com/questions/68956799/how-to-avoid-power-bi-incremental-refresh-duplicated-qu...

[v-easonf-msft]

Hi,  @VitorAzevedo 

You can see if these related tutorials can help.

incremental-refresh-in-powerbi-with-data-from-bigquery 

partitioned-tables-bigquery-power-bi-incremental-daniel-lennartsson 

Best Regards,
Community Support Team _ Eason