RAW.POLYTOMIC_QUICKBOOKS — Schema Profile

Source: QuickBooks Online via Polytomic
Profiled: 2026-03-02

Tables

Table	Rows	Last Altered	Date Range
ACCOUNTS	153	2026-03-02	—
BILLS	252	2026-03-02	2023-10-16 → 2026-03-06
CUSTOMERS	46	2026-03-02	—
CUSTOMER_TYPES	0	2026-03-02	—
INVOICES	199	2026-03-02	2023-08-01 → 2026-04-17
ITEMS	14	2026-03-02	—
JOURNAL_ENTRIES	380	2026-03-02	—
PAYMENTS	186	2026-03-02	2023-08-03 → 2026-02-20
VENDORS	461	2026-03-02	—

Key Tables for Finance Mart

INVOICES

Grain: One row per invoice
Primary key: ID
Key columns:
- ID, TXN_DATE, TOTAL_AMT, BALANCE, HOME_TOTAL_AMT, HOME_BALANCE
- CUSTOMER_REF (VARIANT: { "value": "366", "name": "ABC Home" }) — extract value for customer ID
- DUE_DATE, DOC_NUMBER, LINKED_TXN (ARRAY)
Polytomic metadata: _POLYTOMIC_ID, _POLYTOMIC_CREATED_AT, _POLYTOMIC_UPDATED_AT, _POLYTOMIC_DELETED_AT

BILLS

Grain: One row per bill (AP)
Primary key: ID
Key columns: ID, TXN_DATE, TOTAL_AMT, BALANCE, VENDOR_REF (VARIANT), DUE_DATE, DOC_NUMBER

PAYMENTS

Grain: One row per payment
Primary key: ID
Key columns: ID, TXN_DATE, TOTAL_AMT, CUSTOMER_REF, LINKED_TXN (ARRAY)

JOURNAL_ENTRIES

Grain: One row per journal entry
Key columns: ID, TXN_DATE, TOTAL_AMT, LINE (ARRAY — line items)

CUSTOMERS

Grain: One row per customer
Key columns: ID, DISPLAY_NAME, COMPANY_NAME, FULLY_QUALIFIED_NAME, BALANCE, BALANCE_WITH_JOBS

VENDORS

Grain: One row per vendor
Key columns: ID, DISPLAY_NAME, COMPANY_NAME, BALANCE

Staging Model Guidance

Extract refs: Use CUSTOMER_REF:value::TEXT, VENDOR_REF:value::TEXT for FK joins
Handle VARIANT/ARRAY: Flatten LINE arrays for invoice/bill line items if needed
Soft deletes: Filter _POLYTOMIC_DELETED_AT IS NULL for active records
Currency: CURRENCY_REF is VARIANT; TOTAL_AMT is in transaction currency