Schema-on-Read Pitfalls
Concepts covered: Reading & Writing
Each Parquet file carries its own schema in its footer. A directory of raw Parquet is a pile of files written by different jobs over months, and nothing makes their schemas agree. When you read the directory, Spark settles on a single schema before touching any data. With spark.sql.parquet.mergeSchema at its default of false, Spark's documentation says that schema comes from the summary file, or from a random data file if there is none, and every file is read through it.
Four files, 3 kinds of drift
| File | Schema in its footer |
|---|---|
| day=2024-03-01/part-0.parquet | order_id bigint, amount int, cust_id bigint |
| day=2024-03-08/part-0.parquet | order_id bigint, amount int, cust_id bigint, coupon string |
| day=2024-03-12/part-0.parquet | order_id bigint, amount double, cust_id bigint, coupon string |
| day=2024-03-14/part-0.parquet | order_id bigint, amount double, customer_id bigint, coupon string |
An added column
The March 8 job added coupon. If Spark takes the March 1 footer, coupon is missing from the DataFrame and select('coupon') fails at analysis. If it takes any later footer, the March 1 rows return coupon as null. The same code can change behavior after new files land.
A changed type
On March 12, amount became a double. Suppose the chosen schema says double. The read returns at once and printSchema() looks right, but the task that opens the March 1 file fails at the action with Parquet column cannot be converted ... Column: [amount], Expected: double, Found: INT32. The error can arrive an hour into a job, from the task holding an old file. That is Spark 3.x behavior; newer releases read some widenings such as int to double, so check the version you run.
spark.read.parquet('/lake/orders') returns at once and printSchema() shows amount: double. Forty minutes into the job, a single task fails with Expected: double, Found: INT32. What happened?
- An older file stores amount as INT32, and the failure waits until a task opens that file
- The input file was corrupted during its write, so it must be rebuilt from source
- mergeSchema widened amount to double, and that widening failed on the largest file
- Spark inferred amount from a directory name, and a directory held a decimal value
A renamed column
On March 14, cust_id became customer_id. Spark matches Parquet columns by name, so it sees 2 unrelated columns. Files written under the other name return nulls for whichever one the schema holds, and nothing fails. An inner join on customer_id then drops every older order, since null keys never match.
Types inferred from directory names
Partition values get types too, inferred from directory names. store_id=007 becomes the integer 7, and a join against store codes stored as '007' finds nothing. Set spark.sql.sources.partitionColumnTypeInference.enabled to false to keep them as strings, or declare the type in an explicit schema.
Reading defensively
An explicit schema makes the result independent of which footer Spark picks, and gives the rename a single place to be reconciled:
orders_schema = StructType([
StructField('order_id', LongType()),
StructField('amount', DoubleType()),
StructField('cust_id', LongType()),
StructField('customer_id', LongType()),
StructField('coupon', StringType()),
])
orders = (
spark.read.schema(orders_schema).parquet('/lake/orders')
.withColumn('customer_key', F.coalesce('customer_id', 'cust_id'))
)On Spark 3.x this schema still cannot read the int files as double; read those days separately, cast amount, and union the 2 DataFrames. mergeSchema handles the added column by combining every footer's fields, at the price of a job that opens every file before yours starts, and it treats a rename as 2 different columns.
- Do
- Pass an explicit schema to any directory that several jobs write
- Coalesce renamed columns into a single key right after the read
- Count nulls per file with input_file_name() after upstream changes
- Avoid
- Let whichever footer Spark samples define the table
- Treat a clean printSchema() as proof every file can be read
- Join on a renamed key before reconciling its old and new names
Producers that version their payloads let you see drift before it breaks a read. The seed's webhook_payloads table records a schema_version on every delivery. Run the cell to count deliveries per version and see when each version first and last arrived; it is the same audit you would run over files grouped by input_file_name().
Reading a slow scan in order
When a scan reads more than it should, check its FileScan line from the coarsest level down. PartitionFilters comes first, because a directory dropped there never becomes a task. PushedFilters comes next, for the row groups the footers can rule out, and ReadSchema last, for the column chunks decoded. The codec and the schema change how each surviving byte is decoded and typed, and neither one changes which files a task opens.
Capstone. events is partitioned by event_date, sorted by user_id within each day and compressed with ZSTD. This query opens every file in the table. Which single change cuts its I/O the most?
result = (
spark.read.parquet('/lake/events')
.filter(F.to_date('event_ts') == '2024-03-12')
.filter(F.col('user_id') == 9001)
.select('event_id', 'event_type')
)- Rewrite the files with gzip, since a higher ratio means fewer bytes per scan
- Turn on mergeSchema, so the reader sees every column in every file
- Add repartition(400) before the filters, to spread the scan over more tasks
- Add F.col('event_date') == '2024-03-12' beside the timestamp filter, so the driver prunes
About this lesson
This section is part of the Storage Formats: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.