Question

A retail analytics team stores a customer dimension table in BigQuery. They must preserve full historical attribute values (e.g., a customer's address as it was at the time of each past order) so that historical reports remain accurate even after a customer's details change. Analysts frequently join fact tables to this dimension using the value that was effective at the transaction date. Which schema design should the team implement?

Accepted Answer

Implement a Type 2 slowly changing dimension: add surrogate keys plus effective_start_date and effective_end_date columns, inserting a new row each time an attribute changes.

Answer

Overwrite the existing dimension row in place whenever a customer's attribute changes (Type 1), keeping only the current values.

Answer

Store only the current customer attributes and rely on BigQuery time travel to reconstruct historical attribute values for any past date.

Answer

Add a single extra column that holds the previous value of each attribute (Type 3), overwriting it on each subsequent change.