[FEATURE] metadata columns does not appear summary tables

[nbenezri]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I want to add some columns from the metadata column into some of the summary tables:

stripe__balance_transactions
stripe__invoice_details
stripe__invoice_line_item_details
stripe__subscription_details
stripe__customer_overview

With the exception of stripe__customer_overview adding the following vars in dbt_project.yml did not help:

  stripe__charge_metadata: ['col1', 'col2', 'col3', 'col4'] 
  stripe__invoice_metadata: ['col1', 'col2', 'col3', 'col4']
  stripe__subscription_metadata: ['col1', 'col2', 'col3', 'col4']
  stripe__customer_metadata: ['col1']

For now, I did the same as here , adding manually

{% if var('stripe__charge_metadata',[]) %}
      {% for metadata in var('stripe__charge_metadata') %}
          ,charge.{{ metadata }} as charge_{{ metadata }}
      {% endfor %}
    {% endif %}

    {% if var('stripe__customer_metadata',[]) %}
      {% for metadata in var('stripe__customer_metadata') %}
          ,customer.{{ metadata }} as customer_{{ metadata }}
      {% endfor %}
    {% endif %}

    {% if var('stripe__charge_metadata',[]) %}
      {% for metadata in var('stripe__charge_metadata') %}
          ,refund_charge.{{ metadata }} as refund_charge_{{ metadata }}
      {% endfor %}
    {% endif %}

To files like stripe__balance_transactions.sql

Relevant error log or model output

No error was reported as dbt run and test work.

Expected behavior

Columns col1, col2, col3, col4 appear in:

stripe__balance_transactions
stripe__invoice_details
stripe__invoice_line_item_details
stripe__subscription_details

Column col1 appears in (it is):
stripe__customer_overview

dbt Project configurations

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'fivetran_dbt'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'fivetran_dbt'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  fivetran_dbt:
  stripe:
  stripe_source:

vars:
  using_schedules: False
  stripe_source:
    stripe_database: dkall
    stripe_schema: stripe
  stripe_database: dkall
  stripe_schema: stripe
  stripe__subscription_history: True
  stripe_source:
    stripe__using_invoice_line_sub_filter: false
  stripe__charge_metadata: ['col1', 'col2', 'col3', 'col4'] 
  stripe__invoice_metadata: ['col1', 'col2', 'col3', 'col4']
  stripe__subscription_metadata: ['col1', 'col2', 'col3', 'col4']
  stripe__customer_metadata: ['col1']

Package versions

packages:
  - package: fivetran/stripe
    version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

redshift

dbt Version

Core:
  - installed: 1.5.1
  - latest:    1.5.1 - Up to date!

Plugins:
  - postgres: 1.5.1 - Up to date!
  - redshift: 1.5.4 - Up to date!

Additional Context

No response

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

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

[fivetran-joemarkiewicz]

Hi @nbenezri thanks for opening this issue.

I understand you are wanting to bring the metadata fields through to the end models. However, we did intentionally exclude those from a number of end models in a previous release as we were being mindful of scope creep and were not sure if users wanted these fields to persist to the end models. As such, I will restructure this issue as a feature request to include the metadata columns to the end models.

That being said, I believe you have the right approach for how this should be integrated into the end models in the manual code snippets you shared above. Before accepting this feature I would want to understand a bit more which metadata fields you are most interested in persisting to which downstream models. The main reason we excluded these in the first place was we found extra complexity being added and were unsure of the benefits for including the fields if we made the models more complex. Or would you prefer all metadata fields are persisted downstream to the relevant models no matter what?

[nbenezri]

I thought the whole purpose was to show them at the end models.

I would want whatever we configure in the vars to be visible everywhere possible (at the end models).

So if I set, for example, stripe__charge_metadata: ['col1', 'col2', 'col3', 'col4'] I expect to see those columns everywhere the charge table is used. Of course, never all the metadata columns, just what I've put in the vars.
It can be also a path like col1.data (might be already supported, never tested).

[fivetran-joemarkiewicz]

In the current build the intention was to bring these metadata columns through to the staging models so users could leverage them in their own transformations. We were unaware at the time of the implementation how relevant these fields would be to users so we decided to only bring them through to the staging models to assess their value and determine how/if they would ultimately be leveraged downstream.

Your input is a huge help in regards to how users intend to leverage these fields! We will share our plans here as we continue to scope and prioritize this feature on our end. If other users are interested in this feature I would highly encourage chiming into this thread so we can understand how to best support this feature.

[seanpwlms]

Would you accept a contribution to implement this behavior? Either way, it would be helpful to document that metadata cannot be passed through to end tables in this section:

Passing Through Additional Fields
This package includes all source columns defined in the macros folder. You can add more columns using our pass-through column variables. These variables allow for the pass-through fields to be aliased (alias) and casted (transform_sql) if desired, but not required. Datatype casting is configured via a sql snippet within the transform_sql key. You may add the desired sql while omitting the as field_name at the end and your custom pass-though fields will be casted accordingly. Use the below format for declaring the respective pass-through variables:

# dbt_project.yml

vars:
  stripe:
    card_pass_through_columns:
      - name: "description"
      - name: "iin"
      - name: "issuer"
        alias: "card_issuer"  # optional: define an alias for the column 
        transform_sql: "cast(card_issuer as string)" # optional: apply transformation to column. must reference the alias if provided

[nbenezri]

I can create a PR with how we're doing it if that's what you asked.