Data warehouses like Redshift, BigQuery, and Snowflake don’t natively support an alter column operation to let a user change a column’s data type once it's been created.
To address this we introduced alter column: Giving the ability to change the name and data type of a column after it's already created.
We currently support 2 alter scenarios:
- Field rename
In the Mapper, just change the name of the field and apply.
If the column type remains the same then we simply rename the column in Redshift.
- Varchar length change
In the Mapper, invoke the type dropdown, change the length in the text box, and apply. We then:
- Rename the old column with a temporary name.
- Add new column with the desired type and either original (or different, if requested) name.
- Load data from the old column to the new column.
- Drop the old column.
If the varchar is made shorter, note that Redshift will automatically truncate any fields that are too long for the new length.
Note that at the moment, these are supported in Redshift only. Also, because the process drops a column and creates a new one, the new column is added at the end of the table, and thus the column position changes.
Rest assured, all of the changes happen in a single transaction. If any steps in the sequence fail, then the entire set of changes is cancelled. This ensures that there are no side effects of an alter that might not necessarily succeed -- such as changing varchars to timestamps where not all the data can be cast.
Not a user of Redshift? Want to change different data types? Those self-serve features are on their way! In the meantime, our support team can help you with any change.