Skip to main content

How do I specify column types?

Simply cast the column to the correct type in your model:

select    id,    created::timestamp as createdfrom some_other_table

You might have this question if you're used to running statements like this:

create table dbt_alice.my_table  id integer,  created timestamp;
insert into dbt_alice.my_table (  select id, created from some_other_table)

In comparison, dbt would build this table using a create table as statement:

create table dbt_alice.my_table as (  select id, created from some_other_table)

So long as your model queries return the correct column type, the table you create will also have the correct column type.

To define additional column options:

  • Rather than enforcing uniqueness and not-null constraints on your column, use dbt's testing functionality to check that your assertions about your model hold true.
  • Rather than creating default values for a column, use SQL to express defaults (e.g. coalesce(updated_at, current_timestamp()) as updated_at)
  • In edge-cases where you do need to alter a column (e.g. column-level encoding on Redshift), consider implementing this via a post-hook.