How to create an 'Event Snapshot' Fact table in Data Modeling (Kimball)

João Barros 17 de September de 2026 5 min read

This tutorial shows how to create an 'Event Snapshot' Fact table in Data Modeling (Kimball): a fact table that records the state of an entity whenever a relevant event occurs. It is useful to analyze event-driven evolution (e.g., order status changes) without conflicting with periodic snapshots or accumulating snapshots.

Prerequisites

Basic knowledge of Dimensional Modeling (Fact/DIMensions).
Access to a SQL database (e.g., SQL Server, PostgreSQL).
Source data with events (e.g., order state logs with timestamp).

Step 1: Understand when to use an Event Snapshot

An Event Snapshot records the entity state each time an event relevant for analysis occurs (e.g., status change, payment registration). Use it when you need event-level history without losing temporal context. It avoids loss of granularity that would occur with periodic snapshots and enables funnel and lead-time analyses between events.

Step 2: Define granularity and keys

Decide the granularity: typically one row per (entity_id, event_id/timestamp). Define keys:

Surrogate key of the fact (fact_snapshot_id) — identity of the fact table.
Natural keys: entity_id and event_timestamp (or event_id if present).
Foreign keys to conformed dimensions (e.g., dim_date, dim_user, dim_location).

-- Exemplo de definição mínima em SQL (SQL Server/Postgres sintaxe geral)
CREATE TABLE fact_event_snapshot (
  fact_snapshot_id BIGSERIAL PRIMARY KEY,
  entity_id VARCHAR(50) NOT NULL,
  event_timestamp TIMESTAMP NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  status_before VARCHAR(50),
  status_after VARCHAR(50),
  amount NUMERIC(18,2),
  dim_date_key INT, -- FK para dim_date
  dim_user_key INT, -- FK para dim_user
  load_batch_id VARCHAR(50) -- rastreio de carga
);

Step 3: Map attributes and identify dimensions

Choose which attributes go into the fact (measures and degenerate attributes) and which go into dimensions. Measures: values that aggregate (amount, quantity). Context attributes that repeat and have low cardinality should go to dimensions (status, event_type can be a small dimension or a direct string in the fact if useful for performance).

Step 4: Extraction and transformation (ETL) — event capture logic

Implement the logic that detects new events and creates a snapshot per event. Typically: read source_events, for each event build a record with before/after state and link dimension keys.

-- Exemplo ETL simplificado em SQL: inserir novos snapshots
INSERT INTO fact_event_snapshot (
  entity_id, event_timestamp, event_type, status_before, status_after, amount, dim_date_key, dim_user_key, load_batch_id
)
SELECT
  e.entity_id,
  e.event_ts,
  e.event_type,
  e.status_before,
  e.status_after,
  e.amount,
  d.date_key,
  u.user_key,
  :batch_id
FROM staging_events e
LEFT JOIN dim_date d ON d.calendar_date = DATE(e.event_ts)
LEFT JOIN dim_user u ON u.user_id = e.user_id
WHERE e.processed_flag = 0; -- só novos eventos

Step 5: Handle duplicates and idempotency

Preventing duplicate inserts is critical. Use a unique constraint or deduplication before inserting. One option is to use a logical unique key (entity_id + event_timestamp + event_type).

-- Criar índice único para evitar duplicados
ALTER TABLE fact_event_snapshot
ADD CONSTRAINT uq_event_snapshot UNIQUE (entity_id, event_timestamp, event_type);

-- Inserção defensiva (exemplo Postgres)
INSERT INTO fact_event_snapshot (...)
SELECT ...
ON CONFLICT (entity_id, event_timestamp, event_type) DO NOTHING;

Step 6: Attribute updates and Slowly Changing Dimensions

If attributes related to dimensions change, handle them as Slowly Changing Dimensions (SCD). The fact_snapshot should reference the correct version of the dimension (surrogate key) to preserve history. In the ETL, perform a lookup of the current or historical dimension as needed.

Verify the result

Verify that each relevant event has a row in the fact:

Count source events vs rows in the fact by period: SELECT COUNT(*) by interval.
Check uniqueness: SELECT entity_id, event_timestamp, COUNT(*) HAVING COUNT(*) > 1.
Test analytical queries: average time between event_type = 'A' and event_type = 'B' per entity_id.

-- Exemplos de verificação
-- 1) Verificar total
SELECT COUNT(*) FROM staging_events WHERE processed_flag = 0;
SELECT COUNT(*) FROM fact_event_snapshot WHERE load_batch_id = :batch_id;

-- 2) Duplicados
SELECT entity_id, event_timestamp, event_type, COUNT(*)
FROM fact_event_snapshot
GROUP BY entity_id, event_timestamp, event_type
HAVING COUNT(*) > 1;

-- 3) Exemplo analítico: tempo entre eventos
SELECT f1.entity_id, EXTRACT(EPOCH FROM (f2.event_timestamp - f1.event_timestamp))/3600 AS hours_between
FROM fact_event_snapshot f1
JOIN fact_event_snapshot f2 ON f1.entity_id = f2.entity_id
WHERE f1.event_type = 'A' AND f2.event_type = 'B' AND f2.event_timestamp > f1.event_timestamp;

Conclusion

An 'Event Snapshot' Fact table provides the granularity to analyze evolution by occurrences without losing history. Next steps: integrate with Power BI for funnel and time visualizations, automate the ETL routine and add quality metrics. Tip: start with indexes/constraints for idempotency and simple validations to avoid duplicate data — what is the first critical event you will capture in your case?