source_db: art_relations
dim_table: dms_customer
export_sql: >-
  SELECT
    customer_id,
    customer_master_master_id,
    stripSpecialChars(name),
    cust_type,
    order_exp,
    payment_term,
    discount,
    territory,
    physical
  FROM
    customer_master
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      customer_master_master_id = st.customer_master_master_id,
      name = st.name,
      cust_type = st.cust_type,
      order_exp = st.order_exp,
      payment_term = st.payment_term,
      discount = st.discount,
      territory = st.territory,
      physical = st.physical,
      last_updated = '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.customer_id = st.customer_id AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.customer_master_master_id = st.customer_master_master_id, false) OR
          ({db}.{schema}.{dim_table}.customer_master_master_id is null and st.customer_master_master_id is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.name = st.name, false) OR
          ({db}.{schema}.{dim_table}.name is null and st.name is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.cust_type = st.cust_type, false) OR
          ({db}.{schema}.{dim_table}.cust_type is null and st.cust_type is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.order_exp = st.order_exp, false) OR
          ({db}.{schema}.{dim_table}.order_exp is null and st.order_exp is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.payment_term = st.payment_term, false) OR
          ({db}.{schema}.{dim_table}.payment_term is null and st.payment_term is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.discount = st.discount, false) OR
          ({db}.{schema}.{dim_table}.discount is null and st.discount is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.territory = st.territory, false) OR
          ({db}.{schema}.{dim_table}.territory is null and st.territory is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.physical = st.physical, false) OR
          ({db}.{schema}.{dim_table}.physical is null and st.physical is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.customer_id,
      st.customer_master_master_id,
      st.name,
      st.cust_type,
      st.order_exp,
      st.payment_term,
      st.discount,
      st.territory,
      st.physical,
      '{current_timestamp}' AS last_updated,
      '{current_timestamp}' AS date_created
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} dc ON dc.customer_id = st.customer_id
    WHERE
      dc.customer_id IS NULL