Skip to main content
DatabricksPySparkSnowflakeApache Airflow

PySpark Intermediate Assessment: Transformations, Joins & UDFs

Real-world data pipelines require aggregating multi-million row datasets, joining disparate fact and dimension tables, applying business logic with conditional expressions, and safely caching intermediate results. This assessment validates your intermediate PySpark DataFrame engineering proficiency.

1. Assessment Overview & Prerequisites​

This intermediate assessment tests your hands-on ability to build production data transformations in PySpark. It covers multi-column aggregations, inner and left outer joins, conditional logic with when/otherwise, User Defined Functions (UDFs), and storage level persistence.

Recommended Prerequisites:

2. What You Will Practice & Validate​

  • Aggregations & Grouping: Calculating counts, sums, and complex group statistics across distributed partitions.
  • Relational Join Semantics: Implementing inner and left outer joins without data loss or accidental cartesian explosions.
  • Conditional Column Logic: Writing performant when().otherwise() expressions using native Spark functions.
  • UDF Overhead & Alternatives: Recognizing the performance cost of Python UDFs and using native SQL functions.

3. How to Use This Assessment & Practice Guide​

This assessment contains 10 intermediate questions:

  1. Practical Code Review: Inspect code snippets and identify correct syntax and optimal performance approaches.
  2. Review Explanations: Understand why native Spark functions beat custom Python UDFs every time.
  3. Target Standard: Achieve 80%+ before advancing to window functions, shuffles, and Catalyst physical plans.

4. Target Technical Concept Matrix​

Architectural AreaOperational PurposeProduction Significance
groupBy & aggCalculates summary metrics per key across distributed partitionsExecutes two-phase aggregation (map-side combine + reduce-side aggregate) to minimize shuffle size.
when / otherwiseNative conditional branching within DataFrame column definitionsExecuted directly in off-heap Tungsten binary format with zero Python serialization cost.
Python UDFsCustom Python functions applied row-by-row over DataFrame columnsForces serialization between JVM and Python worker processes, degrading throughput.
StorageLevel PersistenceCaches data in MEMORY, MEMORY_AND_DISK, or serialized formatProtects expensive intermediate computations from redundant recalculation.

5. Interactive Knowledge Assessment​

1. How do you group by department and count employees?

2. Which function allows custom column logic?

3. How do you cache a DataFrame?

4. Which join returns only matching rows?

5. Apply UDF convert_salary to salary column?

6. Persist DataFrame to memory and disk?

7. Remove duplicates based on columns?

8. Rename a DataFrame column?

9. Difference between map and flatMap?

10. How to perform a left join on id?

6. Deep Dive & Architectural Analysis​

The Hidden Cost of Python UDFs (Py4J Serialization)​

When you invoke a standard Python @udf, Spark cannot execute Python bytecode inside the JVM worker process:

  1. Serialization: The JVM serializes row data and sends it across a local Unix socket to a spawned Python worker process.
  2. Execution: The Python worker deserializes the data, executes the Python function row-by-row in CPython, and re-serializes the output.
  3. Return: The JVM reads the socket, deserializes the result, and wraps it back into a Spark internal row.

This serialization boundary completely bypasses the Catalyst optimizer, disables Tungsten whole-stage code generation, and can slow pipelines down by 5x to 20x compared to built-in functions. Always prefer native functions or Pandas UDFs (Vectorized UDFs using Apache Arrow).

Join Types and Ambiguous Column Names​

When joining tables with identical column names using boolean conditions:

# HAZARDOUS: Creates duplicate 'id' columns in output schema
df1.join(df2, df1["id"] == df2["id"], "left")

# SAFE: Coalesces join keys into a single unambiguous column
df1.join(df2, on="id", how="left")

Using the on="id" syntax automatically merges the join keys, preventing AnalysisException: Reference 'id' is ambiguous when querying downstream.

7. Real-World Production Scenarios & Pitfalls​

⚠️ Chaining Dozens of withColumn Calls​

  • Failure Mode: Writing 50 sequential df = df.withColumn(...) statements to transform columns.
  • Impact: Each withColumn adds a projection projection node to the Catalyst logical plan. Plans with 100+ projections cause StackOverflowError in the Catalyst optimizer during planning.
  • Production Remedy: Use a single df.select() projecting all desired expressions simultaneously.

⚠️ Accidental Cartesian Joins from Missing Join Keys​

  • Failure Mode: Calling df1.join(df2) without passing an on condition or passing an empty list.
  • Impact: Spark attempts to perform a full Cartesian cross-product ($M \times N$ rows), exhausting cluster memory and causing the job to stall indefinitely.
  • Production Remedy: Always pass explicit join keys and ensure spark.sql.crossJoin.enabled is kept at its safe default (false).

8. Canonical Tutorial Reference​

Review the canonical tutorials covering these exact architectures before or after taking this assessment:

9. Next Steps in Your Learning Progression​

Advance to the PySpark Advanced Quiz covering Window functions, repartition vs coalesce, and physical execution plan analysis.

10. Key Engineering Takeaways​

  • Avoid Python UDFs When Possible: Use native functions in pyspark.sql.functions to maintain Catalyst and Tungsten optimizations.
  • Use on="key" in Joins: Prevent duplicate, ambiguous column references by passing key names as strings.
  • Cache Only Reused DataFrames: Persisting single-use DataFrames wastes worker memory and adds unnecessary serialization overhead.