The answer is the star schema with fact and dimension tables. This design optimally balances storage and query performance for a business intelligence data warehouse because it separates transactional data into fact tables for detailed analysis and dimension tables for descriptive context, enabling fast aggregations through star joins while avoiding the storage overhead of full denormalization. On the Google Professional Cloud Database Engineer exam, this concept tests your understanding of schema trade-offs for BI workloads, often appearing in scenario-based questions where you must choose between star, snowflake, or fully denormalized designs—a common trap is selecting the snowflake schema for its normalization, but the star schema avoids the join complexity and performance penalty of snowflake while still supporting both granular transaction queries and high-level rollups. To remember this, think of the star as a hub-and-spoke model: the fact table is the central hub for numeric measures, and dimension tables are the spokes providing context, making it the go-to choice for balancing speed and storage in BI.
PCDE Practice Question: Define data structures and implement SQL for Business Intelligence
This PCDE practice question tests your understanding of define data structures and implement sql for business intelligence. Read the scenario carefully and evaluate each option against the stated constraints before committing to an answer. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A company is designing a data warehouse for BI. They need to support both detailed transaction analysis and high-level aggregated reports. Which schema design best balances storage and query performance?
Clue words in this question
Noticing these words before you look at the options changes how you read each choice.
Clue: "best"
Why it matters: Signals that multiple options may be partially correct. Choose the option that most directly solves the exact problem described, not the one that sounds most complete.
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Star schema with fact and dimension tables
The star schema is the optimal design for balancing storage and query performance in a BI data warehouse because it separates transactional data into fact tables (for detailed analysis) and dimension tables (for context), enabling fast aggregations via star joins while avoiding the storage overhead of full denormalization. This structure directly supports both granular transaction queries and high-level rollups without the complexity or performance penalty of snowflake schemas or the redundancy of fully denormalized tables.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
✗
Fully denormalized single table
Why it's wrong here
Denormalized tables cause data redundancy and slower updates.
✗
Wide column store with no schema
Why it's wrong here
No schema is unsuitable for structured BI reporting.
✓
Star schema with fact and dimension tables
Why this is correct
Star schema is standard for BI, enabling fast aggregations and easy reporting.
Clue confirmation
The clue word "best" in the question point toward this answer.
Related concept
Read the scenario before looking for a memorised answer.
✗
Snowflake schema with normalized dimensions
Why it's wrong here
Snowflake reduces redundancy but increases join complexity, slowing queries.
Common exam traps
Common exam trap: answer the scenario, not the keyword
Google Cloud often tests the misconception that snowflake schemas are always better for storage efficiency, but the trap here is that the question explicitly balances storage and query performance, and the star schema provides the best trade-off by avoiding excessive joins while keeping dimensions manageable.
Detailed technical explanation
How to think about this question
In a star schema, fact tables store measures and foreign keys to dimension tables, which are denormalized to a single level, allowing the database optimizer to use bitmap indexes and star transformations for fast aggregations. Under the hood, this design minimizes the number of join hops (typically one join per dimension), whereas a snowflake schema requires multiple joins per dimension, increasing query plan complexity and I/O. In real-world scenarios, such as an e-commerce BI system with millions of transactions, the star schema reduces query latency for monthly sales reports by 50-70% compared to a snowflake schema, while using only 10-20% more storage than a fully normalized design.
KKey Concepts to Remember
Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
How this comes up in practice
A media company stores terabytes of video archives that are accessed once a year for audit purposes. Moving these objects to a cold storage tier (Azure Archive, S3 Glacier, or Google Nearline) costs a fraction of hot storage. Questions like this test whether you understand storage tiers, access frequency tradeoffs, and retrieval latency requirements.
What to study next
Got this wrong? Here's your next step.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Define data structures and implement SQL for Business Intelligence — This question tests Define data structures and implement SQL for Business Intelligence — Read the scenario before looking for a memorised answer..
What is the correct answer to this question?
The correct answer is: Star schema with fact and dimension tables — The star schema is the optimal design for balancing storage and query performance in a BI data warehouse because it separates transactional data into fact tables (for detailed analysis) and dimension tables (for context), enabling fast aggregations via star joins while avoiding the storage overhead of full denormalization. This structure directly supports both granular transaction queries and high-level rollups without the complexity or performance penalty of snowflake schemas or the redundancy of fully denormalized tables.
What should I do if I get this PCDE question wrong?
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Are there clue words in this question I should notice?
Yes — watch for: "best". Signals that multiple options may be partially correct. Choose the option that most directly solves the exact problem described, not the one that sounds most complete.
What is the key concept behind this question?
Read the scenario before looking for a memorised answer.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A company is designing a data warehouse for business intelligence reporting. They want to organize data into fact and dimension tables to support fast aggregations. Which schema design is most appropriate for this purpose?
easy
✓ A.Star schema
B.Third Normal Form (3NF) schema
C.Snowflake schema
D.Entity-relationship schema
Why A: The star schema is most appropriate for business intelligence reporting because it denormalizes dimension tables around a central fact table, enabling fast aggregations and simple queries. This design minimizes the number of joins required for analytical queries, which is critical for performance in OLAP workloads. In contrast, normalized schemas like 3NF or snowflake increase join complexity and degrade query speed.
Last reviewed: Jun 30, 2026
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.