Forum Discussion
Why does ADX caching result from related dimension table/mv/function
BabatundeDallas thanks for your input. I will comment your proposals below:
1. Caching policy has nothing to do in this case, it is only about storage setup if we want to store data in RAM/SSD or regular disks.
2. What do you mean by refreshing materialized view?
3. Yes, real-time is a must, so I would need to recreate the materialized view each few seconds with backfill of 14 days, so it won't suite here.
It, would be great if materialized view give an option to switch off subquery/lookup results...
Bartek_insight You’re correct that the caching policy doesn’t directly impact your scenario; it was just a possible suggestion for debugging.
When I mention “refreshing,” I mean updating the materialized view with fresh data. In ADX, materialized views are precomputed and stored. To refresh them, you’d typically recompute the view based on the underlying data. However, your requirement for real-time data makes frequent recomputation impractical.
Recreating the materialized view every few seconds with a 14-day backfill is inefficient because it’s resource-intensive and may not align with your use case. I understand your need for an option to control subquery/lookup results. Unfortunately, ADX doesn’t currently provide a direct switch to disable caching or force immediate refresh for subqueries.
However, I recommend exploring alternative approaches like Custom Logic, Scheduled Recomputation, or a Hybrid Approach.
1) Implement custom logic in your queries to handle real-time conditions. For example, materialized view results can be combined with fresh data using UNION or other techniques.
2) Schedule periodic recomputation of the materialized view, such as an hourly one, if feasible. This balances performance and freshness.
3) Or keep the materialized view and query the raw data directly when real-time accuracy is essential. Use the materialized view for historical data and the raw data for recent updates.
Note: ADX doesn’t offer an immediate solution to disable subquery caching. Exploring these alternatives might help you achieve your real-time requirements. I will contact the Microsoft developer team about new features that would provide ADX with a direct switch to disable caching or force immediate refresh for subqueries.
--
If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.
Thanks
BabatundeDallas thanks for your reply,
please explain what do you mean by recomputation of materialized view?Drop and recreate, or what kind of command?
Bartek_insight Recomputing a materialized view involves updating its contents based on the underlying data. This process ensures that the materialized view reflects the most recent information available. You can schedule a job to perform this task automatically. This involves scripting the drop, recreating the process, and scheduling it using ADF.
If recomputation is not a good option for you due to resource constraints or other limitations, you might need to explore alternative strategies, as mentioned in my previous message.
And also, check the https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/materialized-views/materialized-view-use-cases?WT.mc_id=%3Fwt.mc_id%3Dstudentamb_357518 for more helpful tips.
--
If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.
Thanks