Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Expected Xero Data

We're currently doing some private testing on our Xero integration. Want to be a part of it? Get in touch with us.

Now that Xero is successfully connected to your Stitch account, what comes next? In this article, we'll cover:

Replicating Xero Data

Historical Sync

By default, a historical sync of Xero data goes back one year from the Stitch connection date.

Frequency

Stitch will replicate Xero data based on the frequency you define. The default setting is every 6 hours, but you can change it to better suit your needs.

Replication Method

The majority of Xero data is replicated using Incremental Replication. This means that every time Xero data is queued for replication, only new and updated rows will be replicated to your data warehouse.

There are exceptions to this, however. The tables listed below use Full Table Replication, which means that every time Xero data is queued for replication, all rows in these tables - including new, existing, and updated - will be replicated to your data warehouse:

  • xero_branding_themes
  • xero_contact_groups
  • xero_currencies
  • xero_linked_transactions
  • xero_organisation
  • xero_repeating_invoices
  • xero_tax_rates
  • xero_tracking_categories

While these tables tend to be small, it's important to keep in mind the number of tables that use Full Table Replication when setting the Replication Frequency for the integration.

Noticing Large Amounts of Data?

Even though many of the tables in our Xero integration use Incremental Replication, there are several that replicate fully during each sync. Additionally, a large number of rows can still be used during each replication attempt due to how Stitch de-nests nested data structures.

To sum it up: each subtable is replicated according to the Replication Method of the parent table. The xero_purchase_orders table, for example, has several subtables. Each time the xero_purchase_orders table is updated, these subtables may be updated as well. This results in new rows in not one, but several tables.

To adjust for this, many of our clients set their Replication Frequencies to a less frequent interval - for example, to every few hours - to keep their row counts down. You can find more tips on reducing your row count here.

Xero Tables in Your Data Warehouse

Below is a list of the main and subtables that will be created in your data warehouse. Because Xero uses nested arrays to structure data in some of its API's endpoints, Stitch will de-nest these arrays and create subtables. Refer to the Handling of Nested Data Structures doc for more info.

Click a table name below to view a full list of that table’s attributes in Xero’s API documentation.

xero_accounts
Primary Key:
AccountID

The xero_accounts table contains information about the various accounts (ex: banking) connected to your Xero account.

Attributes include the account ID, code, name, type, bank account number and type (if applicable), currency code, status, description, class, system account, tax type, payment application flag, expense claim flag, reporting code and name, and updated at date.

xero_bank_transactions
Primary Key:
BankTransactionID

The xero_bank_transactions table contains information about any bank transactions - for example, overpayments, prepayments, spend, etc. - in  your Xero account.

Attributes include the bank transaction ID, bank transaction type, contact, bank account, reconciliation status, transaction date, currency code, currency rate, status, line amount types, subtotal, tax total, transaction total, prepayment and overpayment IDs (if applicable), and updated at date.

  • xero_bank_transactions__lineitems - This subtable is created from the line items subarray in the bank transactions endpoint.

    The composite key for this table - made up of the bank transaction ID and row ID - can be used to tie it back to its parent table, xero_bank_transactions: _sdc_source_key_banktransactionid:_sdc_level_0_id
     

xero_bank_transfers
Primary Key:
BankTransferID

The xero_bank_transfers table contains information about bank transfers.

Attributes include the bank transfer ID, from and to bank accounts, amount, transfer date, currency rate, bank transaction ID for the source and destination accounts, and created at date.

Subtables

The composite key for the following tables - made up of the bank transfer ID and row ID columns - can be used to tie these tables back to their parent, xero_bank_transfers: _sdc_source_key_banktransferid:_sdc_level_0_id

  • xero_bank_transfers__frombankaccount - This subtable contains information about the source bank account, or where the transfer came from.
  • xero_bank_transfers__tobankaccount - This subtable contains information about the destination bank account, or where the transfer went to.

xero_branding_themes
Primary Key:
BrandingThemeID

The xero_branding_themes table contains information about the branding themes defined for a Xero organisation.

Attributes include the branding theme ID, name, sort order, and created at date.

xero_contacts
Primary Key:
ContactID

The xero_contacts table contains information about the contacts in a Xero organisation.

Attributes include the contact ID, account number, contact status, name, first and last name, email address, Skype user name, bank account number, tax number, AR/AP tax type, supplier flag, customer flag, default currency, AR/AP outstanding balances, AR/AP overdue balances, batch payments info, and updated at date.

Subtables

The composite key for the following tables - made up of the bank transfer ID and row ID columns - can be used to tie these tables back to their parent, xero_contacts: _sdc_source_key_contactid:_sdc_level_0_id

  • xero_contacts__addresses - This subtable contains your contacts’ address data.
  • xero_contacts__contactgroups - This subtable contains data about the groups your contacts belong to.
  • xero_contacts__phones - This subtable contains your contacts’ phone data.
     

xero_contact_groups
Primary Key:
ContactGroupID

The xero_contact_groups table contains information about the contact groups in a Xero organisation.

Attributes include the contact group ID, name, and status.

xero_credit_notes
Primary Key:
CreditNoteID

The xero_credit_notes table contains information about the credit notes in your Xero organisation.

Attributes include the credit note ID, type, issue date, status, contact, subtotal, tax total, credit note total, currency code, fully paid date, credit note number, reference number, sent to flag, currency rate, remaining credit balance, branding theme ID, and updated at date.

Subtables

The composite key for the following tables - made up of the credit note ID and row ID - can be used to tie them back to their parent table, xero_credit_notes: _sdc_source_key_creditnoteid:_sdc_level_0_id

xero_currencies
Primary Key:
code

The xero_currencies table contains information about the currencies set up for your Xero organisation.

Attributes include the currency code and description.

xero_employees
Primary Key:
employeeID

The xero_employees table contains information about the employees used in Xero payrun.

Attributes include the employee ID, status, first and last name, updated at date, and external links, if applicable.

xero_expense_claims
Primary Key:
ExpenseClaimID

The xero_expense_claims table contains information about the expense claims for your Xero organisation.

Attributes include the expense claim ID, status, total, amount due, amount paid, user info, payment due date, reporting date, and updated at date.

xero_invoices
Primary Key:
InvoiceID

The xero_invoices table contains information about the sales invoices and purchase bills for your Xero organisation.

Attributes include the invoice ID, type, issue date, due date, invoice number, reference, branding theme ID, currency code, currency rate, status, sent to flag, expected payment date (sales invoices only), planned payment date, subtotal, tax total, invoice total, amount due, amount paid, fully paid date, amount credited, and updated at date.

Subtables

  • xero_invoices__creditnotes - This subtable contains data about the credit notes associated with invoices.

    The composite key for this table - made up of the invoice ID and the row ID - can be used to tie this table back to its parent, xero_invoices: _sdc_source_key_invoiceid:_sdc_level_0_id
  • xero_invoices__lineitems - This subtable contains data about the line items associated with an invoice.

    The composite key for this table - made up of the invoice ID and the row ID - can be used to tie this table back to its parent, xero_invoices: _sdc_source_key_invoiceid:_sdc_level_0_id
    • xero_invoices__lineitems__tracking - This subtable contains tracking data associated with an invoice’s line items.

      The composite key for this table can be used to tie this table back to the top-level table, xero_invoices: _sdc_source_key_invoiceid:_sdc_level_0_id:_sdc_level_1_id
       

xero_items
Primary Key:
ItemID

The xero_items table contains information about the goods and services in your Xero account.

Attributes include item ID, item name (item), code, name, description, inventory asset account code, is sold/purchased boolean, purchase description, quantity on hand, total cost pool, account code, tax type, unit price, and updated at date.

xero_journals
Primary Key:
JournalID

The xero_journals table contains information about the journals in your Xero account.

Attributes include the journal ID, date, number, source ID, source type, and created at date.

Subtables

  • xero_journals__journallines - This subtable contains details about a journal’s line items.

    The composite key for this table - made up of the journal ID and the row ID - can be used to tie this table back to its parent, xero_journals: _sdc_source_key_journalid:_sdc_level_0_id

    • xero_journals__journallines__trackingcategories - This subtable contains tracking data associated with a journal’s line items.

      The composite key for this table can be used to tie this table back to the top-level table, xero_journals: _sdc_source_key_journalid:_sdc_level_0_id:_sdc_level_1_id

xero_linked_transactions
Primary Key:
LinkedTransactionID

The xero_linked_transactions table contains information about the billable expenses in your Xero account.

Attributes include the linked transaction ID, source transaction ID, source line item ID, target line item ID, status, type, source transaction type code, and updated at date.

xero_manual_journals
Primary Key:
ManualJournalID

The xero_manual_journals table contains information about the manual journals in your Xero account.

Attributes include the manual journal ID, status, line amount types, narration, cash basis report flag, date, and updated at date.

  • xero_manual_journals__journallines - This subtable contains details about a manual journal’s line items.

    The composite key for this table - made up of the manual journal ID and the row ID - can be used to tie this table back to its parent, xero_manual_journals: _sdc_source_key_manualjournalid:_sdc_level_0_id
    • xero_manual_journals__journallines__trackingcategories - This subtable contains tracking data associated with a manual journal’s line items.

      The composite key for this table can be used to tie this table back to the top-level table, xero_manual_journals: _sdc_source_key_manualjournalid:_sdc_level_0_id:_sdc_level_1_id

xero_organisation
Primary Key:
TaxNumber

The xero_organisation table contains information about your Xero organisation.

Attributes include the organisation name, legal name, tax flag, version, base currency, country code, organisation status, registration number, tax number, financial year end month and day, sales tax basis and period, default sales and purchases tax settings, period lock date, end of year lock date, organisation entity type, timezone, shortcode, line of business, external links, payment terms, and updated at date.

Subtables

The composite key for the following tables - made up of the tax number and the row ID - can be used to tie this table back to its parent, xero_organisation: _sdc_source_key_taxnumber:_sdc_level_0_id

  • xero_organisation__addresses - This subtable contains details about the addresses associated with an organisation.
  • xero_organisation__externalinks - This subtable contains details about the external links associated with an organisation.
  • xero_organisation__phones - This subtable contains details about the phone numbers associated with an organisation.
     

xero_overpayments
Primary Key:
OverpaymentID

The xero_overpayments table contains information about overpayments in your Xero account.

Attributes include the overpayment ID, type, date, status, subtotal, total tax, overpayment total, currency code, currency rate, line amount types, remaining credit, and updated at date.

  • xero_overpayments__allocations - This subtable contains info about allocations associated with overpayments.

    The composite key for this table - made up of the overpayment ID and the row ID - can be used to tie this table back to its parent, xero_overpayments: _sdc_source_key_overpaymentid:_sdc_level_0_id

xero_payments
Primary Key:
PaymentID

The xero_payments table contains information about the payments - invoice, credit note, prepayment, or overpayment - in your Xero account.

Attributes include the payment ID, date, amount, currency rate, payment type, status, account ID, and updated at date.

  • xero_payments__invoice - This subtable contains details about the invoices associated with a payment.

    The composite key for this table - made up of the payment ID and row ID - can be used to tie it back to its parent, xero_payments: _sdc_source_key_paymentid:_sdc_level_0_id
    • xero_payments__invoice__contact - This subtable contains contact info for those associated with an invoice.

      The composite key for this table can be used to tie this table back to the top-level table, xero_payments: _sdc_source_key_paymentid:_sdc_level_0_id:_sdc_level_1_id

xero_prepayments
Primary Key:
PrepaymentID

The xero_prepayments table contains information about the preypayments in your Xero account.

Attributes include the prepayment ID, date, type, status, line amount types, subtotal, total tax, total, preypayment total, currency code, currency rate, fully paid on date, remaining credit, and updated at date.

Subtables

The composite key for the following tables - made up of the prepayment ID and the row ID - can be used to tie this table back to its parent, xero_prepayments: _sdc_source_key_prepaymentid:_sdc_level_0_id

  • xero_prepayments__contact - This subtable contains info about the contacts associated with prepayments.
  • xero_prepayments__allocations - This subtable contains info about allocations associated with prepayments.

xero_purchase_orders
Primary Key:
PurchaseOrderID

The xero_purchase_orders table contains information about the purchase orders in your Xero account.

Attributes include the purchase order ID, delivery date, reference, branding theme ID, status, line amount types, currency rate, currency code, delivery address, attention to name, telephone, delivery instructions, expected arrival date, and updated at date.

  • xero_purchase_orders__contact - This subtable contains the contact info associated with a purchase order.

    The composite key for this table - made up of the purchase order ID and row ID - can be used to tie it back to its parent, xero_purchase_orders: _sdc_source_key_prepaymentid:_sdc_level_0_id
  • xero_purchase_orders__lineitems - This subtable contains info about the line items associated with a purchase order.

    The composite key for this table - made up of the purchase order ID and row ID - can be used to tie it back to its parent, xero_purchase_orders: _sdc_source_key_prepaymentid:_sdc_level_0_id
    • xero_purchase_orders__lineitems__tracking - This subtable contains tracking info associated with the line items in a purchase order.

      The composite key for this table can be used to tie this table back to the top-level table, xero_purchase_orders: _sdc_source_key_purchaseorderid:_sdc_level_0_id:_sdc_level_1_id

xero_receipts
Primary Key:
ReceiptID

The xero_receipts table contains information about the expense claim receipts in your Xero account.

Attributes include the receipt ID, user, reference number, line amount types, subtotal, total tax, receipt total, status, receipt number, URL, and updated at date.

  • xero_receipts__lineitems - This subtable contains info about the line items associated with a receipt.

    The composite key for this table - made up of the receipt ID and row ID - can be used to tie it back to its parent, xero_receipts: _sdc_source_key_receiptid:_sdc_level_0_id
    • xero_receipts__lineitems__tracking - This subtable contains tracking info associated with the line items in a purchase order.

      The composite key for this table can be used to tie this table back to the top-level table, xero_receipts: _sdc_source_key_preceiptid:_sdc_level_0_id:_sdc_level_1_id

xero_repeating_invoices
Primary Key:
RepeatingInvoiceId

The xero_repeating_invoices table contains information about repeating invoices in your Xero account.

Attributes include the repeating invoice ID, type, invoice contact info, currency code, reference number, status, subtotal, total tax, repeating invoice total. Schedule data includes the period, unit, due date, due date type, start date, next scheduled date, and end date.

  • xero_repeating_invoices__lineitems - This subtable contains info about line items associated with repeating invoices.

    The composite key for this table - made up of the repeating invoice ID and row ID - can be used to tie it back to its parent, xero_repeating_invoices: _sdc_source_key_repeatinginvoiceid:_sdc_level_0_id
    • xero_repeating_invoices__lineitems__tracking - This subtable contains tracking info associated with the line items in a repeating invoice.

      The composite key for this table can be used to tie this table back to the top-level table, xero_repeating_invoices: _sdc_source_key_repeatinginvoiceid:_sdc_level_0_id:_sdc_level_1_id

xero_tax_rates
Primary Key:
Name

The xero_tax_rates table contains information about the tax rates in your Xero account.

Attributes include the tax rate name, tax type, status, display tax rate setting, effective rate, and booleans for application to assets, equity, expenses, liabilities, and revenue.

  • xero_tax_rates__taxcomponents - This subtable contains info about the tax components associated with a tax rate.

    The composite key for this table - made up of the tax rate name and row ID - can be used to tie it back to its parent, xero_tax_rates: _sdc_source_key_name:_sdc_level_0_id

xero_tracking_categories
Primary Key:
TrackingCategoryID

The xero_tracking_categories table contains information the tracking categories and options in your Xero account.

Attributes include the tracking category ID, name, and status.

  • xero_tracking_categories__options - This subtable contains info about the options associated with a tracking category.

    The composite key for this table - made up of the tracking category ID and row ID - can be used to tie it back to its parent, xero_tracking_categories: _sdc_source_key_trackingcategoryid:_sdc_level_0_id

xero_users
Primary Key:
UserID

The xero_users table contains information about the users in your Xero account.

Attributes include the user ID, first and last name, email address, organisation role, and updated at date.

Related

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!