GCP Professional Data Engineer Practice Question

A retail analytics team is moving to BigQuery. About 100,000 new sales rows are loaded into a fact table each day. The product catalog dimension has over 200 million rows, is updated hourly, and is reused by several other fact tables. Country and currency dimensions change rarely and appear only in the sales fact table. To minimize ETL maintenance while preserving query performance, how should the team model these dimensions in BigQuery?

Store the product catalog in a separate normalized dimension table and embed the country and currency attributes directly in the sales fact table (for example, as nested fields).
Embed the frequently changing product catalog in the sales fact table and keep the rarely changing country and currency in separate dimension tables.
Embed all dimensions, including the product catalog, directly in the sales fact table to eliminate joins.
Keep every dimension in its own table and perform joins for all analytical queries.

Report Issue

Answer Description

Keeping frequently changing or widely shared dimensions in their own tables follows a snowflake-style approach that avoids rewriting every affected fact table whenever the dimension changes. Less volatile, single-use dimensions can be safely denormalized-embedded as scalar or nested columns-to eliminate unnecessary joins and improve query cost and speed. Therefore, isolating the product catalog dimension and embedding the stable country and currency data best balances maintenance effort with performance. Placing all dimensions in the fact table would force frequent rewrites, and normalizing every dimension yields avoidable joins for the stable ones.

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.