Cómo crear una periodic snapshot fact table en SQL

João Barros 13 de July de 2026 6 min de lectura

Una periodic snapshot fact table guarda el estado de un proceso en intervalos regulares: cada día, cada semana o cada mes. Es la forma más sencilla de responder a preguntas como "¿cuánto stock teníamos el 30 de junio?" o "¿cuántas suscripciones activas había cada día del trimestre?", sin recorrer millones de transacciones cada vez. Vamos a construir una, en SQL, con carga diaria.

Requisitos previos

Una base de datos SQL (SQL Server, Azure SQL, Fabric Warehouse o PostgreSQL — la sintaxis aquí es casi toda ANSI).
Una dimensión de calendario (dim_data) y las dimensiones de negocio (dim_produto, dim_armazem).
Una tabla de origen con los movimientos (stg_movimentos_stock), con cantidades positivas en las entradas y negativas en las salidas.
Permisos para crear tablas y ejecutar INSERT y DELETE.

Paso 1: Definir el grano del snapshot

Antes de escribir cualquier CREATE TABLE, escriba el grano en una frase: "una fila por producto, por almacén, por día". Esa frase decide todo lo demás: las claves, las medidas y el tamaño de la tabla.

La diferencia respecto a una transaction fact table es importante: la periodic snapshot tiene una fila para cada combinación de dimensiones en cada periodo, incluso cuando no hubo ningún movimiento. Esa densidad es justamente lo que permite dibujar una evolución sin huecos.

Paso 2: Crear la tabla de hechos

CREATE TABLE fact_stock_diario (
    data_key      INT           NOT NULL,
    produto_key   INT           NOT NULL,
    armazem_key   INT           NOT NULL,
    qtd_em_stock  DECIMAL(18,2) NOT NULL,
    valor_stock   DECIMAL(18,2) NOT NULL,
    qtd_entradas  DECIMAL(18,2) NOT NULL,
    qtd_saidas    DECIMAL(18,2) NOT NULL,
    CONSTRAINT pk_fact_stock_diario
        PRIMARY KEY (data_key, produto_key, armazem_key)
);

Fíjese en los dos tipos de medida. qtd_entradas y qtd_saidas son aditivas: se pueden sumar en cualquier dirección. qtd_em_stock es semiaditiva: se puede sumar entre productos, pero nunca a lo largo del tiempo (sumar el stock del lunes con el del martes da un número que nunca existió).

Paso 3: Calcular el snapshot de un día

La receta tiene dos partes: construir la rejilla completa (todas las combinaciones de dimensiones para esa fecha) y, sobre esa rejilla, acumular los movimientos hasta el final del día.

WITH grelha AS (
    SELECT d.data_key, d.data, p.produto_key, a.armazem_key
    FROM   dim_data d
    CROSS JOIN dim_produto p
    CROSS JOIN dim_armazem a
    WHERE  d.data = '2026-07-13'
),
acumulado AS (
    SELECT g.data_key,
           g.produto_key,
           g.armazem_key,
           SUM(COALESCE(m.quantidade, 0)) AS qtd_em_stock,
           SUM(CASE WHEN m.data_movimento = g.data AND m.quantidade > 0
                    THEN m.quantidade ELSE 0 END) AS qtd_entradas,
           SUM(CASE WHEN m.data_movimento = g.data AND m.quantidade < 0
                    THEN -m.quantidade ELSE 0 END) AS qtd_saidas
    FROM   grelha g
    LEFT JOIN stg_movimentos_stock m
           ON m.produto_key   = g.produto_key
          AND m.armazem_key   = g.armazem_key
          AND m.data_movimento <= g.data
    GROUP BY g.data_key, g.produto_key, g.armazem_key
)
INSERT INTO fact_stock_diario
       (data_key, produto_key, armazem_key,
        qtd_em_stock, valor_stock, qtd_entradas, qtd_saidas)
SELECT a.data_key,
       a.produto_key,
       a.armazem_key,
       a.qtd_em_stock,
       a.qtd_em_stock * p.custo_unitario,
       a.qtd_entradas,
       a.qtd_saidas
FROM   acumulado a
JOIN   dim_produto p ON p.produto_key = a.produto_key;

El LEFT JOIN es esencial: garantiza que un producto sin movimientos aparezca igualmente, con stock cero. Si usa INNER JOIN, pierde exactamente las filas que quería tener.

Paso 4: Hacer la carga idempotente

Un snapshot se carga todos los días y, tarde o temprano, tendrá que volver a ejecutarlo para el mismo día (llegó un fichero con retraso, hubo una corrección). Borre siempre la fecha antes de volver a insertarla:

DECLARE @data_key INT = 20260713;

DELETE FROM fact_stock_diario
WHERE  data_key = @data_key;

-- y a continuación ejecute el INSERT del Paso 3 para esa misma fecha

Así la carga puede ejecutarse dos o diez veces: el resultado es siempre el mismo. Es el error más común en estas tablas: sin el DELETE, una reejecución duplica el día y todos los totales se van al doble.

Paso 5: Consultar el snapshot

Para ver la evolución, filtre un día por periodo en lugar de sumar días:

-- Stock en el último día de cada mes
SELECT d.ano,
       d.mes,
       SUM(f.qtd_em_stock) AS qtd_fim_mes
FROM   fact_stock_diario f
JOIN   dim_data d ON d.data_key = f.data_key
WHERE  d.e_ultimo_dia_mes = 1
GROUP  BY d.ano, d.mes
ORDER  BY d.ano, d.mes;

En Power BI, el equivalente es usar CLOSINGBALANCEMONTH o LASTDATE en la medida, para que el usuario no pueda sumar stock a lo largo del tiempo por error.

Verificar el resultado

Dos comprobaciones rápidas le dicen si la carga salió bien. La primera confirma que la rejilla está completa (filas por día = productos × almacenes). La segunda concilia el snapshot con el origen:

-- 1) ¿Está completa la rejilla?
SELECT data_key, COUNT(*) AS linhas
FROM   fact_stock_diario
GROUP  BY data_key;

-- 2) ¿Cuadra el stock con el origen?
SELECT SUM(qtd_em_stock) AS total_snapshot
FROM   fact_stock_diario
WHERE  data_key = 20260713;

SELECT SUM(quantidade) AS total_origem
FROM   stg_movimentos_stock
WHERE  data_movimento <= '2026-07-13';

Los dos totales tienen que ser iguales. Si no lo son, casi siempre es un <= que se convirtió en = en algún punto de la acumulación.

Conclusión

Ya tiene una periodic snapshot fact table funcional, con una carga diaria repetible y medidas semiaditivas bien tratadas. Los siguientes pasos naturales son programar el script en un pipeline (Azure Data Factory o Fabric) y particionar la tabla por data_key, para que cada día se cargue y se reprocese de forma aislada. Y una pregunta para empezar bien: ¿cuál es el grano correcto para su proceso: diario, semanal o mensual? Elija el intervalo más amplio que siga respondiendo a las preguntas del negocio; cada nivel de detalle de más multiplica la tabla.