Informatica PowerCenter - dbt mappings and transformations¶

SELECT
   DimCurrency.CurrencyKey AS CurrencyKey,
   DimCurrency.CurrencyAlternateKey AS CurrencyAlternateKey,
   DimCurrency.CurrencyName AS CurrencyName
FROM
   {{ source('raw', 'DimCurrency') }} AS DimCurrency

SELECT
   sc_Dim_Corp_Hier.Infopro_Div_Nbr AS Infopro_Div_Nbr,
   sc_Dim_Corp_Hier.is_Current AS is_Current
FROM
   {{ source('raw', 'sc_Dim_Corp_Hier') }} AS sc_Dim_Corp_Hier
WHERE
   sc_Dim_Corp_Hier.is_Current = 1

{{ config(materialized='table') }}
-- Self-referencing table(s): 'DimCustomer1' used as both source and target in this mapping.
-- Materialized as TABLE to snapshot source data before the target model overwrites it.
{% if var('NEWMAPPING11_sq_dimcustomer_sql_override', '') != '' %}
{{ var('NEWMAPPING11_sq_dimcustomer_sql_override') }}
{% else %}
-- Mapping-level SQL Query override (from Source Qualifier 'Sql Query' property)
SELECT
   4 AS CustomerKey
{% endif %}

{{ config(
    materialized='view',
    pre_hook="TRUNCATE TABLE staging_load"
) }}
SELECT
   DimEmployee.EmployeeKey AS EmployeeKey,
   DimEmployee.FirstName AS FirstName
FROM
   {{ source('raw', 'DimEmployee') }} AS DimEmployee

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0069 - FLAT FILE SOURCE FILE NAME COULD NOT BE RESOLVED. LANDING PATH '@public.landing_stage/infpc/sources/TestFolder/SRC_DAILY_RATES/UNKNOWN_FILE' REQUIRES A MANUAL FILE BINDING. ***/!!!
SELECT
   $1 :: VARCHAR(10) AS REGION_CD,
   $2 :: VARCHAR(3) AS CURRENCY_CD,
   $3 :: FLOAT AS EXCHANGE_RATE,
   $4 :: TIMESTAMP AS EFFECTIVE_DT
FROM
   @public.landing_stage/infpc/sources/TestFolder/SRC_DAILY_RATES/UNKNOWN_FILE (FILE_FORMAT => 'TestFolder_m_FLAT_FILE_DAILY_RATES_SRC_DAILY_RATES')

CREATE FILE FORMAT IF NOT EXISTS TestFolder_m_FLAT_FILE_DAILY_RATES_SRC_DAILY_RATES
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
ENCODING = 'WINDOWS1252'
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE;

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0068 - FLAT FILE SOURCE 'SRC_FIXED' USES FIXED-WIDTH FORMAT WHICH CANNOT BE MAPPED TO A SNOWFLAKE FILE_FORMAT. MANUAL CONVERSION IS REQUIRED. ***/!!!
SELECT
   null AS COL_A,
   null AS COL_B

WITH source_data AS
(
   SELECT
      NAME1 AS NAME
   FROM
      {{ ref('int_SALARY_RTRTRANS__HIGH_SALARY') }}
)
SELECT
   NAME AS NAME,
   '<No description>' AS Description,
   NAME || '!!!' AS NEW_NAME
FROM
   source_data

WITH source_data AS
(
   SELECT
      OrderID,
      ShipCountry
   FROM
      {{ ref('stg_raw__SQ_Orders') }}
),
lag_values AS
(
   SELECT
      *,
      !!!RESOLVE EWI!!! /*** SSC-EWI-INF0065 - THIS EXPRESSION USES THE LAGGER TECHNIQUE (LOCAL VARIABLE FORWARD REFERENCE) TO ACCESS VALUES FROM PREVIOUS ROWS. TRANSLATED TO LAG() WINDOW FUNCTION WITH NON-DETERMINISTIC ORDER BY 1 BECAUSE NO PRECEDING SORTER TRANSFORMATION WAS FOUND. MANUAL INTERVENTION REQUIRED: REPLACE ORDER BY 1 WITH A DETERMINISTIC COLUMN TO ENSURE CORRECT ROW ORDERING. ***/!!!
      LAG(ShipCountry, 1)
      OVER (
      ORDER BY
         1) AS v_ShipCountry_Lagged
   FROM
      source_data
)
SELECT
   OrderID AS OrderID,
   v_ShipCountry_Lagged AS PrevShipCountry
FROM
   lag_values

SELECT
    SQ_employee_data.EMPLOYEE_ID AS EMPLOYEE_ID,
    null AS FIRST_NAME,
    SQ_employee_data.LAST_NAME AS LAST_NAME,
    SQ_employee_data.EMAIL AS EMAIL,
    SQ_employee_data.SALARY AS SALARY
FROM
    {{ ref('stg_raw__SQ_employee_data') }} AS SQ_employee_data
WHERE
    SALARY > 10
  AND SALARY <= 100000

SELECT
   SQ_FactInternetSales.UnitPrice AS UnitPrice,
   SQ_FactInternetSales.OrderQuantity AS OrderQuantity,
   SQ_FactInternetSales.CurrencyKey AS CurrencyKey,
   SQ_FactInternetSales.ProductKey AS ProductKey1,
   SQ_DimProduct.ProductKey AS ProductKey,
   SQ_DimProduct.EnglishProductName AS EnglishProductName,
   SQ_DimProduct.SpanishProductName AS SpanishProductName
FROM
   {{ ref('stg_raw__SQ_FactInternetSales') }} AS SQ_FactInternetSales
   INNER JOIN
      {{ ref('stg_raw__SQ_DimProduct') }} AS SQ_DimProduct
      ON SQ_FactInternetSales.ProductKey = SQ_DimProduct.ProductKey

WITH a AS
(
   SELECT
      vala vala
   FROM
      {{ ref('stg_raw__SQ_tableA') }}
),
b AS
(
   SELECT
      valb vala
   FROM
      {{ ref('stg_raw__SQ_tableB') }}
)
SELECT
   a.vala vala
FROM
   a
UNION ALL
SELECT
   b.vala vala
FROM
   b

WITH lookup_reference AS
(
   SELECT
      CurrencyKey ,
      CurrencyAlternateKey ,
      CurrencyName
   FROM
      {{ source('raw', 'DimCurrency') }}
   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
),
input_data AS
(
   SELECT
      CurrencyKey CurrencyKey1,
      TotalProductCost SQ_FactInternetSales__TotalProductCost,
      OrderDate SQ_FactInternetSales__OrderDate,
      ProductKey SQ_FactInternetSales__ProductKey1,
      CustomerKey SQ_FactInternetSales__CustomerKey1
   FROM
      {{ ref('stg_raw__SQ_FactInternetSales') }}
)
SELECT
   input_data.CurrencyKey1 ,
   input_data.SQ_FactInternetSales__TotalProductCost ,
   input_data.SQ_FactInternetSales__OrderDate ,
   input_data.SQ_FactInternetSales__ProductKey1 ,
   input_data.SQ_FactInternetSales__CustomerKey1 ,
   lookup_reference.CurrencyKey,
   lookup_reference.CurrencyAlternateKey,
   lookup_reference.CurrencyName
FROM
   input_data
   LEFT JOIN
      lookup_reference
      ON lookup_reference.CurrencyKey = input_data.CurrencyKey1

WITH lookup_reference AS
(
   SELECT
      DeptCode ,
      DeptName
   FROM
      {{ source('raw', 'Tbg_Departments') }}
),
input_data AS
(
   SELECT
      EmpName ,
      DeptCode DeptCode1
   FROM
      {{ ref('stg_raw__SQ_Tbg_Employees') }}
)
SELECT
   input_data.EmpName ,
   input_data.DeptCode1 ,
   lookup_reference.DeptCode,
   lookup_reference.DeptName
FROM
   --** SSC-FDM-INF0061 - CONNECTED LOOKUP 'LKPTRANS' USES MULTI-MATCH POLICY 'USE ALL VALUES'. THE TRANSLATION EMITS A JOIN AGAINST 'Tbg_Departments'. ROW COUNT MAY INCREASE. **
   --** SSC-FDM-INF0088 - CONNECTED LOOKUP 'LKPTRANS' USES CASE-INSENSITIVE STRING COMPARISON ('CASE SENSITIVE STRING COMPARISON' = NO), BUT THE EMITTED JOIN CONDITION IS CASE-SENSITIVE. LOOKUP MATCHES MAY DIFFER FROM POWERCENTER. **
   input_data
   LEFT JOIN
      lookup_reference
      ON lookup_reference.DeptCode = input_data.DeptCode1

{% macro ulkp_departments(in_deptcode) %}
(
    SELECT MAX(DeptName)
    FROM {{ source('raw', 'Departments') }} AS lkp
    WHERE
   lkp.DeptCode = {{ in_deptcode }}
)
{% endmacro %}

      {{ lkptrans1('v_dcPre || \'\'') }} AS v_dc
   FROM
      locals_cte_2
),
results_with_locals AS
(
   SELECT
      *,
      {{ lkptrans1(lkptrans11('\'SOMETHING\'', '\'b\'')) }} AS NEWFIELD,
      '' || {{ lkptrans1('v_dc || \'\'') }} AS v_dc2,
      {{ lkptrans('locals_cte_3.v_dc') }} AS DeptName
   FROM
      locals_cte_3
)

WITH lookup_reference AS
(
   SELECT
      LKP_divID ,
      divCode
   FROM
      {{ ref('stg_flat_file__Lkp_DivToCode') }}
   QUALIFY
      ROW_NUMBER() OVER (
      PARTITION BY
         LKP_divID
      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
),
input_data AS
(
   SELECT
      divID ,
      authNum
   FROM
      {{ ref('stg_raw__SQ_SrcTable') }}
)
SELECT
   input_data.divID ,
   input_data.authNum ,
   lookup_reference.divCode
FROM
   input_data
   LEFT JOIN
      lookup_reference
      ON lookup_reference.LKP_divID = input_data.divID

WITH source_data AS
(
   SELECT
      NAME,
      SALARY,
      DEPARTMENT
   FROM
      {{ ref('stg_raw__SQ_EMPLOYEE') }}
),
aggregation AS
(
   SELECT
      LAST_VALUE(NAME)
      OVER (
      PARTITION BY (
         DEPARTMENT)
      ORDER BY
         NAME,
         SALARY,
         DEPARTMENT) AS NAME,
      SUM(SALARY)
      OVER (
      PARTITION BY (
         DEPARTMENT)) AS deptsalary,
      LAST_VALUE(SALARY)
      OVER (
      PARTITION BY (
         DEPARTMENT)
      ORDER BY
         NAME,
         SALARY,
         DEPARTMENT) AS SALARY,
      LAST_VALUE(DEPARTMENT)
      OVER (
      PARTITION BY (
         DEPARTMENT)
      ORDER BY
         NAME,
         SALARY,
         DEPARTMENT) AS DEPARTMENT
   FROM
      source_data
   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
)
SELECT
    NAME,
    deptsalary,
    SALARY,
    DEPARTMENT
FROM
    aggregation

WITH source_data AS
(
   SELECT
      code,
      label
   FROM
      {{ ref('stg_raw__SQ_RefData') }}
)
SELECT DISTINCT
   code,
   label
FROM
   source_data

WITH source_data AS
(
   SELECT
      product_id,
      price
   FROM
      {{ ref('stg_raw__SQ_Products') }}
),
ranked_data AS
(
   SELECT
      product_id,
      price,
      ROW_NUMBER()
      OVER (
      ORDER BY
         price ASC) AS RANKINDEX
   FROM
      source_data
   QUALIFY
      RANKINDEX <= 10
)
SELECT
   RANKINDEX,
   product_id,
   price
FROM
   ranked_data

WITH source_data AS
(
   SELECT
      employee_id,
      employee_name,
      department,
      salary
   FROM
      {{ ref('stg_raw__SQ_Employees') }}
),
ranked_data AS
(
   SELECT
      employee_id,
      employee_name,
      department,
      salary,
      ROW_NUMBER()
      OVER (
      PARTITION BY (
         department)
      ORDER BY
         salary DESC) AS RANKINDEX
   FROM
      source_data
   QUALIFY
      RANKINDEX <= 3
)
SELECT
   RANKINDEX,
   employee_id,
   employee_name,
   department,
   salary
FROM
   ranked_data

{{ config(
    materialized='table'
) }}
WITH source_data AS
(
   SELECT
      EMP_ID,
      EMP_NAME,
      SALARY,
      DEPT
   FROM
      {{ ref('stg_raw__SQ_SRC_SORT_SIMPLE') }}
),
sorted AS
(
   SELECT
      *
   FROM
      source_data
)
SELECT
   *
FROM
   sorted
ORDER BY
   EMP_ID ASC NULLS LAST

{{ config(
    materialized='table'
) }}
WITH source_data AS
(
   SELECT
      CITY_NAME,
      PROVINCE_CODE,
      CITY_TYPE
   FROM
      {{ ref('stg_raw__SQ_SRC_SORT_DISTINCT') }}
),
sorted AS
(
   SELECT DISTINCT
      CITY_NAME,
      PROVINCE_CODE,
      CITY_TYPE
   FROM
      source_data
)
SELECT
   *
FROM
   sorted
ORDER BY
   CITY_NAME ASC NULLS LAST,
   PROVINCE_CODE ASC NULLS LAST,
   CITY_TYPE ASC NULLS LAST

WITH source_data AS
(
   SELECT
      FirstName,
      LastName,
      Department,
      Credits
   FROM
      {{ ref('stg_raw__SQ_Students') }}
)
SELECT
   FirstName FirstName1,
   LastName LastName1,
   Department Department1,
   Credits Credits1
FROM
   source_data
WHERE
   Credits > 70

WITH source_data AS
(
   SELECT
      empid,
      empname,
      food_expense,
      rent_expense,
      transport_expense,
      medical_expense,
      misc_expense
   FROM
      {{ ref('stg_raw__SQ_NRM_SIMPLE_SRC') }}
),
occurrence_1 AS
(
   SELECT
      empid,
      empname,
      food_expense AS expense,
      1 AS gcid_expense
   FROM
      source_data
),
occurrence_2 AS
(
   SELECT
      empid,
      empname,
      rent_expense AS expense,
      2 AS gcid_expense
   FROM
      source_data
),
normalized AS
(
   SELECT * FROM occurrence_1
   UNION ALL
   SELECT * FROM occurrence_2
),
with_gk AS
(
   SELECT
      empid,
      empname,
      expense,
      gcid_expense,
      ROW_NUMBER()
      OVER (
      ORDER BY
         empid,
         gcid_expense) AS gk_expense
   FROM
      normalized
)
SELECT
   *
FROM
   with_gk

--** SSC-FDM-INF0007 - NORMALIZER GK PORT IS TRANSLATED AS ROW_NUMBER() WHICH ALWAYS STARTS FROM 1 PER QUERY EXECUTION. THIS IS FUNCTIONALLY EQUIVALENT TO RESTART=YES IN INFORMATICA POWERCENTER. VERIFY GK VALUES IF USED AS A FOREIGN KEY IN DOWNSTREAM TARGETS. **

--** SSC-FDM-INF0008 - NORMALIZER RESET=YES IS NOT REPLICATED IN SNOWFLAKE. IN INFORMATICA, RESET=YES RESTORES THE GK COUNTER TO ITS PRE-SESSION VALUE AT SESSION END. IN THE SNOWFLAKE TRANSLATION, ROW_NUMBER() ALWAYS STARTS FROM 1. VERIFY GK VALUES IF USED AS A FOREIGN KEY IN DOWNSTREAM TARGETS. **

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0043 - THE SEQUENCE GENERATOR IS TRANSLATED TO ROW_NUMBER() OVER (ORDER BY 1). ROW ORDERING IS NON-DETERMINISTIC (VALUES MAY BE ASSIGNED TO DIFFERENT ROWS BETWEEN RUNS) AND SEQUENCE STATE IS NOT PERSISTED ACROSS DBT EXECUTIONS (ALWAYS RESTARTS FROM START VALUE). ADD A DETERMINISTIC ORDER BY IF STABLE SEQUENCE ASSIGNMENT IS REQUIRED. ***/!!!
WITH source_data AS
(
   SELECT
      *
   FROM
      {{ ref('stg_raw__SQ_Products') }}
),
sequence_data AS
(
   SELECT
      *,
      ROW_NUMBER()
      OVER (
      ORDER BY
         1) + 9 AS NEXTVAL
   FROM
      source_data
)
SELECT
   *
FROM
   sequence_data

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0059 - A SEQUENCE GENERATOR IS CONNECTED TO MULTIPLE INDEPENDENT PIPELINES (FAN-OUT TOPOLOGY). IN INFORMATICA Power Center, THE SEQUENCE COUNTER IS SHARED ACROSS ALL PIPELINES, PRODUCING NON-OVERLAPPING IDS. IN DBT, EACH PIPELINE RECEIVES AN INDEPENDENT ROW_NUMBER() OVER (ORDER BY 1) STARTING FROM 1, SO COUNTER VALUES WILL OVERLAP BETWEEN PIPELINES. REVIEW THE GENERATED PER-PIPELINE INTERMEDIATE MODELS AND DETERMINE IF SEQUENTIAL NUMBERING ACROSS PIPELINES IS REQUIRED. ***/!!!
WITH source_data AS
(
   SELECT
      *
   FROM
      {{ ref('int_StudentPart2') }}
),
sequence_data AS
(
   SELECT
      *,
      ROW_NUMBER()
      OVER (
      ORDER BY
         1) * 10 AS NEXTVAL
   FROM
      source_data
)
SELECT
   *
FROM
   sequence_data

{{ config(
    materialized='table'
) }}
SELECT
   DeptName AS TXT
FROM
   {{ ref('int_UPDTRANS') }}

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='Id',
    merge_update_columns=['Name'],
    alias='After_Students_Clear'
) }}
SELECT
   SALARY AS Id,
   NAME AS Name
FROM
   {{ ref('int_UPDTRANS') }}

--** SSC-FDM-INF0019 - THE UPDATE STRATEGY LOGIC WAS MOVED TO THE TARGET MODEL. **
SELECT
   order_id,
   customer_name,
   amount,
   status
FROM
   {{ ref('stg_raw__SQ_upd_src_orders') }}

{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}
SELECT
   order_id,
   customer_name,
   amount,
   status
FROM
   {{ ref('int_UPD_orders_insert') }}

{{ config(
    materialized='table'
) }}
--** SSC-FDM-INF0019 - THE UPDATE STRATEGY LOGIC WAS MOVED TO THE TARGET MODEL. **
SELECT
   order_id,
   customer_name,
   amount,
   status
FROM
   {{ ref('stg_raw__SQ_upd_src_orders') }}

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    pre_hook="
      DELETE FROM {{ this }}
      WHERE (order_id) IN (
        SELECT order_id
        FROM {{ ref('int_UPD_orders_delete') }}
      )
    "
) }}
SELECT
   order_id,
   customer_name,
   amount,
   status
FROM
   {{ ref('int_UPD_orders_delete') }}
WHERE
   FALSE

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='product_id',
    merge_update_columns=['product_name', 'price', 'quantity', 'action_flag'],
    pre_hook="
      DELETE FROM {{ this }}
      WHERE (product_id) IN (
        SELECT product_id
        FROM {{ ref('int_UPD_products') }}
        WHERE CASE
   WHEN (product_name IS NULL)
   OR price < 0
      THEN 3 /*REJECT*/
   WHEN action_flag = 'D'
      THEN 2 /*DELETE*/
   WHEN action_flag = 'U'
      THEN 1 /*UPDATE*/
   ELSE 0 /*INSERT*/
END = 2 /*DELETE*/
      )
    "
) }}
SELECT
   product_id,
   product_name,
   price,
   quantity,
   action_flag
FROM
   {{ ref('int_UPD_products') }}
WHERE
   CASE
      WHEN (product_name IS NULL)
      OR price < 0
         THEN 3 /*REJECT*/
      WHEN action_flag = 'D'
         THEN 2 /*DELETE*/
      WHEN action_flag = 'U'
         THEN 1 /*UPDATE*/
      ELSE 0 /*INSERT*/
   END IN (0 /*INSERT*/, 1 /*UPDATE*/)

{{ config(
    materialized='table'
) }}

--** SSC-FDM-INF0019 - THE UPDATE STRATEGY LOGIC WAS MOVED TO THE TARGET MODEL. **
SELECT
   product_id,
   product_name,
   price,
   quantity,
   action_flag
FROM
   {{ ref('stg_raw__SQ_upd_src_products') }}

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='emp_id',
    merge_update_columns=['emp_name', 'department']
) }}
SELECT
   emp_id,
   emp_name,
   department
FROM
   {{ ref('int_UPD_emp_current') }}

WHERE
   CASE
      WHEN status = 'new'
         THEN 0 /*INSERT*/
      WHEN status = 'cancelled'
         THEN 2 /*DELETE*/
      ELSE 1 /*UPDATE*/
   END IN (0 /*INSERT*/, 1 /*UPDATE*/)

{{ AdventureWorksETLs.shared_models__mplt_variables_mapplet() }}

{% macro shared_models__mplt_passive_mapplet(inbound_sources = {}) %}
WITH passive_mapplet_input AS
(
   SELECT
      {{ inbound_sources["passive_mapplet_input"]["columns"]["string_value"] | default('null', true) }} AS string_value,
      
{% for carry_col in inbound_sources.get('passive_mapplet_input', {}).get('carry_columns', []) %}
      {{ inbound_sources['passive_mapplet_input']['source_ref'] }}.{{ carry_col[0] }} AS {{ carry_col[1] }} ,
{% endfor %}
   FROM
      {{ inbound_sources["passive_mapplet_input"]["source_ref"] }}
),
EXPTRANS AS
(
   WITH source_data AS
   (
      SELECT
         string_value,
         
{% for carry_col in inbound_sources.get('passive_mapplet_input', {}).get('carry_columns', []) %}
      {{ carry_col[1] }} ,
{% endfor %}
      FROM
         passive_mapplet_input
   )
   SELECT
      string_value AS string_value,
      string_value || 'hello' AS processed_value,
      
{% for carry_col in inbound_sources.get('passive_mapplet_input', {}).get('carry_columns', []) %}
      {{ carry_col[1] }} ,
{% endfor %}
   FROM
      source_data
),
passive_mapplet_output AS
(
   SELECT
      processed_value,
      
{% for carry_col in inbound_sources.get('passive_mapplet_input', {}).get('carry_columns', []) %}
      {{ carry_col[1] }} ,
{% endfor %}
   FROM
      EXPTRANS
)
SELECT
   *
FROM
   passive_mapplet_output
{% endmacro %}

{{ AdventureWorksETLs.shared_models__mplt_passive_mapplet(inbound_sources = {
    "passive_mapplet_input": {
        "source_ref": ref('stg_raw__SQ_DimEmployee'),
        "columns": {
            "string_value": "LoginID"
        }
,
        "carry_columns": [
            ["FirstName", "SQ_DimEmployee__FirstName"],
            ["LastName", "SQ_DimEmployee__LastName"]
        ]
    }
}) }}

--** SSC-FDM-INF0045 - JAVA TRANSFORMATION 't_maskPII' CONTAINS CUSTOM JAVA CODE THAT CANNOT BE AUTOMATICALLY TRANSLATED. THE FOLLOWING JAVA CODE SNIPPETS MUST BE MANUALLY MIGRATED TO SNOWFLAKE (E.G., JAVA UDF, SNOWPARK PROCEDURE, OR INLINE SQL). PASS-THROUGH COLUMNS ARE FORWARDED BUT JAVA LOGIC IS NOT APPLIED. **
!!!RESOLVE EWI!!! /*** SSC-EWI-INF0047 - OUTPUT-ONLY PORT 'PhoneHash' IS SET BY JAVA CODE AND CANNOT BE AUTOMATICALLY TRANSLATED. MANUAL MIGRATION REQUIRED. ***/!!!
--** 
--** TRANSFORMATION SCOPE: Row
--** 
--** --- IMPORT PACKAGES ---
--** import java.security.MessageDigest;
--** 
--** --- ON INPUT ROW ---
--** // Mask email
--** if (Email != null) {
--**     Email = Email.replaceAll("(.).*(@.*)", "$1***$2");
--** }
--** // Hash phone
--** if (PhoneNumber != null) {
--**     PhoneHash = PhoneNumber.hashCode() + "";
--** }
--** **
WITH source_data AS
(
   SELECT
      CustomerId,
      CustomerName,
      Email,
      PhoneNumber
   FROM
      {{ ref('stg_raw__SQ_Customer') }}
)
SELECT
   CustomerId,
   CustomerName,
   Email
FROM
   source_data

--** SSC-FDM-INF0045 - JAVA TRANSFORMATION 't_activeJava' CONTAINS CUSTOM JAVA CODE THAT CANNOT BE AUTOMATICALLY TRANSLATED. THE FOLLOWING JAVA CODE SNIPPETS MUST BE MANUALLY MIGRATED TO SNOWFLAKE (E.G., JAVA UDF, SNOWPARK PROCEDURE, OR INLINE SQL). PASS-THROUGH COLUMNS ARE FORWARDED BUT JAVA LOGIC IS NOT APPLIED. **
!!!RESOLVE EWI!!! /*** SSC-EWI-INF0046 - ACTIVE JAVA TRANSFORMATION 't_activeJava' CANNOT BE AUTOMATICALLY TRANSLATED. THE TRANSFORMATION MAY CHANGE ROW CARDINALITY. MANUAL MIGRATION TO A SNOWFLAKE JAVA UDTF OR SNOWPARK PROCEDURE IS REQUIRED. ***/!!!
--** 
--** TRANSFORMATION SCOPE: All Input
--** 
--** --- ON INPUT ROW ---
--** generateRow();
--** Derived = CustomerName.toUpperCase();
--** generateRow();
--** **
WITH source_data AS
(
   SELECT
      CustomerId,
      CustomerName
   FROM
      {{ ref('stg_raw__SQ_Customer') }}
)
SELECT
   *
FROM
   source_data

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0040 - THE STORED PROCEDURE TRANSFORMATION 'SP_CalcTax' IS TRANSLATED ASSUMING THE PROCEDURE HAS BEEN CONVERTED TO A UDF. THE ORIGINAL INFORMATICA TRANSFORMATION CALLED THE PROCEDURE ONCE PER ROW. THE TRANSLATION CALLS IT AS AN INLINE UDF. CONVERT THE STORED PROCEDURE TO A SNOWFLAKE UDF THAT RETURNS OBJECT_CONSTRUCT WITH NAMED FIELDS MATCHING THE OUTPUT PORT NAMES. ***/!!!
WITH source_data AS
(
   SELECT
      Salary AS inSalary,
      EmpID AS SQ_SRC_EMPLOYEES__EmpID,
      Salary AS SQ_SRC_EMPLOYEES__Salary
   FROM
      {{ ref('stg_raw__SQ_SRC_EMPLOYEES') }}
),
sp_result AS
(
   SELECT
      source_data.*,
      dbo.sp_calc_tax(source_data.inSalary, source_data.SQ_SRC_EMPLOYEES__EmpID, source_data.SQ_SRC_EMPLOYEES__Salary):outTaxAmount :: NUMBER AS outTaxAmount,
      dbo.sp_calc_tax(source_data.inSalary, source_data.SQ_SRC_EMPLOYEES__EmpID, source_data.SQ_SRC_EMPLOYEES__Salary):RETURN_VALUE :: NUMBER AS RETURN_VALUE
   FROM
      source_data
)
SELECT
   *
FROM
   sp_result

WITH source_data AS
(
   SELECT
       t_dummy_filter_1__TotalPrice,
      GENDER,
       t_dummy_filter_1__description
   FROM
      {{ ref('int_t_dummy_gender') }}
)
SELECT
    sd.t_dummy_filter_1__TotalPrice AS TotalPrice,
    sd.GENDER AS gender,
    sd.t_dummy_filter_1__description AS description
FROM
    source_data AS sd

{{ config(
    post_hook="TRUNCATE TABLE mapping_staging"
) }}
WITH source_data AS
(
   SELECT
      EmployeeKey,
      FirstName
   FROM
      {{ ref('stg_raw__SQ_DimEmployee') }}
)
SELECT
   sd.EmployeeKey AS EmployeeKey,
   sd.FirstName AS FirstName
FROM
   source_data AS sd

CREATE OR REPLACE TASK public.WF_DIRECT_LOAD
AS
SELECT
1;
CREATE OR REPLACE TASK public.TestFolder_WF_DIRECT_LOAD_s_DIRECT_LOAD
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.WF_DIRECT_LOAD
AS
BEGIN
   ---- Start block 'TestFolder.WF_DIRECT_LOAD.s_DIRECT_LOAD'
   COPY INTO YOUR_DB.YOUR_SCHEMA.TGT_EMPLOYEES (
      EMP_ID,
      EMP_NAME,
      COMPENSATION
   )
   FROM
   (
      SELECT
         $1 :: VARCHAR(10),
         $2 :: VARCHAR(100),
         $3 :: FLOAT
      FROM
         @public.landing_stage/infpc/sources/TestFolder/SRC_DIRECT_CSV/employees.csv (FILE_FORMAT => 'TestFolder_m_DIRECT_LOAD_SRC_DIRECT_CSV')
   );
   ---- End block 'TestFolder.WF_DIRECT_LOAD.s_DIRECT_LOAD'

END;

CREATE FILE FORMAT IF NOT EXISTS TestFolder_m_DIRECT_LOAD_SRC_DIRECT_CSV
TYPE = 'CSV'
FIELD_DELIMITER = ','
ENCODING = 'WINDOWS1252'
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE;

-- SnowConvert land zone: every converted flat-file read and unload binds to this stage.
CREATE STAGE IF NOT EXISTS public.landing_stage
  COMMENT = 'SnowConvert-generated land zone. Retarget URL/integration; keep the name and subfolder layout.';