source_db: art_relations
dim_table: dim_subaccount
export_sql: >-
  SELECT
    subaccount_id,
    vendor_id,
    stripSpecialChars(subaccount_name),
    genre_id,
    stripSpecialChars(website_url),
    stripSpecialChars(myspace_url),
    stripSpecialChars(city),
    stripSpecialChars(state_province),
    country_id,
    stripSpecialChars(description),
    commission_override,
    date_deleted,
    date_created,
    last_updated,
    subaccount_split_type
  FROM subaccount
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      labelId = st.labelId,
      subaccountName = st.subaccountName,
      primaryGenre = st.primaryGenre,
      websiteUrl = st.websiteUrl,
      myspaceUrl = st.myspaceUrl,
      city = st.city,
      stateProvince = st.stateProvince,
      country = st.country,
      description = st.description,
      commissionOverride = st.commissionOverride,
      date_deleted = st.date_deleted,
      last_updated = '{current_timestamp}',
      subaccount_split_type = st.subaccount_split_type
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.subaccountId = st.subaccountId AND
      (
        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}.subaccountName = st.subaccountName, false) OR
          ({db}.{schema}.{dim_table}.subaccountName is null and st.subaccountName is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.primaryGenre = st.primaryGenre, false) OR
          ({db}.{schema}.{dim_table}.primaryGenre is null and st.primaryGenre is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.websiteUrl = st.websiteUrl, false) OR
          ({db}.{schema}.{dim_table}.websiteUrl is null and st.websiteUrl is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.myspaceUrl = st.myspaceUrl, false) OR
          ({db}.{schema}.{dim_table}.myspaceUrl is null and st.myspaceUrl is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.city = st.city, false) OR
          ({db}.{schema}.{dim_table}.city is null and st.city is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.stateProvince = st.stateProvince, false) OR
          ({db}.{schema}.{dim_table}.stateProvince is null and st.stateProvince is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.country = st.country, false) OR
          ({db}.{schema}.{dim_table}.country is null and st.country is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.description = st.description, false) OR
          ({db}.{schema}.{dim_table}.description is null and st.description is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.commissionOverride = st.commissionOverride, false) OR
          ({db}.{schema}.{dim_table}.commissionOverride is null and st.commissionOverride is null)
        ) OR
        NOT (
          nvl({dim_table}.date_deleted = st.date_deleted, false) OR
          ({dim_table}.date_deleted is null and st.date_deleted is null)
        ) OR
        NOT (
          nvl({dim_table}.subaccount_split_type = st.subaccount_split_type, false) OR
          ({dim_table}.subaccount_split_type is null and st.subaccount_split_type is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.subaccountId,
      st.labelId,
      st.subaccountName,
      st.primaryGenre,
      st.websiteUrl,
      st.myspaceUrl,
      st.city,
      st.stateProvince,
      st.country,
      st.description,
      st.commissionOverride,
      st.date_deleted,
      '{current_timestamp}' as date_created,
      '{current_timestamp}' as last_updated,
      st.subaccount_split_type
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} ds ON ds.subaccountId = st.subaccountId
    WHERE
      ds.subaccountId IS NULL