Spark SQL: everything engineers need to know in practice

By day 9 of working with Spark, most engineers discover they're writing DataFrames when they could be writing SQL—and then wonder which is actually faster. The answer will surprise you: in production, it often doesn't matter. This guide covers Spark SQL: everything engineers need to know in practice, from creating views to mixing SQL and DataFrame code without breaking performance or readability.

Why Spark SQL matters in your data pipeline

If you've spent the last week building Spark pipelines with DataFrame transformations, you already know the rhythm: df.select(), df.filter(), df.groupBy(), and so on. Spark SQL lets you write the same logic as plain SQL queries. The real benefit isn't syntax sugar—it's having the right tool for the job. Some transformations read clearer in SQL; others are more natural in DataFrames. Teams that mix both fluently ship faster and debug more confidently.

Before we dive into code, understand that Spark SQL runs on the same Catalyst optimizer as DataFrame code. This means you're not trading performance for readability. That fundamental fact unlocks the confidence to choose whichever approach makes your code clearest.

Temporary views: df.createOrReplaceTempView()

To query a DataFrame with SQL, you first need to register it as a temporary view. Think of this as making the DataFrame visible to Spark's SQL engine under a name you choose.

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("OrdersApp").getOrCreate()

# Load or create a DataFrame
orders_df = spark.read.csv("s3://data/orders.csv", header=True, inferSchema=True)

# Register it as a temporary view
orders_df.createOrReplaceTempView("orders")

Once registered, the view exists for the lifetime of your Spark session. If you re-register with the same name, createOrReplaceTempView overwrites the old definition silently. This is handy during iterative development, but in production pipelines where you're registering views dynamically, that silent replacement can mask bugs. Use it intentionally.

There's also createTempView, which raises an error if the view already exists. Use that when you want to be explicit about not overwriting:

# Raises if "orders" already exists
orders_df.createTempView("orders")

And if you need a view to persist across sessions (for example, in a shared cluster), use a global temporary view:

# Global, but namespaced under global_temp
orders_df.createGlobalTempView("orders")
# Later, query it as: SELECT * FROM global_temp.orders

In practice, stick with createOrReplaceTempView for session-scoped views. They're simpler and fit most batch and streaming workloads.

Running SQL queries on your DataFrames

With a view registered, you run SQL via spark.sql(). The result is always a DataFrame, so you can chain more transformations or actions after:

# Simple aggregation
result = spark.sql("SELECT country, SUM(total) as revenue FROM orders GROUP BY country")
result.show()

# Write to storage
spark.sql("""
    SELECT country, SUM(total) as revenue
    FROM orders
    GROUP BY country
    HAVING SUM(total) > 1000
""").write.mode("overwrite").parquet("s3://data/revenue_by_country")

A few practical points: SQL queries in Spark are lazy, just like DataFrame transformations. Nothing executes until you call an action (show, write, collect, etc.). Multi-line strings with triple quotes keep your SQL readable. And Spark's SQL parser is strict—typos in column names or functions will fail loudly at execution time, not before.

Real scenario: you're building an ETL that needs to pivot order data, calculate running totals, and join with customer attributes. The pivot might feel clearer in SQL; the join might feel clearer in DataFrames. Neither is wrong:

# SQL: pivot feels natural
pivot_sql = spark.sql("""
    SELECT customer_id, 
           SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) as completed_revenue,
           SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) as refunded_revenue
    FROM orders
    GROUP BY customer_id
""")

# Then join in DataFrame if that's clearer
result = pivot_sql.join(customers_df, "customer_id", "left")

This mix of SQL and DataFrames is not only allowed—it's encouraged once you're comfortable with both.

Same optimizer: SQL and DataFrame code perform identically

This is the moment that changes how you think about Spark SQL. Internally, both paths feed into Catalyst, Spark's query optimizer. Whether you write:

# DataFrame API
result_df = orders_df.filter(orders_df.total > 100).groupBy("country").sum("total")

# SQL
result_sql = spark.sql("SELECT country, SUM(total) FROM orders WHERE total > 100 GROUP BY country")

Catalyst optimizes both to nearly identical execution plans. You can verify this by inspecting the plan:

result_df.explain(mode="extended")
result_sql.explain(mode="extended")

You'll see predicate pushdown, column pruning, and join reordering applied to both. The execution time will be the same (within noise margins). This means your choice of SQL or DataFrames should be driven by readability and team comfort, not performance anxiety.

Why does this matter? It frees you from micro-optimization paralysis. In many teams, debates about "SQL is slower than DataFrames" or vice versa waste energy. The real bottleneck is usually data volume, shuffle operations, or I/O, not the syntax you chose. Focus on writing correct, clear code first.

That said, understand what each optimizer pass does. If your SQL query isn't filtering early or your DataFrame chains redundant operations, the optimizer might not save you. Always reason about the work being done, not just the syntax.

Mixing both: choose what's clearest for your use case

The most productive teams don't pick one and stick with it. They use SQL when it shines and DataFrames when they do. Here's a realistic pipeline:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum as spark_sum

spark = SparkSession.builder.appName("MixedPipeline").getOrCreate()

# Load raw data
raw_orders = spark.read.csv("s3://raw/orders.csv", header=True, inferSchema=True)
customers = spark.read.parquet("s3://data/customers")

# Step 1: Clean and deduplicate with DataFrame API
orders_clean = (raw_orders
    .dropDuplicates(["order_id"])
    .filter(col("total") > 0)
    .filter(col("order_date").isNotNull()))

# Step 2: Register for SQL
orders_clean.createOrReplaceTempView("orders_clean")

# Step 3: Complex aggregation in SQL (window functions, pivots)
spark.sql("""
    SELECT 
        order_id,
        customer_id,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as recency_rank
    FROM orders_clean
""").createOrReplaceTempView("orders_ranked")

# Step 4: Join with customers in DataFrame API
ranked_orders = spark.sql("SELECT * FROM orders_ranked WHERE recency_rank <= 10")
result = ranked_orders.join(customers, "customer_id", "left")

# Step 5: Final aggregation, pick what reads clearest
# Using SQL for clarity
final = spark.sql("""
    SELECT 
        customer_id,
        COUNT(order_id) as order_count,
        SUM(total) as lifetime_value,
        AVG(total) as avg_order_value
    FROM orders_ranked o
    LEFT JOIN customers c ON o.customer_id = c.id
    WHERE recency_rank <= 10
    GROUP BY customer_id
    HAVING COUNT(order_id) >= 2
""")

final.write.mode("overwrite").parquet("s3://data/customer_metrics")

Notice the flow: DataFrames for I/O and validation, SQL for complex business logic, DataFrames again for filtering and joining. This isn't random—it's chosen for readability at each step. The performance is identical. The code is maintainable.

Common mistakes

• Forgetting to register the view before querying. If you skip createOrReplaceTempView and try to query with SQL, Spark will fail with a table not found error. Always register first.
• Assuming SQL is slower than DataFrames or vice versa. Catalyst optimizes both to the same execution plan. The bottleneck is logic and data, not syntax.
• Using createOrReplaceTempView carelessly in multi-threaded code. If two threads register views with the same name simultaneously, race conditions can occur. Use explicit locks or ensure single-threaded registration in shared environments.
• Writing overly complex SQL without breaking it into intermediate views. A 500-line SQL query is hard to debug. Break it into smaller named views—it's clearer and reusable.
• Mixing column naming conventions between SQL and DataFrames. SQL is case-insensitive by default; DataFrames are case-sensitive. Be explicit about column names to avoid surprises when switching contexts.
• Not checking the explain plan before running expensive queries. Always call .explain() on complex queries to ensure the optimizer is doing what you expect. Surprises at runtime waste infrastructure.

Key takeaways

• Register a DataFrame as a temporary view with createOrReplaceTempView(), then query it via spark.sql().
• SQL queries in Spark are lazy and return DataFrames, so you can chain further transformations.
• Catalyst optimizes SQL and DataFrame code identically—choose based on readability, not performance assumptions.
• Mix SQL and DataFrames freely in the same pipeline. Use whichever is clearer for each step.
• Always inspect the execution plan with .explain() for critical queries to catch optimization surprises early.
• In production, prioritize clarity and maintainability. Future engineers (including yourself) will thank you for readable code over clever micro-optimizations.