Dimensional Modeling and Schema Design Questions

Explain the difference between transaction, periodic snapshot, and accumulating snapshot fact tables. Give one real-world example of each, and describe the kind of query each one is optimized to answer.

Explain Slowly Changing Dimensions (SCD) Types 0, 1, 2, and 3. For each type, describe how it preserves or discards history, how you would implement it in an ETL/ELT pipeline, and give a concrete business scenario where that type is the right choice (for example, a fixed attribute that never changes, a corrected typo that should overwrite, a full historical trail like an address change, or a limited one-prior-value case like a sales territory reassignment).

Define additive, semi-additive, and non-additive measures, and give one business example of each. For each type, explain how it should or should not be aggregated so a dashboard does not silently show a misleading total.

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.

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.