source_db: art_relations
dim_table: dim_owner
export_sql: >-
  SELECT
    owner_id,
    owner_type,
    owner_abbrivation,
    stripSpecialChars(owner_name),
    active_contract,
    is_sme
  FROM owner
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      ownerType = st.ownerType,
      ownerAbbrivation = st.ownerAbbrivation,
      ownerName = st.ownerName,
      activeContract = st.activeContract,
      is_sme = st.is_sme,
      last_updated = '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.ownerId = st.ownerId AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.ownerType = st.ownerType, false) OR
          ({db}.{schema}.{dim_table}.ownerType is null and st.ownerType is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.ownerAbbrivation = st.ownerAbbrivation, false) OR
          ({db}.{schema}.{dim_table}.ownerAbbrivation is null and st.ownerAbbrivation is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.ownerName = st.ownerName, false) OR
          ({db}.{schema}.{dim_table}.ownerName is null and st.ownerName is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.activeContract = st.activeContract, false) OR
          ({db}.{schema}.{dim_table}.activeContract is null and st.activeContract is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.is_sme = st.is_sme, false) OR
          ({db}.{schema}.{dim_table}.is_sme is null and st.is_sme is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.ownerId,
      st.ownerType,
      st.ownerAbbrivation,
      st.ownerName,
      st.activeContract,
      '{current_timestamp}' AS last_updated,
      '{current_timestamp}' AS date_created,
      st.is_sme
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} doo ON st.ownerId = doo.ownerId
    WHERE
      doo.ownerId IS NULL