How to reset Eventhouse Ingestion from Batching to Streaming ?

Hi lavginqo ,

Based on your findings, the root cause is the update policy on the PROD table using the ingestion_time() function, which is not supported with streaming ingestion. Update policy queries execute after ingestion and are subject to streaming ingestion restrictions, therefore ingestion‑context functions such as ingestion_time(), which are evaluated only during ingestion, are not available in update policies. When such an incompatibility is detected, the engine automatically routes ingestion through the batching path to ensure correctness, and this will persist regardless of re enabling the streaming ingestion policy. This also explains why your DEV environment continues to work as expected, since it does not have the same update policy defined.

To restore low latency streaming ingestion, the recommended approach is to modify or remove the update policy so that it no longer uses ingestion_time(). As a best practice, you can separate concerns by keeping the landing table optimized for streaming without update policies or unsupported functions and applying any transformation logic, including time based calculations, in downstream tables or processes. For validation, you may temporarily disable the update policy in PROD and re enable streaming ingestion to confirm that latency returns to expected levels. Once confirmed, you can redesign the update logic in a way that is compatible with streaming ingestion.

Hope this helps.
Thank you.

Hi arabalca,

We found a difference in the update policy in Prod. 

There was an :  order by ingestion_time() desc in the update policy which was apparently adding to the delay

After removing that piece of code- the latency is now gone.

But your response was very very helpful to help us find the problem using the ingestion failure query.

Thanks