How can I prevent duplicate Shopify orders and line items across two scheduled Google Sheets scenarios?

[Pistamiglio_Omar]

What is your goal?

I want to make two connected Make scenarios fully idempotent and reliable before increasing Shopify traffic through advertising.

Scenario 1 runs every 30 minutes and imports paid Shopify orders into an Orders_Log sheet. It stores the Shopify order_id and sets order_items_logged = NO.

Scenario 2 runs every 15 minutes. It searches Orders_Log for paid orders where order_items_logged = NO, retrieves the Shopify order, iterates its line items, adds them to Order_Items_Log, and finally updates the original order row to order_items_logged = YES.

My goal is to prevent duplicate order rows and duplicate line-item rows in case of retries, incomplete executions, timeouts, or overlapping scenario runs.

What is the problem & what have you tried?

The scenarios are currently working, but I would like to validate the architecture before the order volume increases.

My main concern is that two executions of Scenario 2 could select the same order while order_items_logged is still NO. A retry or incomplete execution could also add the same line items more than once before the final status update is completed.

Currently:

1. Orders_Log uses Shopify order_id as the main order identifier.
2. Scenario 2 filters:
   • financial_status = paid
   • analysis_include = YES
   • order_items_logged = NO
3. It retrieves the Shopify order.
4. It iterates all line items.
5. It adds one row per item to Order_Items_Log.
6. Only after all items have been added, it updates Orders_Log:
   • order_items_logged = YES
   • status = logged

I am considering using:

• Shopify order_id as the unique key in Orders_Log.
• order_id + line_item_id as the unique key in Order_Items_Log.
• A temporary status such as processing before starting the Iterator.
• Sequential processing or limiting the number of simultaneous executions.
• A search-before-insert or update/upsert pattern instead of always using Add a Row.

Could you please advise on the safest Make design?

Specifically:

1. Is order_id + line_item_id the correct idempotency key for Shopify line items?
2. Should I change the order status from NO to processing before the Iterator begins?
3. How can I prevent another execution from selecting the same processing order?
4. Is sequential processing sufficient, or should I also set the maximum number of simultaneous runs to 1?
5. Should each line item be searched in Google Sheets before being added?
6. Would an upsert pattern be safer than Add a Row?
7. What should happen if three line items are added successfully but the fourth one fails?
8. Which settings do you recommend for incomplete executions, automatic retries, and error handlers?
9. Are there particular risks when Google Sheets row numbers change or when multiple executions update the same sheet?
10. What is the recommended recovery procedure for orders left in processing after a failed execution?

Error messages or input/output bundles

There is no active error at the moment. This is a preventive architecture and idempotency review.

The main failure cases I want to protect against are:

• The same Shopify order being selected by two scenario executions.
• A retry adding the same line items again.
• An execution failing after only some line items have been written.
• The final Orders_Log update not running after the Iterator.
• Google Sheets rows being updated incorrectly because of concurrent changes.

[Stoyan_Vatov]

Why is this two separate scenarios? And why are they running on a timer?

Why not use an instant trigger when an order arrives, pull its line items directly and log it?

[Pistamiglio_Omar]

That is a fair question.

The two scenarios were created at different stages of the project.

Scenario 1 was built first and is already stable. It handles the order-level operational workflow, including Orders_Log, delivery data, slot availability and downstream operational processes.

Scenario 2 was added later for analytics. It retrieves the full Shopify order, iterates the line items, looks up product costs, calculates net revenue, cost and margin, and writes one row per item into Order_Items_Log.

We kept them separate mainly because:

1. We did not want to modify or risk the already stable operational scenario.
2. The line-item and cost calculation flow is more complex and can fail independently.
3. Orders_Log acts as a queue, so an order can be reprocessed if the analytics step fails.
4. The second scenario can process only rows still marked order_items_logged = NO.

However, I understand that an instant Shopify trigger and a single scenario could reduce concurrency and duplication risks.

Would you recommend one of these architectures?

A. One instant Shopify trigger that writes both the order and all line items in the same scenario.

B. One instant parent scenario for the order, which then calls a separate sub-scenario for line items.

C. Keep the current queue-based design, but add a processing status, sequential execution and idempotency checks.

My concern with a single scenario is partial failure: for example, the order row is written successfully, three line items are written, and the fourth line item fails.

What would be the safest Make architecture for recovering from that failure without duplicating the previously written rows?

[Stoyan_Vatov]

B definitely - add it as a subscenario that the main scenario triggers once its done. Then it can fail on its own without breaking the main scenario.

[Pistamiglio_Omar]

Thank you, that confirms option B.

Before rebuilding it, could you please clarify the safest implementation details in Make?

1. What is the recommended way for the main scenario to call the line-items subscenario?

   • Scenario inputs/outputs
   • Custom webhook
   • Another Make module or method

2. Should the subscenario be called synchronously or asynchronously?

3. What data should the main scenario pass?
   Would passing only the Shopify order_id and retrieving the full order again in the subscenario be the safest option?

4. To prevent duplicate line items, would you recommend:

   • searching Google Sheets for order_id + line_item_id before inserting;
   • using a Make Data Store as an idempotency registry;
   • or another upsert/locking approach?

5. If the subscenario writes three line items and then fails on the fourth, what is the best recovery design?
   Should a retry search for the existing order_id + line_item_id keys and insert only the missing item?

6. Should the main Orders_Log row use statuses such as:
   pending → processing → logged → failed?

7. With an instant Shopify trigger and a separate subscenario, is sequential processing still recommended?

8. Should incomplete executions and automatic retries be enabled only on the subscenario, or on both scenarios?

The current system is already working, so I want to rebuild it safely in a cloned scenario and switch only after successful testing.

[Stoyan_Vatov]

I prefer the custom webhook, since it can have its own separate queue and the main scenario is not dependent on anything that happens in the subscenario this way. So the subscenario can run, fail, rerun, whatever, completely independently of the main one.

Pass whatever data is relevant so the subscenario doesn’t need to search for it again. Like the sheet row number, the order ID, etc.

There shouldn’t be duplicates since the sub scenario will only start when the main scenario adds an item.

What are the causes for failure? Timeouts can be handled with a retry module for example. Or you can use a rollback module to delete the ones that were already entered and retry the entire scenario.

Sure, you can use statuses. Main scenario sets it to pending, when subscenario starts, set it to processing and depending on the outcome, set it to either logged or failed.

Yes, google sheets module will always write on the first available row. So if two items trigger the scenario at the same time, you can get conflicts.

I’ll be honest with you, I still use dedicated Retry error handlers and have not relied on the automatic retries yet.

[Pistamiglio_Omar]

Thank you, this is very helpful.

We will plan to use:

• a custom webhook with its own queue;
• the main scenario setting the order status to pending;
• the subscenario changing it to processing, logged or failed;
• Process data in order on the webhook subscenario;
• dedicated Retry error handlers.

For additional idempotency, I am planning to create a unique item key:

order_id + “|” + line_item_id

Before adding each row to Google Sheets, the subscenario would search for this key and insert the row only if it does not already exist.

This should also allow a failed execution to be rerun safely, because the existing items would be skipped and only the missing items would be inserted.

Would you agree with keeping this idempotency check even when using the custom webhook queue?

[Stoyan_Vatov]

Sure, it will ensure only unique entries and be an additional layer of protection if something fails