Spark SQL & Catalyst interview questions

1. A.a Catalyst optimization that compiles a chain of operators in a stage into a single fused Java function to cut virtual-call and iterator overhead
2. B.the Catalyst component (spark.sql.cbo.enabled) that reorders joins and picks physical operators using collected table and column statistics
3. C.a Spark table layout that hash-distributes rows into a fixed number of files per column so joins and aggregations on that column can skip the shuffle
4. D.an optimization that pushes filter conditions down to the data source so fewer rows or files are read

Answer + AI explanation with Pro

1. A.Whole-stage code generation
2. B.broadcast hint
3. C.Predicate pushdown
4. D.window function

Answer + AI explanation with Pro

1. A.Whole-stage code generation — the Catalyst component (spark.sql.cbo.enabled) that reorders joins and picks physical operators using collected table and column statistics
2. B.Whole-stage code generation — a Catalyst optimization that compiles a chain of operators in a stage into a single fused Java function to cut virtual-call and iterator overhead
3. C.Whole-stage code generation — a Common Table Expression, a named temporary result defined with WITH that improves readability and can be referenced multiple times in a query
4. D.Whole-stage code generation — a Spark SQL hint (/*+ BROADCAST(t) */) forcing the optimizer to broadcast a small table to all executors for a map-side join

Answer + AI explanation with Pro

1. A.the tree of relational operators Spark builds and rewrites through analysis and optimization rules before producing physical plans
2. B.an optimization that pushes filter conditions down to the data source so fewer rows or files are read
3. C.the resolved tree of relational operators produced before physical planning, on which rule-based optimizations are applied
4. D.the ROWS or RANGE clause defining which rows around the current row a window function aggregates, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Answer + AI explanation with Pro

1. A.table statistics
2. B.Physical plan
3. C.Column pruning
4. D.Logical plan

Answer + AI explanation with Pro

1. A.Logical plan — a Spark SQL hint (/*+ BROADCAST(t) */) forcing the optimizer to broadcast a small table to all executors for a map-side join
2. B.Logical plan — collected row counts and column histograms (gathered via ANALYZE TABLE) that the cost-based optimizer uses to choose join order and strategy
3. C.Logical plan — the tree of relational operators Spark builds and rewrites through analysis and optimization rules before producing physical plans
4. D.Logical plan — the resolved tree of relational operators produced before physical planning, on which rule-based optimizations are applied

Answer + AI explanation with Pro

1. A.the executable plan of physical operators (chosen join strategies, exchanges) selected by Catalyst using cost and rules
2. B.the Spark SQL mode (default in Spark 4) that enforces standard SQL semantics, throwing errors on overflow, divide-by-zero, and invalid casts instead of silently returning null
3. C.an optimization that pushes filter conditions down to the data source so fewer rows or files are read
4. D.the Catalyst technique that compiles a chain of operators into a single fused Java function to remove virtual-call and iterator overhead

Answer + AI explanation with Pro

1. A.Predicate pushdown
2. B.Catalyst logical plan
3. C.Physical plan
4. D.Whole-stage code generation

Answer + AI explanation with Pro

1. A.Physical plan — the Spark SQL mode (default in Spark 4) that enforces standard SQL semantics, throwing errors on overflow, divide-by-zero, and invalid casts instead of silently returning null
2. B.Physical plan — the executable plan of physical operators (chosen join strategies, exchanges) selected by Catalyst using cost and rules
3. C.Physical plan — a Spark SQL hint (/*+ BROADCAST(t) */) forcing the optimizer to broadcast a small table to all executors for a map-side join
4. D.Physical plan — an optimization that pushes filter conditions down to the data source so fewer rows or files are read

Answer + AI explanation with Pro

1. A.the executable plan of physical operators (chosen join strategies, exchanges) selected by Catalyst using cost and rules
2. B.an optimization that pushes filter conditions down to the data source so fewer rows or files are read
3. C.a Spark SQL hint (/*+ BROADCAST(t) */) forcing the optimizer to broadcast a small table to all executors for a map-side join
4. D.a SQL construct computing a value over a frame of rows related to the current row (using OVER with PARTITION BY and ORDER BY) without collapsing them into a group

Answer + AI explanation with Pro

1. A.Predicate pushdown
2. B.whole-stage code generation
3. C.Cost-based optimization
4. D.bucketing

Answer + AI explanation with Pro

1. A.Predicate pushdown — a Common Table Expression that references itself to traverse hierarchies, supported in Spark SQL from version 3.5 onward
2. B.Predicate pushdown — the Parquet and ORC reader path in Spark that decodes batches of column values at once into in-memory column vectors for cache-friendly processing
3. C.Predicate pushdown — an optimization that pushes filter conditions down to the data source so fewer rows or files are read
4. D.Predicate pushdown — an optimization that reads only the columns referenced by a query, especially effective on columnar formats like Parquet

Answer + AI explanation with Pro

1. A.an AQE feature that splits oversized skewed partitions into smaller sub-partitions so one task does not dominate a join
2. B.an optimization that reads only the columns referenced by a query, especially effective on columnar formats like Parquet
3. C.an AQE-related optimization that prunes fact-table partitions at runtime using the filtered keys of a joined dimension table
4. D.the ROWS or RANGE clause defining which rows around the current row a window function aggregates, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Answer + AI explanation with Pro

1. A.Catalyst logical plan
2. B.Cost-based optimization
3. C.window function
4. D.Column pruning

Answer + AI explanation with Pro

1. A.Column pruning — a SQL construct computing a value over a frame of rows related to the current row (using OVER with PARTITION BY and ORDER BY) without collapsing them into a group
2. B.Column pruning — a Catalyst optimization that compiles a chain of operators in a stage into a single fused Java function to cut virtual-call and iterator overhead
3. C.Column pruning — an optimization that reads only the columns referenced by a query, especially effective on columnar formats like Parquet
4. D.Column pruning — the Catalyst technique that compiles a chain of operators into a single fused Java function to remove virtual-call and iterator overhead

Answer + AI explanation with Pro

1. A.a Catalyst optimization that compiles a chain of operators in a stage into a single fused Java function to cut virtual-call and iterator overhead
2. B.the Spark SQL mode (default in Spark 4) that enforces standard SQL semantics, throwing errors on overflow, divide-by-zero, and invalid casts instead of silently returning null
3. C.an AQE-related optimization that prunes fact-table partitions at runtime using the filtered keys of a joined dimension table
4. D.a Catalyst mode that uses table and column statistics to pick join order and join strategies

Answer + AI explanation with Pro

1. A.Dynamic partition pruning
2. B.broadcast hint
3. C.whole-stage code generation
4. D.Cost-based optimization

Answer + AI explanation with Pro

1. A.Dynamic partition pruning — the tree of relational operators Spark builds and rewrites through analysis and optimization rules before producing physical plans
2. B.Dynamic partition pruning — an AQE-related optimization that prunes fact-table partitions at runtime using the filtered keys of a joined dimension table
3. C.Dynamic partition pruning — an optimization that reads only the columns referenced by a query, especially effective on columnar formats like Parquet
4. D.Dynamic partition pruning — the Catalyst component (spark.sql.cbo.enabled) that reorders joins and picks physical operators using collected table and column statistics

Answer + AI explanation with Pro

1. A.a Common Table Expression, a named temporary result defined with WITH that improves readability and can be referenced multiple times in a query
2. B.the ROWS or RANGE clause defining which rows around the current row a window function aggregates, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3. C.a Catalyst mode that uses table and column statistics to pick join order and join strategies
4. D.the resolved tree of relational operators produced before physical planning, on which rule-based optimizations are applied

Answer + AI explanation with Pro

1. A.Cost-based optimization
2. B.ANSI mode
3. C.Dynamic partition pruning
4. D.vectorized reader

Answer + AI explanation with Pro

1. A.Cost-based optimization — a Catalyst mode that uses table and column statistics to pick join order and join strategies
2. B.Cost-based optimization — an AQE-related optimization that prunes fact-table partitions at runtime using the filtered keys of a joined dimension table
3. C.Cost-based optimization — a SQL construct computing a value over a frame of rows related to the current row (using OVER with PARTITION BY and ORDER BY) without collapsing them into a group
4. D.Cost-based optimization — a Common Table Expression that references itself to traverse hierarchies, supported in Spark SQL from version 3.5 onward

Answer + AI explanation with Pro

1. A.the Catalyst technique that compiles a chain of operators into a single fused Java function to remove virtual-call and iterator overhead
2. B.an AQE-related optimization that prunes fact-table partitions at runtime using the filtered keys of a joined dimension table
3. C.an AQE feature that splits oversized skewed partitions into smaller sub-partitions so one task does not dominate a join
4. D.a SQL construct computing a value over a frame of rows related to the current row (using OVER with PARTITION BY and ORDER BY) without collapsing them into a group

Answer + AI explanation with Pro

1. A.Whole-stage code generation
2. B.CTE
3. C.Skew join optimization
4. D.cost-based optimizer

Answer + AI explanation with Pro

1. A.Skew join optimization — the tree of relational operators Spark builds and rewrites through analysis and optimization rules before producing physical plans
2. B.Skew join optimization — an AQE feature that splits oversized skewed partitions into smaller sub-partitions so one task does not dominate a join
3. C.Skew join optimization — a Common Table Expression, a named temporary result defined with WITH that improves readability and can be referenced multiple times in a query
4. D.Skew join optimization — the executable plan of physical operators (chosen join strategies, exchanges) selected by Catalyst using cost and rules

Answer + AI explanation with Pro

1. A.the window expression assigning a unique sequential rank within each partition ordered by a column
2. B.prints the parsed, analyzed, optimized, and physical plans for a query in a structured layout
3. C.the DDL that defines a bucketed Spark table to enable shuffle-free joins on the bucket column
4. D.collects table and column statistics so the cost-based optimizer can choose better plans

Answer + AI explanation with Pro

1. A.EXPLAIN FORMATTED
2. B.ANALYZE TABLE t COMPUTE STATISTICS
3. C.CREATE TABLE t (id INT) CLUSTERED BY (id) INTO 50 BUCKETS
4. D.ANALYZE TABLE t COMPUTE STATISTICS FOR ALL COLUMNS

Answer + AI explanation with Pro

1. A.ANALYZE TABLE t COMPUTE STATISTICS — collects table and column statistics so the cost-based optimizer can choose better plans
2. B.ANALYZE TABLE t COMPUTE STATISTICS — the command that prints Spark's parsed, analyzed, optimized, and physical plans in a structured layout for tuning
3. C.ANALYZE TABLE t COMPUTE STATISTICS — marks a DataFrame to be stored in memory on first action using the default MEMORY_AND_DISK storage level
4. D.ANALYZE TABLE t COMPUTE STATISTICS — prints the parsed, analyzed, optimized, and physical plans for a query in a structured layout

Answer + AI explanation with Pro

1. A.prints the parsed, analyzed, optimized, and physical plans for a query in a structured layout
2. B.the window expression assigning a unique sequential rank within each partition ordered by a column
3. C.the Spark SQL command that gathers column-level statistics to feed the cost-based optimizer
4. D.the DDL that defines a bucketed Spark table to enable shuffle-free joins on the bucket column

Answer + AI explanation with Pro

1. A.CREATE TABLE t (id INT) CLUSTERED BY (id) INTO 50 BUCKETS
2. B.ANALYZE TABLE t COMPUTE STATISTICS FOR ALL COLUMNS
3. C.ANALYZE TABLE t COMPUTE STATISTICS
4. D.EXPLAIN FORMATTED

Answer + AI explanation with Pro

1. A.EXPLAIN FORMATTED — prints the parsed, analyzed, optimized, and physical plans for a query in a structured layout
2. B.EXPLAIN FORMATTED — the command that prints Spark's parsed, analyzed, optimized, and physical plans in a structured layout for tuning
3. C.EXPLAIN FORMATTED — the DDL that defines a bucketed Spark table to enable shuffle-free joins on the bucket column
4. D.EXPLAIN FORMATTED — the window expression assigning a unique sequential rank within each partition ordered by a column

Answer + AI explanation with Pro