Modelling customer onboarding in fact or dimension table?

Question

I'm modelling the customer onboarding pipeline as an accumulating snapshot fact table. I'm considering the Joy Mundy's design tip to model this as a long-lived business process where I have a set of milestones which are updated as customers move through the pipeline. On top of this, some facts such as days from stage to stage are calculated.

As this table will have the same amount of records as my customer dimension, is it best practice to just add these fields to the customer dimension or keep a separate fact table with a one-to-one relationship?

Answer 1

When modeling a customer onboarding pipeline as an accumulating snapshot fact table, it's generally a best practice to keep the customer dimension and the onboarding fact table separate, even if the fact table has a one-to-one relationship with the customer dimension.

In my opinion, it's recommended to keep them separate for the following :

1. Clarity of Design: Separating the customer dimension and the onboarding fact table helps maintain a clear and intuitive design. It distinguishes the dimensions (descriptive attributes about the customer) from the facts (measurable events or metrics related to the onboarding process). This separation aligns with the principles of dimensional modeling and enhances the understandability of the schema.

2. Flexibility and Scalability: By keeping the customer dimension and onboarding fact table separate, you can easily accommodate changes and additions to either the dimension or the fact table independently. For example, if you need to add more attributes to the customer dimension or introduce new measures or calculations related to the onboarding process, you can do so without affecting the other component. This modular approach supports flexibility and scalability in your data model.

3. Performance Optimization: Separating the dimension and fact table can have performance benefits. When querying the onboarding process metrics, you can focus solely on the fact table, which contains the specific measures and calculations related to the pipeline. This can improve query performance as you can efficiently retrieve the required information without including irrelevant customer attributes from the dimension.