Shopify Data Model Explained: Entities, Keys and Analytics Gotchas

Quick answer:

Use the Shopify data model to connect your sales and catalog tables, choose revenue fields deliberately, and understand why warehouse totals differ from Shopify reports.

• Entities: Your sales half connects customers, orders and lines; your catalog half connects products, variants and inventory, meeting at LineItem.variant.
• Money: Use currentTotalPriceSet for current order value, line fields for product sales and shopMoney consistently; never sum order totals after joining lines.
• Refund dates: Date returns separately from sales, not against the original order date, and align timestamps and timezone with Shopify reports.
• Connectors: Your connector may split repeating children into tables and retain nested objects as columns or JSON; names and refund grain differ.

You have customers, products, orders, transactions, refunds and collections in your warehouse, but your first revenue query already disagrees with Shopify’s report.

The Shopify data model explains why: nested objects become separate tables, order totals repeat when you join them to lines, and refunds carry dates of their own.

Before you build a Shopify database schema for reporting, you’ll need to know what each row represents and which keys connect it, so this chapter maps the Shopify order data structure through 4 topics:

• Nested data structures, the family tree behind your tables
• The Shopify entity map: objects, keys and grain
• The analytics gotchas that break revenue numbers
• How the main connectors land Shopify data

This chapter sits in the Shopify Analytics Guide, with API facts reflecting Admin API version 2026-10 and SQL patterns you can adapt to your connector.

Nested Data Structures: Your Shopify Data’s Family Tree

Nested data structures have at least 2 levels of data in a parent-child relationship. Each element is a parent, a child, or both…in which case we’re talking about a multilevel nested data structure.

“Multilevel nested data structure” is a mouthful, but the concept isn’t hard to understand. Just think of it as a piece of data’s family tree: everything is related, but the relationships may be more or less distant (and complicated!) depending how far down the branches you go.

Not sure what a family tree has to do with your Shopify store? That same nested structure comes into play every time you make a sale. Take a look:

While a customer might purchase items separately, they’re just as likely to put multiple items in their shopping cart and purchase them all as a single order.

An order line can link to refund lines and contain discount allocations or tax lines, taking a sale 3 or 4 levels deep.

Now that you’ve got the basics of nested data down, let’s map the API behind the Shopify data model.

Where Shopify Data Comes From: The Admin API Today

Shopify’s REST Admin API has been legacy since October 1, 2024. Shopify has published no shutdown date, so legacy does not mean switched off.

• REST is legacy: Existing integrations can continue using the REST Admin API.
• GraphQL-only for new public apps: Required since April 1, 2025; this rule does not cover custom apps.
• Current version: 2026-10 shipped October 1, 2026. Versions ship quarterly with at least 12 months of support.
• Money is a MoneyBag: Set money fields carry shopMoney and presentmentMoney; older non-Set equivalents are deprecated. “Which Revenue Number?” explains the choice.
• Variants: Up to 2,048 per product by default. Read actual ceilings from Shop.resourceLimits.maxProductVariants and maxProductOptions.
• Pagination: GraphQL uses cursors, generally capped at 250 items per page.
• Metafields vs metaobjects: A metafield adds a field to an existing resource, such as a product or order. A metaobject defines a new record type. Undefined metafields are stored as plain strings.
• 60-day order window: Default access covers orders created in the last 60 days. Older history requires read_all_orders alongside read_orders or write_orders. Partner apps need Shopify approval for the additional scope.

Connector migrations also change your Shopify database schema: Fivetran defaults to GraphQL for connections created on or after August 1, 2025, so equivalent entities can have different table names across pipelines.

If your backfill stops 60 days ago, check the access scope first.

The Shopify Entity Map: Core Objects, Keys and Grain

Bookmark this map before joining tables: knowing what 1 row represents prevents order totals from multiplying across child rows.

The core Shopify objects, their keys and how many of each hang off a parent.

The Shopify data model has 2 halves:

• Sales: Customer to Order to LineItem, with refunds and transactions attached
• Catalog: Product to ProductVariant to inventory records and locations, with collections grouping products

They meet at LineItem.variant. A deleted variant can leave that link null, but the order-time sku stays on the line.

Order.customer is nullable for guest checkouts. Where exposed, legacyResourceId carries the numeric REST ID. The keys below describe modeling relationships; connector columns differ.

Object	One row per	Key	Parent key	What analysts use it for
Shop	Store	id	None	ianaTimezone, currencyCode, taxesIncluded for reports
Customer	Customer record	id	None	Shopify-computed lifetime numberOfOrders, amountSpent
Order	Completed checkout	id	customer, nullable	Order counts, AOV, totals
LineItem	Product line on an order	id	order, variant	Units, gross sales, product mix
Refund	Refund event	id	order	Returns by refund date, totalRefundedSet
RefundLineItem	Refunded line per refund	Refund + line item	refund, lineItem	Product return rates, restockType
OrderTransaction	Payment event	id	order, parentTransaction	Cash movement, gateway, fees
Product	Product	id	None	Catalog attributes
ProductVariant	Sellable option combination	id	product	SKU, price, compareAtPrice
InventoryItem	Variant’s stock record	id	Variant	unitCost for margin
InventoryLevel	Item at a location	Item + location	item, location	Available, on-hand, incoming and committed quantities
Collection	Collection	id	None	Merchandising groups, many-to-many with products
FulfillmentOrder	Items shipping from one location	id	order	Fulfillment status, assigned location

Metafields attach one-to-many to an owner, identified within that owner by namespace and key; owners include:

• Product and ProductVariant
• Order and Customer
• Collection and Shop

A completed DraftOrder becomes a normal Order; incomplete drafts are not revenue. Drafts created on or after April 1, 2025 are purged after 1 year of inactivity, so your warehouse copy can outlive Shopify’s.

Collections: A Nested Structure You Can See

To see the Shopify data model in action, picture a store that sells paintings and groups them with collections.

At the parent level, a GraphQL Collection gives you:

• Title: title and a unique handle, which doesn’t change when the title changes
• Image: image.altText, a description belonging to the image, which belongs to the collection
• Rules: Conditions that products must match for automatic membership

Legacy rule-based collections use ruleSet: appliedDisjunctively: true means match at least 1 rule; false means match all. Each rule has a column, relation and condition. Manual collections have products picked by hand.

This illustrative legacy GraphQL-shaped response groups oil paintings of landscapes:

{
  "title": "Landscapes",
  "handle": "landscapes",
  "image": {"altText": "Oil paintings of countryside landscapes"},
  "ruleSet": {
    "appliedDisjunctively": false,
    "rules": [
      {"column": "PRODUCT_TYPE", "relation": "EQUALS", "condition": "Landscape painting"},
      {"column": "TAG", "relation": "EQUALS", "condition": "oil"}
    ]
  },
  "productsCount": {"count": 12}
}

In the 2026-10 Collection model, ruleSet is deprecated in favor of Collection.sources. Sources add products through conditions, including tags or metafields, and manual selections. Legacy rule-based collections still return ruleSet data.

There’s no isSmart field. The collections query supports a collection_type filter with custom or smart.

Collection-to-product membership is many-to-many, modeled in REST through Collect. Airbyte still exposes the REST streams alongside GraphQL Collections:

• custom_collections
• smart_collections
• collects

Use the membership join, not the rules, to answer which products belong to a collection. Rules describe intent; collects or collection-product rows describe actual membership.

Keep this collection in mind as its nested fields become relational tables.

From Nested JSON to Relational Tables

Shopify exports data as JSON, a standard form of nested data that’s great for large data sets but doesn’t play well with most BI tools. To make your data query-ready, a pipeline transforms nested data into a flat relational model ready for your analytical tools.

As in any relational data model, the relationship between parent and child tables is based on keys. Each child row carries its parent’s key, such as order_id on an order line.

Connector schemas differ in names and grain. Fivetran’s legacy ORDER_LINE_REFUND and CData’s RefundLineItems hold line-level data; Airbyte exposes an order_refunds stream. Inspect the nested children before treating these as equivalent tables.

Flattening the Shopify data model changes the family tree:

• Repeating children: Lines, refunds, refund lines and transactions become separate tables.
• Single nested objects: Addresses and money bags become prefixed columns or JSON columns.
• Variable payloads: Gateway-specific receiptJson, which Shopify calls “not a stable contract,” often remains JSON or VARIANT.

SELECT o.id AS order_id, o.name, o.created_at,
       ol.id AS order_line_id, ol.sku, ol.quantity, ol.current_quantity
FROM orders o
JOIN order_line ol ON ol.order_id = o.id
WHERE o.test = FALSE
ORDER BY o.created_at;

Rename these generic tables and columns to your connector’s equivalents, such as Fivetran’s ORDER and ORDER_LINE.

Use this join in Snowflake, BigQuery or DuckDB. Connectors flatten for you; Shopify’s Bulk Operations also perform an initial flattening pass.

Bulk Operations: How Shopify Flattens Its Own Data

A bulk query result is JSONL rather than a nested connection tree: 1 node per line, with children placed after their parent. Each child gets an automatic __parentId; that field isn’t in the schema and can’t be queried explicitly.

This illustrative output contains 1 order and 2 line items:

{"id":"gid://shopify/Order/1001","name":"#1001","createdAt":"2026-10-01T12:00:00Z"}
{"id":"gid://shopify/LineItem/1","quantity":1,"__parentId":"gid://shopify/Order/1001"}
{"id":"gid://shopify/LineItem/2","quantity":2,"__parentId":"gid://shopify/Order/1001"}

Rejoin on child.__parentId = parent.id, the same relationship as the orders/order_line join above.

• Shape: At most 5 connections per query and 2 levels of nested connections; a third is invalid. Split deeper connection trees into another query rather than assuming the entire family tree fits.
• Time: An operation must finish within 10 days. Download its signed result URL within 1 week, before it expires.
• Concurrency: Since API version 2026-01, up to 5 bulk queries can run at once; earlier versions ran 1 at a time. Airbyte’s docs still describe 1 bulk job per shop.

For a full backfill, split by date range and run up to 5 queries in parallel. Load parent and child records separately, then upsert on id.

Which Revenue Number? Order Grain and Money Fields

An Order exposes at least 7 money totals. Which one is revenue depends on your question.

Never sum an order total after joining to lines: the join repeats it once per line. Order fields answer order questions; line fields answer product questions.

Field	What it holds	Use it for
originalTotalPriceSet	Total at creation, before edits, returns and refunds	As-ordered snapshot
totalPriceSet	Total before returns, including taxes and discounts	Gross order value
currentTotalPriceSet	Total after returns and edits	Current order value
subtotalPriceSet / currentSubtotalPriceSet	After discounts, before or after returns respectively	Product revenue at order grain
totalRefundedSet	Amount refunded	Refunds per order
netPaymentSet	Received minus refunded	Cash view
LineItem originalUnitPriceSet multiplied by quantity	Original unit price before discounts multiplied by units	Gross sales before tax adjustments

Choose LineItem fields deliberately:

• Don’t mix twins: Non-current fields represent the sale; current fields reflect returns and edits. Examples include currentTotalDiscountsSet, currentTaxLines and currentTotalTaxSet. Subtracting refunds again from a current total double-counts their effect.
• Quantity vs currentQuantity: quantity includes refunded and removed units; currentQuantity excludes them. Choose original or net units.
• Line sums can miss discounts: discountedTotalSet excludes code-based discounts by default. Use discountAllocations to account for discounts allocated to each line.
• shopMoney vs presentmentMoney: shopMoney is the shop’s base currency; presentmentMoney is the customer’s selected currency. Use shopMoney consistently for aggregate reporting.
• taxesIncluded: Tax-inclusive prices already contain tax. Check taxesIncluded on the shop or order before adding tax again; Shopify’s gross-sales definition excludes tax.

Use currentTotalPriceSet for current order value. Build gross and net sales from lines when matching Shopify’s reports, keeping sales and return events on their respective dates rather than treating current value as historical daily revenue.

Analyst Gotchas: Refunds, Dates, Test Orders and Guest Checkouts

One merchant on the Shopify Community reported API sales of EUR 356,953.18 versus dashboard sales of EUR 355,571.59, despite accounting for refunds and timezone differences. Match the report’s definition, not just its date range.

Gotcha	What goes wrong	Fix
Refund date	Netting returns against the order date changes past days	Date returns by Refund.processedAt or createdAt, separately from sales
Created vs processed time	createdAt is fixed checkout time; imported orders can differ	Use processedAt to align with in-app reports
Timezone	Bucketing in a different timezone moves orders between days	Start with Shop.ianaTimezone; verify the specific report against a known day
Test orders	Bogus Gateway or test-mode orders inflate sales	Apply WHERE test = FALSE
Canceled orders	Removing them breaks gross sales, which includes pending, canceled and unpaid orders	Keep them; flag cancelledAt
Guest checkouts	Null customer links cause inner joins to drop orders	Use LEFT JOIN customer; group guests by email
Pending or failed payments	Authorizations or incomplete transactions get counted as cash	Require status = 'SUCCESS'; count SALE/CAPTURE in, REFUND out
Refund isn’t cash	A Refund object doesn’t guarantee money reached the customer	Confirm a REFUND transaction with SUCCESS status
Refunds without lines	Out-of-stock refunds can appear in adjustments, not refund lines	Include refund lines, orderAdjustments and refundShippingLines
60-day history	Older orders are missing from the backfill	Add approved read_all_orders access alongside read_orders or write_orders
Lifetime customer fields	Shopify aggregates differ from incomplete or differently filtered warehouse history	Recompute amountSpent/numberOfOrders from orders, or use Shopify’s values consistently

Match Shopify’s sales report day by day

Shopify defines net sales as gross sales minus discounts and sales reversals (returns). Total sales adds taxes, duties, shipping and fees.

This Snowflake-style skeleton calculates gross sales less line returns, not complete net sales. Subtract allocated discounts and handle tax-inclusive prices before reconciling.

WITH sales_and_returns AS (
    SELECT
        TO_DATE(CONVERT_TIMEZONE(
            'UTC', 'America/New_York', o.processed_at
        )) AS sales_day, -- your Shop.ianaTimezone
        ol.original_unit_price * ol.quantity AS amount
        -- Subtract allocated line and order discounts here.
    FROM orders o
    JOIN order_line ol ON ol.order_id = o.id
    WHERE o.test = FALSE
    -- Keep canceled orders.

    UNION ALL

    SELECT
        TO_DATE(CONVERT_TIMEZONE(
            'UTC', 'America/New_York', r.processed_at
        )) AS sales_day,
        -rl.subtotal AS amount
    FROM refund r
    JOIN refund_line rl ON rl.refund_id = r.id
    JOIN orders o ON o.id = r.order_id
    WHERE o.test = FALSE
)
SELECT sales_day,
       SUM(amount) AS gross_sales_less_line_returns
FROM sales_and_returns
GROUP BY sales_day
ORDER BY sales_day;

Use shop-currency amounts. The conversion assumes UTC timestamp columns without timezone information; adapt it to your stored timestamp type. Add adjustments for refunds without lines, and keep shipping reversals separate when calculating total sales.

Count cash only when it moved

Use transaction amounts mapped from amountSet.shopMoney, not refund-line subtotals. This query covers successful sale, capture and refund flows; handle CHANGE separately for cash overpayments.

SELECT SUM(
    CASE
        WHEN t.kind IN ('SALE', 'CAPTURE') THEN t.amount
        WHEN t.kind = 'REFUND' THEN -t.amount
        ELSE 0
    END
) AS net_cash_moved
FROM order_transaction t
JOIN orders o ON o.id = t.order_id
WHERE t.status = 'SUCCESS'
  AND o.test = FALSE;

AUTHORIZATION, VOID and SUGGESTED_REFUND do not count here.

Reconcile at line level, 1 report and 1 date basis at a time: timezone first, then refund dates, then test orders.

How Connectors Land Shopify Data in Your Warehouse

Choose your connector by API coverage, refund grain, delete handling and nested-child behavior, not table count.

Connector	How it reads Shopify	Refunds land in	Deletes	Nested data and watch-outs
Fivetran	GraphQL default for connections from 2025-08-01; Legacy mixes APIs	REFUND, ORDER_ADJUSTMENT; Legacy ORDER_LINE_REFUND renamed for GraphQL connections after November 2025	Captures ORDER, CUSTOMER, PRODUCT, ABANDONED_CHECKOUT, DRAFT_ORDER; hard-deletes children	Separate child tables; empty tables omitted; child updates may not bump parents
Airbyte	38 streams; GraphQL/Bulk catalog, transactions, metafields; REST orders, refunds, customers	order_refunds	deleted_at tombstones for Orders, CustomCollections, others; product deletes via Events API	Transactions can precede orders and duplicate in Append; order customer fields require enabling
Hevo Data	Object-based; children follow parents; initial history load	Order children; no standalone refund object listed	Webhooks you create; flagged __hevo__marked_deleted	Merge only; metafields inflate volume; 60-day history without read_all_orders
Stitch	REST; key-based incremental	order_refunds, by created_at; queries every order	Not documented	Denests when destination lacks nested support; default history 1 year
dlt	Customers, orders, products; separate Partner GraphQL query	No refund, transaction, inventory or collection resources out-of-box	Not documented	Incremental updated_at, merge on id; order_status filter
Singer / Meltano	4 variants: Matatika default, Josue Sehnem, singer-io, Airbyte wrapper	Variant-dependent	Variant-dependent	Schema-flattening capability; check variant repository for streams

Rivery and Portable also offer Shopify connectors; their table lists are not publicly documented, so request sample schemas. Portable reads GraphQL and lists these destinations:

• Snowflake
• BigQuery
• Redshift
• PostgreSQL
• MySQL

The Fivetran dbt package

dbt_shopify provides models including:

• shopify__orders and shopify__order_lines
• shopify__transactions and shopify__daily_shop
• shopify__refunds: 1 row per refund transaction
• shopify__refund_lines: refunded-line grain, including restock classification

Use refund lines for product-level returns. The shopify_api variable selects REST or GraphQL models, not both; GraphQL names differ. End-model monetary values use the shop’s default currency.

The decision log flags daily models truncating created_timestamp, while Shopify’s in-app reports use processed_timestamp. Align the date basis before comparing totals.

For modeled refunds and deletes, start with a managed connector plus dbt. For extraction control, use Bulk Operations JSONL or extend dlt’s resources. Compare Shopify ETL tools.

Common questions

Is the Shopify REST Admin API shut down?

No. Shopify’s REST Admin API has been legacy since October 1, 2024, but Shopify has published no shutdown date. New public apps must use GraphQL exclusively from April 1, 2025; that rule does not cover custom apps. Check your connector’s migration plan before assuming its warehouse schema will stay unchanged.

Why does my Shopify order data only go back 60 days?

Shopify’s default order access covers orders created within the last 60 days. Older history requires read_all_orders alongside read_orders or write_orders; the additional scope doesn’t grant access by itself. Partner apps need Shopify approval. Check your connector app’s scopes and approval before changing replication dates to troubleshoot an incomplete historical backfill.

Why don’t my warehouse totals match Shopify’s sales reports?

Warehouse totals differ when refund dates, timestamp choices, timezones or test-order filters differ from Shopify’s report. Returns belong on their processing day, not the original order date. Fivetran’s dbt daily models also use created rather than processed timestamps. See “Analyst Gotchas: Refunds, Dates, Test Orders and Guest Checkouts” above.

Why is the customer missing on some Shopify orders?

Guest checkout can leave Order.customer null, so an inner join to customers drops those orders. Airbyte also leaves top-level customer fields on orders null unless populate_top_level_orders_customer_fields is enabled. Check that setting before treating every missing customer field as a guest checkout, and use a left join to preserve orders.

How many variants can a Shopify product have?

A Shopify product can have up to 2,048 variants by default. Read Shop.resourceLimits.maxProductVariants for the shop’s actual ceiling and maxProductOptions for its option limit. Don’t confuse a product’s total variant capacity with API pagination: multi-product queries still require cursor-based pagination to retrieve the complete result set.

Where to next

Put your order, line and customer tables to work with RFM segments and customer lifetime value. As you build, group guest checkouts by email and keep one currency and one revenue definition throughout. Explore Shopify Customer Analytics for your next customer questions, or compare Shopify ETL tools when choosing how to bring those tables into your warehouse.