TRY_CAST resulting in a NULL value and causing the NULL result in a non-nullable column error

[Lenka72]

I have a SQL Server source table that has 5 columns: 

1.  3 non-nullable INTEGER columns
2.  1 non-nullable DATETIME column that records the database insert date 
3. 1 nullable DATETIME column that records the update date

The MERGE statement looks like this (the names were changed to protect the innocent): 

MERGE INTO "dbo"."my_table_name" T
USING (
SELECT *
FROM "public"."attrep_changes6004D92ED521268C"
WHERE "table_id" = 1
) S
ON (T."ASSIGNMENTID" = IFF("table_id" = 1, TRY_CAST(S."seg1" AS NUMBER(38, 0)), NULL))
AND (T."PERIODID" = IFF("table_id" = 1, TRY_CAST(S."seg2" AS NUMBER(38, 0)), NULL))
WHEN MATCHED AND "replicate_op" = 0 AND "table_id" = 1
THEN DELETE
WHEN MATCHED AND "replicate_op" <> 0 AND "table_id" = 1
THEN UPDATE SET
T."NumericColumn1" = IFF("table_id" = 1, TRY_CAST(S."col1" AS NUMBER(38, 0)), NULL),
T."NumericColumn2" = IFF("table_id" = 1, TRY_CAST(S."col2" AS NUMBER(38, 0)), NULL),
T."NumericColumn2" = IFF("table_id" = 1, TRY_CAST(S."col3" AS NUMBER(38, 0)), NULL),
T."DatabaseInsertDate" = IFF("table_id" = 1, TRY_CAST(S."col4" AS TIMESTAMP(3)), NULL),
T."DatabaseUpdateDate" = IFF("table_id" = 1, TRY_CAST(S."col5" AS TIMESTAMP(3)), NULL)
WHEN NOT MATCHED AND "replicate_op" <> 0 AND "table_id" = 1
THEN INSERT (
"NumericColumn1",
"NumericColumn2",
"NumericColumn3",
"DatabaseInsertDate",
"DatabaseUpdateDate"
)
VALUES (
IFF("table_id" = 1, TRY_CAST(S."col1" AS NUMBER(38, 0)), NULL),
IFF("table_id" = 1, TRY_CAST(S."col2" AS NUMBER(38, 0)), NULL),
IFF("table_id" = 1, TRY_CAST(S."col3" AS NUMBER(38, 0)), NULL),
IFF("table_id" = 1, TRY_CAST(S."col4" AS TIMESTAMP(3)), NULL),
IFF("table_id" = 1, TRY_CAST(S."col5" AS TIMESTAMP(3)), NULL)
);

I figured that the issue is caused by TRY_CAST and that the TRY_CAST is returning a NULL for my DatabaseInsertDate.   The FULL load works fine, but from what I can see the full load is doing a COPY INTO and not running a TRY_CAST.  I have tried adding a transformation on the Table Settings for this table to 

strftime('%Y-%m-%d %H:%M:%f', $DatabaseInsertDate) 

...but that has not helped. 

The public.attrep_changes6004D92ED521268C table goes away as soon as the error occurs so I cannot see the data behind the issue.  

Please help.  Thanks

[john_wang]

Hello @Lenka72 ,

The TRY_CAST function is used to ensure data type compatibility when merging data from the net change table (in this case, attrep_changes6004D92ED521268C) into the final target table.

However, TRY_CAST itself does not generate NULL values—it only returns NULL when the input value is already NULL.

To investigate why some column values are NULL, please do the following:

1. Set SOURCE_CAPTURE and TARGET_APPLY logging levels to Verbose.

2. Enable keepCSVFiles and keepErrorFiles by setting them to True in the target endpoint.

3. Reproduce the issue, then:
    • Review the intermediate CSV files
    • Examine the verbose task log files (decrypt them if required)

This should help identify where and why the NULL values are introduced.

Hope this helps,
John.

[Lenka72 · Accepted solution]

Thanks for your quick response, 

On the TRY_CAST and why I thought it was the culprit, is because TRY_CAST in Snowflake (as well as SQL Server) tries to case a string expression to the specified data type and if it is unable to cast it to that data type results in a NULL.  It seemed reasonable to assume since I know that the source data in the source table did not have null values (More information on that can be found here: TRY_CAST | Snowflake Documentation).

We did follow your recommendations and enable verbose logging for SOURCE_CAPTURE and TARGET_APPLY and keepCSVFiles and keepErrorFiles on the target endpoint.  Looking at the files we found that it was not sure turning the DatabaseInsertDate to NULL, but all the columns were showing up as NULL even though the source had data in all the non-nullable columns. 

After some testing and trying different options we found that removing the column store index from the source table in SQL server allowed the incremental changes to flow through without any issues. 

Thanks again.

[john_wang · Accepted solution]

Glad to hear that! Thank you for your outstanding support! @Lenka72 

BTW, This is known limitation of SQL Server itself: Replication with clustered columnstore indexes is NOT Supported. Nonclustered columnstore indexes are supported.

Regards,

John.

[Rak]

Hello @john_wang , These tables causing issues have Nonclustered Columnstore Indexes (NCCI) and In general, we do not have or use Clustered Columnstore Indexes on any of the tables.
cc: @Lenka72 

[john_wang]

Thanks for the update @Rak @Lenka72 .

Do you mind to share a NCCI sample table definition? I'd like to confirm the behavior in my labs.

[Lenka72]

@john_wang the table structure is simple (again the named have been changed to protect the innocent):

CREATE TABLE [dbo].[MyLinkTable]
(
[NumericColumn1] [int] NOT NULL,
[NumericColumn2] [int] NOT NULL,
[NumericColumn3] [int] NOT NULL,
[DatabaseInsertDate] [datetime] NULL,
[DatabaseUpdateDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AssignmentLink_Period] ADD CONSTRAINT [PK_AssignmentLink_Period] PRIMARY KEY CLUSTERED ([NumericColumn2], [NumericColumn1]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_AssignmentLink_Period_All] ON [dbo].[AssignmentLink_Period] ([NumericColumn2], [NumericColumn3], [NumericColumn1]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_AssignmentLink_Period_AssId_NumericColumn1_NumericColumn3] ON [dbo].[AssignmentLink_Period] ([NumericColumn2], [NumericColumn1]) INCLUDE ([NumericColumn3]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AssignmentLink_Period_NumericColumn3] ON [dbo].[AssignmentLink_Period] ([NumericColumn3]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AssignmentLink_Period_NumericColumn1_NumericColumn3] ON [dbo].[AssignmentLink_Period] ([NumericColumn1], [NumericColumn3]) INCLUDE ([NumericColumn2]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [CSIX_AssignmentLink_Period__Composite1] ON [dbo].[AssignmentLink_Period] ([NumericColumn2], [NumericColumn3], [NumericColumn1]) ON [PRIMARY]
GO

As you can see here the table is very simple but is frequently updated and will have a lot of data. 

We have also tested recreating the column store index and after recreating it seems to still be working, and we were able to see the incremental updates and inserts flow through and properly get applied on the target. 

We have now been advised to update our Microsoft ODBC Drivers to 18.3.  

Thanks @john_wang 

P.S. I will try to keep this conversation up to date on our findings as we continue testing in case other members of the community might find this information helpful. 

[john_wang]

Thank you for your outstanding support! @Lenka72 

BTW, the table works fine in my labs of a SQL Server to Snowflake task whatever some columns values are NULL or not NULL, the task runs smoothly. For example: