Read & Write Spark DataFrames

Ever spent an hour fighting a CSV that won't parse, only to realize the header row had a trailing space or the date column was in three different formats? That pain is familiar to anyone who has wrestled with data loading scripts. Apache Spark DataFrames in Databricks turn that struggle into a predictable pipeline: you declare the schema, point Spark at the source, and get a distributed, typed table you can filter, join, and write back with confidence. In this lesson, you'll learn to read and write data with Spark DataFrames — the single most important skill for any data engineer working on the Databricks Lakehouse.

The problem this lesson solves

Raw data is messy. It lives in dozens of formats — CSV, JSON, Parquet, Delta — and it's scattered across object stores, databases, and streaming sources. Manually parsing files with Python's csv module or pandas.read_csv() works on a laptop, but it breaks the moment your data exceeds a few gigabytes or arrives in a non-standard schema. You need a tool that can:

• Scale across a cluster — a DataFrame isn't just an in-memory table; it's a distributed collection of rows partitioned across worker nodes.
• Handle schema drift — production data changes shape, and your loading code should survive that.
• Write to multiple destinations — from Delta Lake tables to Parquet files for downstream analytics.

Without a solid grasp of DataFrame I/O, you'll end up with brittle scripts, silent data corruption, and jobs that fail at 2 AM. This lesson is your bridge from reading files painfully to writing robust, scalable data pipelines.

Core concept / mental model

Think of Spark DataFrames as a universal adapter between your data sources and your analytics. If you've used SQL, you already know the feeling of SELECT * FROM table — DataFrames give you that same declarative power, but across any source.

What exactly is a DataFrame?

A DataFrame is an immutable, distributed collection of rows and columns. Every column has a schema — a defined data type (StringType, IntegerType, TimestampType, etc.). When you read a CSV without specifying a schema, Spark infers it by sampling the data. When you write, Spark serializes the DataFrame according to the destination format's rules.

The read–transform–write cycle

Every Spark job follows the same mental model:

1. Read — create a DataFrame from a source (file, table, query).
2. Transform — apply operations like filter(), select(), join(), groupBy().
3. Write — persist the result to a sink.

Spark is lazy: transformations are recorded in a lineage graph and only executed when an action (like count() or write()) triggers a job. This means you can define a complex pipeline without moving a single byte until you're ready.

Why Spark reads are different from pandas

If you've used pandas, you know pd.read_csv() blocks until the file is fully loaded. Spark splits the source into partitions and reads them in parallel across executors. That's why a 10 GB file that crashes pandas might complete in seconds on a cluster.

Pro tip: Always specify a schema when reading structured files. Inference reads the entire file twice and can misdetect types — a classic performance and correctness trap.

How it works step by step

Let's trace the life of a DataFrame from source to sink. You'll see the same pattern every time you use spark.read or df.write.

Step 1: Create the read path

In Databricks, spark is a global SparkSession object. To read data, call spark.read.format("csv") (or parquet, json, delta). Each format has its own options — for CSV you might set header, inferSchema, delimiter; for JSON, multiLine; for Delta, versionAsOf for time travel.

Step 2: Apply transformations (optional but common)

Once you have a DataFrame, you can chain transformations. These are lazy — nothing runs until you perform an action.

Step 3: Trigger with an action

When you call df.count(), df.show(), or df.write(), Spark builds an optimized execution plan and distributes the work across executors.

Step 4: Write to a sink

df.write.format("parquet").save("/path") serializes partitions in parallel. If you're writing to a Delta table, use saveAsTable to register it in the metastore.

Step 5: Verify — read it back

Good practice: after writing, read the output back to confirm schema and row counts match expectations.

Here's a minimal example in code:

# 1. Read
sales_df = spark.read.format("csv") \
    .option("header", "true") \
    .option("inferSchema", "true") \
    .load("/mnt/datasets/sales.csv")

# 2. Transform (lazy)
filtered = sales_df.filter(sales_df["revenue"] > 1000)

# 3. Action — triggers the job
print(f"Rows: {filtered.count()}")

# 4. Write to Parquet
filtered.write.format("parquet").mode("overwrite").save("/mnt/out/sales_high_value")

# 5. Read back to verify
check = spark.read.parquet("/mnt/out/sales_high_value")
check.show(5)

Expected output:

Rows: 2345
+----+-----+-------+--------+
|  id|name |revenue|  date  |
+----+-----+-------+--------+
|   1| Alice| 2500.0|2024-01-01|
|   2|  Bob| 3100.0|2024-01-02|
| ...|  ...|    ...|    ...|
+----+-----+-------+--------+

Hands-on walkthrough

Let's build a complete ETL mini-project: read a CSV, clean it, write to Delta, and read it back as a table. You'll use a Databricks notebook with a running cluster.

0. Setup a sample dataset

First, create a CSV file in DBFS (Databricks File System) to play with:

dbutils.fs.put("/tmp/users.csv",
  """id,name,age,city\n1,Alice,32,Seattle\n2,Bob,45,Denver\n3,Cathy,28,Portland\na,David,99,NYC""",
  True)

1. Read CSV with a defined schema

Don't trust inference for production code. Define a schema using StructType:

from pyspark.sql.types import StructType, StructField, IntegerType, StringType, LongType

schema = StructType([
    StructField("id", LongType(), True),
    StructField("name", StringType(), True),
    StructField("age", IntegerType(), True),
    StructField("city", StringType(), True)
])

df = spark.read.format("csv") \
    .schema(schema) \
    .option("header", "true") \
    .option("mode", "FAILFAST") \
    .load("/tmp/users.csv")

df.show()

Expected output:

+---+-----+----+-------+
| id| name| age|   city|
+---+-----+----+-------+
|  1|Alice|  32|Seattle|
|  2|  Bob|  45|Denver |
|  3|Cathy|  28|Portland|
|  a|David|  99|NYC    |
+---+-----+----+-------+

Notice that id is LongType but the value a appears — because FAILFAST mode would have thrown; here Spark parsed a as null? Actually, the default mode is PERMISSIVE, which inserts null for malformed fields. Let's inspect the actual schema to confirm:

print(df.schema)

You'll see that id is LongType, and the a became null — a silent data quality issue! Catching this is why schema enforcement matters.

2. Transform: clean the bad row

Drop rows with null IDs:

clean_df = df.dropna(subset=["id"])
print("Cleaned count:", clean_df.count())

3. Write to Delta Lake

Delta is the recommended format on Databricks — it supports ACID transactions, time travel, and schema evolution:

clean_df.write.format("delta") \
    .mode("overwrite") \
    .saveAsTable("default.users")

4. Read the Delta table back

Now you have a managed table registered in the metastore:

users_table = spark.table("default.users")
users_table.select("name", "age").show(5)

Expected output:

+-----+---+
| name|age|
+-----+---+
|Alice| 32|
|  Bob| 45|
|Cathy| 28|
+-----+---+

5. Write to Parquet and read it back

Sometimes you need plain files for downstream tools:

users_table.write.parquet("/tmp/users_parquet")
parquet_df = spark.read.parquet("/tmp/users_parquet")
parquet_df.printSchema()

Pro tip: Parquet is columnar — always faster for analytical queries than CSV, and it preserves schema and compression.

Compare options / when to choose what

You have several formats and read/write modes. Here's a decision matrix:

Write modes

• overwrite — replaces the existing data.
• append — appends rows to an existing location.
• ignore — writes nothing if the path already exists.
• errorifexists (default) — throws an exception if data already exists.

For Delta tables, you'll often use merge for upserts — but that's covered in a later lesson.

Troubleshooting & edge cases

Even experienced engineers stumble on these. Here's how to fix common issues:

The schema was inferred as all strings

Symptom: show() displays every column as string, and arithmetic fails. Cause: CSV inference can miss numeric types, especially with missing values. Fix: Always use a StructType schema. You can also cast columns:

df = df.withColumn("age", df["age"].cast("int"))

Your write overwrote a file you needed

Symptom: Data vanished. Cause: Used .mode("overwrite") without thinking. Fix: Use overwrite deliberately. For Delta, you can recover with time travel:

spark.read.format("delta").option("versionAsOf", 1).table("default.users")

The DataFrame is empty after reading a JSON file

Symptom: df.count() returns 0, but the file has data. Cause: JSON is multi-line by default? No — Spark expects each line to be a complete JSON object. If your JSON has pretty-printed formatting, Spark misreads. Fix: Use .option("multiLine", "true").

Partition explosion when writing to Parquet

Symptom: Thousands of tiny files, slow reads. Cause: Too many partitions given the data volume. Fix: Use .coalesce(n) or .repartition(n) before writing, or enable auto-compaction in Delta.

Column name mismatches between read and write

Symptom: Schema inference gives random names like _c0. Cause: CSV files without headers, or inconsistent headers. Fix: Set .option("header", "true") or rename columns explicitly.

Pro tip: Use .option("mode", "FAILFAST") in development to catch schema issues early, then switch to PERMISSIVE for production if you must tolerate bad rows.

What you learned & what's next

You now know how to read and write data with Spark DataFrames — from CSV and JSON to Parquet and Delta. You can:

• Create a DataFrame with spark.read, specifying format and options.
• Define an explicit schema to enforce data quality.
• Write to different sinks with the right write mode.
• Debug common schema and formatting issues.

Your next lesson covers DataFrame transformations and actions — where you'll learn to select(), filter(), join(), and groupBy() with confidence. That's where the real analytical power of DataFrames emerges.

Go ahead and practice what you've learned — then move on to the next part of the Databricks learning path.