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
A self-join matches rows within one table, so sales_fact would be joined to itself rather than to product_dim and time_dim, leaving category and month attributes unavailable for grouping. Self-joins suit hierarchical data, such as an employee table referencing a manager column in the same table.
- ✗
Cross join between fact and dimension tables
Why it's wrong here
A cross join produces the Cartesian product of every fact row with every dimension row, so sales totals would be multiplied by unrelated dimension combinations rather than grouped by category. It is used for generating exhaustive row pairings, such as building a date scaffold, not for aggregating measures along a dimension.
- ✓
Inner join between fact table and dimension tables
Why this is correct
An inner join matches each fact row to its related dimension rows via the foreign keys, letting the query group sales by product category. Because every sales_fact row has valid dimension references, inner joins return all required combinations without dropping matching data.
- ✗
Left outer join between fact and dimension tables
Why it's wrong here
A left outer join preserves unmatched fact rows by padding dimension columns with nulls, but the requirement is an inner join matching sales_fact foreign keys to product_dim and time_dim so category totals for last month aggregate correctly. Left joins suit retaining fact rows lacking a matching dimension record.
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.