How do I specify column types?

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

select
id,
created::timestamp as created
from 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.