Shopify to BigQuery

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 BigQuery

Example: one row per order line item

SELECT
  o.id AS order_id,
  JSON_VALUE(li, '$.title') AS product,
  SAFE_CAST(JSON_VALUE(li, '$.quantity') AS INT64) AS quantity
FROM shopify.orders AS o,
  UNNEST(JSON_QUERY_ARRAY(o.line_items)) AS li
WHERE o._airbyte_extracted_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

Assumes a dataset named shopify and the default typed tables. Adjust names to match your destination.

Set it up

1. 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).
2. Create the BigQuery destinationAuthenticate with A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, using a JSON key. You need: A Google Cloud project with BigQuery turned on, and a dataset to write to. Create it in the same location as the datasets you will join against. A service account with the BigQuery User and BigQuery Data Editor roles. For production loads, a Cloud Storage bucket for staging with an HMAC key, and the Storage Object Admin role for the service account.
3. Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Shopify supports full refresh and incremental (append); BigQuery supports every mode: full refresh overwrite, append and overwrite deduped, incremental append and incremental append deduped.
4. Run, then scheduleThe first sync loads each stream in full. Then set the schedule.

Step-by-step guides: Shopify and BigQuery.

What lands in BigQuery

• A table per stream with typed columns plus _airbyte_raw_id, _airbyte_generation_id, _airbyte_extracted_at and _airbyte_meta.
• Tables partitioned by day on _airbyte_extracted_at and clustered by that column and the primary key, so filtering on the partition column scans less data.

Type mapping

• object and array → JSON
• string → STRING
• integer → INT64
• number → NUMERIC
• timestamp with time zone → TIMESTAMP
• timestamp without time zone → DATETIME
• boolean → BOOL

When Shopify's schema changes

The destination tables are updated as the source changes. Namespaces map to BigQuery datasets, and invalid characters in names are replaced with underscores.

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