PipeloomShopify to Snowflake
Load Shopify orders, customers, products and inventory into Snowflake as typed tables you can join to the rest of your warehouse, on a schedule you choose.
The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.
What to know about Shopify into Snowflake
Nested order data arrives as OBJECT and ARRAY columns
Shopify orders carry line items, addresses and discounts as nested structures. Snowflake receives them as OBJECT and ARRAY columns rather than flattened tables, so you query them with FLATTEN in place and keep the raw structure for fields Shopify adds later.
Use deduped incremental for streams that change
Shopify's source syncs incrementally by appending changes. An order edited after it was first synced would otherwise appear twice. For streams with a primary key such as orders and customers, choose Incremental | Append + Deduped, and the Snowflake final table holds the latest version of each row while the raw table keeps the history.
One schema, no namespaces
The Shopify source has no namespaces, so every stream lands in the single schema you configure on the Snowflake destination. Pick a dedicated schema (for example SHOPIFY) so Shopify tables do not mix with other sources.
Customer data needs governance from day one
The customers and orders streams include personal data. Create the Pipeloom role with write access only, and apply masking or row access policies in Snowflake before analysts get read access.
Example: one row per order line item
SELECT
o.ID AS order_id,
li.value:title::STRING AS product,
li.value:quantity::NUMBER AS quantity
FROM SHOPIFY.ORDERS o,
LATERAL FLATTEN(input => o.LINE_ITEMS) li;Assumes a schema named SHOPIFY and the default typed tables. Snowflake upper-cases unquoted names, so the table is ORDERS and the column LINE_ITEMS.
Set it up
- Create the Shopify sourceAuthenticate with OAuth 2.0, or an API password for a private app. You need: An active Shopify store. If it is a client's store, request access to it first. A custom Shopify app with read_ scopes enabled (for example read_customers, read_orders, read_discounts, read_fulfillments).
- Create the Snowflake destinationAuthenticate with Key pair (an RSA key of 2048 bits or more). You need: A Snowflake account and the ACCOUNTADMIN role for the one-time setup. A warehouse, database, user and role for Pipeloom, with a key pair attached to the user. Network policies that allow Pipeloom to connect.
- Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Shopify supports full refresh and incremental (append); Snowflake supports every mode: full refresh overwrite, overwrite deduped, append, incremental append and incremental append deduped.
- Run, then scheduleThe first sync loads each stream in full. Then set the schedule.
What lands in Snowflake
- A final table per stream with typed columns, plus _AIRBYTE_RAW_ID, _AIRBYTE_GENERATION_ID, _AIRBYTE_EXTRACTED_AT, _AIRBYTE_LOADED_AT and _AIRBYTE_META.
- A raw table per stream in the airbyte_internal schema (or the internal dataset name you set), holding each record as JSON.
Type mapping
- object → OBJECT
- array → ARRAY
- union and unknown → VARIANT
- timestamp with time zone → TIMESTAMP_TZ
- integer → NUMBER
- number → FLOAT (or NUMBER(38,9))
When Shopify's schema changes
New columns are added and types changed as the source changes. A column removed at the source is kept, with its data, and new rows get NULL. Column names that are SQL reserved words get a leading underscore.
Streams you can sync
Includes Orders, Customers, Abandoned Checkouts, Draft Orders, Fulfillments, Inventory Levels (GraphQL), Metafields (GraphQL), Discount Codes (GraphQL) and more. The full list is in the reference.
What it costs
A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 4 minutes uses 10 credits. Run 24 times a day, that is about 7,200 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.
Questions and fixes
Which Shopify data can I sync to Snowflake?
Orders, customers, abandoned checkouts, draft orders, fulfillments, inventory, collections, metafields, discount codes and more. The source reference lists every stream, and you choose which to sync.
How often can it run?
On Free, as often as hourly. On Starter and Team, as often as every 15 minutes.
Will it load my whole history the first time?
Yes. The first sync is a full load of each selected stream, and later syncs are incremental. Large stores take longer on the first run.
Why do I see a "Caught retryable error" warning?
The connector hit Shopify's rate limit (HTTP 429). It backs off and the sync carries on, so the warning is expected on busy stores.
What does "checkpoint collision is detected" mean?
An incremental GraphQL bulk stream (most often a metafield stream or discount_codes) found more rows sharing one cursor value than the BULK Job checkpoint (rows collected) setting allows. If a later sync succeeded it cleared itself. If not, raise that setting in steps; it must exceed the number of rows sharing the value, up to 1,000,000.
Why does it say another BULK job is running?
Shopify allows one bulk operation at a time per store. Check the operation's progress with Shopify's bulk API, or cancel it, then rerun.
Why does the sync fail with "Current role does not have permissions on the target schema"?
The Pipeloom role is missing a grant on the schema it writes to. Re-run the role setup from the Snowflake guide, or grant the role usage and create-table rights on that schema.
Sync Shopify to Snowflake today
Free forever, no card. Starter is $19 a month for three seats.