Schema Information for Shopify

Need help with something?

Talk to data expert

In this article, we will look at the schema information of the supported APIs and tables for  Shopify.

Find the links to other articles related to Shopify Below:

Tables/APIs Supported

The tables and APIs associated with the Shopify connector are as follows.

orders

Purpose:  An order is a customer's completed request to purchase one or more products from a shop. An order is created when a customer completes the checkout process, during which time they provide an email address or phone number, billing address, and payment information. This Retrieves a list of orders.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/order#get-orders?status=any

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
admin_graphql_api_id STRING
browser_ip STRING
buyer_accepts_marketing BOOLEAN
cart_token STRING
checkout_id NUMERIC
checkout_token STRING
client_details RECORD
confirmed BOOLEAN
contact_email STRING
created_at TIMESTAMP
currency STRING
current_subtotal_price STRING
current_subtotal_price_set RECORD
current_total_discounts STRING
current_total_discounts_set RECORD
current_total_price STRING
current_total_price_set RECORD
current_total_tax STRING
current_total_tax_set RECORD
discount_codes RECORD
email STRING
financial_status STRING
gateway STRING
landing_site STRING
landing_site_ref STRING
name STRING
note_attributes RECORD
number NUMERIC
order_number NUMERIC
order_status_url STRING
payment_gateway_names STRING
phone STRING
presentment_currency STRING
processed_at TIMESTAMP
processing_method STRING
reference STRING
referring_site STRING
source_identifier STRING
source_name STRING
subtotal_price STRING
subtotal_price_set RECORD
tags STRING
tax_lines RECORD
taxes_included BOOLEAN
test BOOLEAN
token STRING
total_discounts STRING
total_discounts_set RECORD
total_line_items_price STRING
total_line_items_price_set RECORD
total_outstanding STRING
total_price STRING
total_price_set RECORD
total_price_usd STRING
total_shipping_price_set RECORD
total_tax STRING
total_tax_set RECORD
total_tip_received STRING
total_weight NUMERIC
updated_at TIMESTAMP
billing_address RECORD
customer RECORD
discount_applications RECORD
fulfillments RECORD
line_items RECORD
payment_details RECORD
refunds RECORD
shipping_address RECORD
shipping_lines RECORD

transactions

Purpose: Transactions are created for every order that results in an exchange of money. This table retrieves a list of transactions.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/transaction#get-orders-order-id-transactions

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
order_id NUMERIC
kind STRING
gateway STRING
status STRING
created_at TIMESTAMP
test BOOLEAN
authorization STRING
parent_id NUMERIC
processed_at TIMESTAMP
source_name STRING
amount STRING
currency STRING
payment_id STRING
total_unsettled_set RECORD
admin_graphql_api_id STRING

refunds

Purpose: The Refund resource has two major components:

  • Transaction records of money returned to the customer
  • The line items included in the refund, along with restocking instructions

This table retrieves a list of refunds for an order.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/refund#get-orders-order-id-refunds

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
order_id NUMERIC
created_at TIMESTAMP
note STRING
user_id NUMERIC
processed_at TIMESTAMP
restock BOOLEAN
admin_graphql_api_id STRING
refund_line_items RECORD
transactions RECORD

fulfillment_orders

Purpose:  The FulfillmentOrder resource represents either an item or a group of items in an order that are to be fulfilled from the same location. There can be more than one fulfillment order for an order at a given location. This table retrieves a list of fulfillment orders for a specific order.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/fulfillmentorder#get-orders-order-id-fulfillment-orders

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
order_id NUMERIC
status STRING
created_at TIMESTAMP
service STRING
updated_at TIMESTAMP
tracking_company STRING
location_id NUMERIC
line_items RECORD
tracking_number STRING
tracking_numbers STRING
tracking_url STRING
tracking_urls STRING
receipt RECORD
name STRING
admin_graphql_api_id STRING

fulfillment_events

Purpose:  This table retrieves a list of fulfillment events for a specific fulfillment

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/fulfillmentevent#get-orders-order-id-fulfillments-fulfillment-id-events

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
fulfillment_id NUMERIC
status STRING
message STRING
happened_at TIMESTAMP
city STRING
province STRING
province STRING
zip STRING
address1 STRING
latitude STRING
longitude STRING
shop_id NUMERIC
created_at TIMESTAMP
updated_at TIMESTAMP
estimated_delivery_at TIMESTAMP
order_id NUMERIC
admin_graphql_api_id STRING

tender_transactions

Purpose:  Each tender transaction represents money passing between the merchant and a customer. A tender transaction with a positive amount represents a transaction where the customer paid money to the merchant. A negative amount represents a transaction where the merchant refunded money back to the customer. Tender transactions represent transactions that modify the shop's balance.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/tendertransaction#get-tender-transactions

Replication: Key Based Incremental

Replication Key: processed_at_min, processed_at_max

Column Name Data Type
id NUMERIC
order_id NUMERIC
amount STRING
currency STRING
test BOOLEAN
processed_at TIMESTAMP
remote_reference STRING
payment_method STRING

customers

Purpose: The Customer resource stores information about a shop's customers, such as their contact details, their order history, and whether they've agreed to receive email marketing. This table retrieves a list of customers

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/customer#get-customers?ids=207119551,1073339465

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
email STRING
accepts_marketing BOOLEAN
created_at TIMESTAMP
updated_at TIMESTAMP
first_name STRING
last_name STRING
orders_count NUMERIC
state STRING
total_spent STRING
verified_email BOOLEAN
tax_exempt BOOLEAN
tags STRING
currency STRING
phone STRING
addresses RECORD
accepts_marketing_updated_at TIMESTAMP
admin_graphql_api_id STRING
default_address RECORD

customer_address

Purpose:  This table retrieves the list of address stored for all the customers

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/customer-address#get-customers-customer-id-addresses?limit=1

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Name Data Type
id NUMERIC
customer_id NUMERIC
address1 STRING
address2 STRING
city STRING
province STRING
country STRING
zip STRING
phone STRING
name STRING
province_code STRING
country_code STRING
country_name STRING
default BOOLEAN

events

Purpose:  Events are generated by some Shopify resources when certain actions are completed, such as the creation of an article, the fulfillment of an order, or the addition of a product. This table retrieves the list of events

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/event#get-events

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
subject_id NUMERIC
created_at TIMESTAMP
subject_type STRING
verb STRING
arguments STRING
message STRING
author STRING
description STRING
path STRING

products

Purpose: This table retrieves the list of products in the given shopify store

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/product#get-products?ids=632910392,921728736

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
subject_id NUMERIC
created_at TIMESTAMP
subject_type STRING
verb STRING
arguments STRING
message STRING
author STRING
description STRING
path STRING

payouts

Purpose: Payouts represent the movement of money between a Shopify Payments account balance and a connected bank account. This table retrieves the list of all payouts

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/payouts

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
status STRING
date DATE
currency STRING
amount STRING
summary RECORD

balance_transactions

Purpose: Balance Transactions make up the canonical "statement of account", or ledger for a Shopify Payments account. Every single movement of money in or out of the account is recorded here.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/transactions

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
type STRING
test BOOLEAN
payout_id NUMERIC
payout_status STRING
currency STRING
amount STRING
fee STRING
net STRING
source_id NUMERIC
source_type STRING
processed_at TIMESTAMP

collects

Purpose: This table retrieves a list of collects associated with the shopify store

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/collect

Replication: Full Replication

Replication Key: N/A

Name Data Type
id NUMERIC
collection_id NUMERIC
product_id NUMERIC
created_at TIMESTAMP
updated_at TIMESTAMP
position NUMERIC
sort_value STRING

custom_collections

Purpose: This table retrieves a list of custom collections.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/customcollection#get-custom-collections?ids=395646240,691652237,841564295

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
handle STRING
title STRING
updated_at TIMESTAMP
body_html STRING
published_at TIMESTAMP
sort_order STRING
published_scope STRING
admin_graphql_api_id STRING
image RECORD

smart_collections

Purpose: This table retrieves a list of smart collections.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/smartcollection#get-smart-collections?since-id=482865238

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
handle STRING
title STRING
updated_at TIMESTAMP
published_at TIMESTAMP
sort_order STRING
disjunctive BOOLEAN
rules RECORD
published_scope STRING
admin_graphql_api_id STRING

abandoned_checkouts

Purpose: This table retrieves a count of checkouts from the past 90 days

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/abandoned-checkouts#get-checkouts?created-at-max=2013-10-12T07:05:27-02:00

Replication: Key Based Incremental

Replication Key: updated_at_min, updated_at_max

Column Name Data Type
id NUMERIC
token STRING
cart_token STRING
email STRING
buyer_accepts_marketing BOOLEAN
created_at TIMESTAMP
updated_at TIMESTAMP
landing_site STRING
note STRING
note_attributes RECORD
referring_site STRING
shipping_lines RECORD
taxes_included BOOLEAN
total_weight NUMERIC
currency STRING
completed_at TIMESTAMP
line_items RECORD
name STRING
abandoned_checkout_url STRING
source_name STRING
presentment_currency STRING
buyer_accepts_sms_marketing BOOLEAN
total_discounts STRING
total_line_items_price STRING
total_price STRING
total_tax STRING
subtotal_price STRING
billing_address RECORD
shipping_address RECORD
customer RECORD

policies

Purpose: You can use the Policy resource to access the policies that a merchant has configured for their shop, such as their refund and privacy policies.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/policy

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
body STRING
created_at TIMESTAMP
updated_at TIMESTAMP
handle STRING
title STRING
url STRING

countries

Purpose:  The Country resource represents the tax rates applied to orders from the different countries where a shop sells its products.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/country

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
name STRING
code STRING
tax_name STRING
tax NUMERIC
provinces RECORD

shop

Purpose: The Shop resource is a collection of general business and store management settings and information about the store. The resource lets you retrieve information about the store, but it doesn't let you update any information.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/shop

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
address1 STRING
zip STRING
city STRING
phone STRING
latitude NUMERIC
longitude NUMERIC
primary_locale STRING
address2 STRING
created_at TIMESTAMP
updated_at TIMESTAMP
country_code STRING
country_name STRING
currency STRING
customer_email STRING
timezone STRING
iana_timezone STRING
shop_owner STRING
money_format STRING
money_with_currency_format STRING
weight_unit STRING
province_code STRING
county_taxes BOOLEAN
plan_display_name STRING
plan_name STRING
has_discounts BOOLEAN
has_gift_cards BOOLEAN
myshopify_domain STRING
money_in_emails_format STRING
money_with_currency_in_emails_format STRING
eligible_for_payments BOOLEAN
requires_extra_payments_agreement BOOLEAN
password_enabled BOOLEAN
has_storefront BOOLEAN
finances BOOLEAN
primary_location_id NUMERIC
cookie_consent_level STRING
visitor_tracking_consent_preference STRING
checkout_api_supported BOOLEAN
multi_location_enabled BOOLEAN
setup_required BOOLEAN
pre_launch_enabled BOOLEAN
enabled_presentment_currencies STRING
transactional_sms_disabled BOOLEAN
marketing_sms_consent_enabled_at_checkout BOOLEAN

locations

Purpose: A location represents a geographical location where your stores, pop-up stores, headquarters, and warehouses exist. This table retrieves a list of locations

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/location#get-locations

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
name STRING
address1 STRING
city STRING
zip STRING
province STRING
country STRING
created_at TIMESTAMP
updated_at TIMESTAMP
country_code STRING
country_name STRING
province_code STRING
legacy BOOLEAN
active BOOLEAN
admin_graphql_api_id STRING
localized_country_name STRING
localized_province_name STRING

inventory_levels

Purpose: An inventory level represents the quantities of an inventory item for a location. Each inventory level belongs to one inventory item and has one location. This table fetches all the inventory_levels in all locations

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/inventorylevel#get-inventory-levels?location-ids=655441491

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
inventory_item_id NUMERIC
location_id NUMERIC
available NUMERIC
updated_at TIMESTAMP
admin_graphql_api_id STRING

inventory_items

Purpose: An inventory item represents a physical good. It holds essential information about the physical good, including its SKU and whether its inventory is tracked. There is a 1:1 relationship between a product variant and an inventory item. This table fetches all the inventory_items

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/inventoryitem#get-inventory-items?ids=808950810,39072856,457924702

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
sku STRING
created_at TIMESTAMP
updated_at TIMESTAMP
requires_shipping STRING
cost STRING
tracked BOOLEAN
admin_graphql_api_id STRING

gift_cards

Purpose: In order to use this API, you need to reach out to the Merchant Success Manager and ask them to enable the gift cards API. This table fetches all the gift cards associated with the store

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/gift-card

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
balance STRING
created_at TIMESTAMP
updated_at TIMESTAMP
currency STRING
initial_value STRING
disabled_at TIMESTAMP
line_item_id NUMERIC
api_client_id NUMERIC
user_id NUMERIC
customer_id NUMERIC
note STRING
template_suffix STRING
expires_on DATE
last_characters STRING
order_id NUMERIC

carrier_services

Purpose: A carrier service (also known as a carrier calculated service or shipping service) provides real-time shipping rates to Shopify. Some common carrier services include Canada Post, FedEx, UPS, and USPS.

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/carrierservice#get-carrier-services

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
name STRING
active BOOLEAN
service_discovery BOOLEAN
carrier_service_type STRING
admin_graphql_api_id STRING

price_rules

Purpose: This table lists all the price_rules associated with the entered shopify store 

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/pricerule#get-price-rules

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
value_type STRING
value STRING
customer_selection STRING
target_type STRING
target_selection STRING
allocation_method STRING
once_per_customer BOOLEAN
starts_at TIMESTAMP
ends_at TIMESTAMP
created_at TIMESTAMP
updated_at TIMESTAMP
title STRING
admin_graphql_api_id STRING

disputes

Purpose: This table lists all the disputes associated with the entered shopify store

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-04/resources/dispute#get-shopify-payments-disputes?initiated-at=2013-05-03

Replication: Full Replication

Replication Key: N/A

Column Name Data Type
id NUMERIC
order_id NUMERIC
type STRING
amount STRING
currency STRING
reason STRING
network_reason_code STRING
status STRING
evidence_due_by TIMESTAMP
evidence_sent_on TIMESTAMP
finalized_on TIMESTAMP
initiated_at TIMESTAMP

order_customerjourneysummary

Purpose: This table has all the data related to customer's visiting activities on a shop's online store associated with the orders placed.

Note: The Sessions data is huge so currently, we only provide support for a 60-day history for this specific table. If you require data from a period before these 60 days, you will need to have the "read_all_orders" scope. Feel free to contact us if you require access to a longer history of data.

Scope: This table fetches

Source API Documentation: https://shopify.dev/docs/api/admin-graphql/2023-07/objects/Order

Replication: Key Based Incremental

Replication Key: created_at

Column Name Data Type
momentsCount NUMERIC
moments RECORD
daysToConversion NUMERIC
customerOrderIndex NUMERIC
name STRING
id STRING
landingPageUrl STRING
customer RECORD
createdAt TIMESTAMP
confirmed BOOLEAN
closedAt TIMESTAMP
closed BOOLEAN
capturable BOOLEAN

product_sessiondata

Purpose: This table has data to analyse about product performance. This dataset combines sales and sessions data at a product_id grain to provide a holistic view of product performance.

Note: The Sessions data is huge so currently we only provide support for a 60-day history for this specific table. Reach out to us if you require access to a longer history of data.

Source API Documentation: https://shopify.dev/docs/api/shopifyql/datasets/products-dataset

Replication: Key Based Incremental

Replication Key: date of product sold(since, until)

Column Name Data Type
product_id NUMERIC
product_title STRING
product_type STRING
view_sessions NUMERIC
cart_sessions NUMERIC
checkout_sessions NUMERIC
purchase_sessions NUMERIC
view_cart_sessions NUMERIC
view_cart_checkout_sessions NUMERIC
view_cart_checkout_purchase_sessions NUMERIC
checkout_purchase_sessions NUMERIC
view_purchase_sessions NUMERIC
quantity_added_to_cart NUMERIC
quantity_purchased NUMERIC
gross_sales NUMERIC
ordered_product_quantity NUMERIC
returned_product_quantity NUMERIC
net_product_quantity NUMERIC
discounts NUMERIC
returns NUMERIC
net_sales NUMERIC
taxes NUMERIC
product_price NUMERIC
product_price_after_discounts NUMERIC

users

Purpose: This table provides all users for the shopify store.In order to use this API, you need to reach out to the Merchant Success Manager and ask them to enable the read_users scope

Source API Documentation: https://shopify.dev/docs/api/admin-rest/2023-07/resources/user#get-users

Replication: Full Replication

Replication Key: NA

Column Name Data Type
id NUMERIC
first_name STRING
email STRING
last_name STRING
account_owner BOOLEAN
receive_announcements NUMERIC
permissions STRING
locale STRING
user_type STRING
admin_graphql_api_id STRING
tfa_enabled BOOLEAN