Dimensional Modeling and Schema Design Questions

A product attribute has historically lived in product_dim but, after an organizational change, now logically belongs in supplier_dim. You must refactor the dimensional model to move it without losing history and while keeping existing facts joinable. Describe the migration steps, how you preserve the historical correctness of existing SCD Type 2 rows, and include a rollback plan.

Explain the accumulating snapshot fact table pattern: one row per process instance, updated in place as it passes through milestones (for example order_id, created_date, shipped_date, delivered_date, status, and measures). Describe when it is appropriate, and how you would use it to report on time-to-fulfillment and funnel conversion.

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.

Design a dimension table pattern and ETL process to support 'role history', where a person can hold multiple roles over time (for example employee then contractor), and analysts need to know which role was in effect at the time of a given transaction. Explain how you would store the role versions and how a fact table joins to the correct one.

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.