How to load Shopify data into Snowflake with OWOX Data Marts
To get Shopify data into Snowflake, create an Admin API access token for your store, set up a connector Data Mart with the token and shop domain, choose an endpoint such as Orders and the fields you need, and publish. Orders land one row each in your Snowflake account, with line items stored as JSON that Snowflake can unpack in a query.
Shopify’s own reports answer what sold. They do not answer what it cost to sell it. Ad spend is in the ad platforms, and Shopify cannot join to them.
Once orders are in Snowflake, next to the ad platform tables, that join is one query. This guide loads Shopify data into Snowflake with the Shopify in OWOX Data Marts: the token it needs, the endpoints worth loading, what the orders table looks like and the SQL to get from it to daily sales and product-level revenue.
What you end up with
One table per endpoint in your own Snowflake account. The one you will query most is shopify_orders: one row per order, with its financial and fulfilment status, totals, customer reference and timestamps.
The connector reads the Shopify Admin API, writes the tables and repeats on a schedule. It does not model the data. Net sales, cohorts and margin are SQL you write on top.
Before you start
In Shopify. Your store’s address, such as yourstore.myshopify.com, and permission to create and install an app in the store.
In Snowflake. A storage of type Snowflake connected to your project, as described in the storage guide: account identifier, warehouse, and a key pair or a username with a programmatic access token. The role must be able to create a database, a schema and tables. A small dedicated warehouse with auto-suspend is enough, and Snowflake pricing explained shows what a daily load costs.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Step 1: create the access token
Step 1: create the access token
The connector authenticates with an Admin API access token. You get one by creating an app for your own store. The credentials guide has the exact request. In outline:
- In the Shopify Dev Dashboard, create an app and give its version read scopes only: orders, customers, products, inventory, discounts, checkouts, locations, fulfilment orders and content.
- Release the version and install the app in your store.
- From the app’s settings, take the client ID and secret, and request a token from your store’s token endpoint. The response contains the access token.
Every scope is a read scope. The connector cannot change anything in the store.
Step 2: create the Data Mart and connect the store
Step 2: create the Data Mart and connect the store
Click New Data Mart, enter a title such as “Shopify Orders”, select the Snowflake storage and click Create Data Mart. On the Data Setup tab, set Definition Type to Connector and choose Shopify.
Enter the Shop Domain and the Admin API Access Token.
Step 3: choose the endpoint and the fields
Step 3: choose the endpoint and the fields
Each Data Mart loads one endpoint. Shopify offers many. These are the ones that matter for analytics.
- Orders. Financial and fulfilment status, totals, discounts, refunds, customer and timestamps. Start here.
- Customers. Contact details, marketing consent, order count and lifetime spend.
- Products and Product Variants. The catalogue: vendor, type, status, SKU, price and inventory quantity.
- Abandoned Checkouts. Checkouts that did not complete, with items and prices.
- Discount Codes, Collections, Inventory Items, Locations, Fulfillment Orders and Tender Transactions for operations reporting.
There are also endpoints for store content and for metafields, the custom fields attached to orders, products, customers and other objects.
Then select fields.
Two things to decide here.
Personal data. The Orders endpoint offers email, phone and shipping and billing address fields. If your reporting does not need them, do not load them. A table without personal data is a table you can grant access to freely. customerId is enough to count and join customers.
Nested fields. lineItems, refunds, shippingLines and customer are loaded as JSON text inside the order row. Select lineItems if you want product-level reporting. The query further down unpacks it.
The last screen asks for the database, schema and table. The setup proposes a database named after the source, the schema PUBLIC and a table named after the endpoint.
Step 4: publish, backfill and schedule
Step 4: publish, backfill and schedule
Click Publish & Run Data Mart. Publishing starts the first import. Afterwards the Data Setup tab shows the source, the Snowflake table and its columns.
History. Click Manual Run and choose Backfill (custom period). One backfill run covers at most 31 days. For older orders, run consecutive periods, each after the previous one has finished.
Schedule. A Data Mart without a trigger does not run again. On the Triggers tab, add a trigger of type Connector Run: daily, weekly, monthly or at an interval.
Scheduled runs are incremental. Each one covers the current day plus the days set by the Reimport Lookback Window, two by default. The connector selects orders by when they were last updated, not when they were placed, so an old order that is refunded today is picked up by today’s run. Orders are merged on their id: the changed order replaces its earlier version and is not added a second time.
The same rule applies to a backfill. Its period is matched against the update time of the order.
What the orders table looks like in Snowflake
What the orders table looks like in Snowflake
Names are case-sensitive, and in camel case. The connector creates the schema, table and column names quoted, exactly as Shopify spells them: totalPrice, processedAt, displayFinancialStatus. In Snowflake a quoted name is case-sensitive and an unquoted one is read as upper case, so SELECT totalPrice fails. Quote every column, the schema and the table: shopify_owox."PUBLIC"."shopify_orders". The database name is not quoted.
Money is numeric, in the shop’s currency. totalPrice, subtotalPrice, totalTax, totalDiscounts, totalShippingPrice and totalRefunded can be summed directly. currencyCode says which currency.
There are four timestamps. createdAt, processedAt, updatedAt and closedAt. For sales by day, processedAt is the usual choice. Pick one and use it everywhere.
Status is two columns. displayFinancialStatus covers payment and displayFulfillmentStatus covers shipping. A sales report nearly always needs a filter or a split on the first.
Query the data
Both queries were validated on Snowflake against a table loaded by the connector.
Orders, gross sales, refunds and net sales by day:
SELECT
DATE_TRUNC('day', "processedAt") AS day,
"currencyCode" AS currency,
COUNT(*) AS orders,
SUM("totalPrice") AS gross_sales,
SUM("totalRefunded") AS refunded,
SUM("totalPrice") - SUM("totalRefunded")
AS net_sales,
SUM("totalPrice") / NULLIF(COUNT(*), 0)
AS average_order_value
FROM shopify_owox."PUBLIC"."shopify_orders"
WHERE "processedAt" >=
DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY 1;“Net sales” here is the order total minus refunds. Whether yours also excludes tax and shipping is a decision to make once, in a published query.
Units and sales by product, unpacking the line items:
SELECT
li.value:sku::STRING AS sku,
li.value:title::STRING AS title,
SUM(li.value:quantity::NUMBER) AS units,
SUM(
li.value:quantity::NUMBER
* li.value:discountedUnitPriceSet.shopMoney.amount::FLOAT
) AS sales
FROM shopify_owox."PUBLIC"."shopify_orders" o,
LATERAL FLATTEN(
input => PARSE_JSON(o."lineItems")
) li
WHERE o."processedAt" >=
DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY sales DESC;PARSE_JSON turns the text into a structure, and LATERAL FLATTEN produces one row per line item. The keys inside the JSON are case-sensitive as well, so write discountedUnitPriceSet exactly. One limit to know: the connector stores up to 250 line items per order.
If product reporting is a daily need, turn this query into a view or a table in a reporting schema, so reports read rows and not JSON. Snowflake as a reusable reporting layer shows the pattern.
From table to report
A connector Data Mart can be reported from directly. On its Destinations tab you attach reports to Sheets, Data Studio, Slack or email, each with its own columns and schedule. Snowflake reports in Google Sheets covers the delivery.
The report most stores want is sales next to ad spend. With the ad platforms loaded into the same Snowflake account, that is the daily sales query above joined on the day to a unified spend query. Self-service marketing analytics on Snowflake builds the spend side.
Where to go next
The ad platforms load into the same Snowflake account with the same kind of Data Mart: Facebook Ads, Google Ads, Microsoft Ads, LinkedIn Ads, TikTok Ads and Reddit Ads.
For the platform itself, start with what Snowflake is and how it works.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How are refunded or changed Shopify orders updated in Snowflake?
The connector selects orders by when they were last updated. An order that is refunded, fulfilled or edited is picked up by the next scheduled run, and because rows are merged on the order ID, the new version replaces the earlier one. Each run covers the current day plus the Reimport Lookback Window, two days by default.
Should I load customer emails and phone numbers from Shopify?
Only if a report needs them. The Orders endpoint offers email, phone and address fields, and each is optional in the field selection. The customerId field is enough to count and join customers. A table without personal data is simpler to grant access to and to share through reports.
How do I deliver Shopify data from Snowflake to spreadsheets and dashboards?
On the Data Mart's Destinations tab, create reports to Google Sheets, Data Studio, Slack or email, each with its own columns and schedule. For net sales or product-level numbers, publish your own query as a SQL Data Mart and report from that, so every report uses the same definition.
How do I report on Shopify line items in Snowflake?
The orders table stores line items as JSON text in the lineItems column. In a query, use PARSE_JSON on the column and LATERAL FLATTEN to get one row per line item, then read sku, title, quantity and the unit price from each. The keys inside the JSON are case-sensitive. The connector stores up to 250 line items per order.
Why does my query fail on Shopify column names in Snowflake?
The connector creates schema, table and column names quoted, exactly as Shopify spells them in camel case, for example totalPrice and processedAt. In Snowflake a quoted name is case-sensitive and an unquoted name is read as upper case, so the column is not found. Write every name in double quotes exactly as created.
What do I need before connecting Shopify to Snowflake?
Your store address, permission to create and install an app in the store, and an Admin API access token issued for that app with read scopes. In Snowflake you need a connected storage with an account identifier, a warehouse and a key pair or a username with a programmatic access token, on a role that can create a database, schema and tables.
Which Shopify data can I load into Snowflake?
Orders, customers, products and product variants, abandoned checkouts, discount codes, collections, inventory items, locations, fulfillment orders and tender transactions, plus store content and metafields. Each endpoint is loaded by its own Data Mart into its own table. For sales reporting, start with Orders.
How do I load Shopify data into Snowflake?
Create an Admin API access token for your store, then create a Data Mart in OWOX Data Marts on your Snowflake storage with the definition type Connector and the source Shopify. Enter the shop domain and the token, pick an endpoint such as Orders and the fields you need, and publish. A Connector Run trigger on the Triggers tab repeats the load on a schedule.
