[Bug] Bundled invoices get duplicated in int_quickbooks__invoice_double_entry and inflate the P&L #224

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We invoice a lot of our customers with bundles in QuickBooks Online. In the raw Fivetran tables everything looks right: invoice_line has the lines, invoice_line_bundle has the bundle components, and the totals match the invoice.

Once the Quickstart models run, though, the bundled invoices get duplicated in int_quickbooks__invoice_double_entry, and those dupes flow straight into quickbooks__profit_and_loss. Our sales line on the dbt P&L came out higher than the actual QuickBooks P&L in most months, and it took us a while to work out why, since nothing errors and the numbers look plausible.

We tracked it down to the invoice_bundles join that came in with #175. It matches bundle rows to invoice lines by invoice only:

left join invoice_bundles
    on invoice_bundles.invoice_id = invoice_lines.invoice_id
    and invoice_bundles.source_relation = invoice_lines.source_relation

(lines 271-273 at v1.10.0, with the amount taken from coalesce(invoice_bundles.amount, invoice_lines.amount) at line 213). So on an invoice with a bundle, each bundle component gets repeated once for every line on the invoice, and the regular lines on that same invoice lose their own amounts.

Once we stopped using the model and built our invoice entries straight from the raw rows, the numbers matched QuickBooks again.

Relevant error log or model output

No errors. The build succeeds; the invoice amounts are just duplicated.

Expected behavior

The credits for each invoice in the double-entry model should add up to the invoice total, and the P&L should match the QuickBooks P&L report.

An easy way to see it: sum the model's credit rows per invoice and compare with invoice.total_amount. Invoices without bundles tie; invoices with a bundle plus other lines don't.

Possible solution

stg_quickbooks__invoice_line_bundle already has invoice_line_index, so matching at the line level should fix it:

left join invoice_bundles
    on invoice_bundles.invoice_id = invoice_lines.invoice_id
    and invoice_bundles.invoice_line_index = invoice_lines.index
    and invoice_bundles.source_relation = invoice_lines.source_relation

What we do on our side instead is skip the join: take the regular sales lines, add the bundle component rows on their own, and debit A/R for the invoice total. That ties to every invoice total we have.

dbt Project configurations

Default Quickstart configuration.

Package versions

Whatever Quickstart runs today (v1.9.x). The pending October update to v1.10.0 doesn't change this model.

What database are you using dbt with?

bigquery

How are you running this dbt package?

Fivetran Quickstart Data Model

dbt Version

n/a (Quickstart)

Additional Context

Happy to review a fix or help with the PR if that's useful.

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

• Yes.
• Yes, but I will need assistance.
• No.