DA0-002 Data Concepts and Environments Practice Question
A financial application requires fast query performance for aggregations on large historical datasets. The schema has many lookup tables. Which schema design is most efficient for this workload?
⚠ Common exam trap
It's easy for candidates to confuse normalization with performance, assuming snowflake or 3NF schemas are faster due to reduced redundancy, when in fact denormalization in a star schema minimizes joins for analytical queries.
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
✓
Star schema
The star schema is most efficient for this workload because it denormalizes lookup tables into dimension tables, reducing the number of joins required for aggregations. This design optimizes query performance for large historical datasets by enabling faster full table scans and simpler query plans, which is critical for financial applications needing rapid aggregations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Snowflake schema
Why it's wrong here
Snowflake schema normalises lookup tables into multiple related dimensions, so aggregation queries must join more tables, adding processing overhead on large historical datasets. It is tempting because it reduces storage redundancy, and would be correct where storage efficiency and update consistency matter more than query speed.
- ✓
Star schema
Why this is correct
A star schema keeps a central fact table joined to denormalised dimension tables, minimising joins for aggregations over large historical datasets. This satisfies the stem's requirement for fast aggregation performance despite many lookup tables, unlike snowflake schemas that normalise dimensions and add join depth.
- ✗
Wide table
Why it's wrong here
A wide table flattens all attributes into one relation, so it cannot model the many lookup tables the schema requires without massive duplication and update anomalies. It is tempting because it removes joins, and would be correct for a denormalised reporting extract where lookup relationships are already resolved.
- ✗
Third normal form (3NF)
Why it's wrong here
Third normal form eliminates redundancy by splitting data into many related tables, so aggregations require numerous joins across the large fact data, slowing queries. It is tempting because it guarantees update integrity, and would be correct for transactional systems where write consistency outweighs analytical read performance.
About these practice questions
Courseiva writes every DA0-002 question from scratch — 1,004 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 DA0-002 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 DA0-002 exam.