A nightly sales report joins catalog.sales.facts_sales to catalog.sales.dim_stores on store_id to append each store's region and manager. After the join, the report is missing thousands of sales: several new stores started selling before their dim_stores records were created. The business rule requires every sale to appear in the report even when store attributes are unknown. Which join change satisfies this rule?

1. A. Switch to a LEFT JOIN cautiously — an outer join still discards non-matching rows before combining, so the unregistered stores would keep disappearing 💬 A
2. B. Replace the INNER JOIN with a LEFT JOIN — sales whose store_id has no dimension row are kept, with NULLs filling the store attribute columns ✓ B
3. C. No join change is needed — Spark inspects the table sizes and decides on its own whether INNER or LEFT semantics apply to the query 💬 C
4. D. Keep the INNER JOIN and add an ORDER BY — INNER and LEFT return exactly the same rows and differ only in how the output is sorted 💬 D

Reveal answer

Correct answer: Replace the INNER JOIN with a LEFT JOIN — sales whose store_id has no dimension row are kept, with NULLs filling the store attribute columns

Reference: https://docs.databricks.com/aws/en/pyspark/reference