Como criar uma periodic snapshot fact table em SQL

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

Uma periodic snapshot fact table guarda o estado de um processo em intervalos regulares — todos os dias, todas as semanas ou todos os meses. É a forma mais simples de responder a perguntas como "qual era o stock a 30 de junho?" ou "quantas subscrições ativas tínhamos em cada dia do trimestre?", sem ter de varrer milhões de transações de cada vez. Vamos construir uma, em SQL, com carga diária.

Pré-requisitos

Uma base de dados SQL (SQL Server, Azure SQL, Fabric Warehouse ou PostgreSQL — a sintaxe aqui é quase toda ANSI).
Uma dimensão de calendário (dim_data) e as dimensões de negócio (dim_produto, dim_armazem).
Uma tabela de origem com os movimentos (stg_movimentos_stock), com quantidades positivas nas entradas e negativas nas saídas.
Permissões para criar tabelas e correr INSERT e DELETE.

Passo 1: Definir o grão do snapshot

Antes de escrever qualquer CREATE TABLE, escreva o grão numa frase: "uma linha por produto, por armazém, por dia". Esta frase decide tudo o resto — as chaves, as medidas e o volume da tabela.

A diferença face a uma transaction fact table é importante: a periodic snapshot tem uma linha para cada combinação de dimensões em cada período, mesmo quando não houve movimento nenhum. É essa densidade que permite gráficos de evolução sem buracos.

Passo 2: Criar a tabela de factos

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)
);

Repare nos dois tipos de medida. qtd_entradas e qtd_saidas são aditivas: podem ser somadas em qualquer direção. qtd_em_stock é semi-aditiva: pode somar-se entre produtos, mas nunca ao longo do tempo (somar o stock de segunda com o de terça não dá stock nenhum que exista).

Passo 3: Calcular o snapshot de um dia

A receita tem duas partes: criar a grelha completa (todas as combinações de dimensões para a data) e, sobre essa grelha, acumular os movimentos até ao fim do dia.

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;

O LEFT JOIN é essencial: garante que um produto sem movimentos aparece na mesma, com stock zero. Se usar INNER JOIN, perde exatamente as linhas que queria ter.

Passo 4: Tornar a carga idempotente

Um snapshot é carregado todos os dias, e mais cedo ou mais tarde vai ter de o correr outra vez para o mesmo dia (chegou um ficheiro atrasado, houve uma correção). Apague sempre a data antes de a inserir de novo:

DECLARE @data_key INT = 20260713;

DELETE FROM fact_stock_diario
WHERE  data_key = @data_key;

-- e a seguir corra o INSERT do Passo 3 para essa mesma data

Assim a carga pode correr duas ou dez vezes: o resultado é sempre o mesmo. É o erro mais comum nestas tabelas — sem o DELETE, uma reexecução duplica o dia e todos os totais ficam a dobrar.

Passo 5: Consultar o snapshot

Para ver a evolução, filtre um dia por período em vez de somar dias:

-- Stock no último dia de cada mês
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;

No Power BI, o equivalente é usar CLOSINGBALANCEMONTH ou LASTDATE na medida, para o utilizador não conseguir somar stock ao longo do tempo por engano.

Verificar o resultado

Duas verificações rápidas dizem-lhe se a carga ficou bem. A primeira confirma que a grelha está completa (linhas por dia = produtos × armazéns). A segunda confronta o snapshot com a origem:

-- 1) A grelha está completa?
SELECT data_key, COUNT(*) AS linhas
FROM   fact_stock_diario
GROUP  BY data_key;

-- 2) O stock bate certo com a origem?
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';

Os dois totais têm de ser iguais. Se não forem, quase sempre é o <= que virou = algures no acumulado.

Conclusão

Tem agora uma periodic snapshot fact table funcional, com carga diária repetível e medidas semi-aditivas bem tratadas. Os próximos passos naturais são agendar o script num pipeline (Azure Data Factory ou Fabric) e particionar a tabela por data_key, para que cada dia seja carregado e reprocessado isoladamente. E uma pergunta para começar bem: qual é o grão certo para o seu processo — diário, semanal ou mensal? Escolha o maior intervalo que ainda responda às perguntas do negócio; cada nível de detalhe a mais multiplica a tabela.