Search code examples
snowflake-cloud-data-platformdbt

Changing snowflake table datatype using DBT

We are using DBT to add table to snowflake. We created a table with a model silmilar to the one below:

{{
config(
    materialized='table'
)
}}
select
NAME,
RIGHT(ADDRESS,6) AS PIN
FROM {{ source('PERSON','PERSON_DETAILS')}}

When the table gets created, the datatype for NAME is correctly shown as varchar(50) in snowflake which is the datatype of the source column. However the datatype for PIN is varchar(16777216) which is the max size for varchar in snowflake. Is there any way to alter the column size using dbt. Changes made using snowflake UI won't suffice as we need the dbt code to move table to higher environments.

Solution

  • Using explicit CAST:

    ...
select
    NAME,
    CAST(RIGHT(ADDRESS,6) AS VARCHAR(6)) AS PIN
FROM {{ source('PERSON','PERSON_DETAILS')}}