ExamFinch.com My Study

Study guide

2.4 Spark SQL Query Fundamentals

Practice this exam

Databricks Certified Data Engineer Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the Databricks Certified Data Engineer Associate Exam Study Plan and Strategies

2 Domain 1: Databricks Intelligence Platform (10%)

1.1 The Lakehouse Architecture 1.2 Databricks Workspace and Components 1.3 Delta Lake Fundamentals 1.4 Databricks Repos, Secrets, and Development Tools 1.5 Databricks SQL and the Photon Engine 1.6 Databricks Runtime and Compute Configuration

3 Domain 2: Development and Ingestion (30%)

2.1 Auto Loader (cloudFiles) 2.2 Reading Data with Spark SQL and PySpark 2.3 Writing Data to Delta Tables 2.4 Spark SQL Query Fundamentals 2.5 Complex Data Types and Higher-Order Functions 2.6 User-Defined Functions (UDFs) 2.7 Structured Streaming Fundamentals 2.8 Databricks Notebooks and Code Execution 2.9 Data Extraction from External Sources 2.10 Table Management and Properties

4 Domain 3: Data Processing & Transformations (31%)

3.1 The Medallion Architecture (Bronze, Silver, Gold) 3.2 Delta Lake Optimization: OPTIMIZE, VACUUM, and Liquid Clustering 3.3 Advanced SQL Transformations 3.4 Incremental Data Processing Patterns 3.5 Lakeflow Declarative Pipelines (Delta Live Tables) 3.6 Implementing a Multi-Hop Pipeline 3.7 Slowly Changing Dimensions (SCD) Patterns 3.8 Data Deduplication Strategies

5 Domain 4: Productionizing Data Pipelines (18%)

4.1 Databricks Workflows and Lakeflow Jobs 4.2 Databricks Asset Bundles (Declarative Automation Bundles) 4.3 Pipeline Monitoring, Alerting, and Error Handling 4.4 Testing and Deployment Best Practices 4.5 Pipeline Performance and Cost Optimization 4.6 Designing Idempotent and Recoverable Pipelines

6 Domain 5: Data Governance & Quality (11%)

5.1 Unity Catalog: Architecture and Three-Level Namespace 5.2 Access Control and Permissions 5.3 Data Lineage and Audit Logging 5.4 Delta Sharing 5.5 Lakehouse Federation 5.6 Data Quality Best Practices 5.7 Row-Level and Column-Level Security 5.8 Compliance and PII Data Management

2.4 Spark SQL Query Fundamentals

Key Takeaways Explain This Topic AI

Logical evaluation order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — WHERE filters rows before grouping, HAVING filters groups after aggregation.

Join types include INNER, LEFT/RIGHT/FULL OUTER, LEFT SEMI, LEFT ANTI, and CROSS; semi and anti joins filter the left side without duplicating columns.

Common table expressions (WITH ... AS) name a subquery to make multi-step queries readable and reusable within one statement.

Window functions (OVER with PARTITION BY / ORDER BY) compute ranking and running totals without collapsing rows, unlike GROUP BY.

ROW_NUMBER, RANK, and DENSE_RANK differ in how they handle ties when ranking partitioned, ordered data.

Last updated: June 2026

Query Structure and Evaluation Order

A Spark SQL SELECT is written in one order but logically evaluated in another. Understanding this order explains why some clauses can reference aliases and others cannot:

  • Step Clause Role
  • 1 FROM / JOIN Choose and combine sources
  • 2 WHERE Filter individual rows (pre-aggregation)
  • 3 GROUP BY Collapse rows into groups
  • 4 HAVING Filter groups (post-aggregation)
  • 5 SELECT Compute output columns
  • 6 ORDER BY Sort the result

The key tested distinction: WHERE filters rows before grouping , so it cannot reference aggregate functions; HAVING filters after aggregation , so HAVING count(*) > 5 is valid but WHERE count(*) > 5 is an error. Aggregate functions ( count , sum , avg , min , max ) combine many rows into one value per group.

Joins

Spark supports the full set of SQL joins. Beyond the familiar inner/outer joins, semi and anti joins are frequently tested because they filter the left table without adding the right table's columns:

  • Join Result
  • INNER JOIN Only matching rows from both
  • LEFT OUTER JOIN All left rows; nulls where right has no match
  • RIGHT / FULL OUTER Symmetric / all rows from both
  • LEFT SEMI JOIN Left rows that have a match (no right columns)
  • LEFT ANTI JOIN Left rows that have no match
  • CROSS JOIN Cartesian product SELECT c.* FROM customers c
  • LEFT ANTI JOIN orders o ON c.id = o.cust_id; -- customers with zero orders

A LEFT SEMI JOIN is logically an EXISTS filter; a LEFT ANTI JOIN is a NOT EXISTS filter. Neither duplicates left rows even if multiple right matches exist, which is why they are preferred over inner joins for pure filtering.

CTEs and Window Functions

A common table expression (CTE) names a subquery with WITH , improving readability for multi-step logic: WITH ranked AS (

  • SELECT region, sales,
  • ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS rn
  • FROM monthly)
  • SELECT * FROM ranked WHERE rn = 1; -- top seller per region

Window functions compute a value across a set of rows related to the current row without collapsing them — unlike GROUP BY , the row count is preserved. The OVER clause defines the window with PARTITION BY (groups) and ORDER BY (sequence). Ranking functions differ on ties:

  • Function Behavior on ties
  • ROW_NUMBER() Unique 1,2,3 even for ties (arbitrary)
  • RANK() Ties share a rank, then a gap (1,1,3)
  • DENSE_RANK() Ties share a rank, no gap (1,1,2)

Other window functions include LAG / LEAD (previous/next row) and running aggregates like SUM(x) OVER (ORDER BY d) .

Aggregation, Subqueries, and Set Operations

Aggregate queries collapse many rows into summary values. The standard aggregates are count , count(DISTINCT col) , sum , avg , min , max , plus collect_list / collect_set to gather values into an array. GROUP BY defines the grouping keys, and GROUP BY with ROLLUP or CUBE produces subtotals and grand totals — useful for reporting cubes.

Spark SQL also supports subqueries in several positions: scalar subqueries (returning one value) in SELECT , correlated subqueries with EXISTS / IN in WHERE , and derived tables in FROM . As shown with semi/anti joins, an EXISTS correlated subquery is often rewritten by Catalyst into a semi join.

Set operations combine two result sets that share a schema:

  • Operator Result
  • UNION Rows from both, duplicates removed
  • UNION ALL Rows from both, duplicates kept (faster)
  • INTERSECT Rows in both
  • EXCEPT / MINUS Rows in the first not in the second

A frequent exam trap: UNION deduplicates and therefore incurs a shuffle, while UNION ALL simply concatenates and is cheaper — choose UNION ALL unless you specifically need distinct rows. Finally, CASE WHEN ... THEN ... ELSE ... END provides conditional logic inside SELECT , and COALESCE(a, b) returns the first non-null argument, both heavily used when shaping query output. Combining CTEs, window functions, and aggregates lets you express sophisticated analytics in a single readable Spark SQL statement that Catalyst compiles into an optimized physical plan.

Two more tested mechanics: ORDER BY sorts the final result and can reference SELECT aliases (since it runs last), whereas WHERE and GROUP BY cannot reference SELECT aliases because they run earlier in the logical order. LIMIT n caps the returned rows and, on Databricks, QUALIFY filters directly on a window-function result ( QUALIFY ROW_NUMBER() OVER (...) = 1 ) without needing an outer subquery — a concise alternative to the CTE-plus-filter pattern for top-N-per-group queries.

Null handling also trips up many candidates. In SQL, NULL is unknown, so col = NULL is never true — you must write col IS NULL / IS NOT NULL . Aggregates like count(col) ignore nulls while count(*) counts every row, and avg / sum skip nulls entirely. Use coalesce(a, b, c) to substitute the first non-null value and nullif(a, b) to turn a sentinel into null. When joining, rows with null keys never match in an inner join (null is not equal to null), which is a frequent cause of unexpectedly missing rows.

Mastering these null semantics, the logical evaluation order, the join taxonomy, and window functions covers the bulk of the Spark SQL questions on the exam.