What causes COPY INTO Null Value in Non-Nullable Column?

Source file contains empty fields that map to NOT NULL columns in the target table. EMPTY_FIELD_AS_NULL file format option is set to TRUE, converting empty strings to NULL. Source system started omitting previously required fields. Delimiter parsing issue causing field boundaries to misalign, producing unexpected NULLs. File format's NULL_IF parameter converting non-empty values (e.g. 'N/A', '0') to NULL

How do I fix COPY INTO Null Value in Non-Nullable Column?

Inspect the failing file: `SELECT $ FROM @stage/file.csv WHERE $ IS NULL LIMIT 10`. If empty fields should be treated as a default value, use a SELECT transformation: `COPY INTO t FROM (SELECT COALESCE($3, 'default') FROM @stage/file.csv)`. Temporarily set `ON_ERROR = CONTINUE` to load valid rows and get a full count of NULL violations. Fix the upstream source system or pipeline to populate required fields. Consider altering the table column to allow NULLs if the business logic permits missing values. Review the file format's `NULL_IF` setting to ensure it is not unexpectedly converting valid values

High severitydata loading

Power BI Refresh Error:
COPY INTO Null Value in Non-Nullable Column

What does this error mean?

A COPY INTO command failed because a NULL or empty value in the source file was loaded into a column defined as NOT NULL in the target table.

Common causes

1Source file contains empty fields that map to NOT NULL columns in the target table
2EMPTY_FIELD_AS_NULL file format option is set to TRUE, converting empty strings to NULL
3Source system started omitting previously required fields
4Delimiter parsing issue causing field boundaries to misalign, producing unexpected NULLs
5File format's NULL_IF parameter converting non-empty values (e.g. 'N/A', '0') to NULL

How to fix it

1Inspect the failing file: `SELECT $<col_num> FROM @stage/file.csv WHERE $<col_num> IS NULL LIMIT 10`
2If empty fields should be treated as a default value, use a SELECT transformation: `COPY INTO t FROM (SELECT COALESCE($3, 'default') FROM @stage/file.csv)`
3Temporarily set `ON_ERROR = CONTINUE` to load valid rows and get a full count of NULL violations
4Fix the upstream source system or pipeline to populate required fields
5Consider altering the table column to allow NULLs if the business logic permits missing values
6Review the file format's `NULL_IF` setting to ensure it is not unexpectedly converting valid values

Frequently asked questions

What does EMPTY_FIELD_AS_NULL do?

When set to TRUE (the default for CSV), empty fields in the file are loaded as SQL NULL. If your table has NOT NULL constraints, set it to FALSE and handle empty strings explicitly in a transformation.