Virtualize or persist data in business satellites?


Let me start with the background - there is a Offer hub, which has more than 10+ satellites, satellites are generated for different sources and tenants. To present consolidated object for consumption a view is created that not only uses left join and coalesce to get data for specific business key, but there are also attributes that require calculation based on satellites of additional objects.
View itself does the work, but is very slow and not suitable for consumption by the end users. The following options were considered instead:

  1. Create a PIT for this hub a RV satellites, rewrite the View, keep the logic virtualized - PIT get’s pretty wide, performance might not be good enough becase other objects need to be accessed through links as well…
  2. Create a business satellite for all the attributes of the object and leverage only BSAT in consumption layer - you are restricted pretty much to the attributes you precalculated.
  3. Create a business satellite for only newly calculated attributes + PIT for both raw and business satellite - looks pretty complex.

Do you apply any of the above options by default or there is yet a better option to be considered?


PIT or SNOPIT (even thinner); although SNOPIT is on Snowflake the technique should be usable in other platforms as well.