search

fivetran / dbt_netsuite

Data models for Fivetran's Netsuite connector, built using dbt.
https://fivetran.github.io/dbt_netsuite/
Apache License 2.0
32 stars 34 forks source link

[Bug] Income Statement in Netsuite2 Snowflake from Fivetran dbt package only shows handful of transactions (whereas transaction_details has all) #50

Closed wwilliamsnuwest closed 1 year ago

wwilliamsnuwest commented 1 year ago

Our Fivetran Netsuite2 dbt package-created table netsuite2__income_statement in Snowflake only shows handful of transactions (whereas netsuite2transaction_details has all). For example, if I run: `SELECT * FROM dbt_db.dbt_prod_netsuite.netsuite2income_statement`

I only see these 7 transactions, whereas we should have 10s or 100s of thousands to sum to a much higher dollar amount. It's possible that there's custom dev in Netsuite, but I would assume the core functionality should work for income statement since it seems to work for transaction_details. One other note is that we do everything in USD, so perhaps there's some issue with exchange rates, but again, that's just a wild guess (and anecdotally I dug through some of the exchange rate tables in the dbt-shown lineage and they appeared to have populated exchange rates of 1 for USD.)

image

wwilliamsnuwest commented 1 year ago

Showing the ~500 K row count for transaction_details: image

And the 7 row count for income_statement: image

I haven't looked at balance_sheet, so I can't say anything about whether or not that one's correct), but it has 296 rows, which feels small, but again, I'm not sure what that table is supposed to look like:

image

fivetran-joemarkiewicz commented 1 year ago

HI @wwilliamsnuwest thanks for opening this issue and sorry to see you are experiencing issues.

I agree with you that I would imagine the income statement model would have more rows. However, I am curious of a few things:

Let me know what you find!

fivetran-joemarkiewicz commented 1 year ago

Hi @wwilliamsnuwest I just wanted to check back in if you were able to find anything from your investigation?

I know the other issues you created tend to go back to the duplicate entry from the source data. Do you think the same issue could be arising here? Happy to help dig in more if needed 😄

wwilliamsnuwest commented 1 year ago

I haven't, and haven't been looking recently since I've been busy with other stuff. Thanks for asking though, so far as I know it's still an outstanding problem, but I haven't checked in the past 4 weeks or so to see if anything's changed on the dbt side.

Get Outlook for Androidhttps://aka.ms/AAb9ysg

From: Joe Markiewicz @.> Sent: Monday, October 31, 2022 4:56:07 AM To: fivetran/dbt_netsuite @.> Cc: Ward Williams @.>; Mention @.> Subject: Re: [fivetran/dbt_netsuite] [Bug] Income Statement in Netsuite2 Snowflake from Fivetran dbt package only shows handful of transactions (whereas transaction_details has all) (Issue #50)

Hi @wwilliamsnuwesthttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fwwilliamsnuwest&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C5e1fda68bb7f43bc6e0f08dabb500afc%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638028249702023287%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=TdBDnoahG9098E17u8NQQd8RA9j191zv34yz0plftUo%3D&reserved=0 I just wanted to check back in if you were able to find anything from your investigation?

I know the other issues you created tend to go back to the duplicate entry from the source data. Do you think the same issue could be arising here? Happy to help dig in more if needed 😄

— Reply to this email directly, view it on GitHubhttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffivetran%2Fdbt_netsuite%2Fissues%2F50%23issuecomment-1297212758&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C5e1fda68bb7f43bc6e0f08dabb500afc%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638028249702179537%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=vdT%2BjXU2JjDW0tWL7i1QtRBc2JBr8pTO0tz%2BfuxZ1Hk%3D&reserved=0, or unsubscribehttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAYA77UY2QEF54BVSUAC274TWF7MYPANCNFSM6AAAAAAQ65BZ44&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C5e1fda68bb7f43bc6e0f08dabb500afc%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638028249702179537%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=AJ0X5Q8AEqVsy5JiCJN6Io2qoCSE%2BziEb2O97yATc5I%3D&reserved=0. You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

Hey @wwilliamsnuwest I think I may have found the culprit to this issue! I am testing on our own data and noticed an odd discrepancy when comparing Netsuite to Netsuite2. It seemed that our Netsuite2 income statement was only outputting non_posting transactions in the final model. 🤔

It turns out we have a problematic filter in the int_netsuite2__tran_lines_w_accounting_period model that is filtering on the wrong records. You can see here that we are filtering out posting accounts. Whereas we should be filtering to only include posting accounts as opposed to the inverse.

I found it interesting that this was not an issue within Netsuite and went to look at the mirror model to see how we were performing this filter. It seems Netsuite just named the original field very confusingly non_posting_account and we filtered out the yes records. As yes for non_posting_account means that it is not a posting account, and no means it is. 🤕 You can see the Netsuite filter here.

As such, I believe if we change the Netsuite2 model to perform the following adjustment to the filter, then we should address this problem.

-- models/netsuite2/intermediate/int_netsuite2__tran_lines_w_accounting_period

  where lower(transactions.transaction_type) != 'revenue arrangement'
    and transaction_lines.is_posting -- previously "and not transaction_lines.is_posting"
wwilliamsnuwest commented 1 year ago

Sounds like great news Joe! Please let me know whenever Fivetran releases this change as an update to the dbt model & I will run 'dbt deps' to get the latest in my dev code (my prod code will automatically bring it in.)

Thanks!!! Ward

From: Joe Markiewicz @.> Sent: Tuesday, November 29, 2022 9:04 AM To: fivetran/dbt_netsuite @.> Cc: Ward Williams @.>; Mention @.> Subject: Re: [fivetran/dbt_netsuite] [Bug] Income Statement in Netsuite2 Snowflake from Fivetran dbt package only shows handful of transactions (whereas transaction_details has all) (Issue #50)

Hey @wwilliamsnuwesthttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fwwilliamsnuwest&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C30289c8afe60491095b608dad22bc84d%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638053382743138416%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=gtoWHQd59VWtNTyknaIo%2BnExwgmYKyw9q8ji%2B%2Be5O2w%3D&reserved=0 I think I may have found the culprit to this issue! I am testing on our own data and an odd discrepancy when comparing Netsuite to Netsuite2. It seemed that our Netsuite2 income statement was only outputting non_posting transactions in the final model. 🤔

It turns out we have a problematic filter in the int_netsuite2tran_lines_w_accounting_period model that is filtering on the wrong records. You can see here<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffivetran%2Fdbt_netsuite%2Fblob%2Fc1fe30ec1b1164679e74bfbf03ec605e00ebc400%2Fmodels%2Fnetsuite2%2Fintermediate%2Fint_netsuite2tran_lines_w_accounting_period.sql%23L26&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C30289c8afe60491095b608dad22bc84d%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638053382743138416%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=Ie6QFtpTZ3%2FGFwAELiYOVmNr%2F%2BQPcBDdqQpl9xAuX%2Bg%3D&reserved=0> that we are filtering out posting accounts. Whereas we should be filtering to only include posting accounts as opposed to the inverse.

I found it interesting that this was not an issue within Netsuite and went to look at the mirror model to see how we were performing this filter. It seems Netsuite just named the original field very confusingly non_posting_account and we filtered out the yes records. As yes for non_posting_account means that it is not a posting account, and no means it is. 🤕 You can see the Netsuite filter herehttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffivetran%2Fdbt_netsuite%2Fblob%2Fc1fe30ec1b1164679e74bfbf03ec605e00ebc400%2Fmodels%2Fnetsuite%2Fintermediate%2Fint_netsuite__transaction_lines_w_accounting_period.sql%23L26&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C30289c8afe60491095b608dad22bc84d%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638053382743294658%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=GPr9TgnweD5VmaLLllFksFl76rjF1XYGi6L%2FUe7Fq4A%3D&reserved=0.

As such, I believe if we change the Netsuite2 model to perform the following adjustment to the filter, then we should address this problem.

-- models/netsuite2/intermediate/int_netsuite2__tran_lines_w_accounting_period

where lower(transactions.transaction_type) != 'revenue arrangement'

and transaction_lines.is_posting -- previously "and not transaction_lines.is_posting"

— Reply to this email directly, view it on GitHubhttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffivetran%2Fdbt_netsuite%2Fissues%2F50%23issuecomment-1330977633&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C30289c8afe60491095b608dad22bc84d%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638053382743294658%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=a6peoIgFrORZ5sXDP9ouYD9%2FZFBV4uBQs5kn5mtI7gg%3D&reserved=0, or unsubscribehttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAYA77U4HAHPCZFRLPPEQYFDWKYZR5ANCNFSM6AAAAAAQ65BZ44&data=05%7C01%7Cwwilliams%40nuwestgroup.com%7C30289c8afe60491095b608dad22bc84d%7C5f334365592d48068c5dc237622d6bfe%7C0%7C0%7C638053382743294658%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=sAznAK8XGgmvkeWI5%2BkOqL13doPgSWM%2B1esFMV2nStU%3D&reserved=0. You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

Hi @wwilliamsnuwest I apologize for the late response. On 12/5 we were able to push the update to the v0.6.3 release of the package.

As such, this issue should be resolved and I will close out the issue. Please feel free to re-open the issue if you see the issue persist.