fivetran / fivetran/dbt_quickbooks

[Bug] "DescriptionOnly" detail_type causes incorrect profit_and_loss

Open
#113 6 comments 0 reactions 0 assignees View on GitHub
error:forced type:enhancement
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.