CODEWITHSUNDEEP
snowflake-cloud-data-platformdbt
Neethin Nandakumar
Neethin Nandakumar

Reputation: 49

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.

Upvotes: 2

Views: 3265

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Using explicit CAST:

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

Upvotes: 3

Related Questions