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.

At petabyte scale, compare three modeling choices for an analytical schema: fully normalized (3NF), a dimensional star schema, and a wide denormalized table. Discuss the trade-offs in storage, query performance (joins versus I/O scan volume), update complexity, and ETL cost, and explain when nested or repeated (RECORD/array) types in a columnar warehouse are a better fit than any of the three flat options.

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 junk dimension, and when would you use one? Describe how you would implement a junk dimension that consolidates several low-cardinality flags and indicators (for example is_gift, is_first_time_customer, and coupon_code_present) into a single dimension table instead of leaving them as separate columns or tiny dimensions.

Describe the trade-offs between normalization and denormalization when designing data models for analytics. Give an example scenario where you would normalize to reduce storage and avoid update anomalies, and one where you would denormalize to optimize for read performance in dashboards that need sub-second responses.