sql - Snowflake Function with MD5 Fails in Informatica, UPPER Works - Why? - Stack Overflow

I’m encountering an issue when invoking a Snowflake SQL function from Informatica. The function works fine in Snowflake but fails in Informatica when using MD5, while it succeeds with UPPER. My goal is to compute a hash in Snowflake and return a STRING to Informatica.

This does not work when called from Informatica:

CREATE OR REPLACE FUNCTION DEIDENTIFY(
    DATABASE_NAME VARCHAR,
    SCHEMA_NAME VARCHAR,
    TABLE_NAME VARCHAR,
    ATTRIBUTE_NAME VARCHAR,
    INPUT_STRING VARCHAR
)
RETURNS STRING
LANGUAGE SQL
AS
$$
    MD5(UPPER(INPUT_STRING))
$$;

Output in Snowflake: "a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6" (a STRING), but it fails in Informatica (no error in log, but we receive null).

This works fine in both Snowflake and Informatica:

CREATE OR REPLACE FUNCTION DEIDENTIFY(
    DATABASE_NAME VARCHAR,
    SCHEMA_NAME VARCHAR,
    TABLE_NAME VARCHAR,
    ATTRIBUTE_NAME VARCHAR,
    INPUT_STRING VARCHAR
)
RETURNS STRING
LANGUAGE SQL
AS
$$
    UPPER(INPUT_STRING)
$$;

Output: e.g., "JOHN123" – maps correctly to a STRING field in Informatica.

Details:

Database: Snowflake – both functions execute successfully when tested directly.

Informatica: Using Snowflake connector, target field is STRING.

Requirement: Compute a hash (e.g., MD5) in Snowflake, not in Informatica.

What I’ve Noticed:

Functions like MD5, SHA1, HEX_ENCODE, etc., fail when invoked from Informatica, even though they return STRING in Snowflake.

UPPER with concatenation (e.g., 'ID_' || UPPER(INPUT_STRING) || '_' || TABLE_NAME) works fine too.

Length is not an issue. I tried returning hardcoded hash value as a string. Works just fine.

Convert to TO_VARCHAR also do not work.

The issue is specific to Informatica invocation; Snowflake execution is not the problem.

Question:

Why does MD5 (and similar functions) fail in Informatica while UPPER works, given both return STRING? Is this a known issue with the Informatica-Snowflake connector? How can I compute a hash in Snowflake that Informatica will accept? Any specific error logs or connector settings I should check?