fivetran-joemarkiewicz
commented
1 year ago Thanks for opening this issue @wwilliamsnuwest and for your detailed investigation into the issue.
An interesting piece of this puzzle is that the transaction_date (which seems to incorrectly represent the transaction and its reversal) is not modified within our dbt package. You can see it is selected from the staging table (which is a direct query from the source transactions table synced by Fivetran). https://github.com/fivetran/dbt_netsuite/blob/c1fe30ec1b1164679e74bfbf03ec605e00ebc400/models/netsuite2/netsuite2__transaction_details.sql#L87
I would be curious to understand why the data being synced is not accurately reflecting the date of the reversal. If I am understanding your bug report correctly, if we address the one of the two records to take place on 12/31 then it should resolve the issue (please correct me if I am wrong).
If the above is the case, I think this may be a Fivetran Support related inquiry and should be investigated at the connector level. Happy to chat more if you feel the transformation is causing this instead.
wwilliamsnuwest
fivetran-reneeli
Is there an existing issue for this?
Describe the issue
We have a transaction reversal that is causing a weird issue. When I look in Snowflake's dbt Netsuite2 netsuite2__transaction_details, there's -$8.2 M Income charge from two transactions both on 1/1/2022 with Dec 2021 totals of $47 M and Jan 2022 of $58 M.
` SELECT CASE account_type_name WHEN 'Income' THEN '1 - Income' WHEN 'Cost of Goods Sold' THEN '2 - COGS' WHEN 'Expense' THEN '3 - Expense' ELSE account_type_name END AS account_type_name_sorted,
:datebucket(DATE(transaction_date)) AS transaction_date_bucket, -- Grouping by Month in Snowflake filter not shown
SUM(transaction_amount) AS transaction_amount
FROM dbt_db.dbt_prod_netsuite.netsuite2__transaction_details WHERE account_type_name IN ( 'Income')
AND DATE(transaction_date) BETWEEN '2021-12-01' AND '2022-01-31'
GROUP BY 1,2 ORDER BY 1,2 ` Query above to get these monthly totals in Snowflake:
Query to isolate the $4.1 M charges: `SELECT CASE account_type_name WHEN 'Income' THEN '1 - Income' WHEN 'Cost of Goods Sold' THEN '2 - COGS' WHEN 'Expense' THEN '3 - Expense' ELSE account_type_name END AS account_type_name_sorted, account_number, parent_account_name, transaction_date, transaction_amount, transaction_line_id
FROM dbt_db.dbt_prod_netsuite.netsuite2__transaction_details WHERE account_type_name IN ('Expense', 'Income', 'Other Expense', 'Other Income', 'Cost of Goods Sold')
AND DATE(transaction_date) = '2022-01-01' AND account_number = 45100 `
However, in Netsuite.com on the Income Statement page, that transaction appears to be split between 12/31/2022 and 1/1/2022, resulting in monthly Dec 2021 totals of $47 M and Jan 2022 of $58 M:
Here's the transaction records from Netsuite showing a +$4.1 M on 12/31/2022 and a -$4.1 M on 1/1/2022:
Relevant error log or model output
No response
Expected behavior
I'd expect the monthly totals to match in Netsuite.com vs Snowflake, but instead they don't.
dbt Project configurations
Nothing important to add here.
Package versions
packages:
What database are you using dbt with?
snowflake
dbt Version
Not sure if this is relevant since I'm using dbt cloud.
Additional Context
No response
Are you willing to open a PR to help address this issue?