Need help using incremental to append new campaigns to the table and incrementing surrogate key by 1

Help
, ,
1

I need help in creating my first incremental model.

In the campaignchannel table, I have a surrogate_key column which takes the clientcode_id and concats the index number to create it, which acts like an unique key. The idea is that when a new campaign is created we will find the max surrogate_id for that client and +1 so we have a way of having a unique id for all campaigns by client. Will incremental work for this case?

Also, is it good to have a backup for incremental models?

This is python sql code

-- dbt campaign channel level dimension : dim_campaignchannel

{{ config(materialized='incremental', unique_key=surrogate_key) }}

SELECT  --ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername) AS index_number,
CAST(CONCAT(c.clientcode_id, ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername)) as numeric) AS surrogate_key, c.* from
(
SELECT b.clientcode_id, a.clientcode, a.adservername, a.mediachannel, a.adtech, a.programname, a.funnelstage, a.period, a.season, a.campaigntype
      ,a.lob, a.businessline, a.objective, a.market, a.targettype, a.subcampaign, a.campaignyear, a.startdate, a.enddate
FROM  public.map_campaign_segments a
JOIN
public.map_client_segments b ON a.clientcode = b.clientcode
where a.adservername != '-'
order by a.clientcode, a.adservername) c
order by c.clientcode, c.adservername
2

This is what I came up with:

-- dbt campaign channel level dimension : dim_campaignchannel_master

{{ config(materialized='incremental',
          unique_key='surrogate_key') }}

SELECT
    CASE
        WHEN EXISTS (
            SELECT 1 FROM dim_campaignchannel WHERE adservername = c.adservername
        ) THEN
            CAST(CONCAT(c.clientcode_id, ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername)) as numeric) -- Generate a new surrogate key if adservername exists
        ELSE
            (SELECT MAX(surrogate_key) + 1 FROM dim_campaignchannel WHERE clientcode_id = c.clientcode_id) -- Increment the surrogate key for existing clientcode
    END AS surrogate_key,
    c.*
FROM (
    SELECT
        b.clientcode_id, a.clientcode, a.adservername, a.mediachannel, a.adtech, a.programname, a.funnelstage, a.period, a.season, a.campaigntype,
        a.lob, a.businessline, a.objective, a.market, a.targettype, a.subcampaign, a.campaignyear, a.startdate, a.enddate
    FROM
        public.map_campaign_segments a
    JOIN
        public.map_client_segments b ON a.clientcode = b.clientcode
    WHERE
        a.adservername != '-'
    ORDER BY
        a.clientcode, a.adservername
) c

{% if is_incremental() %}

  -- this filter will only be applied on an incremental run
  -- (uses >= to include records arriving later on the same day as the last run of this model)
  WHERE
      NOT EXISTS (
          SELECT 1 FROM dim_campaignchannel WHERE adservername = c.adservername
      )

{% endif %}

ORDER BY
    c.clientcode, c.adservername

Not sure if this is the correct approach