How should I model conversion events?
For instance, if a sales opportunity goes from "stage: prospecting" to "stage: validating", should that be treated as a changing dimension, or a conversion event in the fact table?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that involve a predefined series of steps. The timestamped.
You'll have a date key in the table that represents the date the opportunity hit each stage. If you need to reproduce what pipeline looked like on a given day or track the fact that an opportunity went into and out of a stage multiple times, you might need to consider the timestamped accumulating snapshot.