https://community.databricks.com/t5/data-engineering/merge-didn-t-fail-while-inserting-wrong-data-type-values/m-p/25533#M17772 <P>Hello. During some example cases we were running, in order to identify how Databricks treats possible wrong actions we could make, we noticed that merge doesn't fail while inserting different data type values from the ones in the corresponding table. Please find the example below:</P><P></P><P><B>Target table schema </B></P><P><span class="lia-inline-image-display-wrapper" image-alt="Target table schema"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1298iDDE65D2FD3B75971/image-size/large?v=v2&amp;px=999" role="button" title="Target table schema" alt="Target table schema" /></span><B>View used to update target table</B></P><P>Here you can see that we accidentally inserted the Status values in the Eff_Dt column (Date data type)</P><P><span class="lia-inline-image-display-wrapper" image-alt="merge_failure"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1304i06FAFD930AC51314/image-size/large?v=v2&amp;px=999" role="button" title="merge_failure" alt="merge_failure" /></span><B>Merge along with the success result</B></P><P><span class="lia-inline-image-display-wrapper" image-alt="image"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1310iE71E7097A7D7A096/image-size/large?v=v2&amp;px=999" role="button" title="image" alt="image" /></span><B>Target table after the result</B></P><P><span class="lia-inline-image-display-wrapper" image-alt="merge_failure3"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1308i4C512CE0D2CD06C6/image-size/large?v=v2&amp;px=999" role="button" title="merge_failure3" alt="merge_failure3" /></span>You can see that rows 10 &amp; 11 inserted in the table without failing but with null value in EFF_DT (date type while we tried to insert string values). We would expect the merge to fail and not complete any of the DML actions. Moreover, we noticed that in this case in the identity column, id #9 was skipped. </P><P></P><P>Is this behavior expected? Could you please provide any insight? Thank you in advance</P> Thu, 27 Oct 2022 08:43:39 GMT elgeo 2022-10-27T08:43:39Z https://community.databricks.com/t5/data-engineering/merge-didn-t-fail-while-inserting-wrong-data-type-values/m-p/25533#M17772 <P>Hello. During some example cases we were running, in order to identify how Databricks treats possible wrong actions we could make, we noticed that merge doesn't fail while inserting different data type values from the ones in the corresponding table. Please find the example below:</P><P></P><P><B>Target table schema </B></P><P><span class="lia-inline-image-display-wrapper" image-alt="Target table schema"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1298iDDE65D2FD3B75971/image-size/large?v=v2&amp;px=999" role="button" title="Target table schema" alt="Target table schema" /></span><B>View used to update target table</B></P><P>Here you can see that we accidentally inserted the Status values in the Eff_Dt column (Date data type)</P><P><span class="lia-inline-image-display-wrapper" image-alt="merge_failure"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1304i06FAFD930AC51314/image-size/large?v=v2&amp;px=999" role="button" title="merge_failure" alt="merge_failure" /></span><B>Merge along with the success result</B></P><P><span class="lia-inline-image-display-wrapper" image-alt="image"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1310iE71E7097A7D7A096/image-size/large?v=v2&amp;px=999" role="button" title="image" alt="image" /></span><B>Target table after the result</B></P><P><span class="lia-inline-image-display-wrapper" image-alt="merge_failure3"><img src="https://community.databricks.com/t5/image/serverpage/image-id/1308i4C512CE0D2CD06C6/image-size/large?v=v2&amp;px=999" role="button" title="merge_failure3" alt="merge_failure3" /></span>You can see that rows 10 &amp; 11 inserted in the table without failing but with null value in EFF_DT (date type while we tried to insert string values). We would expect the merge to fail and not complete any of the DML actions. Moreover, we noticed that in this case in the identity column, id #9 was skipped. </P><P></P><P>Is this behavior expected? Could you please provide any insight? Thank you in advance</P> Thu, 27 Oct 2022 08:43:39 GMT https://community.databricks.com/t5/data-engineering/merge-didn-t-fail-while-inserting-wrong-data-type-values/m-p/25533#M17772 elgeo 2022-10-27T08:43:39Z https://community.databricks.com/t5/data-engineering/merge-didn-t-fail-while-inserting-wrong-data-type-values/m-p/25534#M17773 <P>Hello. Any update on this please? Thank you in advance</P> Tue, 01 Nov 2022 07:40:30 GMT https://community.databricks.com/t5/data-engineering/merge-didn-t-fail-while-inserting-wrong-data-type-values/m-p/25534#M17773 elgeo 2022-11-01T07:40:30Z