How to Build a Periodic Snapshot Fact Table in SQL

João Barros 13 de July de 2026 5 min read

A periodic snapshot fact table stores the state of a process at regular intervals — every day, every week, every month. It is the simplest way to answer questions such as "what was our stock on 30 June?" or "how many active subscriptions did we have on each day of the quarter?", without scanning millions of transactions every time. Let's build one in SQL, with a daily load.

Prerequisites

A SQL database (SQL Server, Azure SQL, Fabric Warehouse or PostgreSQL — the syntax here is almost all ANSI).
A calendar dimension (dim_data) and the business dimensions (dim_produto, dim_armazem).
A source table with the movements (stg_movimentos_stock), holding positive quantities for receipts and negative ones for issues.
Permissions to create tables and run INSERT and DELETE.

Step 1: Define the snapshot grain

Before writing any CREATE TABLE, write the grain as one sentence: "one row per product, per warehouse, per day". That sentence decides everything else — the keys, the measures and the size of the table.

The difference from a transaction fact table matters: a periodic snapshot has a row for every combination of dimensions in every period, even when nothing moved. That density is exactly what lets you chart a trend with no gaps.

Step 2: Create the fact table

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

Note the two kinds of measure. qtd_entradas and qtd_saidas are additive: they can be summed in any direction. qtd_em_stock is semi-additive: you may sum it across products, but never across time (adding Monday's stock to Tuesday's produces a number that never existed).

Step 3: Compute one day's snapshot

The recipe has two parts: build the full grid (every dimension combination for that date) and, on top of that grid, accumulate the movements up to the end of the day.

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;

The LEFT JOIN is essential: it guarantees that a product with no movements still shows up, with zero stock. Use an INNER JOIN and you lose exactly the rows you wanted.

Step 4: Make the load idempotent

A snapshot is loaded every day, and sooner or later you will have to run it again for the same day (a late file arrived, a correction came in). Always delete the date before inserting it again:

DECLARE @data_key INT = 20260713;

DELETE FROM fact_stock_diario
WHERE  data_key = @data_key;

-- then run the INSERT from Step 3 for that same date

Now the load can run twice or ten times: the result is always the same. This is the most common error with these tables — without the DELETE, a re-run duplicates the day and every total doubles.

Step 5: Query the snapshot

To see a trend, filter one day per period instead of summing days:

-- Stock on the last day of each month
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;

In Power BI, the equivalent is to use CLOSINGBALANCEMONTH or LASTDATE inside the measure, so that users cannot accidentally sum stock over time.

Verify the result

Two quick checks tell you whether the load is sound. The first confirms the grid is complete (rows per day = products × warehouses). The second reconciles the snapshot against the source:

-- 1) Is the grid complete?
SELECT data_key, COUNT(*) AS linhas
FROM   fact_stock_diario
GROUP  BY data_key;

-- 2) Does the stock match the source?
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';

Both totals must be equal. When they are not, it is nearly always a <= that turned into an = somewhere in the accumulation.

Conclusion

You now have a working periodic snapshot fact table, with a repeatable daily load and semi-additive measures handled properly. The natural next steps are scheduling the script in a pipeline (Azure Data Factory or Fabric) and partitioning the table by data_key, so each day can be loaded and reprocessed in isolation. And one question to start well: what is the right grain for your process — daily, weekly or monthly? Pick the widest interval that still answers the business questions; every extra level of detail multiplies the table.