SnowConvert AI - Informatica PowerCenter Functional Differences

This section provides detailed documentation for the Functional Difference (FDM) messages that SnowConvert may generate during Informatica PowerCenter conversion. An FDM marks generated code that runs in Snowflake but may behave differently from the original Informatica PowerCenter mapping, so you should verify the converted results.

For assistance with any FDM, you can use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions, or contact snowconvert-support@snowflake.com for additional support.

SSC-FDM-INF0001

Lookup policy on multiple match requires proper ordering when multiple rows match. Replace NULL with appropriate ORDER BY column(s) to ensure deterministic match selection.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter mapping that uses a lookup policy that can return more than one matching row. When multiple rows match, the lookup policy needs explicit ordering to choose a single row deterministically, so you should replace the NULL placeholder with the appropriate ORDER BY column or columns.

Best Practices

  • Replace the NULL placeholder with the ORDER BY column or columns that reproduce the original match selection, then verify the converted results against the original mapping.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0002

All input columns will be used to determine group order.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter mapping, so you should verify the converted behavior matches the original. In this case, all input columns are used to determine the group order, which may affect how rows are grouped and ordered in the converted output.

Converted Code

QUALIFY
   --** SSC-FDM-INF0002 - ALL INPUT COLUMNS WILL BE USED TO DETERMINE GROUP ORDER. **
   ROW_NUMBER() OVER (
   PARTITION BY (
      DEPARTMENT)
   ORDER BY
      NAME,
      SALARY,
      DEPARTMENT) = 1

Best Practices

  • Verify the converted grouping and ordering against the original mapping, and add explicit ordering columns where a specific order is required.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0003

IIF function missing onFalseValue parameter defaulted to NULL.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an IIF function that does not provide the false-branch value. Because the false value is missing, SnowConvert defaults it to NULL, so you should replace it with an appropriate default value based on the data type of the true-branch value.

Best Practices

  • Replace the defaulted NULL with an explicit default value that matches the data type of the true-branch value, then verify the converted results against the original mapping.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0004

TO_CHAR expression data type could not be determined and might present different results.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a TO_CHAR expression whose input data type could not be determined. Because the data type is unknown, the converted expression might present different results, so you should verify the converted behavior matches the original.

Best Practices

  • Verify the converted TO_CHAR output against the original mapping, and add an explicit data type or format where the input type is ambiguous.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0005

INSTR linguistic comparison mode is not fully supported in Snowflake.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an INSTR expression that uses linguistic comparison. This comparison mode is not fully supported because Snowflake uses binary comparison, so language-specific collation rules may not be applied and results may differ.

Best Practices

  • Verify the converted INSTR results against the original mapping, especially for strings that rely on language-specific collation rules.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0006

TO_DATE without format uses session default date format which may differ in Snowflake.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a TO_DATE expression that does not specify a format. Without a format, the expression relies on the session default date format, which may differ between Informatica PowerCenter and Snowflake, so results may differ.

Best Practices

  • Verify that the Snowflake DATE_INPUT_FORMAT parameter matches the Informatica session date format, or add an explicit format string to the converted expression.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0007

Normalizer GK port uses ROW_NUMBER; behavior equivalent to Restart=YES.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a Normalizer generated key port to ROW_NUMBER, which always starts from 1 for each query execution. This behavior is functionally equivalent to the Restart set to YES option in Informatica PowerCenter, so you should verify the generated key values if they are used as a foreign key in downstream targets.

Best Practices

  • Verify the generated key values against the original mapping, especially where those keys are used as foreign keys in downstream targets.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0008

Normalizer GK port uses ROW_NUMBER; Reset=YES behavior not replicated.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a Normalizer generated key port to ROW_NUMBER. The Reset set to YES behavior is not replicated in the dbt translation, because in Informatica PowerCenter that option restores the generated key counter to its pre-session value at session end, while ROW_NUMBER always starts from 1. You should verify the generated key values if they are used as a foreign key in downstream targets.

Best Practices

  • Verify the generated key values against the original mapping, especially where those keys are used as foreign keys in downstream targets.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0009

Sorter Transformation Scope Transaction not modelled.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a Sorter transformation whose scope is set to Transaction. Transaction boundaries are not modelled in Snowflake SQL, so the sort is applied globally across all input rows. You should verify that global sorting produces correct results for the downstream logic.

Best Practices

  • Verify that global sorting produces the correct results for the downstream logic, and add transaction-level partitioning where the original scope is required.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0010

ROUND(date) converted to DATE_TRUNC which always truncates instead of rounding.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter ROUND on a date to Snowflake DATE_TRUNC. Informatica rounds to the nearest date boundary, so a date past the midpoint of a period rounds up, while DATE_TRUNC always truncates to the start of the period. Results may differ for dates past the midpoint.

Best Practices

  • Verify the converted date values against the original mapping, especially for dates that fall past the midpoint of a period, and add explicit rounding logic where needed.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0011

Informatica ROUND uses half-away-from-zero rounding which may differ from Snowflake banker’s rounding on FLOAT types.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts Informatica ROUND, TO_INTEGER, or TO_BIGINT, which use half-away-from-zero rounding, so 0.5 rounds to 1 and -0.5 rounds to -1. Snowflake ROUND on FLOAT types uses banker’s rounding, which rounds half to even, so results may differ for values exactly at the .5 boundary.

Best Practices

  • Consider using NUMERIC or DECIMAL types for deterministic rounding, and verify the converted results against the original mapping for values at the .5 boundary.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0012

IS_DATE without format parameter depends on session date format.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica IS_DATE that has no format parameter. In Informatica this depends on the session date format, which is not migrated, while Snowflake TRY_TO_DATE without a format uses the DATE_INPUT_FORMAT session parameter, so results may differ.

Best Practices

SSC-FDM-INF0013

Informatica CASE_FLAG parameter is not supported in Snowflake.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an expression that uses the Informatica CASE_FLAG parameter for case-insensitive comparison. This parameter is not supported in Snowflake and has been removed from the translated expression, so the equivalent Snowflake operation is always case-sensitive.

Best Practices

  • Consider using UPPER or LOWER on both sides of the comparison to reproduce case-insensitive behavior, then verify the converted results against the original mapping.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0014

SESSSTARTTIME converted to CURRENT_TIMESTAMP() which may differ in long-running sessions.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts Informatica SESSSTARTTIME to Snowflake CURRENT_TIMESTAMP. Informatica SESSSTARTTIME returns a static timestamp captured at session initialization, while CURRENT_TIMESTAMP returns the current time at each row evaluation, so results may differ in long-running sessions.

Best Practices

  • Verify the converted timestamp values against the original mapping, and capture a single static timestamp where a session start time is required.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0015

The parameter file path requires manual mapping to a Snowflake stage.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter mapping that references a parameter file path. This path requires manual mapping to a Snowflake stage, and the original parameter file must be converted to the expected JSON format.

Best Practices

  • Map the parameter file path to an appropriate Snowflake stage and convert the original parameter file to the expected JSON format, then verify the converted behavior matches the original.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0016

Informatica PowerCenter Decision task condition is empty; translated as an unconditional pass-through.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter Decision task whose condition is empty. Because there is no condition, the task has been translated as an unconditional pass-through that always evaluates to true, so you should verify the converted behavior matches the original.

Best Practices

  • Verify the intended decision condition against the original mapping and add the explicit condition where a specific branch behavior is required.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0018

Update Strategy overridden by session settings.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter mapping in which the Update Strategy was overridden by session settings. Because the session settings take precedence, the effective behavior may differ from the Update Strategy defined in the mapping, so you should verify the converted behavior matches the original.

Best Practices

  • Verify the effective update behavior against the original session settings and mapping, and adjust the converted target logic where needed.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0019

The update strategy logic was moved to the target model.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica PowerCenter mapping and moves the update strategy logic to the target model. Because the logic now lives in the target model, you should verify the converted behavior matches the original.

Best Practices

SSC-FDM-INF0020

Informatica RR date format element has century-guessing logic that differs from Snowflake YY.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica RR date format element to Snowflake YY. The Informatica RR element uses century-guessing logic, so 99 maps to 1999 and 25 maps to 2025, while Snowflake YY always maps two-digit years to the current century, so 99 maps to 2099. You should review date values to ensure correct century interpretation.

Best Practices

SSC-FDM-INF0021

Informatica POWER rounds non-integer exponents when the base is negative; Snowflake raises an error.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica POWER call that may receive a negative base with a non-integer exponent. Informatica rounds the exponent to the nearest integer before computing, while Snowflake POWER raises an invalid floating point operation error for a negative base with a non-integer exponent. You should review expressions that use POWER with potentially negative bases and non-integer exponents.

Best Practices

  • Review expressions that use POWER with potentially negative bases and non-integer exponents, and add explicit rounding or guards where needed.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0022

Informatica MD5 may produce different hashes due to encoding differences.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica MD5 call. Informatica allows configurable character encoding through the data movement mode property, which can be ASCII or Unicode, while Snowflake always uses UTF-8, so different encodings can produce different MD5 hashes for the same input string.

Best Practices

SSC-FDM-INF0023

SETMAXVARIABLE/SETMINVARIABLE per-row return value on OUTPUT port may differ from Informatica.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a SETMAXVARIABLE or SETMINVARIABLE call on an output port. Informatica accumulates the variable row by row, so rows after a new maximum or minimum reflect the updated value, while Snowflake compares all rows against the same initial value, so per-row return values may differ. The final persisted variable value is equivalent.

Best Practices

  • Verify the per-row return values against the original mapping where downstream logic depends on the accumulated value, keeping in mind the final persisted value is equivalent.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0024

SETVARIABLE persisted value may differ when row order matters.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a SETVARIABLE call. Informatica persists the last row’s value, which is order-dependent, while Snowflake uses a maximum or minimum across all rows instead, so the persisted value may differ when row order matters.

Best Practices

SSC-FDM-INF0025

COUNT-based SET*VARIABLE translated to COUNT(port).

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts a SETVARIABLE call with the count aggregate function, or a SETCOUNTVARIABLE call, to COUNT over the port. Informatica counts row-by-row invocations, so if the expression is conditional, results may differ.

Best Practices

  • Verify the converted count against the original mapping, especially where the expression is conditional, and add explicit filtering where needed.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0026

Informatica REG_REPLACE numReplacements differs from Snowflake REGEXP_REPLACE occurrence.

Severity

None

Description

SnowConvert reports this functional difference (FDM) when it converts an Informatica REG_REPLACE call to Snowflake REGEXP_REPLACE. The Informatica number of replacements parameter replaces the first N matches, while the Snowflake occurrence parameter replaces only the Nth match, so only the values 0 for all matches and 1 for the first match are equivalent.

Best Practices

  • Verify the converted replacement behavior against the original mapping, especially when the number of replacements is greater than one.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0027

SYSTIMESTAMP format argument dropped. Snowflake CURRENT_TIMESTAMP() always returns full precision.

Severity

None

Description

In Informatica PowerCenter the SYSTIMESTAMP format argument controls the returned precision, such as seconds, milliseconds, microseconds, or nanoseconds. Snowflake CURRENT_TIMESTAMP() always returns nanosecond precision, so SnowConvert drops the format argument because Snowflake provides a superset of that precision. Verify any downstream logic that may depend on the previously truncated precision.

Best Practices

SSC-FDM-INF0028

Informatica reusable transformation may be duplicated across dbt projects.

Severity

None

Description

This transformation is reusable in Informatica PowerCenter, and SnowConvert converts it into separate dbt models in each mapping where it is used, which results in duplicated logic. Consider consolidating the shared logic into a single dbt macro or package for easier maintenance.

Best Practices

SSC-FDM-INF0029

Multiple targets to the same table merged with UNION ALL.

Severity

None

Description

Informatica PowerCenter loads targets sequentially according to the target load order. The translated dbt model uses UNION ALL to combine all pipelines into a single atomic query, so SnowConvert reports this functional difference so you can verify the converted behavior matches the original. If the pipeline execution order affects the final result, review the generated model.

Best Practices

SSC-FDM-INF0030

Informatica PERCENTILE uses a different interpolation algorithm than Snowflake PERCENTILE_CONT.

Severity

None

Description

Informatica PowerCenter PERCENTILE uses the interpolation formula I=(X+1)*P/100, while Snowflake PERCENTILE_CONT uses I=P*(N-1). Because the two interpolation algorithms differ, they can produce different numeric results for the same input data.

Best Practices

SSC-FDM-INF0031

Informatica PowerCenter Email task sender address differs in Snowflake.

Severity

None

Description

The Informatica PowerCenter Email task sends mail through the Integration Service using MAPI or SMTP. Snowflake uses SYSTEM$SEND_EMAIL, which sends from the fixed address no-reply@snowflake.net and does not allow the sender address to be customized. Recipients must be verified Snowflake account users.

Best Practices

SSC-FDM-INF0032

Informatica FIRST translated to ANY_VALUE approximation.

Severity

None

Description

The Informatica PowerCenter FIRST function returns the value from the first row processed in pipeline order, and SnowConvert approximates it with ANY_VALUE. Because ANY_VALUE does not guarantee a specific row, add an explicit ORDER BY or QUALIFY clause if deterministic first-row semantics are required.

Best Practices

SSC-FDM-INF0033

Informatica LAST translated to ANY_VALUE approximation.

Severity

None

Description

The Informatica PowerCenter LAST function returns the value from the last row processed in pipeline order, and SnowConvert approximates it with ANY_VALUE. Because ANY_VALUE does not guarantee a specific row, add an explicit ORDER BY or QUALIFY clause if deterministic last-row semantics are required.

Best Practices

SSC-FDM-INF0045

Java Transformation contains custom Java code that cannot be automatically translated.

Severity

None

Description

This Java Transformation contains custom Java code that SnowConvert cannot automatically translate. The Java code snippets must be manually migrated to Snowflake, for example as a Java UDF, a Snowpark procedure, or inline SQL. Pass-through columns are forwarded, but the Java logic is not applied.

Best Practices

SSC-FDM-INF0046

SETCOUNTVARIABLE row ordering is non-deterministic.

Severity

None

Description

The Informatica PowerCenter SETCOUNTVARIABLE is translated to ROW_NUMBER() OVER (ORDER BY 1). This row ordering is non-deterministic and may differ from Informatica’s pipeline-processing order, so add a deterministic ORDER BY if stable row numbering is required.

Best Practices

SSC-FDM-INF0047

STDDEV returns NULL in Snowflake for single-record groups, whereas Informatica returns 0.

Severity

None

Description

Informatica PowerCenter STDDEV returns 0 for single-record groups because there is no variance. Snowflake STDDEV returns NULL because the sample standard deviation is undefined for a group of one row. Verify downstream logic that depends on STDDEV returning 0 for single-row groups.

Best Practices

SSC-FDM-INF0048

IS_SPACES does not match vertical tab (0x0B) in Snowflake.

Severity

None

Description

Informatica PowerCenter IS_SPACES treats the vertical tab character, CHR(11) or 0x0B, as whitespace, but the Snowflake REGEXP_LIKE pattern \s does not match it. This difference only affects data that contains vertical tab characters.

Best Practices

SSC-FDM-INF0049

Informatica CRC32 uses ASCII encoding; Snowflake CRC32_UDF uses UTF-8.

Severity

None

Description

Informatica PowerCenter CRC32 computes the checksum over ASCII-encoded bytes, while the generated CRC32_UDF processes the input as UTF-8. For strings that contain only ASCII characters the results are identical, but for strings with non-ASCII characters the checksum values will differ. Review the input data and verify that the function output is acceptable.

Best Practices

SSC-FDM-INF0057

Source Qualifier SQL converted using non-default dialect.

Severity

None

Description

The SQL in this Source Qualifier was converted using a dialect other than the default dialect. SnowConvert reports this functional difference so you can verify the converted behavior matches the original.

Best Practices

SSC-FDM-INF0058

Table is used as both source and target.

Severity

None

Description

This table is used as both a source and a target in the same mapping. SnowConvert adjusted the dbt execution order to prevent circular data corruption, so review the staging and mart models for the details.

Best Practices

SSC-FDM-INF0059

Local variable expression inlined into lookup macro argument; keep both copies in sync.

Severity

None

Description

A local variable expression was inlined into a lookup macro argument because outer select-list aliases are not visible inside the macro’s correlated subquery. If you edit the local variable’s expression in the column list, also update the matching macro argument so the two copies stay in sync.

Best Practices

  • If you edit the local variable’s expression in the column list, also update the matching macro argument to keep the two copies in sync.
  • Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this issue.
  • If you need more support, email us at snowconvert-support@snowflake.com.

SSC-FDM-INF0060

The Informatica PowerCenter task was disabled in the source workflow and the converted Snowflake task has been suspended.

Severity

None

Description

This Informatica PowerCenter task was disabled in the source workflow. The converted Snowflake task has been suspended with ALTER TASK ... SUSPEND to preserve the task graph while preventing execution.

Best Practices

SSC-FDM-INF0061

Connected Lookup with ‘Use All Values’ policy fans out rows.

Severity

None

Description

This Connected Lookup uses the multi-match policy ‘Use All Values’, and the translation emits a join against the lookup table. Because every matching row is returned, the row count may increase.

Best Practices

SSC-FDM-INF0062

$PMRepositoryUserName translated to CURRENT_USER() - identity model differs.

Severity

None

Description

Informatica PowerCenter $PMRepositoryUserName returns the PowerCenter repository connection user configured on the Integration Service, while Snowflake CURRENT_USER() returns the session user, which is typically the dbt service account. Both values are usually constant per environment, but they represent different identity spaces. Verify audit columns, predicates that filter on an owner, and incremental merge keys that reference this value.

Best Practices

SSC-FDM-INF0063

Session-level connection override could not be resolved to a Snowflake database by SnowConvert AI.

Severity

None

Description

A session-level connection override could not be resolved to a Snowflake database because the connection is not present in the exported XML. Set the database override variables, which use the _db suffix, to the real database.

Best Practices

SSC-FDM-INF0064

Assignment target variable type may behave differently at runtime.

Severity

None

Description

The assignment target variable has a source type that SnowConvert mapped to a Snowflake type. This mapping may lose precision, such as decimal scale, or may require format-dependent parsing for date and time values, so verify the runtime behavior.

Best Practices

SSC-FDM-INF0068

Expression Lagger Technique ORDER BY inferred from an upstream Source Qualifier.

Severity

None

Description

The Lagger Technique ORDER BY was inferred from an upstream Source Qualifier instead of from a Sorter transformation. Validate that the inferred ordering columns are the intended row order for the LAG window function.

Best Practices

SSC-FDM-INF0069

Union input group with unresolved upstream lineage emitted as a no-row null branch.

Severity

None

Description

A Union input group could not be resolved to an upstream transformation, for example a Router default or others branch that has no downstream connector. Its columns are emitted as NULL literals with no rows, using LIMIT 0. Verify that the branch is intentionally unconnected rather than an unresolved-lineage defect.

Best Practices

SSC-FDM-INF0070

Use Any Value returns an arbitrary row; in Snowflake the selected row may vary per run.

Severity

None

Description

The ‘Use Any Value’ policy returns an arbitrary row when multiple rows match, and in Snowflake the selected row may vary from one run to the next. Add an ORDER BY to the lookup SQL override only if a specific row is required.

Converted Code

QUALIFY
   ROW_NUMBER() OVER (
   PARTITION BY
      CurrencyKey
   ORDER BY
      (
         SELECT
            --** SSC-FDM-INF0070 - Use Any Value RETURNS AN ARBITRARY ROW WHEN MULTIPLE ROWS MATCH; IN SNOWFLAKE THE SELECTED ROW MAY VARY PER RUN. ADD AN ORDER BY TO THE LOOKUP SQL OVERRIDE ONLY IF A SPECIFIC ROW IS REQUIRED. **
            null
      )) = 1

Best Practices

SSC-FDM-INF0076

ROUND on a floating-point value was cast to NUMERIC to match Informatica’s exact-decimal rounding; large-magnitude values may still differ.

Severity

None

Description

A ROUND applied to a floating-point value was cast to NUMERIC so the result matches Informatica’s exact-decimal rounding. Even with this cast, large-magnitude values may still differ, so verify the converted results.

Best Practices