5.2 Built-in SQL Expressions and an Integrated Query

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.2 Built-in SQL Expressions and an Integrated Query

Key Takeaways Explain This Topic AI

concat and concat_ws have different null behavior; choose the one required by the output contract.

SQL substring is one-based; a split array is indexed differently by bracket access.

CASE WHEN evaluates branches and COALESCE chooses a non-null fallback; these functions do not change missing-data business policy.

Date parsing depends on the pattern and ANSI/parser settings; retain raw input when investigating failed conversions.

Last updated: October 2026

4. Built-in SQL Functions and Expressions

Spark SQL provides hundreds of optimized built-in functions. Built-in functions expose expression semantics to Spark and often execute efficiently in JVM/vectorized paths. Code generation support and performance depend on the specific function; arbitrary Python UDF logic is opaque to Catalyst.

String Manipulation Functions

Function Syntax Description & Exam Considerations

concat concat(col1, col2, ...) Concatenates strings. Returns NULL if ANY argument is NULL .

concat_ws concat_ws(sep, col1, col2, ...) Concatenates strings with a delimiter. Skips NULL inputs automatically.

substring substring(str, pos, len) Extracts substring. 1-based index! pos = 1 starts at the first character. Negative pos counts from the end.

trim / ltrim / rtrim trim(str) ; SQL also supports TRIM(BOTH chars FROM str) Strips leading/trailing whitespace or designated characters.

split split(str, regex [, limit]) Splits string by regex pattern into an ArrayType[StringType] .

upper / lower upper(str) , lower(str) Converts characters to uppercase or lowercase.

length length(str) Returns the character count of the string.

initcap initcap(str) Capitalizes the first letter of each word in the string.

regexp_replace regexp_replace(str, regex, rep) Replaces occurrences matching regex with replacement string. SELECT

• concat_ws(' ', first_name, last_name) AS full_name,
• substring(postal_code, 1, 3) AS postal_district,
• split(email, '@')[1] AS email_domain
• FROM sales.customers;
• Date and Timestamp Functions

Spark SQL date math assumes calendar standards (ISO 8601) and supports microsecond timestamp precision:

Function Syntax Return Type Description

current_date current_date() DATE Current date evaluated at query start.

current_timestamp current_timestamp() TIMESTAMP Current date and time evaluated at query start.

to_date to_date(col [, fmt]) DATE Parses string to DateType using optional pattern (e.g. 'yyyy-MM-dd' ). Invalid-input behavior depends on ANSI mode and parsing policy; use tolerant parsing when required.

to_timestamp to_timestamp(col [, fmt]) TIMESTAMP Parses string to TimestampType. Invalid-input behavior depends on ANSI mode and parsing policy; use tolerant parsing when required.

date_add date_add(startDate, numDays) DATE Adds integer days to a date.

date_sub date_sub(startDate, numDays) DATE Subtracts integer days from a date.

datediff datediff(endDate, startDate) INT Calculates endDate - startDate in days. Positive if endDate > startDate .

months_between months_between(endDate, startDate [, roundOff]) DOUBLE Calculates number of months between dates. Positive if endDate > startDate .

date_trunc date_trunc(format, timestamp) TIMESTAMP Truncates timestamp to specified unit: 'year' , 'month' , 'day' , 'hour' , 'minute' .

• year / month / day year(col) , month(col) , dayofmonth(col) INT Extracts date component as an integer. SELECT
• order_id,
• order_date,
• date_add(order_date, 30) AS payment_due_date,
• datediff(current_date(), order_date) AS days_since_order,
• date_trunc('month', order_date) AS order_month
• FROM sales.orders;
• Conditional Expressions and Null Handling

Spark SQL supports standard ANSI branching expressions and null substitution primitives:

• CASE WHEN : Standard multi-branch conditional evaluation. CASE
• WHEN order_amount >= 1000 THEN 'Platinum'
• WHEN order_amount >= 500 THEN 'Gold'
• WHEN order_amount >= 100 THEN 'Silver'
• ELSE 'Standard'
• END AS customer_tier

COALESCE(expr1, expr2, ...) : Evaluates arguments from left to right and returns the first non-null expression. If all arguments are null, returns NULL .

NVL(expr1, expr2) : Two-argument shorthand equivalent to COALESCE(expr1, expr2) . If expr1 is null, returns expr2 .

IF(condition, true_val, false_val) : Functional ternary branch. Returns true_val if condition evaluates to true; otherwise returns false_val .

NULLIF(expr1, expr2) : Returns NULL if expr1 == expr2 ; otherwise returns expr1 .

NANVL(expr1, expr2) : Used for floating-point columns. Returns expr2 if expr1 is NaN ( Double.NaN or Float.NaN ); otherwise returns expr1 .

5. End-to-End Production Scenario Walkthrough

Consider an enterprise e-commerce platform that needs to compute customer loyalty risk tiers. The query must identify high-value customers, compute their days since last purchase, handle missing contact info, and flag inactive accounts: WITH

• - Step 1: Clean transactions and filter for valid sales
• valid_sales AS (
• SELECT
• order_id,
• customer_id,
• order_amount,
• to_date(order_timestamp) AS order_date
• FROM ecommerce.orders
• WHERE order_status = 'DELIVERED'
• ),
• - Step 2: Compute per-customer aggregates
• customer_metrics AS (
• SELECT
• customer_id,
• COUNT(order_id) AS total_orders,
• SUM(order_amount) AS total_revenue,
• MAX(order_date) AS latest_order_date
• FROM valid_sales
• GROUP BY customer_id
• ),
• - Step 3: Identify VIP threshold using a scalar subquery
• vip_threshold AS (
• SELECT AVG(total_revenue) * 1.5 AS threshold_val
• FROM customer_metrics
• )
• - Final Step: Join with customer catalog and assign operational flags
• SELECT
• c.customer_id,
• concat_ws(' ', c.first_name, c.last_name) AS customer_name,
• COALESCE(c.phone_number, 'NOT_PROVIDED') AS contact_phone,
• cm.total_orders,
• round(cm.total_revenue, 2) AS total_revenue,
• cm.latest_order_date,
• datediff(current_date(), cm.latest_order_date) AS days_inactive,
• CASE
• WHEN cm.total_revenue >= (SELECT threshold_val FROM vip_threshold) THEN 'VIP Tier'
• WHEN cm.total_orders >= 5 THEN 'Loyal Regular'
• ELSE 'Standard Customer'
• END AS loyalty_segment,
• IF(datediff(current_date(), cm.latest_order_date) > 90, 'CHURN_RISK', 'ACTIVE') AS risk_status
• FROM customer_metrics cm
• JOIN ecommerce.customers c ON cm.customer_id = c.customer_id
• WHERE c.customer_id NOT IN (
• - Filter out test and internal accounts, ensuring NULLs are excluded
• SELECT customer_id FROM ecommerce.internal_test_accounts WHERE customer_id IS NOT NULL
• )
• ORDER BY cm.total_revenue DESC;