JSData & Media Start a project

← All connectors

Schema · Entity relationship diagram

Shopify Products & Inventory tables and relationships

Products with options, tags and media, variants, collections and their products, inventory items with levels and quantities (available, on hand, committed, incoming ...) by location, locations, and product and collection metafields. Tables and columns are named after the Shopify Admin API and share one shopify schema with Shopify Orders, Shopify Customers, Shopify Payments & Payouts, Shopify Checkouts & Discounts, Shopify Analytics.

Live14 tables · 16 relationships
ConnectPaste an API key
Default schemashopify
Source API docshelp.shopify.com
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
  • The six Shopify connectors load into one shopify schema. The others: Shopify Orders, Shopify Customers, Shopify Payments & Payouts, Shopify Checkouts & Discounts, Shopify Analytics. Dashed boxes are their tables.
  • Tables and columns are named after the Shopify Admin GraphQL API (2026-07): a table per object (Order → orders, its lineItems → order_line_items), a column per field path in snake_case (totalPriceSet.shopMoney.amount → total_price_set_shop_money_amount). Ids are the number in Shopify's global id, so they join across connectors.
  • Lists inside an object (tags, tax lines, custom attributes ...) are child tables keyed by the parent id and index, replaced as a set whenever the parent changes. _jsdata_synced is the load time; _jsdata_deleted is always false.
  • Inventory, collections, locations and metafields are re-read in full every 6 hours (inventory levels change without the product changing); products and variants sync incrementally.
product_tags.product_id → products.idproduct_options.product_id → products.idproduct_option_values.product_option_id → product_options.idproduct_variants.product_id → products.idproduct_variants.inventory_item_id → inventory_items.idproduct_media.product_id → products.idcollection_products.collection_id → collections.idcollection_products.product_id → products.idinventory_levels.inventory_item_id → inventory_items.idinventory_levels.location_id → locations.idinventory_quantities.inventory_level_id → inventory_levels.idproduct_metafields.product_id → products.idcollection_metafields.collection_id → collections.idorder_line_items.variant_id → product_variants.idorder_line_items.product_id → products.idproducts.featured_media_id → product_media.idproduct_option_valuesproduct_option_values7id BIGINT · primary keyidbigintproduct_option_id BIGINT · → product_options.idproduct_option_idbigintindex BIGINTindexbigintname VARCHAR(65535)namevarcharhas_variants BOOLEANhas_variantsbool_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampcollection_metafieldscollection_metafields11id BIGINT · primary keyidbigintcollection_id BIGINT · → collections.idcollection_idbiginttypename VARCHAR(256)typenamevarcharnamespace VARCHAR(65535)namespacevarcharkey VARCHAR(65535)keyvarchartype VARCHAR(65535)typevarcharvalue VARCHAR(65535)valuevarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampproduct_optionsproduct_options7id BIGINT · primary keyidbigintproduct_id BIGINT · → products.idproduct_idbigintindex BIGINTindexbigintname VARCHAR(65535)namevarcharposition BIGINTpositionbigint_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampcollectionscollections9id BIGINT · primary keyidbiginttitle VARCHAR(65535)titlevarcharhandle VARCHAR(65535)handlevarcharupdated_at TIMESTAMPupdated_attimestampsort_order VARCHAR(256)sort_ordervarchartemplate_suffix VARCHAR(65535)template_suffixvarcharproducts_count_count BIGINTproducts_count_countbigint_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestamporder_line_items (Shopify Orders)order_line_items (Shopify Orders)extid BIGINT · primary keyidbigintproduct_id BIGINT · → products.idproduct_idbigintvariant_id BIGINT · → product_variants.idvariant_idbigintorder_id BIGINTorder_idbigintquantity BIGINTquantitybigintproduct_variantsproduct_variants20id BIGINT · primary keyidbigintproduct_id BIGINT · → products.idproduct_idbigintinventory_item_id BIGINT · → inventory_items.idinventory_item_idbiginttypename VARCHAR(256)typenamevarchartitle VARCHAR(65535)titlevarchardisplay_name VARCHAR(65535)display_namevarcharsku VARCHAR(65535)skuvarcharbarcode VARCHAR(65535)barcodevarcharposition BIGINTpositionbigintprice DECIMAL(38,9)pricedecimalcompare_at_price DECIMAL(38,9)compare_at_pricedecimaltaxable BOOLEANtaxableboolavailable_for_sale BOOLEANavailable_for_saleboolinventory_quantity BIGINTinventory_quantitybigintinventory_policy VARCHAR(256)inventory_policyvarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestampselected_options VARCHAR(65535)selected_optionsvarchar_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestamplocationslocations23id BIGINT · primary keyidbigintname VARCHAR(65535)namevarcharis_active BOOLEANis_activeboolis_fulfillment_service BOOLEANis_fulfillment_serviceboolfulfills_online_orders BOOLEANfulfills_online_ordersboolships_inventory BOOLEANships_inventoryboolhas_active_inventory BOOLEANhas_active_inventoryboolcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestampdeactivated_at VARCHAR(65535)deactivated_atvarcharaddress_address1 VARCHAR(65535)address_address1varcharaddress_address2 VARCHAR(65535)address_address2varcharaddress_city VARCHAR(65535)address_cityvarcharaddress_province VARCHAR(65535)address_provincevarcharaddress_province_code VARCHAR(65535)address_province_codevarcharaddress_country VARCHAR(65535)address_countryvarcharaddress_country_code VARCHAR(65535)address_country_codevarcharaddress_zip VARCHAR(65535)address_zipvarcharaddress_phone VARCHAR(65535)address_phonevarcharaddress_latitude DECIMAL(38,9)address_latitudedecimal+3 more (see below) +3 more (see below)collection_productscollection_products5collection_id BIGINT · primary key · → collections.idcollection_idbigintproduct_id BIGINT · primary key · → products.idproduct_idbiginttypename VARCHAR(256)typenamevarchar_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampinventory_levelsinventory_levels9id VARCHAR(256) · primary keyidvarcharinventory_item_id BIGINT · → inventory_items.idinventory_item_idbigintlocation_id BIGINT · → locations.idlocation_idbiginttypename VARCHAR(256)typenamevarcharis_active BOOLEANis_activeboolcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampproduct_mediaproduct_media11id BIGINT · primary keyidbigintproduct_id BIGINT · → products.idproduct_idbiginttypename VARCHAR(256)typenamevarcharalt VARCHAR(65535)altvarcharmedia_content_type VARCHAR(256)media_content_typevarcharstatus VARCHAR(256)statusvarcharimage_url VARCHAR(65535)image_urlvarcharimage_width BIGINTimage_widthbigintimage_height BIGINTimage_heightbigint_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampproductsproducts28id BIGINT · primary keyidbigintfeatured_media_id BIGINT · → product_media.idfeatured_media_idbiginttitle VARCHAR(65535)titlevarcharhandle VARCHAR(65535)handlevarcharstatus VARCHAR(256)statusvarcharvendor VARCHAR(65535)vendorvarcharproduct_type VARCHAR(65535)product_typevarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamppublished_at TIMESTAMPpublished_attimestamptotal_inventory BIGINTtotal_inventorybiginttracks_inventory BOOLEANtracks_inventoryboolhas_only_default_variant BOOLEANhas_only_default_variantboolis_gift_card BOOLEANis_gift_cardboolonline_store_url VARCHAR(65535)online_store_urlvarchartemplate_suffix VARCHAR(65535)template_suffixvarchardescription VARCHAR(65535)descriptionvarcharcategory_id VARCHAR(256)category_idvarcharcategory_name VARCHAR(65535)category_namevarcharcategory_full_name VARCHAR(65535)category_full_namevarchar+8 more (see below) +8 more (see below)product_metafieldsproduct_metafields11id BIGINT · primary keyidbigintproduct_id BIGINT · → products.idproduct_idbiginttypename VARCHAR(256)typenamevarcharnamespace VARCHAR(65535)namespacevarcharkey VARCHAR(65535)keyvarchartype VARCHAR(65535)typevarcharvalue VARCHAR(65535)valuevarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampinventory_quantitiesinventory_quantities7inventory_level_id VARCHAR(256) · primary key · → inventory_levels.idinventory_level_idvarcharindex BIGINT · primary keyindexbigintname VARCHAR(65535)namevarcharquantity BIGINTquantitybigintupdated_at TIMESTAMPupdated_attimestamp_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampinventory_itemsinventory_items15id BIGINT · primary keyidbigintsku VARCHAR(65535)skuvarchartracked BOOLEANtrackedboolrequires_shipping BOOLEANrequires_shippingboolcountry_code_of_origin VARCHAR(256)country_code_of_originvarcharprovince_code_of_origin VARCHAR(65535)province_code_of_originvarcharharmonized_system_code VARCHAR(65535)harmonized_system_codevarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestampunit_cost_amount DECIMAL(38,9)unit_cost_amountdecimalunit_cost_currency_code VARCHAR(256)unit_cost_currency_codevarcharmeasurement_weight_unit VARCHAR(256)measurement_weight_unitvarcharmeasurement_weight_value DECIMAL(38,9)measurement_weight_valuedecimal_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampproduct_tagsproduct_tags5product_id BIGINT · primary key · → products.idproduct_idbigintindex BIGINT · primary keyindexbigintvalue VARCHAR(65535)valuevarchar_jsdata_deleted BOOLEAN_jsdata_deletedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestamp
Primary keyForeign keyReferencesJoins on (not a key)

Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.

Table reference

14 tables · click a table for its columns
products28 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
idPrimary key.bigintPK Primary key.
titlevarchar(65535)
handlevarchar(65535)
statusvarchar(256)
vendorvarchar(65535)
product_typevarchar(65535)
created_attimestamp
updated_attimestamp
published_attimestamp
total_inventorybigint
tracks_inventoryboolean
has_only_default_variantboolean
is_gift_cardboolean
online_store_urlvarchar(65535)
template_suffixvarchar(65535)
descriptionvarchar(65535)
category_idvarchar(256)
category_namevarchar(65535)
category_full_namevarchar(65535)
seo_titlevarchar(65535)
seo_descriptionvarchar(65535)
featured_media_idReferences product_media.id.bigintFKReferences product_media.id.
price_range_v2_min_variant_price_amountdecimal(38,9)
price_range_v2_min_variant_price_currency_codevarchar(256)
price_range_v2_max_variant_price_amountdecimal(38,9)
price_range_v2_max_variant_price_currency_codevarchar(256)
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_tags5 columns · Catalogshow in diagram

Primary key: product_id, index

ColumnTypeKeyDescription
product_idPrimary key (with index). References products.id.bigintPK FKPrimary key (with index). References products.id.
indexPrimary key (with product_id).bigintPK Primary key (with product_id).
valuevarchar(65535)
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_options7 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
product_idReferences products.id.bigintFKReferences products.id.
indexbigint
idPrimary key.bigintPK Primary key.
namevarchar(65535)
positionbigint
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_option_values7 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
product_option_idReferences product_options.id.bigintFKReferences product_options.id.
indexbigint
idPrimary key.bigintPK Primary key.
namevarchar(65535)
has_variantsboolean
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_variants20 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
product_idReferences products.id.bigintFKReferences products.id.
typenamevarchar(256)
idPrimary key.bigintPK Primary key.
titlevarchar(65535)
display_namevarchar(65535)
skuvarchar(65535)
barcodevarchar(65535)
positionbigint
pricedecimal(38,9)
compare_at_pricedecimal(38,9)
taxableboolean
available_for_saleboolean
inventory_quantitybigint
inventory_policyvarchar(256)
created_attimestamp
updated_attimestamp
inventory_item_idReferences inventory_items.id.bigintFKReferences inventory_items.id.
selected_optionsvarchar(65535)
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_media11 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
product_idReferences products.id.bigintFKReferences products.id.
typenamevarchar(256)
idPrimary key.bigintPK Primary key.
altvarchar(65535)
media_content_typevarchar(256)
statusvarchar(256)
image_urlvarchar(65535)
image_widthbigint
image_heightbigint
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
product_metafields11 columns · Catalogshow in diagram

Primary key: id

ColumnTypeKeyDescription
product_idReferences products.id.bigintFKReferences products.id.
typenamevarchar(256)
idPrimary key.bigintPK Primary key.
namespacevarchar(65535)
keyvarchar(65535)
typevarchar(65535)
valuevarchar(65535)
created_attimestamp
updated_attimestamp
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
collections9 columns · Collectionsshow in diagram

Primary key: id

ColumnTypeKeyDescription
idPrimary key.bigintPK Primary key.
titlevarchar(65535)
handlevarchar(65535)
updated_attimestamp
sort_ordervarchar(256)
template_suffixvarchar(65535)
products_count_countbigint
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
collection_products5 columns · Collectionsshow in diagram

Primary key: collection_id, product_id

ColumnTypeKeyDescription
collection_idPrimary key (with product_id). References collections.id.bigintPK FKPrimary key (with product_id). References collections.id.
typenamevarchar(256)
product_idPrimary key (with collection_id). References products.id.bigintPK FKPrimary key (with collection_id). References products.id.
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
collection_metafields11 columns · Collectionsshow in diagram

Primary key: id

ColumnTypeKeyDescription
collection_idReferences collections.id.bigintFKReferences collections.id.
typenamevarchar(256)
idPrimary key.bigintPK Primary key.
namespacevarchar(65535)
keyvarchar(65535)
typevarchar(65535)
valuevarchar(65535)
created_attimestamp
updated_attimestamp
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
inventory_items15 columns · Inventoryshow in diagram

Primary key: id

ColumnTypeKeyDescription
idPrimary key.bigintPK Primary key.
skuvarchar(65535)
trackedboolean
requires_shippingboolean
country_code_of_originvarchar(256)
province_code_of_originvarchar(65535)
harmonized_system_codevarchar(65535)
created_attimestamp
updated_attimestamp
unit_cost_amountdecimal(38,9)
unit_cost_currency_codevarchar(256)
measurement_weight_unitvarchar(256)
measurement_weight_valuedecimal(38,9)
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
inventory_levels9 columns · Inventoryshow in diagram

Primary key: id

ColumnTypeKeyDescription
inventory_item_idReferences inventory_items.id.bigintFKReferences inventory_items.id.
typenamevarchar(256)
idPrimary key. Shopify's inventory level id, kept as text ('<number>?inventory_item_id=<item>'): the number alone is not unique.varchar(256)PK Primary key. Shopify's inventory level id, kept as text ('<number>?inventory_item_id=<item>'): the number alone is not unique.
is_activeboolean
created_attimestamp
updated_attimestamp
location_idReferences locations.id.bigintFKReferences locations.id.
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
inventory_quantities7 columns · Inventoryshow in diagram

Primary key: inventory_level_id, index

ColumnTypeKeyDescription
inventory_level_idPrimary key (with index). References inventory_levels.id.varchar(256)PK FKPrimary key (with index). References inventory_levels.id.
indexPrimary key (with inventory_level_id).bigintPK Primary key (with inventory_level_id).
nameavailable, on_hand, committed, incoming, reserved, damaged, safety_stock or quality_control.varchar(65535)available, on_hand, committed, incoming, reserved, damaged, safety_stock or quality_control.
quantitybigint
updated_attimestamp
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
locations23 columns · Inventoryshow in diagram

Primary key: id

ColumnTypeKeyDescription
idPrimary key.bigintPK Primary key.
namevarchar(65535)
is_activeboolean
is_fulfillment_serviceboolean
fulfills_online_ordersboolean
ships_inventoryboolean
has_active_inventoryboolean
created_attimestamp
updated_attimestamp
deactivated_atvarchar(65535)
address_address1varchar(65535)
address_address2varchar(65535)
address_cityvarchar(65535)
address_provincevarchar(65535)
address_province_codevarchar(65535)
address_countryvarchar(65535)
address_country_codevarchar(65535)
address_zipvarchar(65535)
address_phonevarchar(65535)
address_latitudedecimal(38,9)
address_longitudedecimal(38,9)
_jsdata_deletedTrue when the record was deleted at the source.booleanTrue when the record was deleted at the source.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.

Connect Shopify Products & Inventory All connectors