BUG - transaction_date missing

clairejohnson018 commented 2 years ago

Are you a current Fivetran customer? Yes -- I am a Data Analyst at Chatbooks

Describe the bug We are having an issue with our Netsuite dbt models. We noticed that a good amount of transactions were missing in our reports for September (reports that use transaction_details and income_statement).

The transaction_ids for the missing transactions exist in both raw tables transactions and transaction_lines. But they do not make it into the transformed models transaction_details or income_statement. I have traced it back to the transactions not having a transaction_date on them in the transformed models.

Looking at the SQL file for the transformed model transaction_details, I can see that it calls the field transactions.transaction_date from the raw transactions table. However, in my version of the raw transactions table, the field "transaction_date" does not exist. I do have a field called "trandate" which I am wondering if that should be used in the transaction_details model. But it is possible that there's something not up to date with my raw transactions table to get me the transaction_date field like it should.

Steps to reproduce

Expected behavior These transaction rows should show up in the transformed model.

Project variables configuration

name: 'netsuite'
version: '0.4.0'
profile: 'integration'
require-dbt-version: [">=0.20.0"]
models:
  netsuite:
    materialized: table
    +schema: netsuite
    intermediate:
      materialized: ephemeral
vars:
  netsuite:
    accounting_books: "{{ ref('stg_netsuite__accounting_books') }}"
    accounting_periods: "{{ ref('stg_netsuite__accounting_periods') }}"
    accounts: "{{ ref('stg_netsuite__accounts') }}"
    classes: "{{ ref('stg_netsuite__classes') }}"
    consolidated_exchange_rates: "{{ ref('stg_netsuite__consolidated_exchange_rates') }}"
    currencies: "{{ ref('stg_netsuite__currencies') }}"
    customers: "{{ ref('stg_netsuite__customers') }}"
    departments: "{{ ref('stg_netsuite__departments') }}"
    expense_accounts: "{{ ref('stg_netsuite__expense_accounts') }}"
    income_accounts: "{{ ref('stg_netsuite__income_accounts') }}"
    items: "{{ ref('stg_netsuite__items') }}"
    locations: "{{ ref('stg_netsuite__locations') }}"
    subsidiaries: "{{ ref('stg_netsuite__subsidiaries') }}"
    transaction_lines: "{{ ref('stg_netsuite__transaction_lines') }}"
    transactions: "{{ ref('stg_netsuite__transactions') }}"
    vendor_types: "{{ ref('stg_netsuite__vendor_types') }}"
    vendors: "{{ ref('stg_netsuite__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: []
clean-targets:
- target
- dbt_modules

Package Version

There are two folders, netsuite, and netsuite_source

Netsuite folder:

  - package: fivetran/fivetran_utils
    version: [">=0.2.0", "<0.3.0"]

Netsuite_source folder:

  - package: fivetran/netsuite_source
    version: [">=0.3.0", "<0.4.0"]

Warehouse

- [ ] BigQuery - [ ] Redshift - [x] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency We have had our Netsuite connection up and running for a few months now, but did not have this problem before. It is affecting a few critical dashboards so we would like to get it fixed as soon as possible.

Are you interested in contributing to this package?

[ ] Yes, I can do this and open a PR for your review.
[x] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
[ ] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

fivetran-joemarkiewicz commented 2 years ago

Thanks for opening this Issue @clairejohnson018!

I am sorry to hear that some of these transactions are not flowing through to the final models. I was able to read through your description (thank you for providing all the details 😄 ) and have a few questions and comments:

the transactions.transaction_date is actually being pulled from our staging model and not the raw data. For example the stg_netsuite__transactions model renames the trandate field to be the more accurately named transaction_date which is then used in the downstream models https://github.com/fivetran/dbt_netsuite_source/blob/6ee18aabd60b7366a436ffab54e1aea81cf32739/models/stg_netsuite__transactions.sql#L41
Would you be able to confirm that the trandate within the raw data for these records is in fact not being populated? If they are not, then there may be a permission issue or some other blocker on the Netsuite side that is not allowing these transaction dates to persist in the data Fivetran is syncing.

If you confirm the raw data does have the accurate trandate then our next step would be to look closed in the package to determine why they are not being passed through to the downstream models.

If they do not have the trandate then I would reach out to your Fivetran customer support engineer to help isolate why these transactions do not have dates associated with them.

Let me know if you would like me to clarify any of my comments above. Thanks!!

clairejohnson018 commented 2 years ago

Thanks so much @fivetran-joemarkiewicz! This makes sense. I don't have much experience with dbt staging models so I didn't catch that but I can see now where it is being referenced in the transformed model.

The raw data (in the transactions table) does indeed have the trandate populated for the transactions in question. So somehow these rows are not making it into the transformed models. Let me know if there's any more info I can send to help us diagnose where the issue is in the package.

JoeMark17 commented 2 years ago

Thanks for looking into that! One other catch that I have come across, are you able to see if those transactions have an associated 'accounting_period' in the transaction staging model?

I have seen a few transactions for some reason not populate this field and therefore are not carried through to the final models.

clairejohnson018 commented 2 years ago

When selecting from the staging model itself (stg_netsuite__transactions), there are no rows for the transactionids I am looking for.

They do show up in transactions and do have an associated account_period_id.

fivetran-joemarkiewicz commented 2 years ago

It looks like the only filter we apply within stg_nestuite__transactions is to exclude deleted fields https://github.com/fivetran/dbt_netsuite_source/blob/6ee18aabd60b7366a436ffab54e1aea81cf32739/models/stg_netsuite__transactions.sql#L62 Do you know if the transactions you are looking for are deleted by chance?

Since they do have account_period_id for the transactions I am curious why they may not be included? I think the next best step would be for us to jump on a quick call and debug together. Feel free to set some time up during out office hours and we can chat through this live!

clairejohnson018 commented 2 years ago

The date_deleted is null for these transactions in the transactions table. I've asked our accounting team to double check within Netsuite to confirm they are not deleted.

Thanks for all your help! I've scheduled some time on Thursday to look at this together.

fivetran-joemarkiewicz commented 2 years ago

Hi @clairejohnson018 I just wanted to check back in if the proposed resolution of ensuring the models are being run on the daily jobs did the trick?

clairejohnson018 commented 2 years ago

Hi! Sorry I didn’t let you know last week, I left on vacation on Thursday. But we got it figured out! We had just misnamed the folder name in our python script that runs our dbt jobs. We were calling “netsuite_suite” when it should have been “netsuite_source” (or vice versa). So a really easy fix! Thanks again for all your help! Knowing that the staging script hadn’t run clued me in where to look (which I did look at that python script on our call together but didn’t notice the incorrect name on the spot).

Claire

On Wed, Oct 6, 2021 at 17:22 Joe Markiewicz @.***> wrote:

Hi @clairejohnson018 https://github.com/clairejohnson018 I just wanted to check back in if the proposed resolution of ensuring the models are being run on the daily jobs did the trick?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_netsuite_source/issues/8#issuecomment-936488359, or unsubscribe https://github.com/notifications/unsubscribe-auth/AM4S43ZT5DFKAHHQY3E4BX3UFRSTNANCNFSM5E264ZLA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

fivetran-joemarkiewicz commented 2 years ago

Fantastic! Thanks for letting me know. I will close the Issue.

fivetran / dbt_netsuite_source

BUG - transaction_date missing #8