Study guide
8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting
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
8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting
Key Takeaways Explain This Topic AI
Invoking df.groupBy() transitions execution from a DataFrame to a RelationalGroupedDataset , requiring a terminal aggregation method such as count() , sum() , or agg() to produce a transformed DataFrame.
The agg() method accepts multiple aggregate expressions from pyspark.sql.functions ( F.count , F.countDistinct , F.sum , F.avg , F.collect_list , F.collect_set ) and enables explicit column renaming via .alias() .
F.rollup() computes hierarchical subtotals and a grand total along the specified column order ( k + 1 k+1 k + 1 grouping levels for k k k columns), whereas F.cube() generates subtotals for all 2 k 2^k 2 k possible subsets of dimensions.
The pivot() transformation rotates distinct row values into separate columns; specifying an explicit list of values (e.g., values=["Q1", "Q2", "Q3", "Q4"] ) eliminates an expensive preliminary distributed job to compute distinct keys.
Standard aggregation functions ( sum , avg , min , max , count(col) ) ignore null values during computation, whereas F.count("*") or F.count(F.lit(1)) counts all rows including null records.
Last updated: October 2026
Aggregating distributed datasets is one of the most common and resource-intensive operations in data engineering. In Apache Spark, grouping and aggregation require rearranging records across the cluster so that rows sharing identical grouping keys are colocated on the same physical executor partitions. This chapter section covers the programmatic patterns, execution behaviors, and optimization techniques required for aggregating, summarizing, and pivoting DataFrames.
1. The Grouping Lifecycle & RelationalGroupedDataset
When you invoke df.groupBy(*cols) , Spark does not immediately return a new DataFrame . Instead, it instantiates an intermediate object called a RelationalGroupedDataset (or GroupedData in PySpark terminology). # df is a DataFrame
- grouped = df.groupBy("department")
- print(type(grouped))
- # Output: <class 'pyspark.sql.group.GroupedData'>
A RelationalGroupedDataset represents an intermediate state where grouping keys have been defined, but the aggregation logic has not yet been specified. Because it is not a DataFrame , you cannot directly chain standard DataFrame operations such as select() , filter() , or withColumn() onto it. You must first invoke an aggregation method that returns a DataFrame .
Shortcut Aggregation Methods
RelationalGroupedDataset provides convenient built-in shortcut methods for common single-metric summaries across numeric columns: # Direct aggregation methods on GroupedData
- df.groupBy("department").count() # Row count per department
- df.groupBy("department").sum("salary", "bonus") # Sum of specified numeric columns
- df.groupBy("department").avg("salary") # Average (alias: mean())
- df.groupBy("department").min("salary") # Minimum value
- df.groupBy("department").max("salary") # Maximum value
While convenient, these shortcut methods have three notable limitations on production pipelines:
Auto-Generated Column Names: Spark automatically names the resulting column using the pattern <function>(<column>) (e.g., avg(salary) or sum(bonus) ). Renaming these columns requires subsequent withColumnRenamed() calls.
Uniform Operation: You cannot apply different mathematical operations to different columns in a single call (e.g., calculating the average salary and maximum bonus simultaneously).
Limited Functions: Shortcut methods do not support advanced operations such as countDistinct , approx_count_distinct , or array collections ( collect_list , collect_set ).
2. Advanced Aggregations via agg()
To construct multi-metric, fine-grained aggregation queries, use the agg(*exprs) method. By importing pyspark.sql.functions as F , you can pass any number of aggregate column expressions, apply distinct counters, perform array aggregations, and alias output columns directly. from pyspark.sql import functions as F
- summary_df = df.groupBy("department", "region").agg(
- F.count("employee_id").alias("total_employees"),
- F.countDistinct("role").alias("distinct_roles"),
- F.round(F.avg("salary"), 2).alias("avg_salary"),
- F.sum("salary").alias("total_payroll"),
- F.min("hire_date").alias("earliest_hire"),
- F.max("salary").alias("max_salary"),
- F.collect_set("skills").alias("unique_skillsets")
- )
- Column Expression Syntax vs. Dictionary Syntax
agg() also supports a Python dictionary mapping column names to string function names: # Dictionary syntax (Not recommended for complex pipelines)
- df.groupBy("department").agg({
- "salary": "avg",
- "employee_id": "count"
- })
Warning
Avoid Dictionary Syntax in Production Code: The dictionary syntax does not support .alias() , cannot compute distinct counts ( countDistinct ), cannot execute nested function calls (such as F.round(F.avg(...), 2) ), and can lead to non-deterministic column ordering. Exam questions heavily favor the column expression syntax using F.<function>() .
Core Built-in Aggregation Functions
- Function Behavior Null Handling
- F.count(col) Counts non-null values in the specified column Ignores nulls
- F.count("*") / F.count(F.lit(1)) Counts total rows in the group Counts all rows , including null records
- F.countDistinct(col, *cols) Counts distinct non-null combinations Ignores nulls ; triggers a two-phase shuffle
F.approx_count_distinct(col, rsd) Estimates distinct count using HyperLogLog++ algorithm Default relative standard deviation rsd=0.05; not a guaranteed 5% error bound. Sketches reduce memory/communication, but grouping may still shuffle
F.sum(col) / F.avg(col) Computes arithmetic sum / mean of numeric values Ignores nulls ; returns null if all values are null
F.min(col) / F.max(col) Finds minimum / maximum value based on natural ordering Ignores nulls
F.collect_list(col) Produces a Spark SQL array within each result row Preserves duplicates ; non-deterministic order
F.collect_set(col) Aggregates column values into an array of unique values Eliminates duplicates ; non-deterministic order
Important
Memory Risk with collect_list and collect_set : Both functions collect all grouped values into an in-memory array within a single partition row. If a group contains millions of records, executing collect_list() will exhaust executor JVM heap memory and trigger a fatal java.lang.OutOfMemoryError .
3. Multi-Dimensional Aggregations: Rollup and Cube
Standard groupBy() produces an aggregate row for each unique combination of specified keys. However, analytical reporting frequently demands hierarchical subtotals or combinatorial cross-tabulations.
Hierarchical Subtotals with rollup()
df.rollup(*cols) generates hierarchical grouping sets from left to right. For k k k specified columns, rollup() produces exactly k + 1 k + 1 k + 1 grouping levels : # Rollup across: year, quarter, month
- rollup_df = sales_df.rollup("year", "quarter", "month").agg(
- F.sum("revenue").alias("total_revenue")
- )
This single query evaluates four distinct grouping sets:
(year, quarter, month) — Detailed monthly totals.
(year, quarter, null) — Quarterly subtotals.
(year, null, null) — Annual subtotals.
(null, null, null) — Overall grand total.
Notice that subtotals replace the aggregated dimensions with null values to indicate that the subtotal encompasses all values across that dimension.
Combinatorial Cross-Tabulations with cube()
df.cube(*cols) evaluates aggregations across all possible combinations of the specified dimensions. For k k k columns, cube() generates 2 k 2^k 2 k grouping sets : # Cube across: department, location
- cube_df = employee_df.cube("department", "location").agg(
- F.avg("salary").alias("avg_salary")
- )
For 2 columns ( 2 2 = 4 2^2 = 4 2 2 = 4 grouping sets):
(department, location) — Subtotal per department and location.
(department, null) — Department subtotal across all locations.
(null, location) — Location subtotal across all departments.
(null, null) — Grand total across all departments and locations.
Disambiguating Subtotal Nulls: grouping_id()
When the underlying data already contains natural null values, how do you distinguish between a genuine null in the dataset and a synthetic null generated by rollup or cube to represent a subtotal? Use F.grouping_id(*cols) : subtotals_df = sales_df.cube("department", "location").agg(
- F.sum("sales").alias("total_sales"),
- F.grouping_id("department", "location").alias("gid")
- )
grouping_id() calculates a bitmask integer where each bit indicates whether a specific column is aggregated out ( 1 for aggregated out, 0 for grouped). A gid of 0 denotes the most granular group, while 3 (binary 11 ) denotes the grand total.
4. Pivoting Operations & Performance Considerations
Pivoting transposes unique row values from a categorical column into distinct output columns, typically accompanied by an aggregation. # Pivot quarterly sales data per department
- pivoted_df = sales_df.groupBy("department") \
- .pivot("quarter", values=["Q1", "Q2", "Q3", "Q4"]) \
- .agg(F.sum("revenue"))
In this query, each department forms a single row, and four new columns ( Q1 , Q2 , Q3 , Q4 ) are added to the schema containing that department's quarterly revenue.
The Critical Performance Impact of values
Notice the optional values parameter in the .pivot() call. This parameter is the single most common pivot question on certification exams:
Omitting values ( .pivot("quarter") ): Spark cannot construct the output DataFrame schema in advance because it does not know what columns to create. Therefore, Spark must submit an extra distributed job to execute a SELECT DISTINCT quarter FROM table query across the entire dataset to discover all unique values before running the actual pivot aggregation.
Supplying explicit values ( .pivot("quarter", values=[...]) ): Spark immediately knows the target schema. It skips the preliminary distinct job entirely, avoiding that preliminary discovery query; exact job counts depend on the plan.
Tip
Cardinality Warning: Pivoting across a high-cardinality column (e.g., thousands of customer IDs or zip codes) creates an excessively wide DataFrame schema. This causes massive overhead in the Catalyst optimizer and often results in driver OutOfMemory exceptions.
Statistical summaries and validation
DataFrame.summary() returns a DataFrame containing count, mean, standard deviation, min, approximate quartiles, and max for supported numeric and string columns. It is not a Python dictionary. Use an action such as show() to display it, and select named statistics when stable output matters: df.select("salary").summary("count", "mean", "min", "max").show()
df.agg(F.approx_count_distinct("employee_id", 0.02).alias("estimated_people")).show()
The relative standard deviation controls an estimator's accuracy/memory tradeoff; it is not a promise that every estimate lies within that percentage of the exact count. A smaller value can cost more memory. Compare count("*") with count("salary") to detect missing salaries before interpreting a mean, which ignores nulls. Never infer a percentage denominator from a non-null count unless that is the intended population.