Refresh failed full data and success with 2 weeks

Thankyou, Deku, for your response.

Hi ahmedshalabyy12,

We appreciate your question on the Microsoft Fabric Community Forum.

From what I understand, the Power BI refresh worked for only 2 weeks because during that time, the data volume and number of API calls made to Azure Databricks were limited. When you enabled incremental refresh to include 3 years of data, Power BI started sending many parallel queries (one for each partition or date range) to Databricks. This increased the authentication load on the gateway. If the credentials or tokens are not handled properly for each query, it causes the error:
DM_GWPipeline_Gateway_InvalidConnectionCredentials — AccessUnauthorized.

This error can also happen if a large data pull (like 3 years) causes the token to expire or if the Databricks SQL warehouse times out or blocks the long query. That is why a smaller period (2 weeks) works but the full dataset does not.

Please follow these steps which may help fix the issue:

1. If you are using OAuth, long queries might fail because tokens expire. It is better to use a Service Principal or a long-lived Personal Access Token (PAT). Re-enter your credentials in Power BI Service > Settings > Data source credentials.
2. Optimize your SQL Warehouse for large queries. Make sure the Databricks SQL Warehouse has a big enough cluster (use a higher tier if needed), longer query timeouts, auto-stop turned off, and enough concurrency.
3. Adjust Incremental Refresh Settings by going to Modeling > Incremental Refresh in Power BI Desktop. Set it to store data for 3 years but refresh only the recent 1 month. This reduces load and avoids pulling all the data at once.
4. Make sure your on-premises data gateway cluster is working well. Try restarting the gateway.

You can see the troubleshooting step for the error in the screenshot attached below:

Also, here are some helpful links:
Connect Power BI to Azure Databricks - Azure Databricks | Microsoft Learn
Create a SQL warehouse - Azure Databricks | Microsoft Learn

If you find this response useful, please mark it as the accepted solution and give kudos. This will help other community members with similar questions.

If you have any more questions, please feel free to ask the Microsoft Fabric community.

Thank you.

You can also use incremental refresh policy. Apply policy but don't refresh in service. You can manually apply the policy once you have deployed to service with tabular editor, then manually refresh each partition one by one with ssms to reduce the load