'replaceWhere' clause in spark.write for a partitioned table

[TheDataEngineer]

Hi, I want to be clear about 'replaceWhere' clause in spark.write.

Here is the scenario:
I would like to add a column to few existing records.
The table is already partitioned on "PickupMonth" column.

Here is example: Without 'replaceWhere'

spark.read \
.format("delta") \
.load(destination_path) \
.where("PickupMonth == '12' and PaymentType == '3' ") \
.withColumn("PaymentType", lit(4).cast(LongType())) \
.write \
.format("delta") \
.mode("overwrite") \
.save(destination_path)

In the above code we are reading a few records "where("PickupMonth == '12' and PaymentType == '3' ")"
and then adding a new column and writing back to the same table. Does the above code work?

If we do not explicitly mention ".option("replaceWhere", "PickupMonth = '12'") \" during write,
should it not write just the "PickupMonth == '12'" partition automatically, because the table is a partitioned table?

Why should we write as follows with ".option("replaceWhere", "PickupMonth = '12'") \"

spark.read \
.format("delta") \
.load(destination_path) \
.where("PickupMonth == '12' and PaymentType == '3' ") \
.withColumn("PaymentType", lit(4).cast(LongType())) \
.write \
.format("delta") \
.option("replaceWhere", "PickupMonth = '12'") \
.mode("overwrite") \
.save(destination_path)

in other words what is the difference between whether we mention ".option("replaceWhere", "PickupMonth = '12'") \" or not.

Thank you in advance.

[cgrant]

For this style of ETL, there are 2 methods.

The first method, strictly for partitioned tables, is Dynamic Partition Overwrites, which require a Spark configuration to be set and detect which partitions that are to be overwritten by scanning the input data.

The second method, replaceWhere, does not require the target table to be partitioned, but does require the user to explicitly tell the engine about what parts of the data to overwrite. Notably, you can scan the source dataset yourself and create the replaceWhere expressions "dynamically" yourself.

If you use overwrite mode and do not specify either of these options, the target table will be overwritten by the source dataframe, which for this workflow is likely not what you want.