Incremental Refresh Hybrid Table Dataset won't update via Cloud Connection, only through Local Gatew

[dlopesc04]

So I have a Model with Incremental Refresh and Hybrid Tables enabled (I'm using PPU), when publishing it to the PBI Service it works fine, When using this connection:

But I noticed that when using this one the latest data isn't fetched with Direct Query, I Have Automatic Page update Set up and it does refresh the visuals, but the latest data only shows up every 30 minutes after the scheduled refresh.

I assume then it is because I'm using a Local on Premises Gateway (Installed on the company server), I then switch to this connection:

And this connection works fine with smalled datasets (That I've created to test this, fetching a small amount of data from the same DataBase but without Incremental Refresh), but when using it with the larger dataset it always returns this error after some 20 minutes:

[ValidateMarkupTags][ccon]DataSource.Error: An error happened while reading data from the provider: '[ccon]The remote certificate is invalid according to the validation procedure.[/ccon]'. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]DBNAME.rds.amazonaws.com;postgres[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface.

Does anyone know how to fix this error? Or if it just ins't possible to actually get the latest data with direct query via hybrid tables and this was all for nothing?

Further details: I'm using a premium workspace, Only I have access to it and I have not shared this report with anyone, Since I'm fetching data from a AWS RDS PostgreSQL DataBase I had to configure the npgSQL drivers and I'm using the correct not updated version, All my reports work fine, Only import Mode or Only Direct Query mode, all just fine, it is just this one that has the partition that isn't actually fetching data in Direct Query mode. I've checked with SSMS and all the partition were created OK, only the DQ one doesn't seem to work. That looks like this:

 

[v-cgao-msft]

Hi @dlopesc04 ,

--but when using it with the larger dataset it always returns this error after some 20 minutes.
Try configuring the CommandTimeout parameter to be greater than 20 minutes.
PostgreSQL.Database - PowerQuery M | Microsoft Learn

-- But I noticed that when using this one the latest data isn't fetched with Direct Query, I Have Automatic Page update Set up and it does refresh the visuals, but the latest data only shows up every 30 minutes after the scheduled refresh.

 Try changing the storage mode to Dual

Troubleshoot incremental refresh and real-time data in Power BI - Power BI | Microsoft Learn
Automatic page refresh in Power BI Desktop - Power BI | Microsoft Learn

The incremental refresh in Hybrid mode is a bit more specific, please refer here:
Hybrid Tables, Incremental Refresh and Table Partitioning in Power BI – Paul Turley's SQL Server BI ...

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[dlopesc04]

Hi! Thank you for your reply.

I have two tables in hybrid mode and no tables related to these, so there is no table to put in Dual mode. The tables in Hybrid mode have to be in import mode in Power BI Desktop.

Also the timeout is correctly configured, it is not a timeout problem.

From all I could gather my hybrid tables are correctly configured, yet the latest data is never fetched automatically, and only shows after a scheduled or manual refresh.