Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Why Denormalized Tables Lower BigQuery Slot Cost
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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?
Explanations
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.
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.
What Normalized Snowflake Schemas Do
A snowflake schema isolates dimensional hierarchies into discrete, normalized relational tables (for example, splitting product, subcategory, and category into separate tables) to ensure data is stored with zero redundancy (Third Normal Form).
Why It Fails the Requirement
- Underestimates join computation: While dimension tables themselves have a smaller disk footprint, joining them to massive fact tables forces the distributed query engine to repartition or broadcast data across compute slots.
- Increases slot-hour consumption: The compute overhead of building hash tables, sorting, and shuffling keys across workers during multi-table joins consumes far more slot-seconds than sequentially scanning selected columns from a single flat table.
Key Limitations
- High shuffle latency: Distributed joins create intermediate data transfer bottlenecks across cluster nodes.
- Elevated CPU load: Evaluating join predicates and hash joins significantly increases slot usage.
- Complex execution plans: Multi-stage query execution trees increase plan coordination overhead.
This Answer Would Be Correct If
The workload were an Online Transaction Processing (OLTP) system requiring high-frequency single-row updates, inserts, and strict referential integrity rather than large-scale analytical scans.
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 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.
What Columnar Compression Achieves
Modern columnar data storage formats store each column's values contiguously on disk rather than row-by-row. When duplicate attributes occur across millions of rows (such as repeating a city or category name), columnar engines apply sophisticated dictionary encoding, run-length encoding, and bit-packing algorithms.
Why It Fails the Requirement
- Misunderstands compression mechanics: Columnar engines compress repeated values exceptionally well. A string column with high redundancy often achieves compression ratios exceeding 80–90%.
- Miscalculates cloud billing weight: Storage pricing per gigabyte in cloud data warehouses is very low compared to compute slot capacity. The compute slot savings gained by eliminating complex join shuffle stages overwhelmingly outweigh minor storage footprint increases.
Key Limitations
- False cost assumption: Redundant data in a columnar store does not lead to linear or exponential storage cost growth.
- Ignores column projection: Analytical engines only scan the specific columns referenced in the query
SELECTandWHEREclauses, meaning unused wide columns do not incur I/O or compute penalties.
This Answer Would Be Correct If
The underlying storage were a traditional row-oriented uncompressed file system where every redundant attribute directly expands disk consumption on a 1:1 basis without encoding.
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 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.
What Denormalization in Analytical Warehouses Is
Data denormalization involves combining related dimensional attributes and transactional metrics into a single wide, flat table or utilizing nested and repeated structures. In modern cloud analytical architectures, denormalization aligns with distributed columnar execution engines that are optimized for scanning and aggregating large datasets without inter-node relational overhead.
How It Meets Latency, Slot Utilization, and Cost Requirements
- Compute and Slot Efficiency: In a highly normalized snowflake schema, executing analytical queries across multiple dimensional levels requires extensive relational
JOINoperations. In a distributed data warehouse, joins require substantial network data shuffling and memory staging across compute workers, which dramatically increases slot time and CPU load. - Query Latency: Denormalized tables allow the columnar engine to read pre-joined rows sequentially and apply vector optimizations directly, eliminating join evaluation phases and significantly decreasing total query execution latency.
- Storage and Financial Footprint: While denormalizing data introduces duplicate dimensional values across records, columnar storage encoding (such as dictionary encoding and run-length compression) compresses repeated column values with high efficiency. Because analytical compute (slot-hours or scanned bytes) typically accounts for the majority of the total cost compared to storage, reducing compute consumption yields substantial overall cost savings.
Key Advantages
- Minimizes distributed data shuffle: Eliminates shuffle stages required to broadcast or repartition dimensional keys.
- Maximizes columnar compression: Repeated dimensional strings compress heavily when stored contiguously by column.
- Simplifies query syntax: Analysts do not need complex multi-table joins to query common attributes.
- Optimizes slot-hour billing: Shorter execution times translate directly to lower slot resource consumption.
Why This Is Optimal
Denormalizing fact and dimension data trades minimal, heavily compressed storage overhead for drastic reductions in slot utilization and query latency, making it the optimal design pattern for analytical cloud workloads.
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 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.
What Distributed Join Processing Involves
In a distributed analytical query engine, performing a JOIN on foreign keys requires compute slots to exchange data over the network via data shuffling. The engine must redistribute data partitions so matching keys reside on the same worker node to execute the join.
Why It Fails the Requirement
- Shuffle operations are expensive: Network shuffle is one of the most resource-intensive and latency-inducing phases of distributed query execution. It requires extensive memory buffers, CPU serialization, and network bandwidth.
- Column pruning eliminates wide-row penalties: In a wide, flat columnar table, the query engine only reads the specific columns requested by the SQL query (column projection), ignoring all other columns entirely. Reading selected columns from a flat table avoids the network shuffle stage altogether.
Key Limitations
- Memory pressure: Large hash joins can spill to disk if dimension or intermediate result sets exceed slot memory limits.
- Higher overall latency: Shuffling intermediate data across slots introduces significant barrier synchronization delays compared to single-scan pipelines.
This Answer Would Be Correct If
The query engine were a legacy single-node database with severe storage bandwidth constraints where reading narrow normalized rows from local disk was faster than reading wide records.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Spanner Normalization vs BigQuery Denormalization
Deciding the degree of data normalization
AdvancedProfessional Data Engineer
BigQuery ARRAY Of STRUCT
Deciding the degree of data normalization
AdvancedProfessional Data Engineer
Dedicated Interconnect with Private Google Access
Planning migration and validation to Google Cloud (e.g., BigQuery Data Transfer Service, Database Migration Service, Transfer Appliance, Google Cloud networking, Datastream)
AdvancedProfessional Data Engineer
GCP Data Residency CMEK Organization Policies
Designing for data and application portability (e.g., multi-cloud and data residency requirements)
AdvancedProfessional Data Engineer
BigQuery Capacity and Physical Storage Billing
Mapping business requirements
FundamentalAssociate Data Practitioner
CMEK vs CSEK vs GMEK
Identify use cases for customer-managed encryption keys (CMEK), customer-supplied encryption keys (CSEK), and Google-managed encryption keys (GMEK)
Last updated Validated