9.4 Relational Joins, Window Functions, Set Operators, & Pivoting

9.4 Relational Joins, Window Functions, Set Operators, & Pivoting

DP-750 Exam Focus: Master advanced relational data transformations in Azure Databricks. Understand specialized join semantics (left_semi vs left_anti), ranking functions (ROW_NUMBER, RANK, DENSE_RANK), time-series navigation (LEAD, LAG), window frame boundary specifications (ROWS vs RANGE), dynamic dataset reshaping with PIVOT and UNPIVOT, and set operations (UNION, UNION ALL, unionByName).

---

1. Relational Join Types & Specialized Join Semantics

Apache Spark and Delta Lake support standard ANSI SQL join types as well as specialized filtering joins designed for high-performance subquery execution.

+-----------------------------------------------------------------------------------------+
|                            AZURE DATABRICKS JOIN TAXONOMY                               |
+-----------------------------------------------------------------------------------------+
|                                                                                         |
|  1. INNER JOIN                                                                          |
|     - Returns rows where keys match in BOTH left and right datasets.                    |
|                                                                                         |
|  2. LEFT / RIGHT / FULL OUTER JOIN                                                      |
|     - Preserves all rows from Left, Right, or Both datasets; pads non-matches with NULL.|
|                                                                                         |
|  3. LEFT SEMI JOIN (Existence Filter)                                                   |
|     - Returns rows from the LEFT table that have AT LEAST ONE match in the right table. |
|     - Columns from the right table are NEVER projected; never duplicates left rows.     |
|                                                                                         |
|  4. LEFT ANTI JOIN (Exclusion / Orphan Filter)                                          |
|     - Returns rows from the LEFT table that have NO match in the right table.           |
|     - Ideal for finding orphan foreign keys or missing reference records.               |
|                                                                                         |
|  5. CROSS JOIN (Cartesian Product)                                                      |
|     - Produces N x M rows (every left row joined with every right row). Must be explicit|
+-----------------------------------------------------------------------------------------+

Left Semi Join vs. Inner Join

When checking whether a customer has placed an order, an INNER JOIN against the orders table will duplicate the customer row if they placed multiple orders. A LEFT SEMI JOIN checks for existence, returning each customer record at most once without duplicating rows or appending columns from the right table.

-- SQL: Left Semi Join (Find customers who have active subscriptions)
SELECT c.customer_id, c.customer_name
FROM silver.customers c
LEFT SEMI JOIN silver.subscriptions s
    ON c.customer_id = s.customer_id
   AND s.status = 'ACTIVE';

# PySpark: Left Semi and Left Anti Joins
df_semi = df_customers.join(df_subscriptions, on="customer_id", how="left_semi")
df_anti = df_customers.join(df_subscriptions, on="customer_id", how="left_anti") # Customers with NO subscriptions

---

2. Window Function Architecture & Specification

Window functions compute calculations across a defined subset (window) of rows related to the current record without collapsing rows (unlike GROUP BY, which collapses rows into a single summary output).

The OVER Clause Anatomy

FUNCTION() OVER (
    [PARTITION BY partition_col1, partition_col2, ...]
    [ORDER BY sort_col1 [ASC|DESC], ...]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
)

1. PARTITION BY: Defines the boundary lines (slices) that divide the dataset across Spark executor partitions.
2. ORDER BY: Dictates the logical sequence of rows within each partition slice.
3. ROWS | RANGE (Frame Specification): Defines the sliding subset of rows relative to the current row used for the aggregation.

---

3. Ranking & Value Navigation Functions

Ranking Functions Comparison

  Partition Data: Scores = [ 100, 90, 90, 80 ]

  1. ROW_NUMBER()  ==>  1,  2,  3,  4   (Monotonic, unique sequential integers)
  2. RANK()        ==>  1,  2,  2,  4   (Ties get identical rank; skips next rank: 2->4)
  3. DENSE_RANK()  ==>  1,  2,  2,  3   (Ties get identical rank; NO rank skipped)

Analytical Value Functions (LEAD & LAG)

LEAD and LAG allow querying preceding or succeeding rows without self-joining tables, essential for calculating time-series deltas and session intervals.

-- Calculate month-over-month revenue growth
SELECT 
    revenue_month,
    monthly_sales,
    LAG(monthly_sales, 1, 0.0) OVER (ORDER BY revenue_month) AS prior_month_sales,
    monthly_sales - LAG(monthly_sales, 1, 0.0) OVER (ORDER BY revenue_month) AS mom_growth
FROM gold.monthly_revenue;

# PySpark: Window Lead/Lag implementation
from pyspark.sql.window import Window
from pyspark.sql.functions import col, lag, lead

window_month = Window.orderBy("revenue_month")

df_growth = df_revenue.withColumn("prior_month_sales", lag("monthly_sales", 1, 0.0).over(window_month))

---

4. Window Frame Physical vs. Logical Boundaries

Understanding the distinction between ROWS (physical offsets) and RANGE (logical value offsets) is a critical DP-750 exam topic.

+---------------------------------------------------------------------------------------+
|                           ROWS VS. RANGE FRAME DEFINITIONS                            |
+---------------------------------------------------------------------------------------+
|                                                                                       |
|  1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW                                          |
|     - Physical count: Evaluates exactly the 2 preceding physical rows + current row.  |
|                                                                                       |
|  2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW                                  |
|     - Running Total: Evaluates from the very first row of the partition to current.   |
|                                                                                       |
|  3. RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW                           |
|     - Logical value: Evaluates all rows whose date falls within the last 7 days of    |
|       the current row's timestamp, regardless of how many physical rows exist.        |
+---------------------------------------------------------------------------------------+

Default Window Frame Rules

• When ORDER BY is specified without an explicit frame clause, Spark defaults to: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (calculating a cumulative running total).
• When ORDER BY is omitted, Spark defaults to: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (evaluating the entire partition).

-- 7-day rolling moving average of sales
SELECT 
    store_id,
    transaction_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY store_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg_revenue
FROM silver.daily_store_sales;

---

5. Pivoting & Unpivoting Operations

Pivoting (Narrow to Wide)

PIVOT transforms unique row values from a single categorical column into multiple distinct output columns, performing an aggregation across the cross-tabulated dataset.

-- SQL: Pivoting quarterly sales by region
SELECT * FROM (
    SELECT region, quarter, revenue 
    FROM silver.regional_sales
)
PIVOT (
    SUM(revenue) 
    FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);

# PySpark: Pivoting with explicit values (High Performance)
# Providing explicit pivot values prevents an extra Spark scan to discover distinct values
df_pivoted = (df_sales
    .groupBy("region")
    .pivot("quarter", ["Q1", "Q2", "Q3", "Q4"])
    .sum("revenue"))

Unpivoting (Wide to Narrow)

UNPIVOT rotates multiple columns back into key-value attribute rows, normalizing wide spreadsheets into relational tables.

-- SQL: Unpivoting wide quarterly columns back to rows
SELECT region, quarter, revenue
FROM gold.pivoted_sales
UNPIVOT (
    revenue FOR quarter IN (Q1, Q2, Q3, Q4)
);

---

6. Set Operators & Schema Alignment

Schema-Resilient Union in PySpark: unionByName

Standard df.union() matches columns by ordinal position, not by column name. If column orders differ, data will be silently transposed into the wrong columns. unionByName matches columns by name, and supports combining DataFrames with different schemas using allowMissingColumns=True:

# PySpark: Safe schema-resilient union
df_combined = df_source_a.unionByName(df_source_b, allowMissingColumns=True)
# Missing columns in either DataFrame are automatically populated with NULL