IN-DB questions (primary key, changing field types)

Question

Hi,

I've been migrating data to Snowflake.  I figured out how to have my local workflows output to Snowflake tables and found articles on using post-SQL to add primary keys.

For another data set, the data will already be in Snowflake, so I wanted to experiment with in-DB tools to speed up the process.  Two items I have questions on:

(1) the raw data is all varchar(254).  With a local workflow, I can use the Select tool or AutoField to change field types and sizes before outputting to Snowflake.  The in-DB Select tool only lets me change the names.   From other articles, it sounds like my only option is to use the Formula tool to change the name and then use cast (...) to change the type/size if desired?  Are there other options?

(2) With the local workflow, I added post-SQL to the output tool to set the primary keys for the table.   Is there a way to set primary keys when using in-DB tools?   Basically, I want to read data from the raw source table, change fields names/types, add some calcs, then output to a new table and set the primary key(s) all with in-DB tools.

Thanks,

Jeff

jrpaul · Answer

Thanks, @ArtApa . For (1), I meant use Formula IN-DB, so I knew about the solution you offered. For (2), I didn't mean create a unique ID for each row, I meant designating primary key(s) in the database table. So when someone views that table in Snowflake, it will show which fields are the primary key(s). In SQL, I'd do "alter table add primary key ()". This works as a post-SQL command in the regular Output tool. I don't see how to do this with in-DB tools, so wondering if there is an option? Thanks again. Jeff

ArtApa · Answer

Hi @jrpaul

(1) - Alternatively, you can use a Formula In-DB to create a field with a required data type and a value from the original field and then a Select In-DB to deselect the original field

(2) - You can use a Formula In-DB to create a RecordID field with the following expression: ROW_NUMBER() OVER(ORDER BY [Field]), where [Field] is any field in your source.