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 Square Data

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

Replicating Square Data

Historical Sync

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

Frequency

Stitch will replicate Square data based on the frequency you define. The default setting is every 30 minutes, but you can change it to something less frequent based on your needs.

Replication Methods

The majority of Square tables are replicated using Incremental Table replication. This means that every time Square data is queued for replication, only new and updated rows will be replicated.

There are some tables that use Full Table Replication, however. This means that during every replication attempt, all the rows in these tables (and their subtables) - including existing, new, and updated - will be replicated:

  • bank_accounts
  • categories
  • discounts
  • fees
  • inventory
  • items
  • location
  • modifier_lists
  • pages

Square Tables in Your Data Warehouse

Below is a list of the main and subtables that will be created in your data warehouse. Because Square 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 to see a full list of attributes for that table in Square's API documentation.

bank_accounts
Primary Key:
_id

The bank_accounts table contains non-confidential information - this means no full bank account numbers - about a location’s associated bank accounts.

Attributes include the Square-issued bank account ID (id), merchant ID, bank name, bank account name, type, routing number, account number suffix, and currency code.

cash_drawer_shifts
Primary Key: _id

The cash_drawer_shifts table provides the details for all of a location’s cash drawer shifts.

Attributes include the cash drawer shift ID (id), opening and closing times, starting cash amount/currency, refund cash amount/currency, cash paid in amount/currency, cash paid out amount/currency, expected cash amount/currency, the cash drawer state, description, and location ID.

  • cash_drawer_shifts__events - This subtable is created from the events array, which contains all the events that involved the cash drawer during the shift.

categories
Primary Key: _id

The categories table contains data about a location’s item categories.

Attributes include the category ID (id), name, and location ID.

discounts
Primary Key: _id

The discounts table records information about a location’s discounts.

Attributes include the discount ID (id), type, name, rate, color, amount (if a variable amount discount), and PIN requirement setting.

employees
Primary Key: _id

The employees table provides summary information for all of a business’s employees.

Attributes include the employee ID (id), first and last name, status, external ID, role IDs, email address, and created and updated at dates.

fees
Primary Key: _id

The fees table contains information on a location’s fees, or tax items.

Attributes include the fee ID (id), name, location ID, rate, type, calculation phase, adjustment type, custom amount application setting, enabled setting, and inclusion type.

inventory
Primary Key: _id

The inventory table contains inventory information for all of a merchant’s inventory-enabled variations.

Attributes include the variation ID (id), quantity on hand, and location ID.

items
Primary Key: _id

The items table contains information about a location’s items.

Attributes include the item ID (id), name, description, type, abbreviation, color, visibility setting, online availability setting, master image, category, and location ID.

  • items__variations - This subtable is created from the variations array, which contains variation info.
  • items__modifier_lists - This subtable is created from the modifier lists array, which contains data about modifier lists applied to an item, if there are any.
    • item__modifier__lists__modifier__options - This subtable contains the item modifier options in a modifier list.

      The composite key for this table - the modifier list ID and modifier option ID  - can be used to tie it back to its parent table, item__modifier_lists: _sdc_source_key_id:_sdc_level_1_id
  • items__fees - This subtable is created from the fees array, which contains data about taxes or fees that can be applied to an item.

location
Primary Key: _id

The location table provides details for a business’s locations.

Attributes include the location ID (id), contact name and email address, country, language, and currency codes, business name, business contact information, business type, shipping information, account type and details, market URL, and account capabilities.

  • location__account_capabilities - This subtable is created from the account capabilities array, which lists the account capabilities - for example, credit card processing - for a location.

    The composite key for this table - the location ID and account capabilities ID  - can be used to tie it back to its parent table, locations: _sdc_source_key_id:_sdc_level_0_id

modifier_lists
Primary Key: _id

The modifier_lists table contains information about modifications for specific items.

Attributes of this table include the modifier ID (id), name, selection type, and location ID.

  • modifier_lists__modifier_options - This subtable is created from the modifier options array, which contains the modifier options in a modifier list.

    The composite key for this table - the modifier list ID and modifier option ID  - can be used to tie it back to its parent table, modifier_lists: _sdc_source_key_id:_sdc_level_0_id

orders
Primary Key: _id

The orders table provides summary information for a merchant’s online store orders. This table does not contain purchased items data. To view order data alongside purchased items data, you can use the payment_id column in the orders table to the payments table.

Attributes include the order ID (id), order state, buyer info, shipping address, subtotal, total shipping, total tax, total price, total discount, expiration time, payment ID, notes, tender, promo code, location ID, and created and updated at dates.

  • orders__order_history - This subtable is created from the order history array, which contains the history of actions associated with an order.

    The composite key for this table - the order ID and order history ID  - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id

pages
Primary Key: _id

The pages table provides information about favorites pages created in the iPad version of Square Register.

Attributes include the page ID (id), name, page index (position in the list of pages), cell info, and location ID.

  • pages__cells - This subtable is created from the cells array, which contains page cell data.

    The composite key for this table - the page ID and page cell ID  - can be used to tie it back to its parent table, pages: _sdc_source_key_id:_sdc_level_0_id

payments
Primary Key: _id

The payments table provides summary information for all payments taken by a merchant or the merchant’s mobile staff.

Attributes include the payment ID (id), merchant ID, creator ID, device name, payment URL, tax, tip, discount, processing fee, and created at date.

  • payments__additive_tax - This subtable is created from the additive tax array, which contains info about applied additive taxes.
  • payments__inclusive_tax- This subtable is created from the inclusive tax array, which contains info about applied inclusive taxes.
  • payments__tender - This subtable is created from the tender array, which includes details about tenders applied to a payment.
  • payments__refunds - This subtable is created from the refunds array, which includes details about refunds applied to a payment.
  • payments__itemizations - This subtable is created from the itemizations array, which includes details about the items contained in a transaction, or payment.

refunds
Primary Key: The Primary Key for this table is a composite of the payment ID and created at timestamp: payment_id:created_at

The refunds table provides the details for all refunds initiated by a merchant or any of the merchant’s mobile staff.

Attributes include the payment ID, refund type, created and processed at timestamps, reason, refund amount, currency, and location ID.

roles
Primary Key: _id

The roles table provides summary information for all of a business’s employee roles.

Attributes of this table include the role ID (id), name, owner setting, and created and updated at timestamps.

  • roles__permissions - This subtable is created from the permissions array, which contains the permissions associated with a given role.

settlements
Primary Key: _id

The settlements table provides summary information for all deposits and withdraws initiated by Square to a merchant’s bank account. This table does not contain entry data, which lists the individual transactions that contribute to the settlement total.

Attributes include the settlement ID (id), status, Square-issued bank account ID, initiated at date, total amount, currency, and location ID.

timecards
Primary Key: _id

The timecards table provides summary information for all of a business’s employee timecards.

Attributes include the timecard ID (id), clock-in and clock-out timestamps and location, employee ID, and created and updated at timestamps.

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!