Study guide
5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions
Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Spark Architecture and Execution
1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage
2 Chapter 2: Shuffles, Memory and Persistence
2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels
3 Chapter 3: Spark SQL Sessions, Tables and Views
3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views
4 Chapter 4: SQL Sources and Stored Layout
4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout
5 Chapter 5: SQL Queries and Analytics
5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics
6 Chapter 6: DataFrame Construction and Columns
6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions
7 Chapter 7: DataFrame Validation and Inspection
7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion
8 Chapter 8: Aggregations and Dates
8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions
9 Chapter 9: DataFrame Joins and File IO
9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning
10 Chapter 10: Shared Variables and Stateful Processing
10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores
11 Chapter 11: Troubleshooting and Tuning
11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins
12 Chapter 12: Structured Streaming
12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks
13 Chapter 13: Spark Connect and Deployment
13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices
14 Chapter 14: Pandas API on Spark
14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization
5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions
Key Takeaways Explain This Topic AI
Normal SELECT row results are lazy; SQL DDL/DML commands may run when spark.sql is called.
A CTE does not guarantee materialization or exactly one evaluation; inspect reuse in the physical plan.
A scalar subquery returns one column and at most one row; correlated forms have additional analysis restrictions.
A NULL in a NOT IN subquery can make the predicate never TRUE; use deliberate null handling or NOT EXISTS semantics.
Last updated: October 2026
Apache Spark SQL provides a powerful declarative relational interface layered directly over Spark's distributed computation engine. Rather than writing procedural low-level RDD or DataFrame transformations, developers can express complex analytical workflows using standard ANSI SQL. Under the hood, the Catalyst optimizer parses, analyzes, optimizes, and compiles SQL queries into the exact same optimized physical execution plans (consisting of RDD stages and tasks) as code written using the PySpark DataFrame API.
Mastering advanced Spark SQL requires understanding how declarative queries interact with DataFrames, how Common Table Expressions (CTEs) are treated by the query planner, how subqueries are rewritten into distributed joins, and how built-in string, date, and conditional expressions execute across cluster executors.
1. Executing SQL via spark.sql() and DataFrame Interoperability
The primary entry point is spark.sql(). SELECT returns a DataFrame whose row results are normally lazy. SQL commands such as CREATE, INSERT, or DROP may execute when spark.sql() is called; a DataFrame return type does not make every SQL statement lazy. # Executing a standard SQL query returning a DataFrame
- df_orders = spark.sql("""
- SELECT customer_id, COUNT(order_id) AS total_orders, SUM(order_amount) AS lifetime_spend
- FROM sales.orders
- WHERE order_status = 'COMPLETED'
- GROUP BY customer_id
- """)
- Lazy Evaluation Mechanics
A normal SELECT usually defers its row query, whereas eager commands and metadata discovery can do work immediately. Like any PySpark DataFrame transformation, SQL queries are lazily evaluated :
Analysis Phase : Spark parses the SQL string, validates syntax, and resolves table names, column identifiers, and function signatures against the active catalog metastore.
Logical Planning : Catalyst creates an unresolved logical plan, resolves it into an optimized logical plan, and prepares physical execution strategies.
Execution Barrier : No data is scanned from storage and no executor tasks are launched until an action (such as .show() , .count() , .collect() , or .write.save() ) is explicitly invoked on the resulting DataFrame.
Registering DataFrames as SQL Views
To query an existing in-memory PySpark DataFrame using SQL, it must first be registered in the catalog as a temporary view. Spark provides two distinct view registration scopes:
Registration Method Scope Metastore Namespace Lifecycle
df.createOrReplaceTempView("name") Session-scoped Active catalog / current database Dropped when the creating SparkSession terminates
df.createOrReplaceGlobalTempView("name") Application-scoped Dedicated system database: global_temp.name Preserved across all sessions until the Spark driver shuts down # 1. Register a session-scoped temporary view
df_raw.createOrReplaceTempView("staged_transactions")
- # Queryable only within the current active SparkSession
- spark.sql("SELECT * FROM staged_transactions WHERE amount > 1000").show()
- # 2. Register a global temporary view shared across sessions
- df_raw.createOrReplaceGlobalTempView("company_wide_transactions")
- # Must be qualified with the 'global_temp' system database identifier
- spark.sql("SELECT * FROM global_temp.company_wide_transactions").show()
- # Child sessions created via spark.newSession() can access global views, but NOT standard temp views
- analytics_session = spark.newSession()
- analytics_session.sql("SELECT * FROM global_temp.company_wide_transactions").show() # Succeeds
- # analytics_session.sql("SELECT * FROM staged_transactions").show() # Fails: Table not found
Important
createTempView vs createOrReplaceTempView : Invoking createTempView() throws an AnalysisException if a view with that name already exists in the catalog. In contrast, createOrReplaceTempView() silently overwrites any existing temporary view of the same name without raising an error.
Fluent Chaining: Interoperability
Because spark.sql() produces a standard DataFrame, developers can freely interleave SQL expressions and DataFrame API transformations: from pyspark.sql.functions import col, round
- # Chain SQL query with PySpark transformations
- final_df = (
- spark.sql("SELECT customer_id, order_amount, order_date FROM staged_transactions")
- .filter(col("order_amount") > 50.0)
- .withColumn("tax_amount", round(col("order_amount") * 0.0825, 2))
- .select("customer_id", "order_amount", "tax_amount")
- )
- Parameterized Queries in Spark SQL (Spark 3.4+)
To prevent SQL injection and avoid fragile string concatenation, modern Spark SQL supports parameterized query invocation: # Safe parameter binding using named variables
- query = "SELECT * FROM sales.orders WHERE region = :target_region AND order_amount >= :min_spend"
- filtered_df = spark.sql(query, args={"target_region": "EMEA", "min_spend": 250.0})
- 2. Common Table Expressions (CTEs): Syntax, Chaining, and Catalyst Inlining
A Common Table Expression (CTE) defines a temporary named result set that exists solely within the execution scope of a single SQL statement. Declared using the WITH clause, CTEs break complex, deeply nested queries into modular, logical steps.
Syntax and Multiple CTE Chaining
When chaining multiple CTEs, the WITH keyword is declared only once at the beginning of the statement. Subsequent CTE declarations are separated by commas, and later CTEs can reference earlier CTEs: WITH
- - CTE 1: Filter and clean initial transactions
- cleaned_orders AS (
- SELECT order_id, customer_id, order_amount, order_date
- FROM sales.orders
- WHERE order_status = 'COMPLETED' AND order_amount IS NOT NULL
- ),
- - CTE 2: Aggregate customer spend metrics referencing CTE 1
- customer_aggregates AS (
- SELECT
- customer_id,
- COUNT(order_id) AS total_orders,
- SUM(order_amount) AS total_spend,
- MAX(order_date) AS last_order_date
- FROM cleaned_orders
- GROUP BY customer_id
- )
- - Main Outer Query consuming CTE 2
- SELECT
- c.customer_name,
- ca.total_orders,
- ca.total_spend,
- ca.last_order_date
- FROM customer_aggregates ca
- JOIN sales.customers c ON ca.customer_id = c.customer_id
- WHERE ca.total_spend > 5000.0;
- Catalyst Optimizer Plan Inlining
A critical performance and architectural principle tested on the Databricks certification exam is how Spark treats CTEs under the hood:
Syntactic Sugar, Not Materialization : In Spark SQL, CTEs are purely logical abstractions. Declaring a CTE does not create a temporary physical table on disk, nor does it persist the CTE's intermediate output in executor memory.
Plan Inlining : During query optimization, Catalyst may inline eligible CTE definitions; it can also retain/reuse exchanges or subplans. A CTE name alone does not promise materialization or reuse.
Redundant Computation Risk : If an expensive CTE (such as one performing heavy aggregations or multi-table joins) is referenced multiple times within the same statement (for example, in two separate join branches or a UNION ALL ), Spark may evaluate repeated subplans more than once, depending on optimization and exchange reuse; inspect the physical plan. User Query: WITH cte AS (Expensive Scan + Agg) SELECT * FROM cte JOIN cte ...
- │
- ▼
- Catalyst Inlined Plan: (Expensive Scan + Agg) JOIN (Expensive Scan + Agg)
Tip
Mitigating CTE Recomputation : If an analytical query references an expensive CTE more than once, break the query in PySpark. Materialize the intermediate CTE into an explicit DataFrame, invoke df.cache() (or write to a temporary Delta table), and register it as a temporary view before executing the outer query. While Adaptive Query Execution (AQE) can sometimes detect identical subtrees via ReusedExchange , an explicitly materialized cache can enable reuse, but evictions/failures can still require recomputation.
3. Subqueries in Spark SQL: Scalar, Correlated, and Predicates
A subquery is a SELECT statement nested inside an outer SQL query. Spark SQL categorizes subqueries based on their return shape (scalar vs table) and whether they reference columns from outer relations (correlated vs non-correlated).
Scalar Subqueries
A scalar subquery returns at most one row and exactly one column . Because it evaluates to a single scalar value, it can be placed wherever a literal or column expression is valid, including SELECT lists and comparison predicates in WHERE and HAVING clauses. -- Scalar subquery in the SELECT list and WHERE clause
- SELECT
- emp_id,
- emp_name,
- salary,
- salary - (SELECT AVG(salary) FROM enterprise.employees) AS diff_from_company_avg
- FROM enterprise.employees
- WHERE salary > (SELECT AVG(salary) FROM enterprise.employees);
- Runtime Multi-Row Exception
If a scalar subquery returns zero rows at runtime, Spark evaluates the expression as NULL . However, if the scalar subquery returns two or more rows , Spark cannot decide which value to assign to the scalar slot. Physical execution terminates immediately with a runtime error: org.apache.spark.SparkRuntimeException: Scalar subquery returned more than one row \texttt{org.apache.spark.SparkRuntimeException: Scalar subquery returned more than one row} org.apache.spark.SparkRuntimeException: Scalar subquery returned more than one row
This behavior is strictly enforced during runtime execution; the query analyzer cannot always predict how many rows a subquery will produce until the data is scanned.
Correlated vs Non-Correlated Subqueries
Non-Correlated Subquery : Completely self-contained; references no attributes from the outer query. Catalyst evaluates the subquery once, broadcasting or caching the result across executors.
Correlated Subquery : References one or more columns from the enclosing outer query relation (termed an outer reference). -- Correlated subquery: inner query references outer table alias 'e.dept_id'
- SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
- FROM enterprise.employees e
- WHERE e.salary > (
- SELECT AVG(inner_emp.salary)
- FROM enterprise.employees inner_emp
- WHERE inner_emp.dept_id = e.dept_id
- );
During optimization, Catalyst decorrelates subqueries wherever possible, rewriting nested row-by-row lookups into distributed hash or sort-merge joins.
Subquery Predicates in WHERE : IN , EXISTS , NOT IN , NOT EXISTS
When subqueries are placed in WHERE filter clauses, Spark rewrites them into relational joins under the hood: -- 1. Subquery using IN
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);
- - 2. Equivalent subquery using EXISTS
- SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Both queries are transformed by Catalyst into a LEFT SEMI JOIN . A Left Semi Join emits rows from the left table ( customers ) if a match exists in the right table ( orders ), without duplicating left rows and without including right-table columns in the output schema.
The Critical NOT IN NULL Trap
A classic trap on certification exams involves the behavioral difference between NOT IN and NOT EXISTS when NULL values are present.
Under ANSI SQL three-valued logic, boolean expressions evaluate to TRUE , FALSE , or UNKNOWN (represented by NULL ): val NOT IN (1, 2, NULL) ⟺ ( val ≠ 1 ) AND ( val ≠ 2 ) AND ( val ≠ NULL ) \text{val NOT IN (1, 2, NULL)} \iff (\text{val} \neq 1) \text{ AND } (\text{val} \neq 2) \text{ AND } (\text{val} \neq \text{NULL}) val NOT IN (1, 2, NULL) ⟺ ( val = 1 ) AND ( val = 2 ) AND ( val = NULL )
Because any equality or inequality comparison with NULL yields UNKNOWN , the entire chained AND expression evaluates to UNKNOWN whenever val is not in the set. Since WHERE clauses require a strict TRUE to retain a row, all rows are discarded .
Caution
The Exam Trap : If the subquery in a WHERE col NOT IN (SELECT other_col FROM ...) statement returns even a single NULL value , the entire query will return zero rows , regardless of how many valid records exist! To avoid this catastrophe:
Use NOT EXISTS , which evaluates existence and is immune to NULL attributes.
Filter out nulls in the subquery: WHERE other_col IS NOT NULL .
Perform an explicit LEFT ANTI JOIN .