Estadísticas de escalamiento: desviación estándar incremental en SQL con dbt | de Yuval Gorchover | enero de 2025

¿Por qué escanear los datos de ayer cuando puedes incrementar los de hoy?

Imagen del autor

Las funciones de agregación SQL pueden resultar costosas desde el punto de vista computacional cuando se aplican a grandes conjuntos de datos. A medida que los conjuntos de datos crecen, volver a calcular las métricas de todo el conjunto de datos repetidamente se vuelve ineficiente. Para abordar este desafío, agregación incremental A menudo se emplea: un método que implica mantener un estado anterior y actualizarlo con nuevos datos entrantes. Si bien este enfoque es sencillo para agregaciones como COUNT o SUM, surge la pregunta: ¿cómo se puede aplicar a métricas más complejas como la desviación estándar?

Desviación estándar Es una métrica estadística que mide el grado de variación o dispersión de los valores de una variable en relación con su media.
Se obtiene sacando la raíz cuadrada de diferencia.
La fórmula para calcular la varianza de una muestra es la siguiente:

Fórmula de variación de muestra

Calcular la desviación estándar puede ser complejo, ya que implica actualizar tanto la media como la suma de las diferencias al cuadrado en todos los puntos de datos. Sin embargo, con la manipulación algebraica, podemos derivar una fórmula para el cálculo incremental, lo que permite actualizaciones utilizando un conjunto de datos existente e incorporando nuevos datos sin problemas. Este enfoque evita volver a calcular desde cero cada vez que se agregan nuevos datos, lo que hace que el proceso sea mucho más eficiente (hay disponible una derivación detallada en mi GitHub).

Fórmula de varianza muestral derivada

La fórmula se dividió básicamente en 3 partes:
1. La varianza ponderada establecida existente
2. La varianza ponderada del nuevo conjunto.
3. La varianza de la diferencia de medias, que representa la varianza entre grupos.

Este método permite el cálculo de la varianza incremental reteniendo el COUNT (k), AVG (μk) y VAR (Sk) del conjunto existente, y combinándolos con el COUNT (n), AVG (μn) y VAR (Sn) de el nuevo conjunto. Como resultado, la desviación estándar actualizada se puede calcular de manera eficiente sin volver a escanear todo el conjunto de datos.

Ahora que hemos comprendido las matemáticas detrás de la desviación estándar incremental (o al menos hemos captado su esencia), profundicemos en la implementación de dbt SQL. En el siguiente ejemplo, veremos cómo configurar un modelo incremental para calcular y actualizar estas estadísticas para los datos de transacciones de un usuario.

Considere una tabla de transacciones llamada stg__transaccionesque rastrea las transacciones del usuario (eventos). Nuestro objetivo es crear una tabla temporal estática, int__usuario_tx_stateque agrega el “estado” de las transacciones de los usuarios. Los detalles de las columnas de ambas tablas se proporcionan en la siguiente imagen.

Imagen del autor

Para que el proceso sea eficiente, nuestro objetivo es actualizar la tabla de estado de forma incremental combinando los datos de las nuevas transacciones entrantes con los datos agregados existentes (es decir, el estado actual del usuario). Este enfoque nos permite calcular el estado actualizado del usuario sin escanear todos los datos históricos.

Imagen del autor

El siguiente código supone la comprensión de algunos conceptos de dbt; si no está familiarizado con él, es posible que aún pueda comprender el código, aunque le recomiendo encarecidamente que lo revise guía incremental de dbt o leer esta publicación increíble.

Construiremos un SQL dbt completo paso a paso, con el objetivo de calcular agregaciones incrementales de manera eficiente sin escanear repetidamente toda la tabla. El proceso comienza definiendo el modelo como incremental en dbt y usando unique_key para actualizar las filas existentes en lugar de insertar otras nuevas.

-- depends_on: {{ ref('stg__transactions') }}
{{ config(materialized='incremental', unique_key=['USER_ID'], incremental_strategy='merge') }}

A continuación, recuperamos registros del stg__transacciones mesa.
El is_incremental bloquear filtra transacciones con marcas de tiempo posteriores a la última actualización del usuario, incluyendo efectivamente “solo transacciones nuevas”.

WITH NEW_USER_TX_DATA AS (
SELECT
USER_ID,
TX_ID,
TX_TIMESTAMP,
TX_VALUE
FROM {{ ref('stg__transactions') }}
{% if is_incremental() %}
WHERE TX_TIMESTAMP > COALESCE((select max(UPDATED_AT) from {{ this }}), 0::TIMESTAMP_NTZ)
{% endif %}
)

Después de recuperar los nuevos registros de transacciones, los agregamos por usuario, lo que nos permite actualizar incrementalmente el estado de cada usuario en los siguientes CTE.

INCREMENTAL_USER_TX_DATA AS (
SELECT
USER_ID,
MAX(TX_TIMESTAMP) AS UPDATED_AT,
COUNT(TX_VALUE) AS INCREMENTAL_COUNT,
AVG(TX_VALUE) AS INCREMENTAL_AVG,
SUM(TX_VALUE) AS INCREMENTAL_SUM,
COALESCE(STDDEV(TX_VALUE), 0) AS INCREMENTAL_STDDEV,
FROM
NEW_USER_TX_DATA
GROUP BY
USER_ID
)

Ahora llegamos a la parte pesada donde necesitamos calcular las agregaciones. Cuando no estamos en modo incremental (es decir, todavía no tenemos filas de “estado”) simplemente seleccionamos las nuevas agregaciones.

NEW_USER_CULMULATIVE_DATA AS (
SELECT
NEW_DATA.USER_ID,
{% if not is_incremental() %}
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_DATA.INCREMENTAL_COUNT AS COUNT_TX,
NEW_DATA.INCREMENTAL_AVG AS AVG_TX,
NEW_DATA.INCREMENTAL_SUM AS SUM_TX,
NEW_DATA.INCREMENTAL_STDDEV AS STDDEV_TX
{% else %}
...

Pero cuando estamos en modo incremental, necesitamos unir datos anteriores y combinarlos con los nuevos datos que creamos en el INCREMENTAL_USER_TX_DATA CTE basado en la fórmula descrita anteriormente.
Comenzamos calculando la nueva SUMA, CONTEO y AVG:

  ...
{% else %}
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) AS _n, -- this is n
NEW_DATA.INCREMENTAL_COUNT AS _k, -- this is k
COALESCE(EXISTING_USER_DATA.SUM_TX, 0) + NEW_DATA.INCREMENTAL_SUM AS NEW_SUM_TX, -- new sum
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) + NEW_DATA.INCREMENTAL_COUNT AS NEW_COUNT_TX, -- new count
NEW_SUM_TX / NEW_COUNT_TX AS AVG_TX, -- new avg
...

Luego calculamos las tres partes de la fórmula de varianza.

1. La varianza ponderada existente, que se trunca a 0 si el conjunto anterior está compuesto por uno o menos elementos:

    ...
CASE
WHEN _n > 1 THEN (((_n - 1) / (NEW_COUNT_TX - 1)) * POWER(COALESCE(EXISTING_USER_DATA.STDDEV_TX, 0), 2))
ELSE 0
END AS EXISTING_WEIGHTED_VARIANCE, -- existing weighted variance
...

2. La varianza ponderada incremental de la misma forma:

    ...
CASE
WHEN _k > 1 THEN (((_k - 1) / (NEW_COUNT_TX - 1)) * POWER(NEW_DATA.INCREMENTAL_STDDEV, 2))
ELSE 0
END AS INCREMENTAL_WEIGHTED_VARIANCE, -- incremental weighted variance
...

3. La varianza de la diferencia media, como se describió anteriormente, junto con los términos de unión de SQL para incluir datos anteriores.

    ...
POWER((COALESCE(EXISTING_USER_DATA.AVG_TX, 0) - NEW_DATA.INCREMENTAL_AVG), 2) AS MEAN_DIFF_SQUARED,
CASE
WHEN NEW_COUNT_TX = 1 THEN 0
ELSE (_n * _k) / (NEW_COUNT_TX * (NEW_COUNT_TX - 1))
END AS BETWEEN_GROUP_WEIGHT, -- between group weight
BETWEEN_GROUP_WEIGHT * MEAN_DIFF_SQUARED AS MEAN_DIFF_VARIANCE, -- mean diff variance
EXISTING_WEIGHTED_VARIANCE + INCREMENTAL_WEIGHTED_VARIANCE + MEAN_DIFF_VARIANCE AS VARIANCE_TX,
CASE
WHEN _n = 0 THEN NEW_DATA.INCREMENTAL_STDDEV -- no "past" data
WHEN _k = 0 THEN EXISTING_USER_DATA.STDDEV_TX -- no "new" data
ELSE SQRT(VARIANCE_TX) -- stddev (which is the root of variance)
END AS STDDEV_TX,
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_SUM_TX AS SUM_TX,
NEW_COUNT_TX AS COUNT_TX
{% endif %}
FROM
INCREMENTAL_USER_TX_DATA new_data
{% if is_incremental() %}
LEFT JOIN
{{ this }} EXISTING_USER_DATA
ON
NEW_DATA.USER_ID = EXISTING_USER_DATA.USER_ID
{% endif %}
)

Finalmente, seleccionamos las columnas de la tabla, teniendo en cuenta tanto los casos incrementales como los no incrementales:

SELECT
USER_ID,
UPDATED_AT,
COUNT_TX,
SUM_TX,
AVG_TX,
STDDEV_TX
FROM NEW_USER_CULMULATIVE_DATA

Combinando todos estos pasos, llegamos al modelo SQL final:

-- depends_on: {{ ref('stg__initial_table') }}
{{ config(materialized='incremental', unique_key=['USER_ID'], incremental_strategy='merge') }}
WITH NEW_USER_TX_DATA AS (
SELECT
USER_ID,
TX_ID,
TX_TIMESTAMP,
TX_VALUE
FROM {{ ref('stg__initial_table') }}
{% if is_incremental() %}
WHERE TX_TIMESTAMP > COALESCE((select max(UPDATED_AT) from {{ this }}), 0::TIMESTAMP_NTZ)
{% endif %}
),
INCREMENTAL_USER_TX_DATA AS (
SELECT
USER_ID,
MAX(TX_TIMESTAMP) AS UPDATED_AT,
COUNT(TX_VALUE) AS INCREMENTAL_COUNT,
AVG(TX_VALUE) AS INCREMENTAL_AVG,
SUM(TX_VALUE) AS INCREMENTAL_SUM,
COALESCE(STDDEV(TX_VALUE), 0) AS INCREMENTAL_STDDEV,
FROM
NEW_USER_TX_DATA
GROUP BY
USER_ID
),

NEW_USER_CULMULATIVE_DATA AS (
SELECT
NEW_DATA.USER_ID,
{% if not is_incremental() %}
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_DATA.INCREMENTAL_COUNT AS COUNT_TX,
NEW_DATA.INCREMENTAL_AVG AS AVG_TX,
NEW_DATA.INCREMENTAL_SUM AS SUM_TX,
NEW_DATA.INCREMENTAL_STDDEV AS STDDEV_TX
{% else %}
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) AS _n, -- this is n
NEW_DATA.INCREMENTAL_COUNT AS _k, -- this is k
COALESCE(EXISTING_USER_DATA.SUM_TX, 0) + NEW_DATA.INCREMENTAL_SUM AS NEW_SUM_TX, -- new sum
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) + NEW_DATA.INCREMENTAL_COUNT AS NEW_COUNT_TX, -- new count
NEW_SUM_TX / NEW_COUNT_TX AS AVG_TX, -- new avg
CASE
WHEN _n > 1 THEN (((_n - 1) / (NEW_COUNT_TX - 1)) * POWER(COALESCE(EXISTING_USER_DATA.STDDEV_TX, 0), 2))
ELSE 0
END AS EXISTING_WEIGHTED_VARIANCE, -- existing weighted variance
CASE
WHEN _k > 1 THEN (((_k - 1) / (NEW_COUNT_TX - 1)) * POWER(NEW_DATA.INCREMENTAL_STDDEV, 2))
ELSE 0
END AS INCREMENTAL_WEIGHTED_VARIANCE, -- incremental weighted variance
POWER((COALESCE(EXISTING_USER_DATA.AVG_TX, 0) - NEW_DATA.INCREMENTAL_AVG), 2) AS MEAN_DIFF_SQUARED,
CASE
WHEN NEW_COUNT_TX = 1 THEN 0
ELSE (_n * _k) / (NEW_COUNT_TX * (NEW_COUNT_TX - 1))
END AS BETWEEN_GROUP_WEIGHT, -- between group weight
BETWEEN_GROUP_WEIGHT * MEAN_DIFF_SQUARED AS MEAN_DIFF_VARIANCE,
EXISTING_WEIGHTED_VARIANCE + INCREMENTAL_WEIGHTED_VARIANCE + MEAN_DIFF_VARIANCE AS VARIANCE_TX,
CASE
WHEN _n = 0 THEN NEW_DATA.INCREMENTAL_STDDEV -- no "past" data
WHEN _k = 0 THEN EXISTING_USER_DATA.STDDEV_TX -- no "new" data
ELSE SQRT(VARIANCE_TX) -- stddev (which is the root of variance)
END AS STDDEV_TX,
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_SUM_TX AS SUM_TX,
NEW_COUNT_TX AS COUNT_TX
{% endif %}
FROM
INCREMENTAL_USER_TX_DATA new_data
{% if is_incremental() %}
LEFT JOIN
{{ this }} EXISTING_USER_DATA
ON
NEW_DATA.USER_ID = EXISTING_USER_DATA.USER_ID
{% endif %}
)

SELECT
USER_ID,
UPDATED_AT,
COUNT_TX,
SUM_TX,
AVG_TX,
STDDEV_TX
FROM NEW_USER_CULMULATIVE_DATA

A lo largo de este proceso, demostramos cómo manejar eficazmente los modos incremental y no incremental, aprovechando técnicas matemáticas para actualizar métricas como la varianza y la desviación estándar de manera eficiente. Al combinar datos históricos y nuevos a la perfección, logramos un enfoque optimizado y escalable para la agregación de datos en tiempo real.

En este artículo, exploramos la técnica matemática para calcular incrementalmente la desviación estándar y cómo implementarla utilizando los modelos incrementales de dbt. Este enfoque demuestra ser muy eficiente y permite el procesamiento de grandes conjuntos de datos sin la necesidad de volver a escanear todo el conjunto de datos. En la práctica, esto conduce a sistemas más rápidos y escalables que pueden manejar actualizaciones en tiempo real de manera eficiente. Si desea hablar más sobre esto o compartir sus opiniones, no dude en comunicarse con nosotros. ¡Me encantaría escuchar sus opiniones!