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:

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

  • 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

  • Update Method: UPSERT

  • Table Name: [shopify.custom_collections]

JSON ElementDatabase 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 support@daasity.com 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 support@daasity.com 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

  • Update Method: UPSERT

  • Table Name: [shopify.inventory_levels]

JSON ElementDatabase 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 ElementDatabase 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 ElementDatabase 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 ElementDatabase 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 ElementDatabase 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 ElementDatabase 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 support@daasity.com to enable extraction to this table.

JSON ElementDatabase 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 ElementDatabase 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 support@daasity.com to enable extraction to this table.

JSON ElementDatabase 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 support@daasity.com to enable extraction to this table.

JSON ElementDatabase 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