# How to get incremental number by using surrogate key ?

**URL:** <https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970>\
**Category:** Help\
**Created:** [April 8, 2025, 8:37pm UTC](https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970 "2025-04-08T20:37:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Devika](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/devika/32/6421_2.png) [@Devika](https://discourse.getdbt.com/u/Devika)\
**Post date:** [April 8, 2025, 8:37pm UTC](https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970/1 "2025-04-08T20:37:51Z")

</div>

Hi dbt gurus,

I am using `{{ dbt_utils.generate_surrogate_key(['field_a', 'field_b', ...]) }}` to generate a primary key. Instead of a hash, can I generate a surrogate key with sequential/incremental numbers?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 8, 2025, 10:22pm UTC](https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970/2 "2025-04-08T22:22:43Z")

</div>

In my opinion never use sequential numbers. nowadays always use a hash

Note: `@david.dumas` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744150954169989?thread_ts=1744144693.930479&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![creedthoughtsdotgov](https://avatars.discourse-cdn.com/v4/letter/c/ee7513/32.png) [@creedthoughtsdotgov](https://discourse.getdbt.com/u/creedthoughtsdotgov)\
**Post date:** [April 9, 2025, 7:20am UTC](https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970/3 "2025-04-09T07:20:51Z")

</div>

If your db supports AI, then use that, the one that I am using doesn’t, so what I am doing is:

1. Set a variable of the last SK in the target table at the beginning of my model

2. Increment that value in the select

3. Last you need to have a left join to the target table

The big drawback with this approach (along with being ugly) is that it does not support paralel runs as for two models that reached the `get_last_sk()` step at the same time, will get the same `last_sk` value from the target table leading to colliding SKs for the new records.

The code for the `get_last_sk` macro is:

```sql
{% macro get_last_sk(table_name,sk_column_name) %}

{{ log("Retrieving last surrogate key from: " ~ table_name ~ "." ~ sk_column_name) }}	

{% set last_sk_query %}
	select coalesce(max({{sk_column_name}}),0) as sk from {{table_name}}
{% endset %}

{% set results = run_query(last_sk_query) %}

{% if execute %}
	{# return the first value of the first column #}
	{% set result_value = results.columns[0][0] %}
{% else %}
	{% set result_value = 0 %}
{% endif %}

{{ log("Last SK for " ~ table_name ~ " : " ~ result_value) }}

{{ return(result_value) }}

{% endmacro %}

```

Hope that helps.

---

<div class="post-metadata">

**Author:** ![troy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/troy/32/3820_2.png) [@troy](https://discourse.getdbt.com/u/troy)\
**Post date:** [April 17, 2025, 6:53pm UTC](https://discourse.getdbt.com/t/how-to-get-incremental-number-by-using-surrogate-key/18970/4 "2025-04-17T18:53:16Z")

</div>

Another option:

select (seq8()\*1000000000000000 + to\_number(to\_char(current\_timestamp,‘yyyymmddhh24miss’))) as table\_pk,  
col1, col2, etc…

That uses the timestamp as the base of the key and then increments in front of the timestamp, and therefore will produce all unique values that are different every time the select statement is run. It’s a large number, but it is dead simple and works great in lieu of using a sequence generator.