Agree, the suggested idea filters the input DataFrame but does not directly trigger partition pruning on the Delta table.

@ghofigjong Partition pruning occurs when the query explicitly applies filters on the partition columns (year, month, day) to the Delta table (oldData). To achieve this, it needs to be ensured that the filter is part of the Delta table's scan node, not just the input DataFrame. For example, include the filter directly in the Delta table query, like:

MERGE INTO delta.<path of delta table> oldData 
USING (
  SELECT * FROM delta.<path of delta table>
  WHERE year = '2023' AND month = '10' AND day = '12'
) filteredOldData
ON filteredOldData.clientid = newData.clientid
WHEN MATCHED THEN DELETE;

And then use EXPLAIN to confirm if the physical plan mentions partition pruning explicitly for oldData. So you should see partition filters like year = 2023 AND month = 10 AND day = 12 in the Delta Table scan node.