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

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

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 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
      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
      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

!!!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

{{ 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') }}

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