New Databricks SQL window fuction: counter_diff

[lessalucas]

This function was designed to work with cumulative counters in time series. It automatically calculates the difference between the current and previous value, turning a cumulative counter into a per-period variation.

Let's look at a practical example.

Imagine a factory that records the total number of parts produced every hour:

Time Cumulative Production

08:00	1,000
09:00	1,125
10:00	1,260

Using the new counter_diff function:

counter_diff(producao_acumulada) OVER (ORDER BY hora)

The result would be:

Time Cumulative Production Difference

08:00	1,000	NULL
09:00	1,125	125
10:00	1,260	135

Previously, this type of calculation could be handled using the LAG() function.

But what happens when the machine's counter is reset?

11:00 → 1,410
12:00 → 80
13:00 → 210

A simple calculation would produce:

80 - 1,410 = -1,330 ❌

counter_diff can identify this counter reset, preventing the restart from being interpreted as negative production.

It's a small feature, but a very useful one when working with data such as:

• 🏭 Industrial production

• ⚡ Energy consumption

• 💧 Water consumption

• 📊 Other cumulative time-series metrics

Note: This function requires Databricks Runtime 19+.

Documentation: https://docs.databricks.com/aws/en/sql/language-manual/functions/counter_diff

[Khasim_1]

The counter_diff function is a significant upgrade over the traditional LAG() approach for IoT and telemetry data because it is "reset-aware."

While a standard subtraction (Current - LAG(Previous)) produces a large negative number when a hardware counter resets to zero, counter_diff detects the drop and treats the new value as the start of a new sequence (returning the value itself rather than a negative delta). This eliminates the need for complex CASE WHEN logic to handle machine restarts or counter rollovers in manufacturing and energy monitoring pipelines.

Note: This function is available in Databricks Runtime 16.0+ and SQL Warehouse (Pro/Serverless)