account for changing column types in incremental tables

[drewbanin]

Incremental tables are a powerful dbt feature, but there's at least one edge case which makes working with them difficult. During the first run of an incremental model, Redshift will infer a type for every column in the table. Subsequent runs can insert new data which does not conform to the expected type. One example is a varchar(16) field which is inserted into a varchar(8) field.
In practice, this error looks like:

Value too long for character type
DETAIL:
  -----------------------------------------------
  error:  Value too long for character type
  code:      8001
  context:   Value too long for type character varying(8)
  query:     3743263
  location:  funcs_string.hpp:392
  process:   query4_35 [pid=18194]
  -----------------------------------------------

The recommended solution, currently, is to change the model materialization to table, re-run dbt, then change it back to incremental.

One possible solution is to the use temp table generated by dbt during incremental model updates. The types of every temp table column can be compared to the types of every existing table column. If the types differ, then dbt can alter the offending column to the type of temp table column.

This is kind of tricky in practice, because it could be that the existing column is varchar(8) while the new column is varchar(1)... Some logic is required to ensure that columns are not altered to more-restrictive data types.

Additionally, all incremental model operations currently occur within a single transaction. If we were to implement this proposed solution, we'd need to run Python code in between the temp table phase and the delete/insert phase of the incremental model update, thus splitting up the transaction.

CC @jthandy

[jthandy]

I don't believe that running python in the middle of a transaction means we need to "split" it... I agree that there is complexity involved in the running of this, and that it's similar to what we've spoken about before where a single operation can be split across multiple nodes in the graph. I think the important thing to do there is to make it such that multiple nodes can participate in a single transaction.

[jthandy]

(otherwise, I'm 👍 on everything you outlined)

[drewbanin]

0.5.1 release