Databricks-Spark-Assoc Using Spark SQL Practice Question
A data engineer is writing a Spark SQL query that joins a `transactions` table to a `customers` table on `customer_id`. The engineer wants to ensure that rows from `transactions` with no matching customer are still returned, with nulls for customer columns, and also wants to exclude duplicate rows that arise from the join. Which TWO clauses should the engineer include? (Choose two.)
⚠ Common exam trap
The trap here is treating deduplication as something a join type provides, when duplicate elimination requires a separate distinct or aggregate operation after the join is performed.
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
✓
Use a `LEFT OUTER JOIN` between `transactions` and `customers`.
Preserving unmatched left-side rows requires an outer join oriented to the left table, and eliminating duplicated combinations requires a distinct projection. Together, a left outer join plus `SELECT DISTINCT` returns every transaction, matches customers where possible, and collapses repeated pairs into single rows. Inner and full outer joins change which unmatched rows survive and do not meet the stated retention rule.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use a `LEFT OUTER JOIN` between `transactions` and `customers`.
Why this is correct
A `LEFT OUTER JOIN` preserves every row from the left `transactions` table and fills unmatched customer columns with nulls. This satisfies the requirement to retain transactions lacking a matching customer. Inner joins would drop those rows, so the outer join is essential to the scenario's stated goal of not losing left-side records.
- ✓
Apply `SELECT DISTINCT` to the final result set.
Why this is correct
`SELECT DISTINCT` removes duplicate rows produced when multiple customer records match a single transaction, ensuring the output has no repeated combinations. Because the join can multiply rows, deduplication is required to meet the 'exclude duplicate rows' condition. It operates after the join, so it also handles duplicates introduced by the join itself.
- ✗
Use an `INNER JOIN` instead of an outer join.
Why it's wrong here
An `INNER JOIN` returns only rows where `customer_id` matches in both tables, discarding transactions with no customer. That directly contradicts the requirement to keep unmatched transactions with null customer columns. While it avoids some null handling, it fails the primary retention condition, making it unsuitable regardless of deduplication.
- ✗
Use a `FULL OUTER JOIN` between the two tables.
Why it's wrong here
A `FULL OUTER JOIN` returns unmatched rows from both sides, which would include customers with no transactions. The scenario only requires retaining unmatched transactions, so this introduces extra rows the engineer did not ask for. It also does not by itself eliminate duplicates, so it does not satisfy either stated condition cleanly.
- ✗
Add a `GROUP BY` on all columns from both tables.
Why it's wrong here
Grouping by every column effectively deduplicates, but it is a heavy and awkward substitute for `DISTINCT` and can interfere with null handling in outer joins. It also changes the query's semantics and may not produce the intended row-level output. It does not address the left-outer retention requirement and is not the idiomatic deduplication tool here.
About these practice questions
One of 295 original Databricks-Spark-Assoc practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks 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 Databricks-Spark-Assoc exam.