How to create an incremental snapshot in Data Modeling (Kimball)

This tutorial shows how to implement an incremental snapshot (periodic snapshot) in a Kimball schema to record the state of an object over time, covering why it is useful and reducing storage and ETL work. The incremental snapshot is useful when we are only interested in periodic versions of a fact (e.g.: account balance) and want to optimize loads and queries.

Prerequisites

• Basic knowledge of Kimball Modeling (fact and dimension).
• SQL environment (for example SQL Server, PostgreSQL or Azure SQL).
• Transactional source or staging table with change records.

Step 1: Define the use case and granularity

Decide which object and what periodicity you want to capture. Example: daily balance per bank account. Granularity determines the fact keys (account_id + snapshot_date) and which measures will be aggregated (balance, transactions_count).

Step 2: Create the snapshot table structure

Create a fact table that stores only one record per granularity key per period. Include fields to identify whether the record is incremental (updated_flag) and for extraction date control.

CREATE TABLE fact_account_snapshot (
  account_id INT NOT NULL,
  snapshot_date DATE NOT NULL,
  balance DECIMAL(18,2),
  transactions_count INT,
  updated_flag BIT DEFAULT 0,
  load_ts DATETIME2 DEFAULT SYSUTCDATETIME(),
  PRIMARY KEY (account_id, snapshot_date)
);

Primary key ensures uniqueness per period. The updated_flag field helps identify records changed in an incremental load.

Step 3: Prepare the staging table with current data

In the ETL/ELT step, bring the current state of accounts into a staging table with the same granularity. It can be a SELECT from the transactional source or a view that calculates the balance up to the snapshot date.

-- Exemplo de staging com estado actual
CREATE TABLE stg_account_state (
  account_id INT,
  snapshot_date DATE,
  balance DECIMAL(18,2),
  transactions_count INT
);

-- Inserir dados de exemplo
INSERT INTO stg_account_state (account_id, snapshot_date, balance, transactions_count)
VALUES (1, '2026-07-01', 1250.00, 3), (2, '2026-07-01', 540.50, 1);

Step 4: Incremental logic (UPSERT) to load the snapshot

Execute a MERGE/UPSERT that compares staging with the existing fact by (account_id, snapshot_date). Only update if there is a difference in the measures — this reduces writes and preserves the history of previous snapshots.

-- Exemplo MERGE (SQL Server / Azure SQL)
MERGE INTO fact_account_snapshot AS target
USING stg_account_state AS src
  ON target.account_id = src.account_id
  AND target.snapshot_date = src.snapshot_date
WHEN MATCHED AND (
  ISNULL(target.balance,0) <> ISNULL(src.balance,0)
  OR ISNULL(target.transactions_count,0) <> ISNULL(src.transactions_count,0)
) THEN
  UPDATE SET
    target.balance = src.balance,
    target.transactions_count = src.transactions_count,
    target.updated_flag = 1,
    target.load_ts = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (account_id, snapshot_date, balance, transactions_count, updated_flag)
  VALUES (src.account_id, src.snapshot_date, src.balance, src.transactions_count, 1);

If you use PostgreSQL, replace MERGE with INSERT ... ON CONFLICT DO UPDATE or equivalent logic.

Step 5: Cleanup and retention

Define snapshot retention policy. If you don't need all dates, you can compact (for example keep daily for 90 days, then weekly) or remove snapshots without changes.

-- Exemplo: eliminar snapshots sem alterações antigas
DELETE FROM fact_account_snapshot
WHERE updated_flag = 0
  AND snapshot_date < DATEADD(day, -365, CAST(GETDATE() AS date));

-- Depois da limpeza, repor updated_flag a 0 para a próxima carga
UPDATE fact_account_snapshot SET updated_flag = 0 WHERE updated_flag = 1;

Verify the result

Confirm there is one record per (account_id, snapshot_date) and that only changed records were updated.

-- Contagem por chave
SELECT account_id, snapshot_date, COUNT(*) AS cnt
FROM fact_account_snapshot
GROUP BY account_id, snapshot_date
HAVING COUNT(*) > 1;

-- Registos marcados como actualizados na última carga
SELECT * FROM fact_account_snapshot WHERE updated_flag = 1 ORDER BY snapshot_date DESC;

Common errors: forgetting the unique key (leads to duplicates), not correctly comparing NULLs in measures, or not defining a retention policy (infinite growth).

Conclusion

Implementing an incremental snapshot in Kimball Modeling allows capturing states over time with efficiency and control. Next steps: integrate the logic into an automated pipeline (ETL/ELT), add dimensions according to analytical needs and consider compression/partitioning for performance. Tip: start with a small retention window and monitor growth before expanding.