snowflake-cloud-data-platformdbt

How can I create a custom dbt materialization for sequences in Snowflake?


I’m working with dbt and Snowflake and need to create a custom materialization for managing database sequences. Since dbt's built-in materializations are intended for tables and views, I’m having trouble adapting them for sequences.

Custom Materialization Code I’m attempting to create a custom materialization for sequences with the following code:

{%- materialization sequences,default -%}

  {%- set target_relation = this %}

  -- Define the sequence creation SQL
  {% set sequence_creation_sql %}
    CREATE SEQUENCE IF NOT EXISTS {{ this.schema }}.{{ this.identifier }}
  {% endset %}

  -- Run the sequence creation statement
  {% call statement('main') -%}
    {{ sequence_creation_sql }}
  {%- endcall %}


{%- endmaterialization -%}

Model Code In the models directory, I have a model file that uses this materialization:

{{ config(
    schema="dwh_in",
    materialized='sequences'
) }}

trade_sid_seq start with 22036057 increment by 1 noorder

Issues Faced Error Messages:

The materialization ("sequences") did not explicitly return a list of relations to add to the cache.

how can i implement this without any issues, Is there an alternative method to manage sequences in dbt with Snowflake? How can I create or replace a sequence effectively?

Additional Information Database: Snowflake


Solution

  • Since a sequence isn't a relation, you need to return an empty list after your call block.

    {{ return({"relations": []}) }}