What causes NULL_VALUE_IN_NON_NULLABLE_COLUMN?

Source data contains missing values for a required field. A JOIN or transformation produces NULL for a column that was non-nullable in a previous step. COPY INTO file has missing columns or empty fields that map to NOT NULL columns. A DEFAULT value was not set on the NOT NULL column, so absent values produce NULL. An ETL tool generating INSERT statements omits columns that have NOT NULL constraints

How do I fix NULL_VALUE_IN_NON_NULLABLE_COLUMN?

Identify the column causing the error: the error message includes the column name and offending value.. Add a COALESCE in the INSERT/transformation to provide a fallback: COALESCE(source_col, 'unknown').. If NULLs are acceptable, alter the column to remove the NOT NULL constraint: ALTER TABLE my_table ALTER COLUMN my_col DROP NOT NULL.. Set a column DEFAULT: ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT 'N/A'.. For COPY INTO, use the NULL_IF option to convert empty strings to a non-null sentinel: NULL_IF = ('').

Medium severityexecutionSnowflake →

Snowflake Error:
NULL_VALUE_IN_NON_NULLABLE_COLUMN

What does this error mean?

A NULL value was inserted or loaded into a column defined with a NOT NULL constraint. Snowflake enforces NOT NULL at write time and rejects the row.

Common causes

1Source data contains missing values for a required field
2A JOIN or transformation produces NULL for a column that was non-nullable in a previous step
3COPY INTO file has missing columns or empty fields that map to NOT NULL columns
4A DEFAULT value was not set on the NOT NULL column, so absent values produce NULL
5An ETL tool generating INSERT statements omits columns that have NOT NULL constraints

How to fix it

1Identify the column causing the error: the error message includes the column name and offending value.
2Add a COALESCE in the INSERT/transformation to provide a fallback: COALESCE(source_col, 'unknown').
3If NULLs are acceptable, alter the column to remove the NOT NULL constraint: ALTER TABLE my_table ALTER COLUMN my_col DROP NOT NULL.
4Set a column DEFAULT: ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT 'N/A'.
5For COPY INTO, use the NULL_IF option to convert empty strings to a non-null sentinel: NULL_IF = ('').

Frequently asked questions

Does Snowflake enforce NOT NULL on all DML operations?

Yes — NOT NULL is enforced on INSERT, UPDATE, COPY INTO, and MERGE. It is not enforced on the initial CREATE TABLE column definition (you can define a NOT NULL column and then populate it later).

Can I use COPY INTO with ON_ERROR = 'CONTINUE' to skip NULL violations?

Yes — ON_ERROR = 'CONTINUE' will skip rows that violate NOT NULL and load the rest. Combine with COPY_HISTORY to audit how many rows were skipped and which files contained violations.

Source · docs.snowflake.com/en/sql-reference/errors