Skip to content

Quickbooks

Where the Data Lives

All QuickBooks data is replicated into Snowflake by Airbyte's QuickBooks source connector and lives in:

RAW_DB.QUICKBOOKS

Use this database and schema for every query — there is no need to reference any other database.

Airbyte Conventions

Every table in RAW_DB.QUICKBOOKS follows the same Airbyte conventions:

Metadata columns (present on every table): - _AIRBYTE_RAW_ID — unique ID for the raw sync row - _AIRBYTE_EXTRACTED_AT — when Airbyte pulled the row (sync time, not transaction time) - _AIRBYTE_META — sync metadata (VARIANT) - _AIRBYTE_GENERATION_ID — sync generation - AIRBYTE_CURSOR — cursor value used for incremental sync

QuickBooks-native columns (present on most tables): - ID — the QuickBooks entity ID (primary key) - SYNCTOKEN — QuickBooks optimistic-concurrency token - METADATA — OBJECT containing CreateTime and LastUpdatedTime - DOMAIN / SPARSE — QuickBooks API flags; usually safe to ignore - ACTIVE — boolean for soft-deletion on reference tables (filter ACTIVE = TRUE to exclude deleted records)

Reference columns end in REF and are OBJECTs, not scalars. They contain value (the foreign key) and name (the display name). For example, CUSTOMERREF:value::TEXT joins to CUSTOMERS.ID. Other examples: VENDORREF, ACCOUNTREF, DEPARTMENTREF, CLASSREF, ITEMREF, CURRENCYREF, PAYMENTMETHODREF, SALESTERMREF, APACCOUNTREF, ARACCOUNTREF, DEPOSITTOACCOUNTREF, TAXRATEREF, AGENCYREF, PARENTREF.

Transaction line items live in the LINE ARRAY column on transaction tables (INVOICES, BILLS, PURCHASES, etc.). Use LATERAL FLATTEN(input => LINE) to expand them into rows. Each line element is an OBJECT with Amount, DetailType, and a nested detail object (e.g., SalesItemLineDetail, AccountBasedExpenseLineDetail) that contains the item or account reference.

Address and contact columns are also OBJECTs — BILLADDR, SHIPADDR, PRIMARYADDR, PRIMARYEMAILADDR, PRIMARYPHONE, MOBILE, FAX, WEBADDR. Drill in with :Line1, :Address, :FreeFormNumber, etc.

Dates and timestamps: Use TXNDATE (DATE) for financial periods on transaction tables. Use _AIRBYTE_EXTRACTED_AT only when you want to know when the row was synced, not when the transaction happened. METADATA:CreateTime and METADATA:LastUpdatedTime reflect when the record was created or last edited in QuickBooks.

Multi-currency: Transaction tables include CURRENCYREF, EXCHANGERATE, and often HOMETOTALAMT. Use HOMETOTALAMT when summing across currencies; use TOTALAMT for native-currency amounts.

Tables

Master / reference data

Table Rows What it is
ACCOUNTS 252 Chart of accounts. Key columns: NAME, FULLYQUALIFIEDNAME, ACCOUNTTYPE, ACCOUNTSUBTYPE, CLASSIFICATION (Asset/Liability/Equity/Revenue/Expense), CURRENTBALANCE, CURRENTBALANCEWITHSUBACCOUNTS, ACCTNUM, SUBACCOUNT, PARENTREF.
CUSTOMERS 14 Customers. Key columns: DISPLAYNAME, COMPANYNAME, GIVENNAME/FAMILYNAME, BALANCE, BALANCEWITHJOBS, TAXABLE, JOB (true if this row is a sub-job of a parent customer), PARENTREF, address/email/phone OBJECTs.
VENDORS 495 Vendors. Key columns: DISPLAYNAME, COMPANYNAME, BALANCE, VENDOR1099, TAXIDENTIFIER, ACCTNUM, TERMREF, address/email/phone OBJECTs.
EMPLOYEES 1 Employees. Key columns: DISPLAYNAME, GIVENNAME/FAMILYNAME, ACTIVE, HIREDDATE, RELEASEDDATE, EMPLOYEENUMBER, BILLRATE, BILLABLETIME, address/email/phone OBJECTs.
ITEMS 66 Products and services. Key columns: NAME, FULLYQUALIFIEDNAME, TYPE (Inventory/NonInventory/Service), UNITPRICE, PURCHASECOST, QTYONHAND, TRACKQTYONHAND, TAXABLE, INCOMEACCOUNTREF, EXPENSEACCOUNTREF, ASSETACCOUNTREF.
CLASSES 7 Class tracking (for class-based segmentation). NAME, FULLYQUALIFIEDNAME, PARENTREF, SUBCLASS.
DEPARTMENTS 3 Departments / locations. NAME, FULLYQUALIFIEDNAME, PARENTREF, SUBDEPARTMENT.
PAYMENT_METHODS 6 Payment methods (Cash, Check, Credit Card, etc.). NAME, TYPE.
TERMS 5 Payment terms. NAME, TYPE, DUEDAYS, DISCOUNTDAYS, DISCOUNTPERCENT, DAYOFMONTHDUE.
TAX_AGENCIES 3 Tax agencies. DISPLAYNAME, TAXTRACKEDONSALES, TAXTRACKEDONPURCHASES, TAXREGISTRATIONNUMBER.
TAX_CODES 5 Tax codes. NAME, DESCRIPTION, TAXABLE, TAXGROUP, SALESTAXRATELIST, PURCHASETAXRATELIST.
TAX_RATES 7 Tax rates. NAME, RATEVALUE, AGENCYREF, EFFECTIVETAXRATE, DISPLAYTYPE, SPECIALTAXTYPE.
BUDGETS 0 Budget headers with BUDGETDETAIL array. Empty in this workspace.

Sales (money in)

Table Rows What it is
INVOICES 2 Customer invoices. Key columns: TXNDATE, DUEDATE, TOTALAMT, HOMETOTALAMT, BALANCE (>0 = unpaid), CUSTOMERREF, DOCNUMBER, LINE (array of line items), LINKEDTXN (links to payments/credit memos).
SALES_RECEIPTS 459 Point-of-sale sales (paid at the time of sale). Same shape as invoices but no DUEDATE/BALANCE; adds PAYMENTMETHODREF, DEPOSITTOACCOUNTREF, PAYMENTREFNUM.
ESTIMATES 0 Quotes / proposals. TXNSTATUS (Pending/Accepted/Closed/Rejected), TOTALAMT, CUSTOMERREF. Empty in this workspace.
PAYMENTS 1 Customer payments received against invoices. TOTALAMT, UNAPPLIEDAMT, CUSTOMERREF, ARACCOUNTREF, DEPOSITTOACCOUNTREF, PAYMENTMETHODREF, LINKEDTXN (the invoices being paid).
CREDIT_MEMOS 0 Credits issued to customers. TOTALAMT, REMAININGCREDIT, CUSTOMERREF. Empty in this workspace.
REFUND_RECEIPTS 2 Refunds paid out to customers. TOTALAMT, CUSTOMERREF, PAYMENTMETHODREF, DEPOSITTOACCOUNTREF.

Purchases (money out)

Table Rows What it is
BILLS 520 Vendor bills (A/P). TXNDATE, DUEDATE, TOTALAMT, BALANCE (>0 = unpaid), VENDORREF, APACCOUNTREF, DEPARTMENTREF, SALESTERMREF, LINE, LINKEDTXN.
BILL_PAYMENTS 717 Payments made against vendor bills. TXNDATE, TOTALAMT, PAYTYPE (Check/CreditCard), VENDORREF, APACCOUNTREF, CHECKPAYMENT, CREDITCARDPAYMENT, LINE (linked bills).
PURCHASES 5,219 Expenses paid directly (checks, credit-card charges, cash). TXNDATE, TOTALAMT, PAYMENTTYPE (Check/CreditCard/Cash), CREDIT (boolean — true = credit card credit/return), ACCOUNTREF (the bank/CC account funding it), ENTITYREF (vendor/customer/employee paid), LINE.
PURCHASE_ORDERS 23 POs to vendors. POSTATUS (Open/Closed), TOTALAMT, VENDORREF, APACCOUNTREF, LINE, LINKEDTXN (bills created from the PO).
VENDOR_CREDITS 9 Credits received from vendors. TOTALAMT, VENDORREF, APACCOUNTREF, LINE.

Banking & ledger

Table Rows What it is
DEPOSITS 108 Bank deposits — typically bundles of customer payments grouped for deposit. TXNDATE, TOTALAMT, DEPOSITTOACCOUNTREF, CASHBACK, LINE.
TRANSFERS 97 Transfers between bank accounts. AMOUNT, FROMACCOUNTREF, TOACCOUNTREF, TXNDATE.
JOURNAL_ENTRIES 9,675 Manual GL journal entries. TXNDATE, DOCNUMBER, ADJUSTMENT (true = adjusting entry), LINE (the debit/credit lines, each with Amount, PostingType, AccountRef, Entity (Vendor/Customer/Employee), DepartmentRef, ClassRef).

Time tracking

Table Rows What it is
TIME_ACTIVITIES 0 Time entries. TXNDATE, HOURS, MINUTES, HOURLYRATE, BILLABLESTATUS, NAMEOF (Employee or Vendor), EMPLOYEEREF, CUSTOMERREF, ITEMREF. Empty in this workspace.

Common Joins

  • Transaction → customer: JOIN CUSTOMERS c ON c.ID = t.CUSTOMERREF:value::TEXT
  • Transaction → vendor: JOIN VENDORS v ON v.ID = t.VENDORREF:value::TEXT
  • Transaction → account: JOIN ACCOUNTS a ON a.ID = t.ACCOUNTREF:value::TEXT (works for APACCOUNTREF, ARACCOUNTREF, DEPOSITTOACCOUNTREF, FROMACCOUNTREF, TOACCOUNTREF too)
  • Transaction → department / class: same pattern with DEPARTMENTREF / CLASSREF.
  • Line items: FROM <table>, LATERAL FLATTEN(input => LINE) l then access l.value:Amount, l.value:DetailType, l.value:SalesItemLineDetail.ItemRef.value, etc.
  • Customer hierarchy / vendor parent: PARENTREF:value::TEXT joins back to the same table's ID.
  • Invoice → its payments: LINKEDTXN is an ARRAY of {TxnId, TxnType} objects — flatten and filter on TxnType = 'Payment'.

Query Conventions

  • **Never provide employee level information, especially on salaries and wages.
  • Always fully qualify tables: RAW_DB.QUICKBOOKS.<TABLE>.
  • Filter soft-deleted records on reference tables with WHERE ACTIVE = TRUE when appropriate.
  • Cast OBJECT-field extractions explicitly: CUSTOMERREF:value::TEXT, LINE[0]:Amount::FLOAT. Snowflake returns VARIANT otherwise and joins behave unexpectedly.
  • For period-based aggregations use TXNDATE, not _AIRBYTE_EXTRACTED_AT.
  • For multi-currency aggregations prefer HOMETOTALAMT over TOTALAMT.
  • Use FULLYQUALIFIEDNAME over NAME for ACCOUNTS, CLASSES, DEPARTMENTS, ITEMS, and CUSTOMERS when the hierarchy matters (it shows Parent:Child:Grandchild).