Tutorial: Convert a PowerCenter Mapping to a dbt Model¶

-- Export mapping metadata from PowerCenter repository
SELECT
    m.MAPPING_NAME,
    wi.WIDGET_TYPE AS transformation_type,
    wi.WIDGET_NAME AS transformation_name,
    p.PORT_NAME,
    p.PORT_TYPE,  -- INPUT, OUTPUT, INPUT/OUTPUT
    p.EXPRESSION,
    p.DATATYPE,
    p.PRECISION,
    p.SCALE
FROM REP_MAPPINGS m
JOIN REP_WIDGET_INST wi ON m.MAPPING_ID = wi.MAPPING_ID
JOIN REP_WIDGET_ATTR p ON wi.WIDGET_ID = p.WIDGET_ID
WHERE m.MAPPING_NAME = 'm_ORDER_FACT'
ORDER BY wi.WIDGET_NAME, p.PORT_NAME;

-- models/staging/erp/stg_erp__orders.sql

WITH source AS (
    SELECT * FROM {{ source('erp', 'orders') }}
),

renamed AS (
    SELECT
        order_id,
        customer_id,
        product_id,
        region_code,
        CAST(order_date AS DATE) AS order_date,
        CAST(order_amount AS DECIMAL(18, 2)) AS order_amount,
        currency_code,
        order_status,
        updated_at
    FROM source
    WHERE order_status != 'cancelled'  -- equivalent to SQ filter
)

SELECT * FROM renamed

-- models/staging/erp/stg_erp__customers.sql

WITH source AS (
    SELECT * FROM {{ source('erp', 'customers') }}
)

SELECT
    customer_id,
    customer_name,
    customer_segment,
    country_code
FROM source

-- models/staging/ref/stg_ref__exchange_rates.sql

SELECT
    currency_code,
    rate_date,
    exchange_rate_to_usd
FROM {{ source('ref', 'exchange_rates') }}

-- models/intermediate/int_orders__enriched.sql
-- Replaces: SQ_ORDERS join + EXP_DERIVE + LKP_PRODUCT + LKP_REGION

WITH orders AS (
    SELECT * FROM {{ ref('stg_erp__orders') }}
),

customers AS (
    SELECT * FROM {{ ref('stg_erp__customers') }}
),

products AS (
    SELECT * FROM {{ ref('stg_ref__products') }}
),

regions AS (
    SELECT * FROM {{ ref('stg_ref__regions') }}
),

exchange_rates AS (
    SELECT * FROM {{ ref('stg_ref__exchange_rates') }}
),

-- Step 1: Join orders to customers (replaces SQ_ORDERS Joiner)
orders_with_customers AS (
    SELECT
        o.order_id,
        o.customer_id,
        c.customer_name,
        c.customer_segment,
        o.product_id,
        o.region_code,
        o.order_date,
        o.order_amount,
        o.currency_code
    FROM orders o
    INNER JOIN customers c
        ON o.customer_id = c.customer_id
),

-- Step 2: Apply exchange rate (replaces EXP_DERIVE.order_amount_usd)
orders_with_usd AS (
    SELECT
        oc.*,
        COALESCE(er.exchange_rate_to_usd, 1.0) AS exchange_rate,
        oc.order_amount * COALESCE(er.exchange_rate_to_usd, 1.0) AS order_amount_usd,
        YEAR(oc.order_date) AS order_year,
        MONTH(oc.order_date) AS order_month
    FROM orders_with_customers oc
    LEFT JOIN exchange_rates er
        ON oc.currency_code = er.currency_code
        AND oc.order_date = er.rate_date
),

-- Step 3: Lookup product (replaces LKP_PRODUCT)
orders_with_product AS (
    SELECT
        ou.*,
        p.product_name,
        p.product_category
    FROM orders_with_usd ou
    LEFT JOIN products p
        ON ou.product_id = p.product_id
),

-- Step 4: Lookup region (replaces LKP_REGION)
final AS (
    SELECT
        op.*,
        r.region_name,
        -- Derived fields (replaces EXP_DERIVE)
        CASE
            WHEN op.order_amount_usd > 10000 THEN 'Y'
            ELSE 'N'
        END AS is_high_value
    FROM orders_with_product op
    LEFT JOIN regions r
        ON op.region_code = r.region_code
)

SELECT * FROM final

-- models/marts/finance/fct_order_monthly.sql
-- Replaces: AGG_MONTHLY + UPD_INSERT + TGT_FACT_ORDERS

{{ config(
    materialized='incremental',
    unique_key=['customer_id', 'order_year', 'order_month', 'product_category', 'region_name'],
    incremental_strategy='merge'
) }}

SELECT
    -- Dimension keys
    customer_id,
    customer_name,
    order_year,
    order_month,
    product_category,
    region_name,

    -- Measures (replaces AGG_MONTHLY)
    SUM(order_amount_usd) AS total_order_amount_usd,
    COUNT(order_id) AS order_count,
    AVG(order_amount_usd) AS avg_order_amount_usd,
    SUM(CASE WHEN is_high_value = 'Y' THEN 1 ELSE 0 END) AS high_value_order_count,

    -- Metadata
    CURRENT_TIMESTAMP AS loaded_at

FROM {{ ref('int_orders__enriched') }}

{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

GROUP BY
    customer_id,
    customer_name,
    order_year,
    order_month,
    product_category,
    region_name

-- tests/reconciliation/assert_fct_order_monthly_matches_powercenter.sql
-- Compare dbt output to PowerCenter output during parallel run

WITH dbt_totals AS (
    SELECT
        order_year,
        order_month,
        SUM(total_order_amount_usd) AS dbt_total,
        SUM(order_count) AS dbt_count
    FROM {{ ref('fct_order_monthly') }}
    GROUP BY order_year, order_month
),

pc_totals AS (
    SELECT
        order_year,
        order_month,
        SUM(total_order_amount_usd) AS pc_total,
        SUM(order_count) AS pc_count
    FROM {{ source('powercenter', 'fact_order_monthly_pc') }}
    GROUP BY order_year, order_month
)

SELECT
    d.order_year,
    d.order_month,
    d.dbt_total,
    p.pc_total,
    ABS(d.dbt_total - p.pc_total) AS amount_diff,
    d.dbt_count,
    p.pc_count
FROM dbt_totals d
JOIN pc_totals p ON d.order_year = p.order_year AND d.order_month = p.order_month
WHERE ABS(d.dbt_total - p.pc_total) > 0.01  -- tolerance
   OR d.dbt_count != p.pc_count