Integer Surrogate Key

compiled_integer_surrogate_key_example.sql

/*
    Welcome to your first dbt model!
    Did you know that you can also configure models directly within SQL files?
    This will override configurations stated in dbt_project.yml
    Try changing "table" to "view" below
*/





with source_data as (

    select 1 as id
    -- union all
    -- select 2 as id

)


select id, md5_number_lower64(coalesce(cast(id as 
    varchar
), '')) as id_integer
from source_data

/*
    Uncomment the line below to remove records with null `id` values
*/

-- where id is not null
limit 500
/* limit added automatically by dbt cloud */

integer_hash.sql

{% macro integer_hash(field) -%}
  {{ return(adapter.dispatch('hash', 'dbt_utils') (field)) }}
{%- endmacro %}


{% macro default__hash(field) -%}
    md5_number_lower64({{field}})
{%- endmacro %}


{% macro bigquery__hash(field) -%}
    to_hex({{dbt_utils.default__hash(field)}})
{%- endmacro %}

integer_surrogate_key.sql

{%- macro integer_surrogate_key(field_list) -%}
    {# needed for safe_add to allow for non-keyword arguments see SO post #}
    {# https://stackoverflow.com/questions/13944751/args-kwargs-in-jinja2-macros #}
    {% set frustrating_jinja_feature = varargs %}
    {{ return(adapter.dispatch('surrogate_key', 'dbt_utils')(field_list, *varargs)) }}
{% endmacro %}

{%- macro default__surrogate_key(field_list) -%}

{%- if varargs|length >= 1 or field_list is string %}

{%- set error_message = '
Warning: the `integer_surrogate_key` macro now takes a single list argument instead of \
multiple string arguments. Support for multiple string arguments will be \
deprecated in a future release of dbt-utils. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}

{%- do exceptions.warn(error_message) -%}

{# first argument is not included in varargs, so add first element to field_list_xf #}
{%- set field_list_xf = [field_list] -%}

{%- for field in varargs %}
{%- set _ = field_list_xf.append(field) -%}
{%- endfor -%}

{%- else -%}

{# if using list, just set field_list_xf as field_list #}
{%- set field_list_xf = field_list -%}

{%- endif -%}


{%- set fields = [] -%}

{%- for field in field_list_xf -%}

    {%- set _ = fields.append(
        "coalesce(cast(" ~ field ~ " as " ~ dbt_utils.type_string() ~ "), '')"
    ) -%}

    {%- if not loop.last %}
        {%- set _ = fields.append("'-'") -%}
    {%- endif -%}

{%- endfor -%}

{{integer_hash(dbt_utils.concat(fields))}}

{%- endmacro -%}

integer_surrogate_key_example.sql

/*
    Welcome to your first dbt model!
    Did you know that you can also configure models directly within SQL files?
    This will override configurations stated in dbt_project.yml
    Try changing "table" to "view" below
*/


{{ config(materialized='table') }}


with source_data as (

    select 1 as id
    -- union all
    -- select 2 as id

)


select id, {{ integer_surrogate_key(['id']) }} as id_integer
from source_data

/*
    Uncomment the line below to remove records with null `id` values
*/

-- where id is not null

Preview Example

https://docs.snowflake.com/en/sql-reference/functions/md5_number_lower64.html

BigQuery anecdote examples: https://towardsdatascience.com/how-do-column-types-effect-join-speeds-in-data-warehouses-5ddd1933211e