Atomic events vs metrics
Recognize when one atomic event fact can support multiple product metrics.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
A notifications team asks for send volume, click-through rate, and notification opt-out rate by channel. Someone proposes one fact table for each metric.
Metric-shaped tables make every new metric a new model. Atomic events keep the source behavior queryable and let metrics evolve.
- Pick the fact grain that preserves notification behavior.
- Explain how the same event grain supports rate and volume metrics.
- The answer chooses an atomic notification event fact.
- The explanation distinguishes source-of-truth events from derived aggregates.
- Ask what one notification event row should represent.
- Click-through rate is a metric computed from sent and clicked events.
- Creating one fact per metric and losing consistency across definitions.
- Aggregating to channel-day grain before user-level opt-out analysis is possible.
Atomic notification event fact
Use one notification event fact with event_type, event_time, user, channel, and campaign context. A Kimball transaction-fact grain, served the dbt semantic-modeling way.
Optimizes for
- • Multiple metrics from one grain
- • Metric definition changes
- • User and channel debugging
Trade-offs
- • High event volume may need aggregate serving tables
- • Rate metrics require careful numerator and denominator filters
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.