Informatica PowerCenter - Expression functions¶

This page lists the Informatica PowerCenter built-in functions 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¶

Numeric functions¶

Type conversion functions¶

Conditional and null-handling functions¶

Date and time functions¶

Aggregate functions¶

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

Comparison functions¶

Encoding and hash functions¶

Variable functions¶

Informatica variable functions persist a value across rows and sessions. Snowflake SQL has no equivalent persistence, so they are approximated with window functions and the assignment form is converted to a pass-through.

Session-control functions¶

System variables¶

SYSDATE and SYSTIMESTAMP become CURRENT_TIMESTAMP in both output formats. The remaining system variables identify the session, workflow, folder, or mapping that’s running, so their translation depends on the output format. On the dbt path, most of them become a dbt variable that the generated orchestration seeds at run time, as shown in System variable seeding on the dbt path.

System variable seeding on the dbt path¶

The dbt model reads each variable with var(), and the generated orchestration task supplies the run-time value with InsertControlVariable before it calls EXECUTE DBT PROJECT. The following output is for $PMSessionName:

-- dbt model
SELECT
   '{{ var('PMSESSIONNAME') }}' :: VARCHAR AS SessionName
FROM
   source_data;

-- Generated orchestration task
CALL public.InsertControlVariable('PMSESSIONNAME', :scope, TO_VARIANT('s_pmsessionname_test'), 'VARCHAR');

SESSSTARTTIME, $PMWorkflowName, and $PMFolderName are seeded the same way, except that SESSSTARTTIME receives TO_VARIANT(CURRENT_TIMESTAMP()) and the TIMESTAMP type rather than a string. WORKFLOWSTARTTIME is the exception: the orchestration doesn’t seed it, so it keeps the value declared in dbt_project.yml unless you supply one yourself with --vars. dbt_project.yml declares PMSESSIONNAME, PMWORKFLOWNAME, and PMFOLDERNAME with a null default, and the two timestamp variables with the "1753-01-01 00:00:00" sentinel. Because the orchestration passes the real values for the seeded variables through the --vars argument, those defaults only apply when you compile the project or run a model outside the orchestration.

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, NULL is added.

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. It is converted 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 a functional-difference marker is added 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. The flag is mapped to ROUND or TRUNC and the result is cast.

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 the arguments are reversed 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)