Bill payment double-entry creates duplicate GL lines when multiple AP accounts exist

[FDPKM]

Summary

The int_quickbooks__bill_payment_double_entry intermediate model generates duplicate GL entries for every bill payment transaction when the QBO company uses multiple Accounts Payable accounts (e.g., a custom AP account alongside the default "Accounts Payable (A/P)").

Each bill payment produces 4 GL lines instead of 2, doubling both the AP debit and the bank/clearing account credit. This causes massive balance sheet inflation on affected accounts while the P&L remains correct (since bill payments only hit balance sheet accounts).

Impact

• Bank/clearing accounts: balance inflated by the sum of all duplicate credits
• Accounts Payable: phantom debit entries created on the wrong AP account
• Magnitude: Can result in millions of dollars in phantom entries depending on transaction volume. In our case the clearing account was overstated by ~$1.8M.

Reproduction

Environment

• QuickBooks Online company with two AP accounts:
  • Default: Accounts Payable (A/P)
  • Custom: e.g., Accounts Payable - Other
• Bills are created using either AP account via the payable_account_id field on the bill object
• Bill payments processed through a clearing account (e.g., Bill.com Money Out Clearing)

Expected Behavior

A bill payment for $500 paying a bill that uses the custom AP should produce 2 GL lines:

Index	Account	Type	Amount
0	Accounts Payable - Other	Debit	$500
0	Clearing Account	Credit	$500

Actual Behavior

The model produces 4 GL lines:

Index	Account	Type	Amount
0	Accounts Payable (A/P) — wrong AP	Debit	$500
0	Clearing Account	Credit	$500
1	Accounts Payable - Other — correct AP	Debit	$500
1	Clearing Account — duplicate	Credit	$500

• Index 0 always uses the default AP account (regardless of which AP the bill actually uses)
• Index 1 uses the bill's actual payable_account_id
• The bank/clearing credit is duplicated on both indices

This pattern is consistent across 100% of bill payments in our dataset (~1,500 transactions).

Verification Query

-- Every bill payment has exactly 4 lines (should be 2)
SELECT
  lines_per_txn,
  COUNT(*) as num_transactions
FROM (
  SELECT transaction_id, COUNT(*) as lines_per_txn
  FROM int_quickbooks__bill_payment_double_entry
  GROUP BY 1
)
GROUP BY 1;

-- Sample transaction showing the duplication
SELECT transaction_id, index, account_id, transaction_type, amount
FROM int_quickbooks__bill_payment_double_entry
WHERE transaction_id = '<any_bill_payment_id>'
ORDER BY index, transaction_type;

Root Cause

The int_quickbooks__bill_payment_double_entry model creates:

1. A header-level debit/credit pair (index 0) using the bill payment's bank account and the default AP
2. A line-level debit/credit pair (index 1) using the bill payment's bank account and the bill's actual payable_account_id

When the bill's payable_account_id differs from the default AP, both pairs are generated but only one is correct. When they match, both pairs are identical duplicates.

The model should either:

• Only generate the line-level entry (which correctly references the bill's payable_account_id)
• Or generate the header-level entry but resolve the correct AP from the linked bill's payable_account_id

Workaround

We created a corrected GL view that filters bill payment entries to keep only the correct index per transaction by joining through bill_payment_line → bill to determine the correct payable_account_id:

SELECT gl.*
FROM quickbooks__general_ledger gl
JOIN (
  SELECT
    bp.id as bill_payment_id,
    CASE
      WHEN b.payable_account_id = '<default_ap_account_id>' THEN 0
      ELSE 1
    END as correct_index
  FROM bill_payment bp
  JOIN bill_payment_line bpl ON bp.id = bpl.bill_payment_id
  JOIN bill b ON bpl.bill_id = b.id
) correct
  ON gl.transaction_id = correct.bill_payment_id
  AND gl.transaction_index = correct.correct_index
WHERE gl.transaction_source = 'bill payment';

Versions

• fivetran/dbt_quickbooks package (latest as of March 2026)
• Fivetran QuickBooks Online connector
• BigQuery destination

[fivetran-jamie]

Hi there @FDPKM, thanks for opening this.

Currently this is a limitation of the package, as we've decided to enforce Quickbooks' recommended best practice of having one active AP account.

Could you share why your org has multiple Accounts Payable?