# Can you define column data types in dbt?

**URL:** <https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826>\
**Category:** Help\
**Created:** [January 20, 2020, 2:58pm UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826 "2020-01-20T14:58:07Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![rsingh](https://avatars.discourse-cdn.com/v4/letter/r/ee7513/32.png) [@rsingh](https://discourse.getdbt.com/u/rsingh)\
**Post date:** [January 20, 2020, 2:58pm UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/1 "2020-01-20T14:58:07Z")

</div>

Hello DBT, can you kindly advise if there is a way to define “column type” (eg: varchar, date) in dbt?

---

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [January 20, 2020, 7:24pm UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/2 "2020-01-20T19:24:05Z")

</div>

Because dbt uses “Create Table As Select” statements (CTAS) to create tables in relevant database platforms, instead of doing separate “CREATE TABLE” and then “INSERT” statements, it doesn’t natively have a way to specify datatypes.

However, what we do in our dbt models is to force a CAST on every line of the projection part of of the SQL Statement (e.g. the column list normally immediately after SELECT) to ensure that the datatypes coming out are what we think they are.

So, like this:

```
WITH SUBQUERY AS
(SELECT
MD5_BINARY(TO_VARCHAR(DIM_AIRLINE_KEY) || '|' || FLIGHT_NUM || '|' || FLIGHT_LEG_NUM) AS DIM_FLIGHT_LEG_SCHEDULE_KEY,
MD5_BINARY(TO_VARCHAR(DIM_AIRLINE_KEY) || '|' || FLIGHT_NUM) AS DIM_FLIGHT_SCHEDULE_KEY,
-- All of these 5 fields excluded from the star are already in the dimension.
{{ dbt_utils.star(from=ref('stg_flight_leg'), except=["START_DIM_AIRPORT_KEY", "END_DIM_AIRPORT_KEY", "DIM_AIRLINE_KEY", "FLIGHT_NUM"]) }}
FROM {{ ref('stg_flight_leg') }})
SELECT 
    -- Not casting BINARY fields, doesn't make sense
    FACT_FLIGHT_LEG_KEY,
    DIM_FLIGHT_LEG_SCHEDULE_KEY,
    DIM_FLIGHT_SCHEDULE_KEY,
    CAST(FLIGHT_POSITION_DATE AS DATE) AS FLIGHT_POSITION_DATE,
    DIM_AIRPLANE_KEY,
    CAST(FLIGHT_DURATION AS INT) AS FLIGHT_DURATION,
    CAST(FLIGHT_START_DATE AS DATE) AS FLIGHT_START_DATE,
    CAST(FLIGHT_START_TIME AS TIME) AS FLIGHT_START_TIME,
    CAST(FLIGHT_START_TIMESTAMP AS TIMESTAMP) AS FLIGHT_START_TIMESTAMP,
    CAST(FLIGHT_END_DATE AS DATE) AS FLIGHT_END_DATE,
    CAST(FLIGHT_END_TIME AS TIME) AS FLIGHT_END_TIME,
    CAST(FLIGHT_END_TIMESTAMP AS TIMESTAMP) AS FLIGHT_END_TIMESTAMP,
    CAST(FLIGHT_START_LAT AS FLOAT) AS FLIGHT_START_LAT,
    CAST(FLIGHT_START_LONG AS FLOAT) AS FLIGHT_START_LONG,
    CAST(FLIGHT_END_LAT AS FLOAT) AS FLIGHT_END_LAT,
    CAST(FLIGHT_END_LONG AS FLOAT) AS FLIGHT_END_LONG,
    CAST(CNT_LEGS AS NUMBER(18,0)) AS CNT_LEGS,
    CAST(FLIGHT_LEG_NUM AS NUMBER) AS FLIGHT_LEG_NUM,
    CAST(START_AIRPORT_DISTANCE_KM AS FLOAT) AS START_AIRPORT_DISTANCE_KM,
    CAST(END_AIRPORT_DISTANCE_KM AS FLOAT) AS END_AIRPORT_DISTANCE_KM
FROM
SUBQUERY WHERE DIM_FLIGHT_LEG_SCHEDULE_KEY IN
(SELECT DIM_FLIGHT_LEG_SCHEDULE_KEY FROM {{ ref('dim_flight_leg_schedule') }} )

```

Except for the \_KEY fields, which we know are binary so we don’t bother with the cast.

---

<div class="post-metadata">

**Author:** ![Kimcha](https://avatars.discourse-cdn.com/v4/letter/k/94ad74/32.png) [@Kimcha](https://discourse.getdbt.com/u/Kimcha)\
**Post date:** [January 22, 2020, 3:44pm UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/3 "2020-01-22T15:44:23Z")

</div>

> [@josh](#):
>
> – All of these 5 fields excluded from the star are already in the dimension.

I’m wondering what is the best practice of _where_ to do this casting?

For example, my first step is always to create a `base_` table that renames columns. Seems like that would be the right place to do the casting so that the types automatically propagate to downstream queries.

Another consideration is that when you CAST something you always have to specify the column name, which can lead to a lot of repeating as in Josh’s example above.

That could probably be solved through a macro. I checked dbt\_utils and don’t see anything like that yet.

So then the structure could be something like:

```sql
with source as (
    
    select * from stg.ga_page_stats
    
),

casted as (
    select 
        mcast(date, date),
        mcast(pagePath, varchar(3000)),
        mcast(sourceMedium, varchar(512)),
        mcast(campaign, varchar(512)),
        mcast(keyword, varchar(512)),
        mcast(adContent, varchar(512)),
        mcast(fullReferrer, varchar(512)),
        mcast(pageviews, int),
        mcast(uniquePageviews, int)
    from source 
),

renamed as (
    select 
        date as date,
        pagePath as page_path,
        sourceMedium as traffic_source_medium,
        campaign as traffic_campaign,
        keyword as traffic_keyword,
        adContent as traffic_content,
        fullReferrer as full_referrer,
        pageviews as pageviews,
        uniquePageviews as unqiue_pageviews
    from casted 
),

```

Or am I overcomplicating things here?

---

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [January 23, 2020, 4:26am UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/4 "2020-01-23T04:26:23Z")

</div>

We do this casting on every single model. The issue of ensuring that datatypes are what we expect they are is not just an issue for models that are immediately adjacent to sources but for every single model along the way. From an engineering perspective we never want to assume - we always want to verify/confirm/dictate/be prescriptive.

Your idea for a macro to make the CASTing syntax a little more elegant is cool, I like that.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [September 7, 2022, 3:45am UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/5 "2022-09-07T03:45:13Z")

</div>

A post was split to a new topic: [Casting column datatypes based on a seed](https://discourse.getdbt.com/t/casting-column-datatypes-based-on-a-seed/4877)

---

<div class="post-metadata">

**Author:** ![meurant.naude](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/meurant.naude/32/506_2.png) [@meurant.naude](https://discourse.getdbt.com/u/meurant.naude)\
**Post date:** [December 17, 2020, 8:17pm UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/6 "2020-12-17T20:17:55Z")

</div>

We’ve also had a need to cast data types. We unpacked a JSON string.  
This is what we did:

> [@Auto pivot JSON string using schema.yml in BigQuery](https://discourse.getdbt.com/t/auto-pivot-json-string-using-schema-yml-in-bigquery/1592):
>
> Let me start by giving you some background and apologies if the background is longer than the solution. Background The scenario: We work with BigQuery Working with events data split into two data sources due to the constraints of the source system One source is the main set of data, let us call it TABLE\_MAIN The other is the ancillary or supportive data, let us call this one TABLE\_SUPPORT There is a possibility of a 1:n relationship between TABLE\_MAIN and TABLE\_SUPPORT Join these two tables …

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [September 7, 2022, 3:46am UTC](https://discourse.getdbt.com/t/can-you-define-column-data-types-in-dbt/826/7 "2022-09-07T03:46:47Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.