[Bug] Customer & Vendor joins in transaction_details

[jmongerlyra]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

There is a bug in netsuite2__transaction_details model that causes company_name and vendor_name to be NULL on certain lines.

The issue is in the joins to customer and vendor master tables. Certain transaction types can contain both transaction_lines.entity_id (line level) and transactions.entity_id (header level) values. The current join preferences the former in all cases.

However, in the example of a vendor bill with a billable customer referenced at the line level, this logic causes the customer ID to be joined to the vendor master table, resulting in a NULL. Instead, the join should be contingent on transaction type.

We will finalize a patch and submit a PR.

Relevant error log or model output

n/a

Expected behavior

Customer should always be populated on invoices and vendors always populated on bills.

dbt Project configurations

config-version: 2
name: 'netsuite'
version: '0.12.0'
require-dbt-version: [">=1.3.0", "<2.0.0"]

models:
  netsuite:
    +materialized: table
    +schema: netsuite
    netsuite:
      intermediate:
        +materialized: ephemeral
    netsuite2:
      intermediate:
        +materialized: ephemeral

vars:
  netsuite:
    ## Netsuite staging models
    netsuite_accounting_books: "{{ ref('stg_netsuite__accounting_books') }}"
    netsuite_accounting_periods: "{{ ref('stg_netsuite__accounting_periods') }}"
    netsuite_accounts: "{{ ref('stg_netsuite__accounts') }}"
    netsuite_classes: "{{ ref('stg_netsuite__classes') }}"
    netsuite_consolidated_exchange_rates: "{{ ref('stg_netsuite__consolidated_exchange_rates') }}"
    netsuite_currencies: "{{ ref('stg_netsuite__currencies') }}"
    netsuite_customers: "{{ ref('stg_netsuite__customers') }}"
    netsuite_departments: "{{ ref('stg_netsuite__departments') }}"
    netsuite_expense_accounts: "{{ ref('stg_netsuite__expense_accounts') }}"
    netsuite_income_accounts: "{{ ref('stg_netsuite__income_accounts') }}"
    netsuite_items: "{{ ref('stg_netsuite__items') }}"
    netsuite_locations: "{{ ref('stg_netsuite__locations') }}"
    netsuite_subsidiaries: "{{ ref('stg_netsuite__subsidiaries') }}"
    netsuite_transaction_lines: "{{ ref('stg_netsuite__transaction_lines') }}"
    netsuite_transactions: "{{ ref('stg_netsuite__transactions') }}"
    netsuite_vendor_types: "{{ ref('stg_netsuite__vendor_types') }}"
    netsuite_vendors: "{{ ref('stg_netsuite__vendors') }}"
    netsuite2_account_types: "{{ ref('stg_netsuite2__account_types') }}"
    netsuite2_accounting_book_subsidiaries: "{{ ref('stg_netsuite2__accounting_book_subsidiaries') }}"
    netsuite2_accounting_books: "{{ ref('stg_netsuite2__accounting_books') }}"
    netsuite2_accounting_period_fiscal_calendars: "{{ ref('stg_netsuite2__accounting_period_fiscal_cal') }}"
    netsuite2_accounting_periods: "{{ ref('stg_netsuite2__accounting_periods') }}"
    netsuite2_accounts: "{{ ref('stg_netsuite2__accounts') }}"
    netsuite2_classes: "{{ ref('stg_netsuite2__classes') }}"
    netsuite2_consolidated_exchange_rates: "{{ ref('stg_netsuite2__consolidated_exchange_rates') }}"
    netsuite2_currencies: "{{ ref('stg_netsuite2__currencies') }}"
    netsuite2_customers: "{{ ref('stg_netsuite2__customers') }}"
    netsuite2_departments: "{{ ref('stg_netsuite2__departments') }}"
    netsuite2_entities: "{{ ref('stg_netsuite2__entities') }}"
    netsuite2_entity_address: "{{ ref('stg_netsuite2__entity_address') }}"
    netsuite2_items: "{{ ref('stg_netsuite2__items') }}"
    netsuite2_jobs: "{{ ref('stg_netsuite2__jobs') }}"
    netsuite2_location_main_address: "{{ ref('stg_netsuite2__location_main_address') }}"
    netsuite2_locations: "{{ ref('stg_netsuite2__locations') }}"
    netsuite2_subsidiaries: "{{ ref('stg_netsuite2__subsidiaries') }}"
    netsuite2_transaction_accounting_lines: "{{ ref('stg_netsuite2__transaction_accounting_lines') }}"
    netsuite2_transaction_lines: "{{ ref('stg_netsuite2__transaction_lines') }}"
    netsuite2_transactions: "{{ ref('stg_netsuite2__transactions') }}"
    netsuite2_vendor_categories: "{{ ref('stg_netsuite2__vendor_categories') }}"
    netsuite2_vendors: "{{ ref('stg_netsuite2__vendors') }}"
    accounts_pass_through_columns: []
    classes_pass_through_columns: []
    departments_pass_through_columns: []
    transactions_pass_through_columns: []
    transaction_lines_pass_through_columns: []
    balance_sheet_transaction_detail_columns: []
    income_statement_transaction_detail_columns: []

Package versions

packages:
  - package: fivetran/netsuite_source
    version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

snowflake

dbt Version

1.7.3

Additional Context

No response

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

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

[fivetran-jamie]

hey @jmongerlyra thanks for opening this and adding the fix to your PR!

left one question for you in the PR here -- we'll be reviewing your PR this sprint FYI 🤠

[fivetran-avinash]

Thanks for addressing this issue in the existing PR @jmongerlyra ! I should hopefully pick up work on validating that all is well on this PR you've opened in this coming sprint.

[jmongerlyra]

I removed the patch from the existing PR. Needs some optimization. We will submit a separate PR once a new patch is available.

[fivetran-avinash]

No worries @jmongerlyra ! Looking forward for that PR.

I completed a first review of your PR, and we have a review open for your code with some requested changes.

Please go ahead and take a look. We're getting closer to having this ready to fold this into a future release of the package!