Hi, I am seeing what seems to be maybe a limitation of the SCD2 snap. I am using the SCD2 snap to load some Snowflake columns named as such: <parent>.<key>. So for example this could be something like
PERSON.PHONE which pertains to the following in a JSON representation.
{
"person" {
"phone": "value"
}
}
Here are the different formats I have tried and their associated errors, note that "=" means I have it evaluated as an expression.
PERSON.PHONE- net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 184
invalid identifier 'SRC.PERSON.PHONE'
"PERSON.PHONE" - net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 962
invalid identifier '"src.PERSON.PHONE"'
'PERSON.PHONE' - com.snaplogic.snap.api.SnapDataException: com.snaplogic.api.ExecutionException: Unable to fetch data type for: 'PERSON.PHONE'
= '"'+'PERSON.PHONE'+'"' - com.snaplogic.snap.api.SnapDataException: com.snaplogic.api.ExecutionException: Unable to fetch data type for: 'PERSON.PHONE'
= "'"+"PERSON.PHONE"+"'" - com.snaplogic.snap.api.SnapDataException: com.snaplogic.api.ExecutionException: Unable to fetch data type for: 'PERSON.PHONE'
= "PERSON.PHONE" - net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 184
invalid identifier 'SRC.PERSON.PHONE'
= 'PERSON.PHONE' - net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 184
invalid identifier 'SRC.PERSON.PHONE'
= PERSON.PHONE - com.snaplogic.snap.api.ExpressionException: PERSON is undefined. Perhaps you meant: Math
I think the most telling error is the following: com.snaplogic.snap.api.SnapDataException: com.snaplogic.api.ExecutionException: Unable to fetch data type for: 'PERSON.PHONE'
Seems it is at least able to see the column in Snowflake, but cannot properly match to the appropriate data type for the language under the hood.
Any help or feedback would be appreciated. I believe for now we will try a different format such as <parent>__<key> but I feel this is not the best naming convention to use.
