[Bug] column_list_to_dict macro will override the column's metadata if you pass through a column that is already selected from the source.

[tunguyensinh]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

For example, if we configure pass-through column email with a different alias

salesforce__lead_pass_through_columns: # pass columns from lead source
  - name: "email"
    alias: "internal_email"
    renamed_column_name: "Email"
    transform_sql: "case when email like '%domain%'  then true else false end"

This line call column_list_to_dict macro, that will override the existing email's metadata, therefore, cause renamed_column_name field empty, result in invalid SQL syntax (rendered by salesforce_source.coalesce_rename macro):

with fields as (
select ...
),
final as (
select
...
    coalesce(cast( as TEXT),
        cast(email as TEXT))
        as internal_email,
from fields
)
select *
from final
where not coalesce(is_deleted, false)
  );

Relevant error log or model output

syntax error at or near "as" in context "(cast( as", at line 793, column 20
  compiled code at target/run/salesforce_source/models/salesforce/stg_salesforce__lead.sql

Expected behavior

Should not override metadata of existing column,

expected:

    coalesce(cast(Email as TEXT),
        cast(email as TEXT))
        as email,

Possible solution

Add the field {is_rename: True} for pass-through columns in get_*_columns.sql or within the fivetran_utils.add_pass_through_columns macro so that the macro column_list_to_dict does not override them. Keen to hear your suggestions.

dbt Project configurations

salesforce__lead_pass_through_columns: # pass columns from lead source
  - name: "email"
    alias: "internal_email"
    renamed_column_name: "Email"
    transform_sql: "case when email like '%domain%'  then true else false end"

Package versions

">=1.1.0", "<1.2.0"

What database are you using dbt with?

redshift

How are you running this dbt package?

dbt Core™

dbt Version

1.8

Additional Context

No response

Are you willing to open a PR to help address this issue?

• [ ] Yes.
• [X] Yes, but I will need assistance.
• [ ] No.

[fivetran-catfritz]

Hi @tunguyensinh thanks for opening this issue! It is an interesting one. I think your first suggestion

Add the field {isrename: True} for pass-through columns in get*_columns.sql

might be the best path since fivetran_utils.add_pass_through_columns is meant for all our packages.

I saw you checked you were willing to open a PR. If that's still the case, for now you can create a fork of this repo, make your changes, and then open a PR against this repo with your changes. We'll let you know what else we need from there, or let us know if you have any other questions!