Error: DELTA_NOT_NULL_CONSTRAINT_VIOLATED NOT NULL Constraint Violated for Column

Issue

A Databricks Refresh operation in HVR fails with the following error:

[DELTA_NOT_NULL_CONSTRAINT_VIOLATED] NOT NULL constraint violated for column: [<column_name>].

Environment

• HVR 6
• Target: Databricks (Delta Lake)
• Databricks staging: Data is staged as CSV files in a cloud storage location, such as S3 or Azure Data Lake Store Gen2.
• Condition: A target column is defined as NOT NULL but receives a null value from the CSV file.

Resolution

To troubleshoot this issue, follow these two phases:

Phase 1: Diagnostic and isolation steps

1. Add the HVR action HVR_STAGING_KEEP=1 to the TARGET location group environment.

2. Rerun the Refresh operation for the affected table. It will fail, but the CSV staging files will remain in the staging directory.

3. (Optional but recommended) Prepare a test channel:

   i. Export and import the channel to a test environment.

   ii. Replace customer-specific locations and schemas with your own test locations and schemas.

   iii. Enable SQL tracing on the target location: HVR_SQL_TRACE=1.

   iv. Run a test refresh with the Create Target Tables (always recreate) option selected to generate the necessary setup and logs.

4. Locate and copy the complete COPY INTO statement from the HVR trace or log files. This statement shows the column mappings. For example, _cX to column_name.

5. Identify the failing file:

   i. Access the cloud storage location specified in the COPY INTO statement, such as the Azure portal or the S3 console.

   ii. Upload the refresh CSV files one by one to the target directory.

   iii. After each upload, run the captured COPY INTO statement in the Databricks SQL Editor, removing any trailing backslashes (\).

   iv. The file that generates the [DELTA_NOT_NULL_CONSTRAINT_VIOLATED] error is the problematic file.

6. (Optional) Identify the failing rows:

   i. Modify the COPY INTO statement for the failing column to replace null values with a placeholder value, such as 'ZZ'. For example, for the column field04 mapped to source column _c101:

   • Original: _c101::string field04
   • Modified: coalesce(_c101,'ZZ') as field04

   ii. Rerun the modified COPY INTO statement.

   iii. Query the Databricks table to find the rows that caused the constraint violation:

   SELECT * FROM `your_schema`.`item` WHERE field04 = 'ZZ';
   

Phase 2: Permanent workaround in HVR

To prevent this issue, force HVR to treat the null or missing value in the source data as an empty string ('') before it is loaded into the Databricks staging CSV file, satisfying the NOT NULL constraint.

1. In the HVR channel, add a ColumnProperties action on the source location.
2. Set the IntegrateExpression parameter to use the COALESCE function for the specific column. For example, for the column field04:
   • IntegrateExpression: COALESCE({field04},'')
3. Run a new Refresh operation or perform a normal Capture or Integrate cycle. The issue will no longer occur for this column.

Cause

This issue occurs when the HVR default CSV loading process for Refresh operations interprets missing or explicitly defined NULL values in the staging CSV files, often represented as \N, as actual null. Because the corresponding target Delta Lake table column has a NOT NULL constraint, the insertion fails.