Square snapshot

A high-level look at Stitch's Square integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release Status

Released

Supported By

Stitch

Stitch Plan

Free

DATA SELECTION
Table Selection

Unsupported

Column Selection

Unsupported

REPLICATION SETTINGS
Anchor Scheduling

Supported

Table-level Reset

Unsupported

Configurable Replication Methods

Unsupported

TRANSPARENCY
Extraction Logs

Unsupported

Loading Reports

Unsupported

Connecting Square

Connecting your Square data to Stitch a four-step process:

  1. Add Square as a Stitch data source
  2. Define the Historical Sync
  3. Define the Replication Frequency
  4. Authorize Stitch to access Square

Add Square as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Square icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Square” would create a schema called stitch_square in the destination. Note: Schema names cannot be changed after you save the integration.

Define the Historical Sync

The Sync Historical Data setting will define the starting date for your Square integration. This means that:

  • For tables using Incremental Replication, data equal to or newer than this date will be replicated to your data warehouse.
  • For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Square’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Stitch offers two methods of creating a replication schedule:

  • Replication Frequency: This method requires selecting the interval you want replication to run for the integration. Start times of replication jobs are based on the start time and duration of the previous job. Refer to the Replication Frequency documentation for more information and examples.
  • Anchor scheduling: Based on the Replication Frequency, or interval, you select, this method “anchors” the start times of this integration’s replication jobs to a time you select to create a predictable schedule. Anchor scheduling is a combination of the Anchor Time and Replication Frequency settings, which must both be defined to use this method. Additionally, note that:

    • A Replication Frequency of at least one hour is required to use anchor scheduling.
    • An initial replication job may not begin immediately after saving the integration, depending on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

To help prevent overages, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Authorizing Stitch to Access Square

Lastly, you’ll be directed to Square’s website to complete the setup.

  1. Enter your Square credentials and click Login.
  2. After the authorization process successfully completes, you’ll be redirected back to Stitch.
  3. Click All Done.

Initial and historical replication jobs

After you finish setting up Square, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Square Schema

Stitch's Square integration includes these tables:


square_bank_accounts

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

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

Table Info & Attributes

square_bank_accounts Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Bank Account ID (id)

  • location_id

  • merchant_id

  • bank_name

  • name

  • type

  • routing_number

  • account_number_suffix

  • currency_code

square_cash_drawer_shifts

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: Yes

The square_cash_drawer_shifts table contains the details for all of a location’s cash drawer shifts.

Table Info & Attributes

square_cash_drawer_shifts & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

square_cash_drawer_shifts Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Cash Drawer Shift ID (id)

  • location_id

  • cash_drawer_state

  • opened_at

  • ended_at

  • closed_at

  • employee_ids

  • opening_employee_id

  • ending_employee_id

  • closing_employee_id

  • description

  • starting_cash_money

  • cash_payment_money

  • cash_refunds_money

  • cash_paid_in_money

  • cash_paid_out_money

  • expected_cash_money

  • closed_cash_money

  • device

  • events*

square_categories

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

The square_categories table contains info about a location’s item categories.

Table Info & Attributes

square_categories Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Category ID (id)

  • name

  • location_id

square_discounts

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

The square_discounts table contains info about a location’s discounts.

Table Info & Attributes

square_discounts Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Discount ID (id)

  • location_id

  • name

  • rate

  • amount_money

  • discount_type

  • pin_required

  • color

square_employees

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: No

The square_employees table contains summary info for all of a business’s employees.

Table Info & Attributes

square_employees Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Employee ID (id)

  • first_name

  • last_name

  • role_ids

  • authorized_location_ids

  • email

  • status

  • external_id

  • created_at

  • updated_at

square_fees

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

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

Table Info & Attributes

square_fees Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Fee ID (id)

  • location_id

  • name

  • rate

  • calculation_phase

  • adjustment_type

  • applies_to_custom_amounts

  • enabled

  • inclusion_type

  • type

square_inventory

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

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

Table Info & Attributes

square_inventory Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Variation ID (id)

  • location_id

  • quantity_on_hand

square_items

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: Yes

The square_items table contains info about a location’s items.

Table Info & Attributes

square_items & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

square_items Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Item ID (id)

  • location_id

  • name

  • description

  • type

  • abbreviation

  • color

  • visibility

  • available_online

  • master_image

  • <a href=”https://docs.connect.squareup.com/api/connect/v1/#datatype-itemvariation target=”new”>variations</a>*

  • modifier_lists*

  • fees*

  • taxable

square_location

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

The square_location table contains details for a business’s locations.

Table Info & Attributes

square_location Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Location ID (id)

  • name

  • email

  • country_code

  • currency_code

  • business_name

  • business_address__address_line_1

  • business_address__locality

  • business_address__administrative_district_level_1

  • business_address__postal_code

  • business_phone__calling_code

  • business_phone__number

  • business_type

  • shipping_address__address_line_1

  • shipping_address__locality

  • shipping_address__administrative_district_level_1

  • shipping_address__postal_code

  • account_type

  • location_details__nickname

  • market_url

  • account_capabilities*

square_modifier_lists

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: No

The square_modifier_lists table contains info about modifications for specific items.

Table Info & Attributes

square_modifier_lists Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Modifier ID (id)

  • name

  • selection_type

  • modifier_options*

square_orders

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: Yes

The square_orders table contains summary information for a merchant’s online store orders.

Table Info & Attributes

square_orders & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

Purchased Items & Orders

This table does not contain purchased items data. To view order data alongside purchased items data, use the payment_id column in this table to join it to the payments table.

square_orders Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Order ID (id)

  • state

  • buyer_email

  • recipient_name

  • recipient_phone_number

  • shipping_address

  • subtotal_money

  • total_shipping_money

  • total_price_money

  • total_discount_money

  • created_at

  • updated_at

  • expires_at

  • payment_id

  • buyer_note

  • completed_note

  • refunded_note

  • canceled_note

  • tender

  • order_history*

  • promo_code

  • btc_receive_address

  • btc_price_satoshi

square_pages

Replication Method: Full Table
Primary Key: id
Contains Nested Structures?: Yes

The square_pages table contains info about favorites pages created in the iPad version of Square Register.

Table Info & Attributes

square_pages & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

square_pages Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Page ID (id)

  • name

  • page_index

  • cells*

square_payments

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: Yes

The square_payments table contains summary info - including itemizations - for all payments taken by a merchant or the merchant’s mobile staff. Note that Square doesn't always include itemizations in payments when the payment amount is zero.

Table Info & Attributes

square_payments & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

Payment Amounts & Itemizations

Square doesn’t always include itemizations in payments when the payment amount is zero. If you’re missing itzemization data, check the corresponding payment amounts to see if they’re greater than zero.

square_payments Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • PaymentID (id)

  • merchant_id

  • created_at

  • creator_id

  • device

  • payment_url

  • receipt_url

  • inclusive_tax_money

  • additive_tax_money

  • tax_money

  • tip_money

  • discount_money

  • total_collected_money

  • processing_fee_money

  • net_total_money

  • refunded_money

  • inclusive_tax*

  • additive_tax*

  • tender*

  • refunds*

  • itemizations*

square_refunds

Replication Method: Key-based Incremental
Primary Key: payment_id:created_at
Contains Nested Structures?: No

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

Table Info & Attributes

square_refunds Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • payment_id

  • created_at

  • type

  • reason

  • refunded_money

  • processed_at

square_roles

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: Yes

The square_roles table contains summary info for all of a business’s employee roles.

Table Info & Attributes

square_roles & Nested Structures

This table contains nested structures. If you use a data warehouse that doesn't natively support nested structures, some of the attributes listed below may be in a subtable.

These items are marked with a *

square_roles Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Role ID (id)

  • name

  • permissions*

  • is_owner

  • created_at

  • updated_at

square_square_settlements

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: No

The square_square_settlements table contains 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.

Table Info & Attributes

square_square_settlements Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Settlement ID (id)

  • status

  • bank_account_id

  • initiated_at

  • total_money__amount

  • total_money__currency_code

square_square_timecards

Replication Method: Key-based Incremental
Primary Key: id
Contains Nested Structures?: No

The square_square_timecards table contains summary info for all of a business’s employee timecards.

Table Info & Attributes

square_square_timecards Attributes

While we try to include everything Square has here, this may not be a full list of attributes. Refer to Square's documentation for a full list and description of each attribute.

  • Timecard ID (id)

  • employee_id

  • deleted

  • clockin_time

  • clockout_time

  • clockin_location_id

  • clockout_location_id

  • created_at

  • updated_at



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.