Spark Query Optimization Internals | Dataford Interview Questions

Which types of query optimization do you know in Spark, and how do they work internally?

Use the supplied PostgreSQL tables to provide a hands-on SQL illustration of aggregating stage metrics and identifying completed jobs with high shuffle amplification. Explain how the corresponding Spark SQL plan could benefit from Catalyst optimization, predicate pushdown, partition pruning, join strategy selection, and Adaptive Query Execution.

Output

One row per qualifying completed job
Columns: job_id, application_name, input_gb, shuffle_read_mb, shuffle_write_mb, shuffle_ratio, shuffle_rank
Include jobs with a shuffle ratio of at least 0.50, ordered by shuffle_ratio descending, then job_id ascending

Schema

spark_jobs

Column	Type	Description
job_idPK	INT	Unique Spark job identifier
application_name	VARCHAR(100)	Spark application name
submitted_at	TIMESTAMP	Job submission timestamp
status	VARCHAR(20)	Final job status
input_gb	DECIMAL(10,2)	Input data size in gigabytes

spark_stages

Column	Type	Description
stage_idPK	INT	Unique Spark stage identifier
job_id	INT	Parent Spark job identifier
stage_name	VARCHAR(100)	Stage description
shuffle_read_mb	INT	Shuffle data read in megabytes
shuffle_write_mb	INT	Shuffle data written in megabytes

Your question is Spark Query Optimization Internals. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem