Dimensional Modeling and Schema Design Questions

What are conformed dimensions, and why do they matter once you have multiple fact tables or data marts (for example, sales, returns, and shipments)? Describe a concrete plan to implement conformed customer and product dimensions so that revenue, support, and marketing dashboards all report consistent attributes and totals, and how you would detect and prevent divergence across teams over time.

What is a role-playing dimension? Using the example of order_date, ship_date, and delivery_date all referencing the same underlying date dimension, explain how you would model this so users can filter and aggregate on each date role independently in a BI tool without duplicating the date dimension physically.

What is the difference between a fact table and a dimension table in a dimensional model? Give a concrete example (for instance, order line items as a fact table and customers as a dimension table), name the typical columns and cardinality characteristics of each, and explain why separating facts and dimensions matters for query performance and dashboard usability.

For a B2B SaaS analytics product, compare three ways to power the BI dashboards: a classic star schema, a single wide denormalized table, and a lakehouse (raw events plus views). Recommend one approach and justify it in terms of migration effort, query performance, and long-term maintainability.

You are modeling products and promotions where a promotion can apply to many products and a product can be covered by many promotions at once. Design a bridge table to represent this many-to-many relationship, and write an example query that computes revenue attributable to each promotion, commenting on how you would attribute revenue when multiple promotions overlap.