PySpark vs Spark SQL

Here's a thing that surprises newcomers: everything you've learned in the DataFrame API has a twin in plain SQL, and Spark runs both through the exact same optimizer. There is no performance winner — pick whichever expresses the problem clearly.

The same query, two ways. Take "average order value per store, only stores above 1000 total." DataFrame API:

from pyspark.sql import functions as F

orders.groupBy("store_id") \
    .agg(F.sum("amount").alias("total"), F.avg("amount").alias("avg_order")) \
    .filter(F.col("total") > 1000)

Spark SQL:

orders.createOrReplaceTempView("orders")
spark.sql("""
    SELECT store_id, SUM(amount) AS total, AVG(amount) AS avg_order
    FROM orders
    GROUP BY store_id
    HAVING SUM(amount) > 1000
""")

You register the DataFrame as a temp view with createOrReplaceTempView, then query it with spark.sql(...), which returns — of course — another DataFrame. You can mix the two freely: SQL to get a result, then .filter() on top, or vice versa.

When to reach for which. SQL shines for complex multi-join, multi-aggregate queries that read naturally as a single statement — analysts and reviewers grok them instantly. The DataFrame API shines when you're building logic programmatically (looping over columns, parameterizing, composing reusable functions) and when you want your transformations to live in normal, testable Python.

Window functions look great in both. SQL's ROW_NUMBER() OVER (PARTITION BY genre ORDER BY plays DESC) maps one-to-one onto the Window API you learned earlier — same concept, different spelling.

The practical takeaway: fluency in both makes you flexible. In an interview, being able to say "I'd write this in SQL because it's clearer, but here's the DataFrame version too" is a genuinely strong signal. On Spark-Plug, many problems accept either dialect — solve one your favorite way, then try the other for reps.

Practice: take any aggregation or window problem you've solved and rewrite it in the other dialect.