Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right, with NULLs for non-matches. RIGHT JOIN is symmetrical, returning all rows from the right table and matches from the left. FULL JOIN returns all rows from both tables, filling non-matches with NULLs.

The choice of join fundamentally dictates the result set's cardinality and semantic meaning.
INNER JOIN: Includes only rows where the join condition is met in both* tables, yielding the intersection of the two datasets based on the join key.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left* table. If a row in the left table has no match in the right, NULL values are returned for the right table's columns. Commonly used to enrich a primary dataset (left) with attributes from a secondary dataset (right).
RIGHT JOIN (or RIGHT OUTER JOIN): Symmetrical to LEFT JOIN. Returns all rows from the right* table, with NULLs for non-matching left table columns. Less common, as most queries can be rephrased as a LEFT JOIN.
FULL JOIN (or FULL OUTER JOIN): Returns all rows from both* tables. If a row from one table has no match in the other, NULLs are returned for the non-matching table's columns. Useful for comprehensive comparisons or merging disparate datasets.

Using the wrong join leads to incorrect data, impacting aggregations and business decisions.

Data Engineering Considerations

Performance & Scalability: INNER JOINs are generally the cheapest due to producing the smallest result sets. FULL OUTER JOINs are often the most expensive, requiring extensive data shuffling in distributed systems like Spark, potentially impacting cluster performance. For smaller dimension tables, a broadcast join (where the smaller table is sent to all nodes) can significantly optimize LEFT/INNER joins. Data Integrity: When using a LEFT JOIN to a dimension table, it's crucial to ensure the join key in the dimension table is unique. A non-unique key will cause fact rows to duplicate, leading to incorrect metrics. Data quality checks and unique key constraints (e.g., enforced in dbt models) are vital.

SELECT
    f.order_id,
    f.order_date,
    d.customer_name
FROM
    fact_orders f
LEFT JOIN
    dim_customers d ON f.customer_id = d.customer_id;

This example enriches fact_orders with customer_name, even if a customer doesn't exist in dim_customers (resulting in NULL for customer_name).

In the interview, also mention the importance of join key data types and handling NULL values in join conditions.