Copying file from DBFS to a table of Databricks, Is there a way to get the errors at record level ?

[inagar]

We have file of data to be ingested into a table of Databricks. Following below approach,

1. Uploaded file to DBFS
2. Creating a temporary table and loading above file to the temporary table. CREATE TABLE [USING] 
3. Use MERGE INTO to merge temp_table created in step2 with target table.

While doing this operation, there could be failure at step2 or step3. 
Observing the behaviour that, if there are any anomalies or incorrect format in the input file or target table, entire MERGE INTO / CREATE TABLE [USING] command fails.
Is there a way capture the reason for failure at record level ? Tried supplying the argument badRecordsPath as mentioned in the documentation didn't find any error files being created.

Is it safe to assume all records gets into the temp table via the CREATE TABLE command, or none if there are any anomalies with a single record? It's not partial, correct? Same for the MERGE_INTO command—it's not partial, request to confirm.

[Kaniz_Fatma]

Hi @inagar, To capture the reason for failure at the record level, you can leverage the options parameter when creating the temporary table using the CREATE TABLE [USING] command. Specifically, you can use the badRecordsPath option to specify a location where Databricks will store the records that failed to be loaded into the temporary table.

Behavior of CREATE TABLE [USING] and MERGE INTO

Regarding the behavior of the CREATE TABLE [USING] and MERGE INTO commands, your understanding is correct:

1. CREATE TABLE [USING]: If there are any anomalies or incorrect format in the input file, the entire CREATE TABLE [USING] command will fail, and none of the records will be loaded into the temporary table. This is the expected behavior, as Databricks needs to ensure the integrity of the data in the temporary table before proceeding with the merge operation.
2. MERGE INTO: Similarly, if there are any issues with the target table or the data being merged, the entire MERGE INTO command will fail. The operation is not partial, meaning that either all the records are successfully merged, or none of them are if there is a failure.

[Rishabh_Tiwari]

Hi @inagar ,

Thank you for reaching out to our community! We're here to help you. 

To ensure we provide you with the best support, could you please take a moment to review the response and choose the one that best answers your question? Your feedback not only helps us assist you better but also benefits other community members who may have similar questions in the future.

If you found the answer helpful, consider giving it a kudo. If the response fully addresses your question, please mark it as the accepted solution. This will help us close the thread and ensure your question is resolved.

We appreciate your participation and are here to assist you further if you need it!

Thanks,

Rishabh