Hourly Shopify orders sync to BigQuery

Every hour on the hour, list new Shopify orders created since the last successful run and stream them into a BigQuery table named shopify.orders_raw. This is a deterministic pipe from an ecommerce source into a warehouse — no reasoning, no drafting. Use a code workflow.

Cursor. Persist the highest updated_at seen so far in workflow state (fall back to the workflow start time on the very first run). On each run, call Shopify's List Orders with updated_at_min set to that cursor, status=any, and paginate through the Link header until there are no more pages. When the batch is done, update the cursor to the maximum updated_at across every order returned. Using updated_at instead of created_at means edits, cancellations, and refund state changes flow into the warehouse too.

Row shape. For each order, produce one flat row with these columns: order_id (integer), created_at (timestamp), customer_email (string), financial_status (string), fulfillment_status (string), subtotal (numeric), total (numeric), currency (string), line_item_count (integer), and line_items (JSON — the raw line_items array from the Shopify order). Shopify returns monetary amounts as strings, so parse subtotal_price and total_price to numeric before inserting. Preserve the whole line_items array as a JSON column so downstream SQL can unnest SKUs and quantities without another API round trip.

Insert. Send the mapped rows to BigQuery via Stream Insert Rows against dataset shopify, table orders_raw. Batch rows into chunks under BigQuery's 10 MB / 50,000 row per-request limit. Use the order_id as insertId so retries are naturally deduplicated by BigQuery's best-effort dedupe.

Row-level error handling — this is the whole point. Streaming inserts return HTTP 200 even when individual rows fail. Inspect insertErrors on every response. If it is empty or missing, all rows in that request succeeded. If any entry is present, do NOT advance the cursor, do NOT swallow the error — throw with the failing row indices and the BigQuery error messages so the run fails loudly and the operator sees it. Silently dropping orders is worse than a failed run.

Prerequisites the workflow assumes: the BigQuery dataset shopify exists in the target project, and the table orders_raw exists with a schema matching the row shape above (order_id INT64, created_at TIMESTAMP, customer_email STRING, financial_status STRING, fulfillment_status STRING, subtotal NUMERIC, total NUMERIC, currency STRING, line_item_count INT64, line_items JSON). Do not try to create the dataset or table inside this workflow.