sql - How can we add NULL as a value for a field on snowflake that accepts null value if its type is datetime

Question

I am trying to load some data from a stage into a table having the following DDL:

CREATE TABLE IF NOT EXISTS SAT_COUNTRY_PROGRAMME (
  COUNTRY_PROGRAMME_SAT_HASH_KEY VARCHAR(32) NOT NULL,
  LOAD_DT DATETIME NOT NULL,
  LOAD_END_DT DATETIME NULL DEFAULT NULL,
  RECORD_SRC VARCHAR(64) NOT NULL,
  COUNTRY_NAME VARCHAR(64) NOT NULL,
  COUNTRY_PROGRAMME_HASH_KEY VARCHAR(32) NOT NULL,
  PRIMARY KEY (COUNTRY_PROGRAMME_SAT_HASH_KEY),
  CONSTRAINT fk_SAT_COUNTRY_PROGRAMME_HUB_COUNTRY_PROGRAMME1
    FOREIGN KEY (COUNTRY_PROGRAMME_HASH_KEY)
    REFERENCES HUB_COUNTRY_PROGRAMME (COUNTRY_PROGRAMME_HASH_KEY)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
;

As you can see, the LOAD_END_DT could be NULL.

Here is the insert command:

INSERT INTO SAT_COUNTRY_PROGRAMME
(SELECT md5(md5(t.$3)), current_timestamp(), NULL, 'PORTFOLIO', (replace(replace(t.$4, t.$3), ' - ')), md5(t.$3) 
FROM @INGEST_STAGE_TEMP/country_programmes.csv (file_format=>'GENERIC_CSV_FORMAT') t);

The error is:

NULL result in a non-nullable column

Here is the file format:

ALTER FILE FORMAT "DEV_DB_IYCF"."DATA_VAULT_INGEST".GENERIC_CSV_FORMAT 
SET COMPRESSION = 'AUTO' FIELD_DELIMITER = ',' RECORD_DELIMITER = '
' 
SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE' TRIM_SPACE = TRUE 
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE' 
ESCAPE_UNENCLOSED_FIELD = '134' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('NULL');

I tried to use 'NULL' and '' but got the same error.

Answer 1

As the error message says, you are trying to insert a NULL value to a non-nullable column. Therefore you need to check if any of first 4 columns contain NULL values:

SELECT $1, $2, $3, $4 from @INGEST_STAGE_TEMP/country_programmes.csv 
(file_format=>'GENERIC_CSV_FORMAT')
where $1 is null or $2 is null or $3 is null or $4 is null ;

NULLIF is used to convert specific strings such as 'NULL' to NULL values. It's not something that may help you to avoid the "NULL result in a non-nullable column".