source_db: art_relations
dim_table: release_subgenre
export_sql: >-
  SELECT
    upc,
    subgenre_id,
    id
  FROM release_subgenre
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      subgenre_id = st.subgenre_id,
      upc = st.upc,
      last_updated = '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.id = st.id AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.subgenre_id = st.subgenre_id, false) OR
          ({db}.{schema}.{dim_table}.subgenre_id is null and st.subgenre_id is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.upc = st.upc, false) OR
          ({db}.{schema}.{dim_table}.upc is null and st.upc is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.upc,
      st.subgenre_id,
      st.id,
      '{current_timestamp}',
      '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} ds ON ds.id = st.id
    WHERE
      ds.id IS NULL