Integration Specifications

This page will help you learn about how Daasity replicates data from Shopify, the limitations of the data we can extract, and where the data is stored in the Shopify schema.

Integration Overview

Shopify is a leading e-commerce platform that empowers businesses to easily create and manage online stores, offering built-in analytics and seamless integration with third-party tools for data-driven insights.

This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.

Integration Availability

This integration is available for:

• Enterprise

• Growth

API Endpoints

The Daasity Shopify extractor is based on the Shopify REST API and Shopify GraphQL documentation.

Daasity is currently using the 2022-04 version of the REST and GraphQL APIs.

The following endpoints are used by Daasity to replicate data from Shopify:

• Customers

  • Customer

• Discounts

  • Price Rules

  • Discount Codes

• Inventory

  • Inventory Items

  • Inventory Levels

  • Locations

• Orders

  • Order

  • Order (GraphQL)

  • Refund

  • Transaction

• Plus (Available only with the Daasity Shopify Plus extractor)

  • Gift Card

  • User (Please contact [email protected] to enable this endpoint.)

• Products

  • Collect

  • Custom Collection

  • Product

• Shopify Payments

  • Payouts

  • Dispute

  • Transactions (Please contact [email protected] to enable this endpoint.)

• Store Properties

  • Shop

• Tender Transaction

  • Tender Transaction (Please contact [email protected] to enable this endpoint.)

The Orders endpoint runs each hour and all other endpoints are updated daily unless requested otherwise.

Entity Relationship Diagram (ERD)

Click here to view the ERD for the Daasity Shopify integration illustrating the different tables and keys to join across tables.

Shopify & Shopify Plus Schema

The Daasity Shopify extractor creates these tables using the endpoints and replication methods listed. The data is mapped from the source API endpoint to the table based on the mapping logic outlined in each table.

• Collects

• Custom Collections

• Customer Addresses

• Customers

• Discount Codes

• Disputes

• Fulfillment Order Items

• Gift Cards

• Gift Cards Adjustments

• Inventory Items

• Inventory Items Country Harmonized System Codes

• Inventory Levels

• Locations

• Orders

• Order Discount Applications

• Order Discount Codes

• Order Fulfillments

• Order Line Items

• Order Line Duties

• Order Line Duty Tax Lines

• Order Line Item Discount Allocations

• Order Line Item Properties

• Order Note Attributes

• Order Payment Gateway Names

• Order Refund Transactions

• Order Sales Agreements

• Order Shipping Lines

• Order Shipping Line Tax Lines

• Order Tax Lines

• Payment Transactions

• Payouts

• Price Rules

• Products

• Product Images

• Product Variants

• Refunds

• Refund Line Items

• Refund Order Adjustments

• Shops

• Tender Transactions

• Transaction Fee

• Transactions

• Transactions Receipt

• Users

• User Permissions

Collects

• Endpoint: Collect

• Update Method: UPSERT

• Table Name: [shopify.collects]

JSON Element	Database Column
id	collect_id
collection_id	collection_id
product_id	product_id
position	position
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Custom Collections

• Endpoint: Custom Collection

• Update Method: UPSERT

• Table Name: [shopify.custom_collections]

JSON Element	Database Column
id	custom_collection_id
handle	handle
title	title
sort_order	sort_order
published_at	published_at
published_scope	published_scope
updated_at	updated_at
shop::id	shop_id

Customer Addresses

• Endpoint: Customer

• Update Method: UPSERT

• Table Name: [shopify.customer_addresses]

JSON Element	Database Column
id	address_id
customer_id	customer_id
first_name	first_name
last_name	last_name
company	company
address1	address1
address2	address2
city	city
province	state
country	country
zip	zipcode
phone	phone_number
province_code	state_code
country_code	country_code
country_name	country_name
shop::id	shop_id

Customers

• Endpoint: Customer

• Update Method: UPSERT

• Table Name: [shopify.customers]

JSON Element	Database Column
id	customer_id
accepts_marketing	accepts_marketing
default_address::id	address_id
default_address::company	company
default_address::address1	address1
default_address::address2	address2
default_address::city	city
default_address::province	state
default_address::country	country
default_address::zip	zipcode
default_address::phone	phone_number
default_address::province_code	state_code
default_address::country_code	country_code
default_address::country_name	country_name
email	email
first_name	first_name
last_name	last_name
note	note
state	account_status
tags	tags
verified_email	verified_email
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Discount Codes

• Endpoint: Discount Codes

• Update Method: UPSERT

• Table Name: [shopify.discount_codes]

JSON Element	Database Column
id	discount_code_id
code	discount_code
price_rule_id	price_rule_id
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Disputes

• Endpoint: Dispute

• Update Method: UPSERT

• Table Name: [shopify.disputes]

JSON Element	Database Column
id	dispute_id
order_id	order_id
type	type
currency	currency
amount	amount
reason	reason
network_reason_code	network_reason_code
status	status
initiated_at	initiated_at
evidence_due_by	evidence_due_by
evidence_sent_on	evidence_sent_on
finalized_on	finalized_on

Fulfillment Order Items

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.fulfillment_order_items]

JSON Element	Database Column
id	fulfillment_id
order_id	order_id
line_item::id	order_line_id
line_item::variant_id	variant_id
line_item::sku	sku
line_item::quantity	quantity
line_item::fulfillable_quantity	fulfillable_quantity
line_item::fulfillment_status	fulfillment_status
created_at	created_at
updated_at	updated_at
shop::id	shop_id
MD5(fulfillment_id + order_line_id + order_id)	__sync_key

Gift Cards

• Endpoint: Gift Card

• Update Method: UPSERT

• Table Name: [shopify.gift_cards]

NOTE: This table is only available for merchants on Shopify Plus.

Please contact [email protected] to enable this table, if you are a Shopify Plus merchant.

JSON Element	Database Column
id	id
balance	balance
created_at	created_at
updated_at	updated_at
currency	currency
initial_value	initial_value
disabled_at	disabled_at
line_item_id	line_item_id
api_client_id	api_client_id
user_id	user_id
customer_id	customer_id
note	note
expires_on	expires_on
template_suffix	template_suffix
last_characters	last_characters
order_id	order_id
shop::id	shop_id

Gift Card Adjustments

• Endpoint: Gift Card

• Update Method: UPSERT

• Table Name: [shopify.gift_card_adjustments]

NOTE: This table is only available for merchants on Shopify Plus.

Please contact [email protected] to enable this table, if you are a Shopify Plus merchant.

JSON Element	Database Column
id	id
gift_card_id	gift_card_id
number	number
amount	amount
note	note
remote_transaction_ref	remote_transaction_ref
remote_transaction_url	remote_transaction_url
api_client_id	api_client_id
user_id	user_id
order_transaction_id	order_transaction_id
created_at	created_at
updated_at	updated_at
processed_at	processed_at
shop::id	shop_id
MD5(shop_id + order_id + tax_type)	__sync_key

Inventory Items

• Endpoint: Inventory Items

• Update Method: UPSERT

• Table Name: [shopify.inventory_items]

JSON Element	Database Column
id	inventory_item_id
variant_id	variant_id
sku	sku
cost	sku_cost
requires_shipping	required_shipping
tracked	tracked
country_code_of_origin	country_of_origin
created_at	created_at
updated_at	updated_at

Inventory Items Country Harmonized System Codes

• Endpoint: Inventory Items

• Update Method: UPSERT

• Table Name: [shopify.inventory_items_country_harmonized_system_codes]

JSON Element	Database Column
inventory_item_id	inventory_item_id
harmonized_system_code	harmonized_system_code
country_code	country-code
country_harmonized_system_code	country_harmonized_system_code

Inventory Levels

• Endpoint: Inventory Levels

• Update Method: UPSERT

• Table Name: [shopify.inventory_levels]

JSON Element	Database Column
inventory_item_id	inventory_item_id
location_id	location_id
available	inventory_quantity
updated_at	updated_at
shop::id	shop_id
MD5(shop_id + inventory_item_id)	_key

Locations

• Endpoint: Locations

• Update Method: UPSERT

• Table Name: [shopify.locations]

JSON Element	Database Column
id	location_id
name	location_name
address1	address1
address2	address2
city	city
province	state
country	country
zip	zipcode
phone	phone_number
deleted_at	deleted_at
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Orders

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.orders]

JSON Element	Database Column
id	order_id
app_id	app_id
browser_ip	browser_ip
cancel_reason	cancel_reason
cancelled_at	cancelled_at
cart_token	cart_token
currency	currency
customer.id	customer_id
customer_locale	customer_locale
email	email
financial_status	financial_status
fulfillment_status	fulfillment_status
tags	tags
name	order_code
note	note
phone	phone_number
referring_site	referring_site
source_name	source_name
subtotal_price	subtotal_price
total_discounts	total_discounts
total_line_items_price	total_line_items_price
total_price	amount_charged
total_tax	total_tax
total_weight	total_weight
location_id	location_id
billing_address::name	billing_name
billing_address::company	billing_company
billing_address::address1	billing_address1
billing_address::address2	billing_address2
billing_address::city	billing_city
billing_address::province	billing_state
billing_address::country	billing_country
billing_address::zip	billing_zipcode
billing_address::phone	billing_phone
billing_address::province_code	billing_state_code
billing_address::country_code	billing_country_code
billing_address::first_name	billing_first_name
billing_address::last_name	billing_last_name
billing_address::latitude	billing_latitude
billing_address::longitude	billing_longitude
shipping_address::name	shipping_name
shipping_address::company	shipping_company
shipping_address::address1	shipping_address1
shipping_address::address2	shipping_address2
shipping_address::city	shipping_city
shipping_address::province	shipping_state
shipping_address::country	shipping_country
shipping_address::zip	shipping_zipcode
shipping_address::phone	shipping_phone
shipping_address::province_code	shipping_state_code
shipping_address::country_code	shipping_country_code
shipping_address::first_name	shipping_first_name
shipping_address::last_name	shipping_last_name
shipping_address::latitude	shipping_latitude
shipping_address::longitude	shipping_longitude
processed_at	processed_at
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Order Discount Applications

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_discount_applications]

JSON Element	Database Column
order_id	order_id
shop::id	shop_id
discount_application_index	discount_application_index
value	value
value_type	value_type
allocation_method	allocation_method
target_selection	target_selection
target_type	target_type
title	title
description	description

Order Discount Codes

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_discount_codes]

JSON Element	Database Column
order_id	order_id
amount	amount
code	code
type	type
shop::id	shop_id
MD5(shop_id + order_id + code)	__sync_key

Order Fulfillments

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_fulfillments]

JSON Element	Database Column
id	order_fulfillment_id
order_id	order_id
location_id	location_id
status	fulfillment_status
shipment_status	shipment_status
tracking_company	tracking_company
tracking_number	tracking_number
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Order Line Items

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_line_items]

JSON Element	Database Column
id	order_line_id
order_id	order_id
fulfillment_status	fulfillment_status
fulfillment_id	fulfillment_id
price	price
product_id	product_id
quantity	quantity
sku	sku
requires_shipping	requires_shipping
title	product_title
variant_id	variant_id
vendor	vendor
name	product_name
taxable	taxable_flag
total_discount	discount_amount
gift_card	gift_card
_tax_lines_total	tax_lines_total
shop::id	shop_id

Order Line Duties

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_line_duties]

JSON Element	Database Column
id	id
order_id	order_id
order_line_id	order_line_id
admin_graphql_api_id	admin_graphql_api_id
country_code_of_origin	country_code_of_origin
harmonized_system_code	harmonized_system_code
amount	amount
shop::id	shop_id

Order Line Duty Tax Lines

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_line_duty_tax_lines]

JSON Element	Database Column
order_id	order_id
order_line_id	order_line_id
order_line_duty_id	order_line_duty_id
price	price
rate	rate
title	title
shop::id	shop_id

Order Line Item Discount Allocations

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_line_item_discount_allocations]

JSON Element	Database Column
order_id	order_id
order_line_id	order_line_id
amount	amount
discount_application_index	discount_application_index
shop::id	shop_id
MD5(shop_id + order_id + order_line_id + discount_applicaton_index)	__sync_key

Order Line Item Properties

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_line_item_properties]

JSON Element	Database Column
id	order_line_id
order_id	order_id
name	name
value	value
MD5(shop_id + order_id + order_line_id + name)	_key

Order Metafields

• Endpoint: Metafield (filtered for ownerType is ORDER)

• Update Method: UPSERT

• Table Name: [shopify.order_metafields]

If you set up your Shopify integration before July 2024, you need to contact [email protected] to enable extraction to this table.

JSON Element	Database Column
id	metafield_id
owner	order_id
namespace	namespace
key	key
value	value
description	description
type	_type
shop::id	shop_id

Order Note Attributes

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_note_attributes]

JSON Element	Database Column
order_id	order_id
name	name
value	value
shop::id	shop_id
MD5(shop_id + order_id + name)	_key

Order Payment Gateway Names

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_payment_gateway_names]

JSON Element	Database Column
order_id	order_id
value	value
shop::id	shop_id
MD5(shop_id + order_id + value)	__sync_key

Order Refund Transactions

• Endpoint: Refund

• Update Method: UPSERT

• Table Name: [shopify.order_refund_transactions]

JSON Element	Database Column
order_id	order_id
refund_id	refund_id
amount	refund_amount
currency	currency_code
gateway	gateway
source_name	source_name
status	status
created_at	created_at
updated_at	updated_at
shop::id	shop_id
MD5(shop_id + order_id + tax_type)	__sync_key

Order Sales Agreements

• Endpoint: Order (GraphQL)

• Update Method: UPSERT

• Table Name: [shopify.order_sales_agreements]

JSON Element	Database Column
id	id
happened_at	happened_at
order_id	order_id
sale_id	sale_id
action_type	action_type
line_type	line_type
quantity	quantity
sale::totalAmount::shopMoney::amount	shop_money_amount
sale::totalAmount::presentmentMoney::amount	presentment_money_amount
sale::lineItem::id	line_id
sale::lineItem::name	line_name
shop::id	shop_id

Order Shipping Lines

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_shipping_lines]

JSON Element	Database Column
id	shipping_line_id
order_id	order_id
code	shipping_code
price	shipping_amount
source	source
title	shipping_title
carrier_identifier	carrier_identifier
SUM(tax_line::amount)	tax_lines_total
SUM(discount_allocations::amount)	discount_allocations_total
shop::id	shop_id

Order Shipping Line Tax Lines

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_shipping_line_tax_lines]

JSON Element	Database Column
order_id	order_id
shipping_line_id	shipping_line_id
channel_liable	channel_liable
price	price
presentment_money_amount	presentment_money_amount
presentment_money_currency_code	presentment_money_currency_code
shop_money_amount	shop_money_amount
shop_money_currency_code	shop_money_currency_code
rate	rate
title	title
shop::id	shop_id

Order Tax Lines

• Endpoint: Order

• Update Method: UPSERT

• Table Name: [shopify.order_tax_lines]

JSON Element	Database Column
order_id	order_id
price	tax_amount
rate	tax_rate
title	tax_type
shop::id	shop_id
MD5(shop_id + order_id + tax_type)	__sync_key

Payment Transactions

• Endpoint: Transaction

• Update Method: UPSERT

• Table Name: [shopify.payment_transactions]

JSON Element	Database Column
id	id
type	type
test	test
payout_id	payout_id
payout_status	payout_status
currency	currency
amount	amount
fee	fee
net	net
source_id	source_id
source_type	source_type
source_order_transaction_id	source_order_transaction_id
source_order_id	source_order_id
processed_at	processed_at
shop::id	shop_id

Payouts

• Endpoint: Payouts

• Update Method: UPSERT

• Table Name: [shopify.payouts]

JSON Element	Database Column
id	payout_id
status	status
date	payout_date
currency	currency
amount	amount
summary::adjustments_fee_amount	adjustments_fee_amount
summary::adjustments_gross_amount	adjustments_gross_amount
summary::charges_fee_amount	charges_fee_amount
summary::refunds_fee_amount	refunds_fee_amount
summary::refunds_gross_amount	refunds_gross_amount
summary::reserved_funds_fee_amount	reserved_fee_amount
summary::reserved_funds_gross_amount	reserved_gross_amount
summary::retried_payouts_fee_amount	retried_payouts_fee_amount
summary::retried_payouts_gross_amount	retried_payouts_gross_amount

Price Rules

• Endpoint: Price Rules

• Update Method: UPSERT

• Table Name: [shopify.price_rules]

JSON Element	Database Column
id	price_rule_id
title	title
target_type	target_type
target_selection	target_selection
allocation_method	allocation_method
value_type	value_type
value	value
once_per_customer	once_per_customer
usage_limit	usage_limit
customer_selection	customer_selection
starts_at	price_rule_starts_at
ends_at	price_rule_ends_at
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Products

• Endpoint: Product

• Update Method: UPSERT

• Table Name: [shopify.products]

JSON Element	Database Column
id	product_id
handle	product_handle
product_type	product_type
tags	tags
title	product_name
vendor	vendor_name
published_scope	published_scope
published_at	published_at
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Product Images

• Endpoint: Product

• Update Method: UPSERT

• Table Name: [shopify.product_images]

JSON Element	Database Column
id	product_image_id
product_id	product_id
position	position
src	image_url
width	image_width
height	image_height
created_at	created_at
updated_at	updated_at

Product Metafields

• Endpoint: Metafield (filtered for ownerType is PRODUCT)

• Update Method: UPSERT

• Table Name: [shopify.product_metafields]

If you set up your Shopify integration before July 2024, you need to contact [email protected] to enable extraction to this table.

JSON Element	Database Column
id	metafield_id
owner	product_id
namespace	namespace
key	key
value	value
description	description
type	_type
shop::id	shop_id

Product Variant Metafields

• Endpoint: Metafield (filtered for ownerType is PRODUCTVARIANT)

• Update Method: UPSERT

• Table Name: [shopify.product_variant_metafields]

If you set up your Shopify integration before July 2024, you need to contact [email protected] to enable extraction to this table.

JSON Element	Database Column
id	metafield_id
owner	product_variant_id
namespace	namespace
key	key
value	value
description	description
type	_type
shop::id	shop_id

Product Variants

• Endpoint: Product

• Update Method: UPSERT

• Table Name: [shopify.product_variants]

Refunds

• Endpoint: Refund

• Update Method: UPSERT

• Table Name: [shopify.refunds]

JSON Element	Database Column
id	refund_id
order_id	order_id
note	note
restock	restock
processed_at	processed_at
created_at	created_at
shop::id	shop_id

Refund Line Items

• Endpoint: Refund

• Update Method: UPSERT

• Table Name: [shopify.refund_line_items]

JSON Element	Database Column
id	refund_line_item_id
refund::order_id	order_id
refund::id	refund_id
line_item_id	line_item_id
quantity	quantity
refund::created_at	created_at
subtotal	subtotal
total_tax	total_tax
line_item::fulfillment_service	line_item_fulfillment_service
shop::id	shop_id

Refund Order Adjustments

• Endpoint: Refund

• Update Method: UPSERT

• Table Name: [shopify.refund_order_adjustments]

JSON Element	Database Column
id	refund_order_adjustment_id
order_id	order_id
refund_id	refund_id
amount	amount
kind	kind
reason	reason
tax_amount	tax_amount
shop::id	shop_id

Shops

• Endpoint: Shop

• Update Method: UPSERT

• Table Name: [shopify.shops]

JSON Element	Database Column
id	id
name	name
email	email
customer_email	customer_email
domain	domain
myshopify_domain	myshopify_domain
address1	address1
city	city
province	state
country	country
zip	zipcode
phone	phone_number
country_code	country_code
latitude	latitude
longitude	longitude
currency	currency
iana_timezone	timezone_name
shop_owner	shop_owner
multi_location_enabled	multi_location_enabled
created_at	created_at
updated_at	updated_at
shop::id	shop_id

Tender Transactions

• Endpoint: Tender Transaction

• Update Method: UPSERT

• Table Name: [shopify.tender_transactions]

NOTE: Please contact [email protected] to enable this table.

JSON Element	Database Column
id	id
order_id	order_id
amount	amount
currency	currency
user_id	user_id
test	test
processed_at	processed_at
remote_reference	remote_reference
payment_details::credit_card_number	payment_details_credit_card_number
payment_details::credit_card_company	payment_details_credit_card_company
payment_method	payment_method
shop::id	shop::id

Transaction Fee

• Endpoint: Transactions

• Update Method: UPSERT

• Table Name: [shopify.transaction_fee]

NOTE: Please contact [email protected] to enable this table.

JSON Element	Database Column
id	id
order_id	order_id
fee:amount	amount
fee:amount::currencyCode	amount_currency_code
fee:flatFee::amount	flat_fee_amount
fee:flatFee::currencyCode	flat_fee_currency_code
fee:flatFeeName	flat_fee_name
fee:rate	rate
fee::rateName	rate_name
fee:taxAmount::amount	tax_amount
fee:taxAmount::currencyCode	tax_amount_currency_code
fee:type	type
shop::id	shop_id

Transactions

• Endpoint: Transactions

• Update Method: UPSERT

• Table Name: [shopify.transactions]

JSON Element	Database Column
id	transaction_id
order_id	order_id
amount	transaction_amount
authorization	authorization
currency	currency_code
error_code	error_code
gateway	gateway
kind	transaction_type
payment_details::avs_result_code	avs_result_code
payment_details::credit_card_bin	credit_card_bin
payment_details::credit_card_company	credit_card_company
payment_details::cvv_result_code	cvv_result_code
receipt::gift_card_id	receipt_gift_card_id
source_name	source_name
status	status
test	test_transaction
processed_at	processed_at
created_at	created_at
shop::id	shop_id

Transactions Receipt

• Endpoint: Transactions

• Update Method: UPSERT

• Table Name: [shopify.transactions_receipt]

NOTE: Please contact [email protected] to enable this table.

JSON Element	Database Column
transaction_id	transaction_id
order_id	order_id
name	name
value	value

Users

• Endpoint: User

• Update Method: UPSERT

• Table Name: [shopify.users]

NOTE: This table is only available for merchants on Shopify Plus.

Please contact [email protected] to enable this table, if you are a Shopify Plus merchant.

JSON Element	Database Column
id	id
account_number	account_number
bio	bio
email	email
first_name	first_name
im	im
last_name	last_name
phone	phone
receive_announcements	receive_announcements
screen_name	screen_name
url	url
locale	locale
user_type	user_type
store	store
shop::id	shop_id

User Permissions

• Endpoint: User

• Update Method: UPSERT

• Table Name: [shopify.users]

NOTE: This table is only available for merchants on Shopify Plus.

Please contact [email protected] to enable this table, if you are a Shopify Plus merchant.

JSON Element	Database Column
user_id	user_id
permission	value
shop::id	shop_id