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 the ability to alter columns right inside Alooma. Now you can use the Mapper to alter an existing column.
We currently support 2 alter scenarios (and only in Redshift, at the moment): renaming a field and changing the length of a varchar field. If you need to make other changes or prefer to make them directly in the warehouse, see Altering a Column in the Data Warehouse.
In the Mapper, you can just highlight and type over the name of the field (center column) and click Apply.
Since the column type is unchanged, we simply rename the column in Redshift.
Column type: Varchar length change
In the Mapper,
- For the desired field ("email" in the example below), click on the varchar string in the right-hand column.
- Change the length for the varchar in the text box that appears.
- Click Apply.
Note: If the varchar length is made shorter, Redshift will automatically truncate any fields that are too long for the new length.
Alooma then makes the following changes in the target warehouse:
- Rename the old column with a temporary name.
- Add a new column with the desired varchar length and the original column name.
- Load data from the old column to the new column.
- Drop the old column.
Note: 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.