Dimensional Modeling and Schema Design Questions

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.

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.

How do you handle a fact row whose foreign key points to a dimension member that does not exist yet, or where the natural key resolves to NULL (a missing or late-arriving dimension)? Describe the 'unknown member' surrogate-row pattern, at least one alternative (deferred FK resolution via staging and backfill, or a placeholder row updated in place later), and the downstream effect each choice has on dashboard filtering and KPI totals.

What is the difference between a star schema and a snowflake schema for analytics? For an e-commerce analytics model that needs to analyze sales by product, category, customer, and time, describe how each approach structures the dimension tables, and compare them on query performance (join count), storage redundancy, ETL complexity, and how well typical BI tools handle each.

What is a degenerate dimension? Give an example from an order-processing pipeline (such as an order number with no corresponding dimension table), and explain why you would choose to keep an attribute as a degenerate dimension on the fact table rather than moving it into its own dimension table.