Snowflake | Insert overwrite fails with Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got FLOAT for column

Trying to insert overwrite data from one table with x+y columns into table with x+z columns and getting error SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got FLOAT for column DATEMODIFIED. In this case data type for DATEMODIFIED on both tables is TIMESTAMP_NTZ(9). Query goes like below

insert overwrite into tgt_table
select x1,x2,x3, DATEMODIFIED,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED from src_table)

• Column description for source table
• Column description of target table

Note:

• Datemodified is null in source table and even replacing DateModified with COALESCE(DateModified, CURRENT_TIMESTAMP::TIMESTAMP_NTZ) as DateModified doesn't solve the problem.
• Rewriting the query like below doesn't solve the problem

insert overwrite into tgt_table
select x1,x2,x3, DATEMODIFIED::TIMESTAMP_NTZ  as DateModified,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED::TIMESTAMP_NTZ  as DateModified from src_table)

Solution 1:

I suspect that the order of the columns in the target table is different. Try to define target columns in your command like this:

insert overwrite into tgt_table (x1,x2,x3, DATEMODIFIED, z1, z2)
select x1,x2,x3, DATEMODIFIED,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED from src_table)

Reference: INSERT target_col_name