# Surrogate key for multiple-source dimension table

**URL:** <https://discourse.getdbt.com/t/surrogate-key-for-multiple-source-dimension-table/4401>\
**Category:** Archive\
**Created:** [May 24, 2022, 8:33am UTC](https://discourse.getdbt.com/t/surrogate-key-for-multiple-source-dimension-table/4401 "2022-05-24T08:33:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![pipat\_flowacc](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/pipat_flowacc/32/1061_2.png) [@pipat\_flowacc](https://discourse.getdbt.com/u/pipat_flowacc)\
**Post date:** [May 24, 2022, 8:33am UTC](https://discourse.getdbt.com/t/surrogate-key-for-multiple-source-dimension-table/4401/1 "2022-05-24T08:33:10Z")

</div>

I’m modeling a customer dimension table which is made from various sources  
e.g. sql server, mysql and hubspot. I read Claire’s comment on using surrogate key  
in dbt, but I couldn’t come up with a good surrogate key inputs for the table.

> [@Can I create an auto-incrementing ID in dbt?](https://discourse.getdbt.com/t/generating-an-auto-incrementing-id-in-dbt/579/2):
>
> TL:DR: No, it’s not possible to create an auto-incrementing ID in dbt. In fact, auto-incrementing keys in general are a bad idea. The alternative is to generate a surrogate\_key using a hash, and test the uniqueness of this key. Auto-incrementing IDs in the time of ELT This is good context for anyone that doesn’t come from this background! A long time (a few years) ago, SQL statements that were used for ETL usually had two parts – the first created the table using DDL (data definition language…

A customer data might be created at different time in different sources.  
For example, at start, we have staging customer data like this.

```auto
user | mssql | mysql | hubspot 
-----|-------|-------|--------
A | O | X | X
B | X | O | X
C | O | O | X
D | O | X | O
E | X | O | O

```

Then after time passes, other sources populate customer data also.

```auto
user | mssql | mysql | hubspot 
-----|-------|-------|--------
A | O | O | O
B | O | O | X
C | O | O | X
D | O | O | O
E | O | O | O

```

The grain for the dimension table is 1 row 1 customer.  
So, the surrogate key should be made from `dbt_utils.surrogate_key(['mssql_id', 'mysql_id', 'hubspot_id',])`.  
But, this means the customer A’s key will change from “O-X-X” to “O-O-O”.  
The key change would affect all fact tables downstream.

How should I implement a surrogate key for this table?  
So that a row can be pinned and updated when new sources arrive.

---

<div class="post-metadata">

**Author:** ![hags](https://avatars.discourse-cdn.com/v4/letter/h/8e7dd6/32.png) [@hags](https://discourse.getdbt.com/u/hags)\
**Post date:** [May 24, 2022, 6:01pm UTC](https://discourse.getdbt.com/t/surrogate-key-for-multiple-source-dimension-table/4401/2 "2022-05-24T18:01:46Z")

</div>

If I understand your requirement, you just need to create the surrogate key from the user column, bt\_utils.surrogate\_key([‘user’]), which is the unique key of this table. This assumes that the “user” value is consistent across your three sources.

---

<div class="post-metadata">

**Author:** ![pipat\_flowacc](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/pipat_flowacc/32/1061_2.png) [@pipat\_flowacc](https://discourse.getdbt.com/u/pipat_flowacc)\
**Post date:** [May 25, 2022, 2:05am UTC](https://discourse.getdbt.com/t/surrogate-key-for-multiple-source-dimension-table/4401/3 "2022-05-25T02:05:57Z")

</div>

Yeah. I think it’s the only way to do it.  
I need to have a glue id at each of these sources, so that when they are combined,  
they land on the same row.  
Then, I can use that glue id as a surrogate key macro’s input.  
Thx hags.