How to pass a hardcoded string to surrogate key function

Help
1

I’m trying to generate a surrogate key based on the business key of the table, and the name of the source system it is coming from (this is so I can eventually merge data from two different source systems, that use similar conventions for generating ids, without unintentional collisions). At the moment, I basically have something like this


with customers as
(
select  *,
		'My Sales System' as source_system
from    {{ source('datalake', 'customers')}}
)

select 	{{ dbt_utils.generate_surrogate_key(['customer_id', 'source_system']) }},
		*
from 	customers

For simplicity sake, I’d like to skip the CTE and be able to reference the name of the source system directly in the surrogate key function, eg;

select 	{{ dbt_utils.generate_surrogate_key(['customer_id', ''My Sales System'']) }},
		*
from    {{ source('datalake', 'customers')}}

But all my attempts to escape the quote marks seems to fail. How do I pass a hardcode string directly to a function like this (as opposed to a column output)?

2

generate surrogate key is a macro which concat columns and add hash function on top of it
if you want to add string, you have 2 ways i could think of

  1. you have already shown above
  2. create your own macro

Note: @mdayunus originally posted this reply in Slack. It might not have transferred perfectly.

3

You can use double quotes and single quotes to achieve this:

select 	{{ dbt_utils.generate_surrogate_key(['customer_id', "'My Sales System'"]) }},
		*
from    {{ source('datalake', 'customers')}}