hardMultiple ChoiceObjective-mapped
FC0-U71 Practice Question: A large retail chain operates a data warehouse…
A large retail chain operates a data warehouse that combines sales data from multiple source databases. The warehouse is designed using a highly normalized snowflake schema. Analysts frequently run complex queries that aggregate sales across many dimensions (e.g., time, product, store). Recently, the queries have become very slow, often taking hours to complete. The data warehouse team suspects the normalization is causing many joins, degrading performance. The business users need faster reporting. The team must decide on a course of action that balances query performance with maintainability. Which technique is most likely to improve reporting speed without significantly compromising data integrity?
⚠ Common exam trap
CompTIA often tests the misconception that adding more indexes always improves query performance, but in a highly normalized schema with many joins, the overhead of maintaining and scanning multiple indexes can actually slow down complex aggregations.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Denormalize some tables by merging fact and dimension tables
Denormalizing some tables by merging fact and dimension tables reduces the number of joins required for complex analytical queries, directly addressing the performance bottleneck caused by the highly normalized snowflake schema. This technique improves query speed by storing redundant data in a star-like schema, which is a common optimization for data warehouses where read performance is prioritized over write efficiency, while still maintaining data integrity through careful design and ETL processes.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Denormalize some tables by merging fact and dimension tables
Why this is correct
Reduces joins, improving read performance for aggregations.
- ✗
Increase the server's CPU and memory resources
Why it's wrong here
Hardware upgrade is a band-aid; the root cause is schema design.
- ✗
Replace the relational warehouse with a NoSQL document store
Why it's wrong here
NoSQL may not support complex analytic queries and requires major rework.
- ✗
Add more indexes on all foreign key columns
Why it's wrong here
Indexes help but cannot fully compensate for excessive joins.
Go deeper
Related to this question
About these practice questions
Courseiva writes every FC0-U71 question from scratch — 988 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This FC0-U71 practice question is part of Courseiva's free CompTIA 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 FC0-U71 exam.