source_db: art_relations
dim_table: dim_artist
export_sql: >-
  SELECT
    a.artist_id,
    stripSpecialChars(a.name),
    a.vendor_id AS labelId,
    a.orchard_country AS countryId
  FROM
    artist_info a
  INNER JOIN
    vendor v ON v.vendor_id = a.vendor_id
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      artistName = st.artistName,
      labelId = st.labelId,
      countryId = st.countryId,
      last_updated = '{current_timestamp}'
      FROM {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.artistId = st.artistId AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.artistName = st.artistName, false) OR
          ({db}.{schema}.{dim_table}.artistName is null and st.artistName is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.labelId = st.labelId, false) OR
          ({db}.{schema}.{dim_table}.labelId is null and st.labelId is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.countryId = st.countryId, false) OR
          ({db}.{schema}.{dim_table}.countryId is null and st.countryId is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.artistId,
      st.artistName,
      st.labelId,
      st.countryId,
      '{current_timestamp}',
      '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} da ON da.artistId = st.artistId
    WHERE
      da.artistId IS NULL