GCP Professional Data Engineer Practice Question

Your retail analytics team is migrating an on-premises star schema to BigQuery. The Sales fact table currently has foreign keys to the Customer, Store, Product, and Date dimensions. Analysts now need to compare the order date with the refund date for the same transaction in a single query while preserving star-schema best practices. Which modeling change most directly satisfies the new reporting requirement?

Store the refund date as a TIMESTAMP column in the Sales fact table and stop joining to a Date dimension for that value.
Add refund_date_key as a second foreign key in the Sales fact table that points to the shared Date dimension (a role-playing dimension).
Create a separate Refund fact table with its own customer and product keys and link both fact tables in queries.
Add an is_refund column to the Customer dimension to flag transactions that were returned.

Report Issue

Answer Description

In a star schema each business context (who, what, when, where) is placed in its own dimension table, and the fact table holds foreign keys to those dimensions. When the same logical dimension (Date) is needed for multiple business events, you add another foreign key that points to the same Date dimension; this is known as a role-playing dimension. Storing the date as a raw timestamp bypasses shared hierarchies (year, fiscal week), putting comparison logic on every query. Marking a flag in the Customer dimension or creating a separate Refund fact table either mixes unrelated attributes or fragments the transaction, forcing additional joins to analyze sale versus refund side-by-side. Therefore, adding a second foreign key such as refund_date_key that references the existing Date dimension is the most appropriate change.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.