title: How can I achieve following spark behaviour using replaceWhere clause
body: <p>I want to write data in delta tables incrementally while replacing (overwriting) partitions already present in sink. Example:
  Consider this data inside my delta table already partionned by id column:</p>
  <pre><code>+---+---+
  | id|  x|
  +---+---+
  |  1|  A|
  |  2|  B|
  |  3|  C|
  +---+---+
  </code></pre>
  <p>Now, I would like to insert the following dataframe:</p>
  <pre><code>+---+---------+
  | id|        x|
  +---+---------+
  |  2|      NEW|
  |  2|      NEW|
  |  4|        D|
  |  5|        E|
  +---+---------+
  </code></pre>
  <p>The desired output is this</p>
  <pre><code>+---+---------+
  | id|        x|
  +---+---------+
  |  1|        A|
  |  2|      NEW|
  |  2|      NEW|
  |  3|        C|
  |  4|        D|
  |  5|        E|
  +---+---------+
  </code></pre>
  <p>What I did is the following:</p>
  <pre><code>df = spark.read.format(&quot;csv&quot;).option(&quot;sep&quot;, &quot;;&quot;).option(&quot;header&quot;, &quot;true&quot;).load(&quot;/mnt/blob/datafinance/bronze/simba/test/in/input.csv&quot;)
  Ids=[x.id for x in df.select(&quot;id&quot;).distinct().collect()]
  for Id in Ids:
    df.filter(df.id==Id).write.format(&quot;delta&quot;).option(&quot;mergeSchema&quot;, &quot;true&quot;).partitionBy(&quot;id&quot;).option(&quot;replaceWhere&quot;, &quot;id == '$i'&quot;.format(i=Id)).mode(&quot;append&quot;).save(&quot;/mnt/blob/datafinance/bronze/simba/test/res/&quot;)
  spark.read.format(&quot;delta&quot;).option(&quot;sep&quot;, &quot;;&quot;).option(&quot;header&quot;, &quot;true&quot;).load(&quot;/mnt/blob/datafinance/bronze/simba/test/res/&quot;).show()
  </code></pre>
  <p>And this is the result:</p>
  <pre><code>+---+---------+
  | id|        x|
  +---+---------+
  |  2|        B|
  |  1|        A|
  |  5|        E|
  |  2|      NEW|
  |  2|NEW AUSSI|
  |  3|        C|
  |  4|        D|
  +---+---------+
  </code></pre>
  <p>As you can see it appended all value without replacing the partition id=2 which was already present in table.</p>
  <p>I think it is because of <code>mode(&quot;append&quot;)</code>.
  But changing it to <code>mode(&quot;overwrite&quot;)</code> throws the following error:</p>
  <pre><code>Data written out does not match replaceWhere 'id == '$i''.
  </code></pre>
  <p>Can anyone tell me how to achieve what I want please ?</p>
  <p>Thank you.</p>