SnowConvert AI - Informatica PowerCenter - Expression functions¶

This page lists the Informatica PowerCenter built-in functions that SnowConvert AI converts and their Snowflake equivalents. The mapping is the same for both output formats (dbt and Snowflake Scripting), because both produce the same Snowflake expression. For the concept overview, see the Informatica PowerCenter overview.

Most functions map to a Snowflake function with the same name and behavior. The tables below give the Snowflake equivalent for every supported function and flag the ones whose name, arguments, or behavior differ. The Examples section shows a before and after for the functions that change.

Functions that have no Snowflake equivalent (for example, ABORT and ERROR) are converted to NULL and marked with an EWI so you can review them.

String functions¶

Informatica	Snowflake	Notes
`ASCII`	`ASCII`	Same name and behavior.
`CHR`	`CHR`	Same name and behavior.
`CHRCODE`	`ASCII`	Maps to `ASCII`.
`CONCAT`	`CONCAT`	Two arguments; nested for more. The `\|\|` operator is also supported.
`INITCAP`	`INITCAP`	Same name and behavior.
`INSTR`	`POSITION` or `REGEXP_INSTR`	See INSTR.
`INDEXOF`	`CASE` expression	Returns the 1-based position of the first matching value in the list. See INDEXOF.
`LENGTH`	`LENGTH`	Same name and behavior.
`LOWER`	`LOWER`	Same name and behavior.
`LPAD`	`LPAD`	Same name and behavior.
`LTRIM`	`LTRIM`	Same name and behavior.
`REPLACECHR`	`REPLACE` or `REGEXP_REPLACE`	See REPLACECHR and REPLACESTR.
`REPLACESTR`	`REPLACE`	Case-insensitive form is not supported natively. See REPLACECHR and REPLACESTR.
`REG_EXTRACT`	`REGEXP_SUBSTR`	Argument order differs. See REG_EXTRACT.
`REG_MATCH`	`RLIKE`	Maps to `RLIKE`.
`REG_REPLACE`	`REGEXP_REPLACE`	See REG_REPLACE.
`REVERSE`	`REVERSE`	Same name and behavior.
`RPAD`	`RPAD`	Same name and behavior.
`RTRIM`	`RTRIM`	Same name and behavior.
`SUBSTR`	`SUBSTR`	Same name and behavior; both are 1-based.
`UPPER`	`UPPER`	Same name and behavior.

Numeric functions¶

Informatica	Snowflake	Notes
`ABS`	`ABS`	Same name and behavior.
`CEIL`	`CEIL`	Same name and behavior.
`EXP`	`EXP`	Same name and behavior.
`FLOOR`	`FLOOR`	Same name and behavior.
`LN`	`LN`	Same name and behavior.
`LOG`	`LOG`	Same name and behavior.
`MOD`	`MOD`	Same name and behavior.
`POWER`	`POWER`	Same name and behavior.
`ROUND`	`ROUND`	Same name and behavior for numbers.
`SIGN`	`SIGN`	Same name and behavior.
`SQRT`	`SQRT`	Same name and behavior.
`TRUNC`	`TRUNC`	Same name and behavior for numbers.

Type conversion functions¶

Informatica	Snowflake	Notes
`TO_CHAR`	`TO_CHAR`	The format mask is translated to the Snowflake format.
`TO_DATE`	`TO_DATE`	The format mask is translated to the Snowflake format.
`TO_DECIMAL`	`CAST(ROUND(...))` or `CAST(TRUNC(...))`	A flag selects rounding or truncation. See TO_DECIMAL, TO_INTEGER, and TO_BIGINT.
`TO_INTEGER`	`CAST(ROUND(...))` or `CAST(TRUNC(...))`	A flag selects rounding or truncation. See TO_DECIMAL, TO_INTEGER, and TO_BIGINT.
`TO_BIGINT`	`CAST(ROUND(...))` or `CAST(TRUNC(...))`	A flag selects rounding or truncation. See TO_DECIMAL, TO_INTEGER, and TO_BIGINT.
`TO_FLOAT`	`TO_DOUBLE`	Maps to `TO_DOUBLE`.

Conditional and null-handling functions¶

Informatica	Snowflake	Notes
`IIF`	`IFF`	A missing false branch becomes `NULL`. See IIF.
`DECODE`	`DECODE` or `CASE`	`DECODE(TRUE, ...)` becomes a `CASE` expression. See DECODE.
`IN`	`IN`	Becomes a Snowflake `IN` predicate.
`ISNULL`	`IS NULL`	Becomes an `IS NULL` predicate. See ISNULL.
`IS_DATE`	`TRY_TO_DATE(...) IS NOT NULL`	Validates with `TRY_TO_DATE`. See IS_DATE and IS_NUMBER.
`IS_NUMBER`	`TRY_TO_NUMBER(...) IS NOT NULL`	Validates with `TRY_TO_NUMBER`. See IS_DATE and IS_NUMBER.
`IS_SPACES`	`TRIM(...) = '' OR ... IS NULL`	Returns true when the value is blank or null.

Date and time functions¶

Informatica	Snowflake	Notes
`ADD_TO_DATE`	`DATEADD`	The Informatica format code becomes a Snowflake date part. See ADD_TO_DATE.
`DATE_COMPARE`	`CASE` expression	Returns -1, 0, or 1.
`DATEDIFF`	`DATEDIFF`	The date arguments are swapped to preserve the sign. See DATEDIFF.
`GET_DATE_PART`	`DATE_PART`	The format code becomes a Snowflake date part.
`SET_DATE_PART`	`DATEADD` arithmetic	The part is set by adding the difference.
`LAST_DAY`	`LAST_DAY`	Same name and behavior.
`MAKE_DATE_TIME`	`TIMESTAMP_NTZ_FROM_PARTS`	Builds a timestamp from the year, month, day, and time parts.
`SYSDATE`	`CURRENT_TIMESTAMP`	See System variables.
`SYSTIMESTAMP`	`CURRENT_TIMESTAMP`	See System variables.

Aggregate functions¶

The two-argument Informatica aggregate form (an aggregate with a filter condition) becomes the same aggregate wrapped in a CASE expression.

Informatica	Snowflake	Notes
`AVG`	`AVG`	Filter form uses `AVG(CASE WHEN ... END)`. See Aggregates with a filter.
`COUNT`	`COUNT`	Filter form uses `COUNT(CASE WHEN ... END)`.
`SUM`	`SUM`	Filter form uses `SUM(CASE WHEN ... END)`.
`MIN`	`MIN`	Filter form uses `MIN(CASE WHEN ... END)`.
`MAX`	`MAX`	Filter form uses `MAX(CASE WHEN ... END)`.
`MEDIAN`	`MEDIAN`	Filter form uses a `CASE` expression.
`STDDEV`	`STDDEV`	Filter form uses a `CASE` expression.
`PERCENTILE`	`PERCENTILE_CONT`	The percent is divided by 100 and uses `WITHIN GROUP`.
`FIRST`	`ANY_VALUE`	Maps to `ANY_VALUE`; an FDM notes that the returned row is arbitrary.
`LAST`	`ANY_VALUE`	Maps to `ANY_VALUE`; an FDM notes that the returned row is arbitrary.
`CUME`	`SUM` window	Running total with `SUM(...) OVER (...)`.

Comparison functions¶

Informatica	Snowflake	Notes
`GREATEST`	`GREATEST`	Same name and behavior.
`LEAST`	`LEAST`	The case-insensitive option becomes a `CASE` expression on `LOWER(...)`.

Encoding and hash functions¶

Informatica	Snowflake	Notes
`MD5`	`UPPER(MD5(...))`	Informatica returns uppercase hexadecimal, so the result is wrapped in `UPPER`. An FDM notes possible encoding differences.
`ENC_BASE64`	`BASE64_ENCODE`	Maps to `BASE64_ENCODE`.
`CRC32`	`CRC32_UDF(...)`	Snowflake has no native CRC32, so SnowConvert AI generates a `CRC32_UDF`. An FDM notes possible encoding differences.

Variable functions¶

Informatica variable functions persist a value across rows and sessions. Snowflake SQL has no equivalent persistence, so SnowConvert AI approximates them with window functions and converts the assignment form to a pass-through.

Informatica	Snowflake	Notes
`SETVARIABLE`	The value expression	The assignment is dropped; the value passes through.
`SETMAXVARIABLE`	`MAX(...) OVER ()`	Approximates the running maximum.
`SETMINVARIABLE`	`MIN(...) OVER ()`	Approximates the running minimum.
`SETCOUNTVARIABLE`	`COUNT(...) OVER ()`	Approximates the running count.

Session-control functions¶

Informatica	Snowflake	Notes
`ABORT`	`NULL`	Snowflake SQL cannot stop a session, so the call becomes `NULL` and is marked with `SSC-EWI-INF0060`. See ABORT and ERROR.
`ERROR`	`NULL`	Snowflake SQL cannot skip a row, so the call becomes `NULL` and is marked with `SSC-EWI-INF0051`. See ABORT and ERROR.

System variables¶

Informatica	Snowflake	Notes
`SYSDATE`	`CURRENT_TIMESTAMP`	Current date and time.
`SYSTIMESTAMP`	`CURRENT_TIMESTAMP`	Current date and time.
`SESSSTARTTIME`	`TO_TIMESTAMP('{{ run_started_at }}')`	In dbt, the run start time. Use `CURRENT_TIMESTAMP` in plain SQL.

Examples¶

The following examples show the before (Informatica) and after (Snowflake) for the functions whose name, arguments, or behavior change.

IIF¶

IIF becomes Snowflake’s IFF. When the false branch is omitted, SnowConvert AI adds NULL.

Informatica:

IIF(SALARY > 50000, 'High', 'Low')
IIF(STATUS = 'ACTIVE', EMPLOYEE_ID)

Snowflake:

IFF((SALARY > 50000), 'High', 'Low')
IFF((STATUS = 'ACTIVE'), EMPLOYEE_ID, NULL)

DECODE¶

A standard DECODE (equality search) maps directly to Snowflake’s DECODE. The boolean form, DECODE(TRUE, condition, result, ...), becomes a CASE expression.

Informatica:

DECODE(TRUE,
  SALARY > 100000, 'Band A',
  SALARY > 50000,  'Band B',
  'Band C')

Snowflake:

CASE
  WHEN (SALARY > 100000) THEN 'Band A'
  WHEN (SALARY > 50000)  THEN 'Band B'
  ELSE 'Band C'
END

ISNULL¶

ISNULL becomes an IS NULL predicate.

Informatica:

IIF(ISNULL(PHONE_NUMBER), '000-000-0000', PHONE_NUMBER)

Snowflake:

IFF((PHONE_NUMBER IS NULL), '000-000-0000', PHONE_NUMBER)

IS_DATE and IS_NUMBER¶

These validation functions use TRY_TO_DATE or TRY_TO_NUMBER and test the result for IS NOT NULL. For IS_NUMBER, the 'integer' type maps to TRY_TO_NUMBER(value, 38, 0).

Informatica:

IS_DATE(HIRE_DATE_STR, 'YYYY-MM-DD')
IS_NUMBER(QTY_STR, 'integer')

Snowflake:

(TRY_TO_DATE(HIRE_DATE_STR, 'YYYY-MM-DD') IS NOT NULL)
(TRY_TO_NUMBER(QTY_STR, 38, 0) IS NOT NULL)

INSTR¶

A simple, case-sensitive search for the first occurrence becomes POSITION. Any start position, occurrence count, or case-insensitivity flag uses REGEXP_INSTR.

Informatica:

INSTR(FULL_NAME, ' ')
INSTR(LOG_TEXT, 'error', 1, 2)

Snowflake:

POSITION(' ' IN FULL_NAME)
REGEXP_INSTR(LOG_TEXT, 'error', 1, 2)

INDEXOF¶

INDEXOF(value, search1 [, search2, ...]) returns the 1-based position of the first search string that equals value, or 0 when none match. SnowConvert AI converts it to a CASE expression.

Informatica:

INDEXOF(ITEM_CODE, 'A', 'B', 'C')

Snowflake:

CASE
  WHEN ITEM_CODE IS NULL THEN NULL
  WHEN ITEM_CODE = 'A' THEN 1
  WHEN ITEM_CODE = 'B' THEN 2
  WHEN ITEM_CODE = 'C' THEN 3
  ELSE 0
END

REG_EXTRACT¶

REG_EXTRACT maps to REGEXP_SUBSTR. The subpattern (capture group) and case-insensitivity flag move to different argument positions.

Informatica:

REG_EXTRACT(CODE_COL, '([A-Z]{2})-([0-9]+)', 2)

Snowflake:

REGEXP_SUBSTR(CODE_COL, '([A-Z]{2})-([0-9]+)', 1, 1, 'e', 2)

REG_REPLACE¶

REG_REPLACE maps to REGEXP_REPLACE. The case-insensitivity flag becomes the trailing parameters flag.

Informatica:

REG_REPLACE(NOTES, 'confidential', '[REDACTED]', 1)

Snowflake:

REGEXP_REPLACE(NOTES, 'confidential', '[REDACTED]', 1, 0, 'i')

REPLACECHR and REPLACESTR¶

REPLACECHR becomes REPLACE for a single character or REGEXP_REPLACE for a set of characters. REPLACESTR becomes REPLACE. The first argument is a case-sensitivity flag: only the case-sensitive form (1) has an exact Snowflake equivalent, so SnowConvert AI adds a functional-difference marker for the case-insensitive form.

Informatica:

REPLACECHR(1, 'Cleveland', 'eldn', '_')
REPLACESTR(1, DESCRIPTION, 'foo', 'bar')

Snowflake:

REGEXP_REPLACE('Cleveland', '[eldn]', '_')
REPLACE(DESCRIPTION, 'foo', 'bar')

TO_DECIMAL, TO_INTEGER, and TO_BIGINT¶

These conversions take an optional flag that selects rounding (the default) or truncation. SnowConvert AI maps the flag to ROUND or TRUNC and casts the result.

Informatica:

TO_DECIMAL(AMOUNT, 2)
TO_DECIMAL(AMOUNT, 2, 1)

Snowflake:

CAST(ROUND(AMOUNT, 2) AS DECIMAL(38, 2))
CAST(TRUNC(AMOUNT, 2) AS DECIMAL(38, 2))

ADD_TO_DATE¶

ADD_TO_DATE maps to DATEADD, with the Informatica format code translated to a Snowflake date part (for example, DD to DAY, MM to MONTH, YYYY to YEAR).

Informatica:

ADD_TO_DATE(CONTRACT_START, 'MM', -6)

Snowflake:

DATEADD(MONTH, -6, CONTRACT_START)

DATEDIFF¶

DATEDIFF maps to Snowflake’s DATEDIFF, but the date arguments are swapped. Informatica computes date1 - date2, while Snowflake computes end - start, so SnowConvert AI reverses them to keep the same sign.

Informatica:

DATEDIFF('DD', END_DATE, START_DATE)

Snowflake:

DATEDIFF(DAY, START_DATE, END_DATE)

Aggregates with a filter¶

An Informatica aggregate with a filter condition (the two-argument form) becomes the same aggregate over a CASE expression.

Informatica:

SUM(ORDER_AMOUNT, STATUS = 'COMPLETE')

Snowflake:

SUM(CASE WHEN (STATUS = 'COMPLETE') THEN ORDER_AMOUNT END)

ABORT and ERROR¶

ABORT stops the session and ERROR skips a row. Snowflake SQL has neither mechanism, so both become NULL and are marked with an EWI for manual review: SSC-EWI-INF0060 for ABORT and SSC-EWI-INF0051 for ERROR.

Informatica:

IIF(AMOUNT < 0, ERROR('Negative amount not allowed'), AMOUNT)

Snowflake:

IFF((AMOUNT < 0), NULL, AMOUNT)

Note

Because ABORT and ERROR become NULL, the row is not stopped or skipped in Snowflake. Review each SSC-EWI-INF0060 and SSC-EWI-INF0051 marker and reproduce the validation with a pre-load check or a dbt test where the original logic depended on it.