source_db: snowflake
dim_table: dim_canonical_track_metadata
export_sql: >-
  /*This query evaluates canonical (from most recent release) track metadata for all labels.*/
  SELECT
    isrc,
    upc,
    artistname,
    trackname,
    trackid,
    labelid,
    NULL AS subaccountid,
    product_id,
    track_unique_id,
    last_updated
  FROM (
    SELECT
      t.isrc,
      t.upc,
      a.artistname,
      t.trackname,
      t.trackid,
      r.labelid,
      r.product_id,
      t.track_unique_id,
      t.last_updated,
      ROW_NUMBER() OVER(
        PARTITION BY t.isrc, r.labelid
        ORDER BY r.deletions asc, r.releasedate desc, cd desc, track_id desc
      ) order_number
    FROM {db}.{schema}.dim_track t
    INNER JOIN {db}.{schema}.dim_release r ON t.upc = r.releaseid
    INNER JOIN {db}.{schema}.dim_artist a ON r.artistid = a.artistid
    WHERE r.release_status = 'in_content'
      AND r.product_type = 'digital'
  )
  WHERE order_number = 1

  UNION

  /*This query evaluates canonical (from most recent release) track metadata for all subaccounts.*/
  SELECT
    isrc,
    upc,
    artistname,
    trackname,
    trackid,
    labelid,
    subaccountid,
    product_id,
    track_unique_id,
    last_updated
  FROM (
    SELECT
      t.isrc,
      t.upc,
      a.artistname,
      t.trackname,
      t.trackid,
      r.labelid,
      r.subaccountid,
      r.product_id,
      t.track_unique_id,
      t.last_updated,
      ROW_NUMBER() OVER(
        PARTITION BY t.isrc, r.labelid, r.subaccountid
        ORDER BY r.deletions asc, r.releasedate desc
      ) order_number
    FROM {db}.{schema}.dim_track t
    INNER JOIN {db}.{schema}.dim_release r ON t.upc = r.releaseid
    INNER JOIN {db}.{schema}.dim_artist a ON r.artistid = a.artistid
    WHERE r.subaccountid IS NOT NULL
      AND r.release_status = 'in_content'
      AND r.product_type = 'digital'
  )
  WHERE order_number = 1
insert_sql:
  - >-
    TRUNCATE TABLE {db}.{schema}.{dim_table}
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
      (
        isrc,
        upc,
        artistname,
        trackname,
        trackid,
        labelid,
        subaccountid,
        product_id,
        track_unique_id,
        last_updated
      )
    SELECT
      st.isrc,
      st.upc,
      st.artistname,
      st.trackname,
      st.trackid,
      st.labelid,
      st.subaccountid,
      st.product_id,
      st.track_unique_id,
      st.last_updated
    FROM
      {db}.{schema}.{staging_table} st
    ORDER BY
      labelid,
      subaccountid,
      isrc
insert_count_sql: >-
  SELECT count(*) FROM {db}.{schema}.{dim_table}
update_count_sql: >-
  SELECT count(*) FROM {db}.{schema}.{dim_table}