Schema Information for Shopify

Pulse
Daton

Need help with something?

Talk to data expert

Schema Information for Shopify

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

Table of Contents

Tables/APIs Supported orders transactions refunds fulfillment_orders fulfillment_events tender_transactions customers customer_address events products payouts balance_transactions collects custom_collections smart_collections abandoned_checkouts policies countries shop locations inventory_levels inventory_items gift_cards carrier_services price_rules disputes order_customerjourneysummary product_sessiondata users

Schema Information for Shopify

Pulse

Daton