fivetran / fivetran/dbt_quickbooks
[Bug] "DescriptionOnly" detail_type causes incorrect profit_and_loss
- Dominant language
- No language data
- Stars
- 41
- Forks
- 48
- PR merge metrics
- No merged PRs in 30d
Description
### Is there an existing issue for this?
- [X] I have searched the existing issues
### Describe the issue
'DescriptionOnly' DETAIL_TYPE on STG_QUICKBOOKS_INVOICE_LINE causes P&L to report incorrect values.
### Relevant error log or model output
```shell
No errors were thrown during dbt build.
```
### Expected behavior
The profit and loss should match quickbooks reporting.
### dbt Project configurations
```yml
config-version: 2
name: 'quickbooks'
version: '0.12.1'
require-dbt-version: [">=1.3.0", "<2.0.0"]
models:
quickbooks:
+materialized: table
+schema: quickbooks
double_entry_transactions:
+schema: quickbooks_intermediate
+materialized: table
transaction_lines:
+materialized: ephemeral
intermediate:
+materialized: ephemeral
vars:
quickbooks:
account: "{{ ref('stg_quickbooks__account') }}"
address: "{{ ref('stg_quickbooks__address') }}"
bill_line: "{{ ref('stg_quickbooks__bill_line') }}"
bill_linked_txn: "{{ ref('stg_quickbooks__bill_linked_txn') }}"
bill_payment_line: "{{ ref('stg_quickbooks__bill_payment_line') }}"
bill_payment: "{{ ref('stg_quickbooks__bill_payment') }}"
bill: "{{ ref('stg_quickbooks__bill') }}"
bundle_item: "{{ ref('stg_quickbooks__bundle_item') }}"
bundle: "{{ ref('stg_quickbooks__bundle') }}"
credit_memo_line: "{{ ref('stg_quickbooks__credit_memo_line') }}"
credit_memo: "{{ ref('stg_quickbooks__credit_memo') }}"
credit_card_payment_txn: "{{ ref('stg_quickbooks__credit_card_payment_txn') }}"
customer: "{{ ref('stg_quickbooks__customer') }}"
department: "{{ ref('stg_quickbooks__department') }}"
deposit_line: "{{ ref('stg_quickbooks__deposit_line') }}"
deposit: "{{ ref('stg_quickbooks__deposit') }}"
estimate: "{{ ref('stg_quickbooks__estimate') }}"
estimate_line: "{{ ref('stg_quickbooks__estimate_line') }}"
invoice_line: "{{ ref('stg_quickbooks__invoice_line') }}"
invoice_line_bundle: "{{ ref('stg_quickbooks__invoice_line_bundle') }}"
invoice_linked_txn: "{{ ref('stg_quickbooks__invoice_linked_txn') }}"
invoice: "{{ ref('stg_quickbooks__invoice') }}"
item: "{{ ref('stg_quickbooks__item') }}"
journal_entry_line: "{{ ref('stg_quickbooks__journal_entry_line') }}"
journal_entry: "{{ ref('stg_quickbooks__journal_entry') }}"
payment_line: "{{ ref('stg_quickbooks__payment_line') }}"
payment: "{{ ref('stg_quickbooks__payment') }}"
purchase_line: "{{ ref('stg_quickbooks__purchase_line') }}"
purchase: "{{ ref('stg_quickbooks__purchase') }}"
refund_receipt_line: "{{ ref('stg_quickbooks__refund_receipt_line') }}"
refund_receipt: "{{ ref('stg_quickbooks__refund_receipt') }}"
sales_receipt_line: "{{ ref('stg_quickbooks__sales_receipt_line') }}"
sales_receipt: "{{ ref('stg_quickbooks__sales_receipt') }}"
transfer: "{{ ref('stg_quickbooks__transfer') }}"
vendor_credit_line: "{{ ref('stg_quickbooks__vendor_credit_line') }}"
vendor_credit: "{{ ref('stg_quickbooks__vendor_credit') }}"
vendor: "{{ ref('stg_quickbooks__vendor') }}"
financial_statement_ordinal: []
cash_flow_statement_type_ordinal: []
using_address: true
using_bill: true
using_credit_memo: true
using_department: true
using_deposit: true
using_estimate: true
using_invoice: true
using_invoice_bundle: false
using_journal_entry: true
using_payment: true
using_refund_receipt: true
using_transfer: true
using_vendor_credit: true
using_sales_receipt: false
using_credit_card_payment_txn: false
analysis-paths: ["analysis"]
clean-targets:
- target
- dbt_modules
- dbt_packages
```
### Package versions
Forked copy of most recent version
### What database are you using dbt with?
snowflake
### dbt Version
1.7.4
### Additional Context
invoice_filter as (
select *
from invoice_join
where invoice_line_transaction_type not in ('SubTotalLineDetail','NoAccountMapping', 'DescriptionOnly')
)
Updating int_quickbooks__invoice_double_entry.sql to exclude descriptiononly lines seems to solve the issue.
### 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](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-office-hours) for guidance
- [ ] No.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in int_quickbooks__invoice_double_entry.sql and inspect the invoice_filter CTE, especially its invoice_line_transaction_type exclusions. Run dbt build with the reported configuration and compare the resulting profit and loss against QuickBooks; done means DescriptionOnly invoice lines no longer produce incorrect values.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- data, databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100