Skip to the page
Tecyfy

PySpark interview question · medium

What is the difference between Spark SQL and DataFrame APIs?

The short answerThey are two front ends to the same engine.

The full answer

They are two front ends to the same engine. SQL queries and DataFrame operations are both turned into the same logical plan, optimised by Catalyst and executed identically, so performance is the same for the same logic. The differences are in how you write, compose, test and maintain the code.

Spark SQLDataFrame API
FormSQL strings or .sql filesMethod calls in Python, Scala, Java or R
Errors foundWhen the query is parsed and analysed at runtimePython: at runtime; Scala Datasets: some at compile time
CompositionCTEs and viewsVariables, functions, loops, parameters
ReuseViews, SQL user-defined functionsPython functions returning DataFrames, libraries
Dynamic logicString building (risky) or parameter markersNatural: build expressions in code
ReadabilityFamiliar to analysts; clear for joins and aggregationsFamiliar to engineers; clear for multi-step pipelines
TestingHarder to unit test piecesEasy: test each function with small DataFrames
ToolingSQL editors, BI tools, notebooksIDEs with completion and linting

Same plan, same speed

spark.sql("SELECT country, SUM(amount) AS revenue FROM orders WHERE amount > 0 GROUP BY country")

from pyspark.sql import functions as F
(spark.table("orders").filter(F.col("amount") > 0)
      .groupBy("country").agg(F.sum("amount").alias("revenue")))

explain() on both shows the same optimised plan.

When SQL fits better

  • Analytical queries with several joins and aggregations, especially when analysts will read or change them.
  • Logic shared with SQL-first tools: dbt models, BI tools, SQL warehouses.
  • Window functions and set operations, which many people find clearer in SQL.

When the DataFrame API fits better

  • Pipelines with steps that depend on configuration: columns chosen at runtime, transformations applied in a loop, conditional logic.
  • Reusable, unit-tested transformation functions.
  • Integration with Python libraries, UDFs and machine learning code.
  • Avoiding SQL string concatenation, which is fragile and a security risk with user input.

Things to know

  • Both support the same functions; SQL-only functions are reachable from DataFrames through F.expr() or selectExpr.
  • Parameterised SQL (spark.sql("... WHERE country = :c", args={"c": "DE"})) keeps SQL safe when values come from code.
  • Python UDFs, when needed, can be registered and called from SQL.

Most real projects mix the two; see the next question for how.

Last updated 16 October 2026