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

**URL:** <https://discourse.getdbt.com/t/need-help-using-incremental-to-append-new-campaigns-to-the-table-and-incrementing-surrogate-key-by-1/13145>\
**Category:** Help\
**Tags:** incremental, python-models, postgres\
**Created:** [May 1, 2024, 1:46pm UTC](https://discourse.getdbt.com/t/need-help-using-incremental-to-append-new-campaigns-to-the-table-and-incrementing-surrogate-key-by-1/13145 "2024-05-01T13:46:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dchen](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dchen/32/2529_2.png) [@dchen](https://discourse.getdbt.com/u/dchen)\
**Post date:** [May 1, 2024, 1:46pm UTC](https://discourse.getdbt.com/t/need-help-using-incremental-to-append-new-campaigns-to-the-table-and-incrementing-surrogate-key-by-1/13145/1 "2024-05-01T13:46:45Z")

</div>

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

```auto
-- 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

```

---

<div class="post-metadata">

**Author:** ![dchen](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dchen/32/2529_2.png) [@dchen](https://discourse.getdbt.com/u/dchen)\
**Post date:** [May 1, 2024, 5:29pm UTC](https://discourse.getdbt.com/t/need-help-using-incremental-to-append-new-campaigns-to-the-table-and-incrementing-surrogate-key-by-1/13145/2 "2024-05-01T17:29:14Z")

</div>

This is what I came up with:

```auto
-- 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