[Feature] include foreign key IDs in end models

[fivetran-jamie]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

In our end models, we do not currently include foreign key IDs in the final CTE.

Currently, we use these FKs to join in information about their entities (i.e. subsidiaries, locations, vendors, departments, entity), which we bring into the end model's final CTE. This leaves information without an easy way to join back to the source entity from which the info came. Moreover, we don't bring in ALL the information available in these joins. Having the ID in there lets people grab more info than we are (but maybe we should bring in more fields by default as well?)

Describe alternatives you've considered

To get around this, I created passthrough columns that basically copy the foreign key ID fields and alias them so as to not cause duplicate column errors. Then, in a model in my own dbt project, I rename the the aliased FK field to its original name (since the FK has been excluded from our end models)

the dbt_project.yml looks like

vars:
  transaction_lines_pass_through_columns:
    - name: "department_id" 
      alias: "department_id_copy"
    - name: "subsidiary_id"
      alias: "subsidiary_id_copy"
    - name: "location_id"
      alias: "location_id_copy"

this is a messy and a lil annoying to do (but functional)

Are you interested in contributing this feature?

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

Anything else?

No response

[fivetran-jamie]

department_id is now included in transaction_details here

subsidiary_id is included in transaction_details here

But we're still missing:

• location_id
• class_id
• vendor_id
• customer_id
• currency_id
• parent_account_id
• vendor_category_id (if netsuite2__using_vendor_categories is true)

[fivetran-avinash]

This was addressed in the latest version of dbt_netsuite, closing this issue.