DirectQuery stale data

[markmsc]

Hello all -- I have a DirecyQuery model atop two small (hundreds of rows) tables published to the service.  Any report page I build atop this model will not show the latest data, with the data being stale by an hour or more.  If I open the model in Desktop, the query results are current.  The data is only stale when querying the model after publishing it to the service.  As an additional bit of weirdness, only the data coming off one of the two tables is stale.  The other table's data is current with every report fetch.

I'm assuming there must be a caching setting or some other service-level configuration setting causing this.  I've turned off all such settings that I can find, to no avail.  Any searching I do for this issue results in articles and posts about DQ query performance, which isn't the issue here (data comes back off these small tables immediately; it's just not the current data).  So I turn to the community for help.  Any guidance?  I've not seen this before with other DQ models on the same database and in the same workspace.  Thank you.

EDIT: I can somewhat answer my own question.  I had a PQ column filter on an audit column in the stale table, restricting it to bringing back only rows added in the last 24 hours.  When I remove that filter, the table refreshes with current data in the model.  So now I know the cause, just not the reason those filter results appear to be cached and only refreshed periodically.  

[Poojara_D12 · Accepted solution]

Hi @markmsc 

What you're experiencing is a subtle caching behavior in Power BI Service related to DirectQuery and the way Power Query transformations are applied before the data reaches the model. Even though DirectQuery is designed to fetch live data, when you apply Power Query steps—such as filtering on a column—the service can cache query results for performance optimization, especially when the query is complex or the data is filtered. This caching can cause the data shown in reports to appear stale, updating only periodically rather than instantly, which explains why your filtered table shows outdated data while the other table without such filters returns current data immediately.

When you remove the filter on the audit column in Power Query, the query becomes simpler or more straightforward, causing the service to bypass or reduce caching, and thus data refreshes correctly with current values. The caching mechanism here is intended to reduce load on the source system and improve performance, but it can cause confusion when data freshness is critical.

Unfortunately, Power BI Service doesn’t provide direct controls to disable this caching behavior for queries involving Power Query filters on DirectQuery sources. The best practice is to minimize complex Power Query transformations on DirectQuery tables or move such filtering logic into the source database as a view or stored procedure, which ensures the service queries live data directly without intermediate caching. Alternatively, periodically refreshing the dataset or adjusting incremental refresh policies might help mitigate the effect. Understanding this nuance helps explain why only the filtered table’s data appears stale while the other remains current.

[v-karpurapud]

Hello @markmsc 

Thank you @rohit1991 for you prompt response.

Thank you for reaching out to the Microsoft Fabric community. We understand you are experiencing an issue with direct query.

Unlike Power BI Desktop, Power BI Service can apply optimizations like query folding or intermediate result caching to enhance performance, which may cause stale data issues. To address this, consider the following steps:

• Apply a relative time filter in Power BI Desktop by selecting the table visual, adding a filter on the Audit Column, and setting it to "In the last 1 day" to ensure real-time data updates.

• Query folding ensures that filtering occurs at the data source level instead of within Power BI. To verify, right-click on the filtered step in Power Query, select "View Native Query", and if it's greyed out, Power BI might be caching the result instead of applying query folding.

• If using a Shared Capacity workspace, ensure that refresh policies allow frequent queries.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you!

[v-karpurapud]

Hello @markmsc 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[markmsc]

Hello -- no, I have not. I have tried a few different ways of applying the PQ data filter and have turned off every caching setting that seems to exist, but none of it matters.  With any filter in place the DQ data is instantly delayed.  I will update this thread if I find a solution.

[v-karpurapud]

Hi @markmsc 

We are following up once again regarding your query. If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-karpurapud]

Hi @markmsc 

Thank you for your reply! Looking forward to your response.

[rohit1991]

Hi @markmsc ,

DirectQuery is built to fetch up-to-date data, but when your Power Query (PQ) transformations (like filtering on an audit column) can’t be folded all the way to the source, Power BI Service starts caching the results. This means you might see current data in Desktop, but after publishing, the Service will sometimes serve up cached results until it decides to refresh.

How to tackle it:

• Push filters to the source: Try to do as much filtering as possible in your SQL view or stored procedure, not in PQ. For your “last 24 hours” filter, see if you can do it at the database level. This makes the queries fully foldable and helps avoid caching.

• Check query folding: In Power Query, right-click each step and look for “View Native Query.” If it’s available at every step, you’re good. As soon as it greys out, that step isn’t folding, and you’re likely getting cached results in the Service.

• Dataset settings in Service: Set Query Caching to “Off” in the Power BI Service settings. If available, turn on “DirectQuery data is always up-to-date.” Consider enabling Automatic Page Refresh (APR) if your workspace allows it.

• Manual refreshes: If you’re stuck with non-foldable steps, sometimes a manual refresh is needed to force the cache to update.

Caching is mostly unavoidable if your PQ steps don’t fold, no matter the workspace or settings. The best fix is to rework your queries so everything folds to the source otherwise, stale data will pop up whenever the Service caches your filtered results.

[markmsc]

Hi Rohit, and thank you for the detailed reply.  My thinking is the same as yours, that the defualt query results for this table are being cached.  However, I've ensured that Query Caching is disabled for the published model (see screenshot below), and it has no effect.  Also I do not seem to have the "DirectQuery data is always up-to-date" setting that you mention.  Where would I find that?  Thank you.