DA0-002 Data Concepts and Environments Practice Question
An e-commerce company uses a star schema for its data warehouse. The fact table 'sales_fact' contains foreign keys to dimension tables: customer_dim, product_dim, time_dim, and store_dim. A business user wants to know the total sales for each product category in the last month. Which join operation is required to retrieve this data?
⚠ Common exam trap
Watch out — candidates often confuse the need for a left outer join to 'preserve all fact rows,' but in a well-designed star schema with referential integrity, inner join is sufficient and more performant, and left outer join is only needed when fact rows might lack matching dimension keys (e.g., orphaned records).
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
✓
Inner join between fact table and dimension tables
To retrieve total sales for each product category, you need to join the fact table with the product dimension table to map product keys to categories, and with the time dimension table to filter on the last month. An inner join is correct because it returns only rows where matching keys exist in both tables, which is the standard approach for star-schema queries where all required dimension attributes are present. This ensures that only valid sales transactions with corresponding product and time entries are included in the aggregation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Self-join on the fact table
Why it's wrong here
Self-join is used for hierarchical data within the same table.
- ✗
Cross join between fact and dimension tables
Why it's wrong here
Cross join combines all rows, leading to incorrect aggregation.
- ✓
Inner join between fact table and dimension tables
Why this is correct
Inner join returns only matching rows, which is typical in star schema queries.
- ✗
Left outer join between fact and dimension tables
Why it's wrong here
Outer join may include nulls, but facts should have matching dimensions.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DA0-002 question from scratch — 986 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.