Schema Normalization Slot Utilization Storage Tradeoffs

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

An enterprise analytics team is designing a high-throughput reporting platform on Google Cloud to serve complex analytical dashboards. The workload executes ad-hoc aggregations across multi-terabyte fact datasets joined with multiple dimensional hierarchies.

The lead data engineer needs to decide between implementing a highly normalized snowflake schema versus a denormalized flat table structure to minimize query latency and optimize compute slot utilization and total infrastructure costs.

Which statement accurately describes the architectural trade-offs between these two schema designs?

A normalized snowflake schema guarantees lower query latency because distributed shuffle operations across normalized foreign keys require less memory and CPU than reading wide columnar rows.

A denormalized flat schema eliminates multi-table join operations, reducing slot utilization and data shuffle overhead to lower query latency, while columnar compression mitigates the financial impact of redundant storage.

A denormalized flat schema creates severe cost disadvantages because columnar storage engines cannot compress redundant string fields, leading to exponential storage billing increases that outweigh compute savings.

A normalized snowflake schema optimizes compute slot efficiency because scanning smaller, distinct dimension tables requires significantly fewer compute slots than scanning a wide denormalized table.

Professional Cloud Data Engineer

Schema Normalization Slot Utilization Storage Tradeoffs

Related questions