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 :
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.
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.
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.
