Study guide

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

Microsoft Certified: Azure Databricks Data Engineer Associate DP-750

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Azure Databricks Architecture & Compute Selection

1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute

2 Chapter 2: Compute Configuration, Performance & Cost Management

2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions

3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup

3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

4 Chapter 4: Unity Catalog Privilege Model & Access Control

4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

5 Chapter 5: Advanced Data Security, Masking & ABAC

5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks

6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing

6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions

7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming

7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory

8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance

8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg

9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture

9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment

10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables

10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs

11 Chapter 11: Workflow Orchestration with Lakeflow Jobs

11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling

12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles

12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines

13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning

13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics

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

Key Takeaways Explain This Topic AI

Relational join types in Spark SQL/PySpark include standard joins (inner, left, right, full), Cartesian cross joins, and existence/exclusion joins (left_semi for matching existence, left_anti for orphan identification).

Window specifications (OVER (PARTITION BY ... ORDER BY ... [ROWS | RANGE] BETWEEN ... AND ...)) enable computing running aggregates, cumulative sums, and moving averages across distributed partitions without collapsing rows.

Ranking window functions distinguish between ROW_NUMBER() (unique sequential integers), RANK() (same rank for ties with gaps), and DENSE_RANK() (same rank for ties without gaps), while value functions (LEAD, LAG, FIRST_VALUE, LAST_VALUE) support time-series delta calculations.

Window frame boundary definitions (ROWS physical offsets vs RANGE logical value offsets, UNBOUNDED PRECEDING, CURRENT ROW, n PRECEDING / FOLLOWING) dictate exact row inclusion for rolling window calculations.

Dataset reshaping and combination leverage PIVOT / UNPIVOT for cross-tabulation, and set operators (UNION with distinct deduplication, UNION ALL for fast concatenation, and PySpark unionByName(allowMissingColumns=True)).

Last updated: August 2026

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]
  • )

PARTITION BY : Defines the boundary lines (slices) that divide the dataset across Spark executor partitions.

ORDER BY : Dictates the logical sequence of rows within each partition slice.

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)
  • Function Ties Behavior Gaps in Sequence Common Use Case
  • ROW_NUMBER() Arbitrary sequential assignment No Strict top-1 deduplication
  • RANK() Same rank assigned to ties Yes (gap = number of ties) Olympic-style competitions, score leaderboards
  • DENSE_RANK() Same rank assigned to ties No gaps Pricing tiers, salary percentiles, dense rankings
  • NTILE(n) Divides partition into n equal buckets No Quartiles ( NTILE(4) ), deciles ( NTILE(10) )
  • 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

Operator Deduplication Performance Schema Behavior

UNION Yes (Performs distinct row deduplication) Slower (requires distributed shuffle) Must have identical column count and compatible types

  • UNION ALL No (Direct row append) Fastest (Zero shuffle) Must have identical column count and compatible types
  • INTERSECT Returns only rows present in BOTH datasets Shuffle required Matches by ordinal column position

EXCEPT / MINUS Returns rows in left dataset that do NOT exist in right Shuffle required Matches by ordinal column position

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

Loading diagram... Window Function Execution & Frame Slicing