Dimensional Modeling and Schema Design Questions

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.

Explain surrogate keys versus natural (business) keys in dimension tables. Why do dimensional models commonly use surrogate keys instead of natural business keys, and what problems arise if you use a natural key as a dimension's primary key, especially once Slowly Changing Dimensions and multiple source systems are involved?

What is a date (calendar) dimension, and why is it worth building as a dedicated dimension rather than deriving date parts on the fly at query time? List the attributes you would include, and explain how a BI tool uses them for built-in time-intelligence features.

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.

Discuss options for implementing Slowly Changing Dimensions on columnar cloud warehouses (BigQuery, Snowflake, Redshift): update-in-place, partition swap, time-travel or versioned tables, and append-only historical tables. For each approach, compare performance cost, storage cost, the ability to query current versus historical state, and suitability for high-update-volume dimensions.