Spark DataFrames in Practice: Read, Transform, Write and Spark SQL

Part one covered Spark’s concepts: driver and executors, partitions, lazy evaluation, shuffle. This post actually transforms a dataset with a DataFrame — read it in, clean it, aggregate it, write it out — and looks at how Spark SQL and the DataFrame API arrive at the same place by different routes.

Why it’s nearly always a DataFrame and not an RDD

Part one mentioned that the RDD is the lowest-level abstraction. But nine tenths of day-to-day work uses a DataFrame, because it has two things an RDD doesn’t:

• A schema (columns and types): it’s a table with structure, and reading it feels like reading SQL.
• The Catalyst optimizer: you write what you want, and Catalyst works out how to do it fastest (reordering, pushing filters down…). An RDD does exactly what you told it to, in the order you said, with nobody optimizing on your behalf.

Put differently: a DataFrame is declarative and optimized; an RDD is imperative and leaves performance to you. Unless you need very fine-grained control, the DataFrame is both faster and easier to read.

Reading: give it a schema

from pyspark.sql import SparkSession, functions as F

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

# parquet carries its own schema and types — faster and more accurate than csv inferSchema
df = spark.read.parquet("orders.parquet")
df.printSchema()   # look at the columns and types

CSV works too (spark.read.csv(..., header=True, inferSchema=True)), but inferSchema costs an extra pass over the data and can still guess a type wrong; for a real pipeline I use parquet, or state the schema explicitly.

Common transformations: like working on a table

monthly = (
    df.filter(F.col("status") == "paid")                         # filter (narrow)
      .withColumn("month", F.date_format("order_date", "yyyy-MM"))  # add a column
      .groupBy("month")                                          # group (wide, needs a shuffle)
      .agg(
          F.sum("amount").alias("revenue"),
          F.countDistinct("customer_id").alias("buyers"),
      )
      .orderBy("month")
)
monthly.show()

select / withColumn / filter are narrow: each partition does its own work with no data crossing nodes, so they’re nearly free. groupBy / join are wide: pulling one group’s rows together means a shuffle — the “most expensive operation there is” from part one.

Spark SQL: the same data, written as SQL

Register the DataFrame as a temporary view and you can write SQL against it — and the result is exactly equivalent to the DataFrame version above:

df.createOrReplaceTempView("orders")

monthly = spark.sql("""
    select date_format(order_date, 'yyyy-MM') as month,
           sum(amount)                        as revenue,
           count(distinct customer_id)        as buyers
    from orders
    where status = 'paid'
    group by 1
    order by 1
""")

Why are the two equivalent? Because Catalyst compiles both into the same physical execution plan:

Writing out: partitionBy makes a re-run idempotent

monthly.write \
    .mode("overwrite") \
    .partitionBy("month") \
    .parquet("out/monthly")

partitionBy("month") splits the output into a folder per month; paired with mode("overwrite"), re-running one month overwrites only that month’s partition. Which lands exactly on the idempotence from the Airflow scheduling post: recompute the same slice of data and the result replaces the old one instead of doubling it. Spark does the computing, Airflow does the scheduling, and only when both sides hold idempotence can the pipeline be backfilled and re-run at all.

Reflection

Don’t pick a side between DataFrames and SQL — pick readability

I’ve never understood the dogma of “always use the DataFrame API” or “always write SQL”. They compile to the same plan and perform identically, so there’s exactly one criterion left: which one makes this piece of logic easier to read and maintain. Complex multi-step transformations, something I’ll split into reusable functions — DataFrame API. A plain aggregation query — SQL is shorter and clearer, and everyone on the team can read SQL. Mix them where mixing helps; don’t impose a rule on yourself.

Performance instinct still comes back to shuffle, four times out of five

After writing a pile of DataFrame operations, what actually drags is almost always a wide transformation. My habit is to ask three things first: can I filter the data down before the groupBy/join? Is the other side of the join a small table I can broadcast to skip the shuffle? Is the output partition count sensible? Getting those right pays far better than agonising over how to write a select. Same conclusion as part one: shuffling less is what Spark performance actually is.

The point of declarative is handing optimization to something smarter than you

The most worthwhile thing to absorb about DataFrames is “I describe what I want, Catalyst decides how”. That’s a different posture from hand-carving every step as an RDD and owning the ordering yourself. Most of the time, trusting the optimizer and spending your attention on “is the logic right, is there too much shuffle” beats line-by-line tuning — and it’s where modern data tooling is heading in general: free people from implementation detail so they can work on intent.