[Question] Custom metadata column don't use alias feature

[nbenezri]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Following https://github.com/fivetran/dbt_stripe/issues/65 I am trying to add a column from the metadata which already exists in the table.

we have currency in the metadata that we want to extract. Adding it to the var:

vars:
  stripe__invoice_metadata: ['amounts', 'currency']

Results an issue:

15:45:04  Database Error in model stg_stripe__invoice (models/stg_stripe__invoice.sql)
15:45:04    column name "currency" is duplicated
15:45:04    compiled Code at target/run/stripe_source/models/stg_stripe__invoice.sql

So I was trying to add the alias like in the doc:

vars:
  stripe__invoice_metadata:
    - name: amounts
    - name: currency
      alias: metadata_currency

and it fails with:

08:43:00  Database Error in model stripe__invoice_details (models/stripe__invoice_details.sql)
08:43:00    syntax error at or near "{"
08:43:00    compiled Code at target/run/stripe/models/stripe__invoice_details.sql
08:43:00

I guess that I'm not utilizing the alias feature in the code:

    {% if var('stripe__invoice_metadata',[]) %}
      {% for metadata in var('stripe__invoice_metadata') %}
          ,invoice.{{ metadata }} as invoice_{{ metadata }}
      {% endfor %}
    {% endif %}

Would love for some guidelines

Relevant error log or model output

No response

Expected behavior

currency from the metadata appears in the final invoice table.

dbt Project configurations

As described in https://github.com/fivetran/dbt_stripe/issues/65

Package versions

As described in https://github.com/fivetran/dbt_stripe/issues/65

What database are you using dbt with?

redshift

dbt Version

As described in https://github.com/fivetran/dbt_stripe/issues/65

Additional Context

No response

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

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-jamie]

hey there 👋 -- so just to clarify, the below code is something you've added manually to a forked version of the stripe__invoice_details model?

    {% if var('stripe__invoice_metadata',[]) %}
      {% for metadata in var('stripe__invoice_metadata') %}
          ,invoice.{{ metadata }} as invoice_{{ metadata }}
      {% endfor %}
    {% endif %}

if i'm understanding that correctly, i believe you would need to adjust the above to treat stripe__invoice_metadata as a dictionary instead of a list. so something like

    {% if var('stripe__invoice_metadata',[]) %}
      {% for metadata in var('stripe__invoice_metadata') %}
          ,invoice.{{ metadata.alias if metadata.alias else metadata.name }} as invoice_{{ metadata.alias if metadata.alias else metadata.name }}
      {% endfor %}
    {% endif %}

[nbenezri]

You nailed it.

Unfortunately, it returns:

16:03:15  Database Error in model stripe__invoice_line_item_details (models/stripe__invoice_line_item_details.sql)
16:03:15    syntax error at or near "{"
16:03:15    compiled Code at target/run/stripe/models/stripe__invoice_line_item_details.sql

[fivetran-jamie]

ah looks like you may need to make the same change in your stripe__invoice_line_item_details model in addition to stripe__invoice_details