Data Imputation Techniques

In this lesson, you will learn how to mathematically reconstruct missing time-series observations using interpolation techniques directly within your database queries.

The Challenge of Missing Intervals

In high-frequency monitoring, gaps are inevitable. Whether caused by network blips or sensor dormancy, missing data points break the continuity required for rolling averages or rate-of-change calculations. Rather than discarding incomplete sequences, we use interpolation to estimate missing values based on their nearest neighbors.

Linear Interpolation in SQL

Linear interpolation assumes that the change between two known points ($y_0$ at $t_0$ and $y_1$ at $t_1$) follows a straight line. For a target timestamp $t_i$ between them, the estimated value $y_i$ is calculated as: $y_i = y_0 + (y_1 - y_0) \times \frac{t_i - t_0}{t_1 - t_0}$

In modern analytical databases supporting window functions, we can implement this by identifying the previous and next available observations.

WITH Neighbors AS (
    SELECT 
        ts, 
        val,
        LAG(ts) OVER (ORDER BY ts) as prev_ts,
        LAG(val) OVER (ORDER BY ts) as prev_val,
        LEAD(ts) OVER (ORDER BY ts) as next_ts,
        LEAD(val) OVER (ORDER BY ts) as next_val
    FROM sensor_data
)
SELECT 
    ts,
    -- Interpolate missing values (where val is NULL)
    COALESCE(val, 
        prev_val + (next_val - prev_val) * 
        ((EXTRACT(EPOCH FROM ts) - EXTRACT(EPOCH FROM prev_ts)) / 
         (EXTRACT(EPOCH FROM next_ts) - EXTRACT(EPOCH FROM prev_ts)))
    ) as imputed_val
FROM Neighbors;

Why Interpolation Matters

By filling these gaps, you prevent downstream errors in your windowing functions. If your system calculates a rate of change—which relies on the delta between sequential points—a null gap would propagate as a null result, potentially triggering false-positive alerts in your monitoring stack.

While simple linear interpolation is sufficient for many steady-state signals, be cautious with high-variance datasets. If your data exhibits rapid, non-linear spikes, consider segmenting your interpolation logic to avoid "flattening" critical anomaly signatures during the imputation process.

Key Takeaway: Interpolation bridges gaps in time-series sequences by mathematically estimating missing values based on the trajectory of surrounding data points, ensuring continuous analytical integrity.