How to change default 'Transient' materialization with Snowflake

Help
1

Hi Team,

It looks like dbt is creating a ‘Transient’ table by default when connected with Snowflake. ‘Transient’ table in Snowflake will only have a data retention period of 1 day. When i see final target sql queries generated for each model starts with “create or replace TRANSIENT TABLE”.

Instead of ‘Transient’, I would like to create a ‘Permanent’ table in Snowflake which will have a data retention period of 90 days. Please help how can i change this default behavior. What I want dbt to generate SQLs is with “create or replace TABLE” which will create a ‘Permanent’ table by default in Snowflake.

Thanks in advance for all your suggestions.

1 Like
2

See this section of the documentation: https://docs.getdbt.com/reference/resource-configs/snowflake-configs#transient-tables

Note: @Josh Devlin (he) originally posted this reply in Slack. It might not have transferred perfectly.

3

Thank you. It helped.

4

Hi @rsnt2010 , You can simply use the config here to make the permeant tables.

{{ config(materialized=“table”) }}

if you simple mention the materialized=“table” it will create the permeant table or else if you want to create transient for particular model only there you can simply mention the

{{ config(materialized=“table”, transient=true) }}

Hope this helps.

5

@akashsolanke22 this is not actually correct, dbt-snowflake defaults to transient tables.