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

João Barros 27 de October de 2026 4 min read

This tutorial teaches how to model an "Event Stream" Fact table in Data Modeling (Kimball) to record continuous transactional events (logs, clicks, messages). An Event Stream fact table is useful for behavior analysis, funnels, latency and time series, allowing aggregation by time and dimension.

Prerequisites

Basic knowledge of Data Modeling (Kimball), star schema and concepts of dimensions and fact tables.
SQL environment (e.g., SQL Server, PostgreSQL or Azure SQL) to create tables and load data.
Example event source (CSV, stream or staging table) with timestamp, user_id, event_type and minimal payload.

Step 1: Define the purpose and the grain of the Event Stream Fact

Before creating tables, clearly state the purpose: analyze individual events (each row = 1 event) with high temporal resolution. The grain defines that each record represents an event generated at an instant by an actor or system. This avoids ambiguities in aggregation.

Step 2: Choose the conformed dimensions and essential attributes

Select dimensions required for analyses: DimDate (time), DimUser, DimEventType and, if needed, DimDevice or DimSession. Do not load large payloads into the fact; store references or keys for detailed attributes in dimensions or in a OneLake/Lakehouse area.

Step 3: Physical schema of the Event Stream Fact

Create the minimal structure with surrogate keys for dimensions, timestamp and count measures. Include columns for event_id, event_ts, keys and measures (event_value if applicable). SQL example:

CREATE TABLE fact_event_stream (
  event_sk BIGINT IDENTITY(1,1) PRIMARY KEY,
  event_id VARCHAR(100),         -- id original do evento
  event_ts DATETIME2 NOT NULL,   -- timestamp do evento
  date_sk INT NULL,              -- FK para DimDate (date part)
  user_sk INT NULL,              -- FK para DimUser
  eventtype_sk INT NULL,         -- FK para DimEventType
  session_sk INT NULL,           -- FK opcional para DimSession
  event_value FLOAT NULL,        -- medida opcional
  raw_payload NVARCHAR(MAX) NULL -- só se necessário para debugging
);

Step 4: Population and enrichment (ETL/ELT)

When loading, assign the surrogate keys from the dimensions. If using ELT, perform JOINs between the event staging and the conformed dimensions to obtain the SKs. Keep the process idempotent using event_id as the natural key.

-- Exemplo simplificado (INSERT com lookup de chaves)
INSERT INTO fact_event_stream (event_id,event_ts,date_sk,user_sk,eventtype_sk,event_value)
SELECT s.event_id, s.event_ts, d.date_sk, u.user_sk, et.eventtype_sk, s.value
FROM staging_events s
LEFT JOIN dim_date d ON CAST(s.event_ts AS DATE) = d.calendar_date
LEFT JOIN dim_user u ON s.user_id = u.user_id
LEFT JOIN dim_eventtype et ON s.event_type = et.event_type
WHERE NOT EXISTS (
  SELECT 1 FROM fact_event_stream f WHERE f.event_id = s.event_id
);

Step 5: Optimizations for high cardinality and performance

Event streams generate many records. Use partitioning by date_sk or by event_ts (day/month) and indexes on lookup columns (event_ts, user_sk, eventtype_sk). Avoid NVARCHAR(MAX) in production; keep raw_payload separate in a log storage or OneLake.

Step 6: Handling duplicate events and ordering

Define a policy for duplicates: deduplicate by event_id and, in identical streams without an id, by (user_id,event_ts,event_type). For temporal ordering, use event_ts with sufficient precision and, if necessary, a sequence_number column generated by the producer.

-- Deduplicação por combinação quando não há event_id
WITH candidate AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, event_ts, event_type ORDER BY ingest_ts) rn
  FROM staging_events
)
INSERT INTO fact_event_stream (...) SELECT ... FROM candidate WHERE rn = 1;

Verify the result

Validate that the grain is correct and that there are no duplicates. Examples of verification queries:

-- Contagem por dia
SELECT d.calendar_date, COUNT(*) total_events
FROM fact_event_stream f
JOIN dim_date d ON f.date_sk = d.date_sk
GROUP BY d.calendar_date
ORDER BY d.calendar_date DESC;

-- Checar duplicados
SELECT event_id, COUNT(*) c FROM fact_event_stream GROUP BY event_id HAVING COUNT(*) > 1;

Conclusion

An Event Stream fact table enables analyzing events in detail and at scale — the key is to define the grain, use conformed dimensions, partitioning and deduplication. Next steps: create pre-computed aggregations (materialized views), adjust retention and integrate with Power BI or streaming tools. Tip: start with a reduced set of events and monitor cardinality growth.