loop through and rename columns

Help
1

Hi,

I’m pretty new to dbt Cloud. Here’s what I’m trying to do:

I’ve got stage files that import every field from my source (snowflake, if it matters). I’m going to go through these staging files and do some field selection and I’d like to have an explicit naming convention

So I’m going to end up doing something like this like 60 times.

select
field1 as mytable_field1,
field2 as mytable_field2,
field3 as mytable_field3,
field5 as mytable_field5
from mytable

What I’m looking for:

  1. I’d like to find some automation that would allow me to loop through each column in the stage file and rename each field procedurally.
  2. Ideally I could come up with a pattern that would let me select specific fields to include up front. This would be a bonus and would ultimately save me a ton of time.

For 1, I found some code on stack overflow that uses this dbt_utils.star function that les you loop through like ‘for each col in dbt_utils.star(mymodel)’ but the trouble seems to be that when you iterate through the the columns the “as name” piece seems to be included in the output which does not give me the opportunity to rename.

So in psuedo code I expect the solution looks like this:
fieldlist = (‘field1’,‘field2’,‘field3’,‘field5’)
select
for col in somefunction(mymodel)
if col in fieldlist
col as mytable_col,
from mymodel

This seems like something that should be achievable but I think I’m just too green to know how to approach this competently.

2

Download VS Code, if you don’t already have it. Open your list of fields in VS Code, then ctrl + f to open the find and replace. Select regex mode, and then enter regex within parenthesis to capture each stage file name, and hit replace all.

You’ll almost certainly need to adjust that regex to suit your field names. But you can do more complicated find and replaces as needed like in the second screenshot.

I recommend using http://regex101.com|regex101.com for getting the right regex.

Edit: you can see attachments from Slack. Here’s the final regex.

Find:
(file[0-9])
Replace:
$1 as my_table_$1,

Note: @Damon Gudaitis originally posted this reply in Slack. It might not have transferred perfectly.

3

Hi,

Thanks for the response. Are you saying I should try to use regex over the value that’s returned from dbt_utils.star()?

4

I’m saying that you should do this outside of dbt and then paste the results back in your dbt project.

Use VS Code to do the formatting and then copy and paste that. Their search box has a regex option which is what makes this practical.

Note: @Damon Gudaitis originally posted this reply in Slack. It might not have transferred perfectly.