Dimensional Modeling and Schema Design Questions

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.

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.

For reporting daily account balances (an end-of-day balance per account), explain why a periodic snapshot fact table is generally preferred over deriving the balance from transactional facts at query time. Describe how you would build and store the daily snapshot table, including partitioning and load strategy.

Given sales_fact(order_item_id, order_id, product_key, date_key, quantity, unit_price), product_dim(product_key, product_name, category_key), and category_dim(category_key, category_name), write SQL to return the top 10 categories by revenue last quarter. Then explain how snowflaking the category into its own table (versus denormalizing it directly onto product_dim) affects this query, and whether you would denormalize for reporting.

Design a dimensional model for a marketing analytics dashboard that must show daily and weekly funnel conversions broken down by campaign, channel, and geography. Specify the fact and dimension tables, the recommended partitioning columns and grain, and how you would support both ad-hoc analysis and precomputed daily aggregates for fast dashboards.