36. When to use which — and proof they're the same

You've seen SQL and DataFrame code give identical results. Here's the thing that makes it concrete: your DataFrame chain literally compiles to SQL. Every DataFrame has a .sql() method that shows it:

q = (
    orders.dropDuplicates()
    .withColumn("line_total", F.col("quantity") * F.col("unit_price"))
    .groupBy("category")
    .agg(F.round(F.sum("line_total"), 2).alias("revenue"))
    .orderBy(F.col("revenue").desc())
)

print(q.sql(pretty=True))   # the SQL your DataFrame code becomes
q.show()

When people say "a DataFrame pipeline is a query," this is what they mean — the API is a programmatic way to build SQL. If you took the SQL course, everything you learned there is running underneath your PySpark.

So which should you write?

Reach for SQL when:

• The logic is naturally set-based (joins, group-bys, window functions) and reads cleanly as one statement.
• Teammates are SQL-fluent and will maintain it.
• You're translating an existing SQL report.

Reach for the DataFrame API when:

• You're building the query up programmatically — looping over columns, parameterizing from Python, conditionally adding steps.
• You want composable, testable functions that each take and return a DataFrame.
• You need Python-side logic (config, control flow) interleaved with the transformation.

Most real codebases use both, and that's fine — they compile to the same plan. Pick per step, by readability.

Your turn

Build a DataFrame chain (the DataFrame API, not spark.sql) that returns each category and its total quantity (alias total_qty), ordered by total_qty descending, from deduplicated orders. Assign to result.

result = orders  # <- dropDuplicates, groupBy category, sum(quantity) AS total_qty, order desc
result.show()

result = (
    orders.dropDuplicates()
    .groupBy("category")
    .agg(F.sum("quantity").alias("total_qty"))
    .orderBy(F.col("total_qty").desc())
)