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

FileSchema in its footer
day=2024-03-01/part-0.parquetorder_id bigint, amount int, cust_id bigint
day=2024-03-08/part-0.parquetorder_id bigint, amount int, cust_id bigint, coupon string
day=2024-03-12/part-0.parquetorder_id bigint, amount double, cust_id bigint, coupon string
day=2024-03-14/part-0.parquetorder_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?

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')
)

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.