search

fivetran / dbt_netsuite

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

[Bug] Currency Conversions not happening in Balance Sheet #110

Closed rishi-tripathy closed 7 months ago

rishi-tripathy commented 7 months ago

Is there an existing issue for this?

Describe the issue

A customer is reporting that the output of the balance sheet transform is not reflecting converted values in the main subsidiary's base currency as described here.

Rishi took a Cleanshot - 2024-03-11 at 12 58 50

I confirmed this by comparing to an export of a report in NetSuite.

I can no longer see the data lineage graph in the FiveTran UI so I'm unable to troubleshoot or trace where the conversion may / may not be happening here

Relevant error log or model output

dbt run --models +netsuite2__transaction_details +netsuite2__income_statement +netsuite2__balance_sheet
03:27:26
Running with dbt=1.7.3
03:27:27
Registered adapter: snowflake=1.7.0
03:27:28
Unable to do partial parsing because saved manifest not found. Starting full parse.
03:27:50
Found 47 models, 46 tests, 19 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
03:27:50
03:27:54
Concurrency: 4 threads (target='prod')
03:27:54
03:27:54
1 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__account_types_tmp [RUN]
03:27:54
2 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_period_fiscal_cal_tmp [RUN]
03:27:54
3 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_periods_tmp [RUN]
03:27:54
4 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounts_tmp [RUN]
03:27:56
3 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_periods_tmp [SUCCESS 1 in 2.10s]
03:27:56
5 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__classes_tmp [RUN]
03:27:56
4 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounts_tmp [SUCCESS 1 in 2.50s]
03:27:56
1 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__account_types_tmp [SUCCESS 1 in 2.61s]
03:27:56
6 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__currencies_tmp [RUN]
03:27:56
7 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__customers_tmp [RUN]
03:27:57
2 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_period_fiscal_cal_tmp [SUCCESS 1 in 2.75s]
03:27:57
8 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__departments_tmp [RUN]
03:27:59
5 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__classes_tmp [SUCCESS 1 in 2.85s]
03:27:59
9 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entities_tmp [RUN]
03:27:59
6 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__currencies_tmp [SUCCESS 1 in 2.80s]
03:27:59
10 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entity_address_tmp [RUN]
03:27:59
8 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__departments_tmp [SUCCESS 1 in 2.69s]
03:27:59
7 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__customers_tmp [SUCCESS 1 in 2.86s]
03:27:59
11 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__items_tmp [RUN]
03:27:59
12 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__location_main_address_tmp [RUN]
03:28:01
9 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entities_tmp [SUCCESS 1 in 2.48s]
03:28:02
13 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__locations_tmp [RUN]
03:28:02
10 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entity_address_tmp [SUCCESS 1 in 2.41s]
03:28:02
14 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__subsidiaries_tmp [RUN]
03:28:02
11 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__items_tmp [SUCCESS 1 in 2.63s]
03:28:02
12 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__location_main_address_tmp [SUCCESS 1 in 2.58s]
03:28:02
15 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_accounting_lines_tmp [RUN]
03:28:02
16 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_lines_tmp [RUN]
03:28:04
13 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__locations_tmp [SUCCESS 1 in 2.90s]
03:28:05
17 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transactions_tmp [RUN]
03:28:05
14 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__subsidiaries_tmp [SUCCESS 1 in 3.24s]
03:28:05
18 of 39 START sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__vendors_tmp [RUN]
03:28:05
15 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_accounting_lines_tmp [SUCCESS 1 in 3.08s]
03:28:05
19 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_periods [RUN]
03:28:06
16 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_lines_tmp [SUCCESS 1 in 3.40s]
03:28:06
20 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounts [RUN]
03:28:07
17 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transactions_tmp [SUCCESS 1 in 2.88s]
03:28:07
21 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__account_types [RUN]
03:28:08
18 of 39 OK created sql view model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__vendors_tmp [SUCCESS 1 in 2.75s]
03:28:08
22 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_period_fiscal_cal [RUN]
03:28:09
19 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_periods [SUCCESS 1 in 4.22s]
03:28:10
23 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__classes [RUN]
03:28:10
20 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounts [SUCCESS 1 in 3.90s]
03:28:10
24 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__currencies [RUN]
03:28:11
21 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__account_types [SUCCESS 1 in 3.19s]
03:28:11
25 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__departments [RUN]
03:28:11
22 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__accounting_period_fiscal_cal [SUCCESS 1 in 3.07s]
03:28:11
26 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__customers [RUN]
03:28:12
23 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__classes [SUCCESS 1 in 2.30s]
03:28:12
27 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entities [RUN]
03:28:12
24 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__currencies [SUCCESS 1 in 2.42s]
03:28:12
28 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entity_address [RUN]
03:28:13
25 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__departments [SUCCESS 1 in 2.35s]
03:28:13
29 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__items [RUN]
03:28:14
26 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__customers [SUCCESS 1 in 2.65s]
03:28:14
30 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__location_main_address [RUN]
03:28:15
27 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entities [SUCCESS 1 in 2.70s]
03:28:15
28 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__entity_address [SUCCESS 1 in 2.49s]
03:28:15
32 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__subsidiaries [RUN]
03:28:15
31 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__locations [RUN]
03:28:16
30 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__location_main_address [SUCCESS 1 in 2.22s]
03:28:16
33 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_accounting_lines [RUN]
03:28:17
29 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__items [SUCCESS 1 in 3.36s]
03:28:17
34 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_lines [RUN]
03:28:17
32 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__subsidiaries [SUCCESS 1 in 2.71s]
03:28:17
31 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__locations [SUCCESS 1 in 2.72s]
03:28:17
35 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transactions [RUN]
03:28:17
36 of 39 START sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__vendors [RUN]
03:28:18
33 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_accounting_lines [SUCCESS 1 in 2.15s]
03:28:19
34 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transaction_lines [SUCCESS 1 in 2.79s]
03:28:20
36 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__vendors [SUCCESS 1 in 3.03s]
03:28:21
35 of 39 OK created sql table model netsuite_suiteanalytics_netsuite_source.stg_netsuite2__transactions [SUCCESS 1 in 3.27s]
03:28:21
37 of 39 START sql table model netsuite_suiteanalytics_netsuite.netsuite2__balance_sheet [RUN]
03:28:21
38 of 39 START sql table model netsuite_suiteanalytics_netsuite.netsuite2__income_statement [RUN]
03:28:21
39 of 39 START sql table model netsuite_suiteanalytics_netsuite.netsuite2__transaction_details [RUN]
03:28:28
38 of 39 OK created sql table model netsuite_suiteanalytics_netsuite.netsuite2__income_statement [SUCCESS 1 in 6.71s]
03:28:30
37 of 39 OK created sql table model netsuite_suiteanalytics_netsuite.netsuite2__balance_sheet [SUCCESS 1 in 9.13s]
03:28:31
39 of 39 OK created sql table model netsuite_suiteanalytics_netsuite.netsuite2__transaction_details [SUCCESS 1 in 10.50s]
03:28:31
03:28:31
Finished running 18 view models, 21 table models in 0 hours 0 minutes and 41.45 seconds (41.45s).
03:28:32
03:28:32
Completed successfully
03:28:32
03:28:32
Done. PASS=39 WARN=0 ERROR=0 SKIP=0 TOTAL=39

Expected behavior

I'd expect the EUR values to be converted to USD in the consolidated report, but the values are reported without a conversion rate applied.

dbt Project configurations

Quickstart

Package versions

Quickstart

What database are you using dbt with?

snowflake

dbt Version

Quickstart

Additional Context

No response

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

rishi-tripathy commented 7 months ago

@fivetran-catfritz could you share where the currency conversions are happening in the package here? I know the introduction of subsidiary information to the quickstart is relatively new given the last issue we paired on!

fivetran-jamie commented 7 months ago

Hey there :wave: So at this moment, this is a known limitation for Quickstart, in which users are unable to make use of the netsuite2__using_exchange_rate variable to convert currencies.

Fivetran Engineering is currently working on a fix to make this available in Quickstart. We'll make sure to update you here when the fix is live!

rishi-tripathy commented 7 months ago

Hey Jamie, do you have an estimated time of remediation or a contact at FiveTran Engineering I could maintain as a POC here? Thanks for the update

fivetran-jamie commented 7 months ago

@rishi-tripathy for that i would recommend going to the Fivetran feature portal and creating a feature request about this issue, and the Fivetran PM team can keep you updated there. Apologies for having you make multiple tickets, but the Feature Portal is the currently the go-to place for Engineering to communicate about updates they're currently working on

i'll go ahead and close this github issue but feel free to re-open if there's anything else on the package side of things!