When using Azure Data Explorer to process near real time data, it’s often important to understand how quickly or slowly the data arrives in the source table. For this post, we’ll assume that our source data has an EventTime field which denotes when the event actually happened on the source entity.
The quickest way to determine latency is to look for the latest EventTime and compare it to the current time. If you do this repeatedly, you’ll get a rough idea of how often the table is getting updated and how fresh the data is.
MyEventData
| summarize max(EventTime)
We can do a lot better than that though. In the background, Kusto is keeping track of the time that every row was ready to be queried. That information is available in the ingestion_time() scalar function. Comparing the ingestion time to the EventTime will show the lag for every row:
MyEventData
| project lag = ingestion_time() - EventTime
At this point I can run some basic aggregations like min, avg and max, but let’s do more and build a cumulative distribution function for the latency. This will tell me how much of the data arrives within X minutes of the event time.
I'll start by creating a function which calculates the cumulative distribution for a table of two values. This function uses the invoke operator which receives the source of the invoke as a tabular parameter argument.
.create-or-alter function CumulativePercentage(T:(x:real,y:real)) {
let sum = toreal(toscalar(T | summarize sum(y)));
T
| order by x asc
| summarize x=make_list(x), y=make_list(y/sum * 100)
| project x = x, y = series_iir(y, dynamic([1]), dynamic([1,-1]))
| mv-expand x to typeof(real), y to typeof(real)
}
Now we need to get our ingestion data into the format that the CumulativePercentage function requires, invoke that function and render a linechart with the results.
MyEventData
| project lag = round((ingestion_time() - EventTime)/1m, 1)
| summarize count() by lag
| project x=toreal(lag), y=toreal(count_)
| invoke CumulativePercentage()
| render linechart
Now I can see that if I wait 2.6 minutes, about 48% of the data will have arrived in Kusto. That information is handy if I’m doing manual debugging on logs, setting up a scheduled job to process to the data, or monitoring the latency of various data sources.
[Update 3/12/2019] Replaced mvexpand and makelist with the newer/preferred versions: mv-expand and make_list.