source_db: snowflake
dim_table: dim_subgenre
export_sql: >-
  SELECT
    orchard_id AS subgenreid,
    name,
    genre_id AS genreid,
    music_video,
    composer
  FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.subgenre
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      subgenreid = st.subgenreid,
      name = st.name,
      genreid = st.genreid,
      music_video = st.music_video,
      composer = st.composer,
      last_updated = '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {dim_table}.subgenreid = st.subgenreid
      AND (
        {dim_table}.name <> st.name
        OR {dim_table}.genreid <> st.genreid
        OR {dim_table}.music_video <> st.music_video
        OR {dim_table}.composer <> st.composer)
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.subgenreid,
      st.name,
      st.genreid,
      st.music_video,
      st.composer,
      '{current_timestamp}',
      '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} dsg on dsg.subgenreid = st.subgenreid
    WHERE
      dsg.subgenreid is null