When analysing data sources that need to be included in the data-warehouse (Kimball), how does a developer determine whether a schema should be a star type or constellation type?

Business processes which can be reflected at one of 3 levels (transaction, periodic snapshot or accumulating snapshot). However, there can exist business processes where the captured data is effectively identical, at any of these levels for any number of these processes.

What are the remaining considerations? (Performance..)

  • Data architects, not developers, design the data warehouse model based on, to put it simply, the answers it needs to provide. Data sources, update methods, performance, etc. have nothing to do with it. – mustaccio Oct 17 '19 at 16:49
  • I think this question is unclear. Do you want to know how you should model the target data warehouse? That would be too broad. Do you want to figure out if the database you are connected to implements a star schema or not? That would be too trivial. – Laurenz Albe Oct 17 '19 at 17:21
  • @LaurenzAlbe In a scenario where you want to measure 2 business processes at the transaction level; point of purchase and refunds (both processes have the same granularity). Why might one choose to separate the two sets of measurements into 2 FACT tables instead of one. – fpsdkfsdkmsdfsdfm Oct 17 '19 at 18:18
  • @alwaysLearning If there is always a purchase and a refund at the same coordinate (speaking in dimensions), you'd keep them in a single table. If they occur at - say - different times, you'd keep them in different tables and are happy that the size of the fact tables is smaller. Once you aggregate the data (e.g. purchases and refunds per day) it will again make sense to have them in a single table (less overhead). – Laurenz Albe Oct 18 '19 at 6:15