cancel
Showing results for 
Search instead for 
Did you mean: 

SCD2 Snap: Potential limitation in "dot" notation naming of columns in Snowflake

jakescud
New Contributor

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.

0 REPLIES 0