Epic fail while implementing third-party integration with Shopify

Intro

Integrating systems that were built with completely different assumptions about how data works is harder than it looks. Much harder.

The initial problem was standard: Add third-party inventory to a Shopify store. Catalog and pricing available via SFTP. Push orders back to the supplier Update orders with tracking numbers from the supplier

Something like this: Any experienced engineer knows a “simple” task like this is full of corner cases. I’ve spent 15 years in e-Commerce — I thought I’d seen everything. This time, I was first firmly humbled, then humiliated by the gods of e-Commerce.

Chapter one, where I deal with common edge cases

After carefully reading supplier data exchange specification, I started working on common edge cases. I needed to make sure imported data wouldn’t be duplicated. So I designed the file import to be idempotent — files with the same name and content get processed once, no matter how many times the system encounters them.

Because supplier system used file creation/modification timestamps to inform us about updates, I used internal SyncMyOrders database to store information about processed files.

This one worked like a charm and became a reusable automation for many other parts of this project. Once the file part was fully ironed out, I handled many common edge cases to ensure import itself is properly handling various cases, like avoiding duplicates, handling multiple locations, and syncing only a subset of suppliers catalog.

As you can see on the GIF above, the catalog import is rather complicated, and based on the number of branches, you can see it covers many different “what ifs”.

Chapter two, me being a smart cookie

After some quick math, it became clear we can’t brute force our way during the synchronization - the size of the suppliers catalog meant we would consume all Shopify API limits, if we sync it in full every time.

I was prepared for this, though a bit annoyed by the fact supplier just sent the whole catalog every time. Later, I would learn exactly why the supplier didn’t bother with delta updates. An undocumented, hidden business process on the supplier side, which dramatically affected my relationship with our customer and destroyed months of built-up trust.

Okay, so back to the integration. Since 1:1 data push would result in too many API calls (and customer already had issues with another tool sending too many API requests), I went with caching and building internal delta updates mechanism.

SyncMyOrders has a database engine built-in, which allowed me to detect changes in supplier data files, and only push modified data to Shopify.

This resulted in impressive synchronization speed, drastically reduced calls to Shopify, and I unknowingly prepared a foundation for an embarrassing failure in the future.

Chapter three, when I’m being slapped in the face. Twice

The first disturbing signal came when I got an email from customer saying there is a discrepancy between supplier inventory and data in Shopify. A sticky, unpleasant anxiety started growing inside me. Logically, everything was sound - all the corner cases covered, synchronization intervals were designed to reduce probability of discrepancies, but still SOMETHING has happened.

I double-checked everything, ran several tests and increased synchronization rate to ensure we never face this situation again. And everything was fine, until my customer reported several more discrepancies and I realized the trust was ruined. The problem was not discrepancy itself, but the fact it happened for products with stock out - so basically supplier didn’t have the product anymore, and my customer still had it in the store.

After analyzing all the discrepancies, comparing historical files, and email exchange with the supplier, I figured out I’d made an embarrassingly wrong assumption from the start. I took a file with inventory and looked at zeros - it’s a normal thing for supplier to run out of some products, and of course this case was handled. But then I noticed something was off. Products with discrepancies in Shopify were absent in supplier catalog completely!

An email to supplier IT department confirmed my wildest fear - they didn’t simply put products without stock to zero, they also completely removed it from the file. Something, I failed to notice during the initial design step. A small detail, not clearly reflected in the specification as well.

It became clear why they were not providing delta updates - it would not be possible to reflect such a change in their existing file format. And in 1:1 full catalog sync the problem would not exist, except for the fact it would completely break synchronization with limited destinations.

I’d love to say I immediately resolved the issue, explained everything to the customer and the story has a happy end. Instead, I shot myself in the leg for the second time. Once the issue has been identified, automation was updated to handle disappearing catalog items correctly. I got an angry letter from the customer on the same topic again and realized I’m completely cooked. I experienced something worse than second-hand embarrassment — first-hand embarrassment at my own oversight. While the issue has been fixed, a rate-limit sync process was slowly processing discrepancies, and I got unlucky enough for some of the discrepancies to be “moved to another day” - those were skipped due to per-day optimization limit. And predictably met without any excitement by our customer.

The Finale

After sending another awkward email, embarrassed as I’ve never been, I ran the script several times to pick up all the batches of affected products, created a shell script to verify product catalog is fully in sync and scheduled post-mortem with the customer. This purpose-built test suite ensures discrepancies can be easily detected and explained.

Additionally, automation was extended with more edge cases to further reduce the probability of discrepancy. Now stockouts are immediately pushed to Shopify without any delays to avoid unpleasant surprises in the future. Combined with the verification script, it allowed to achieve 99.9% match rate.

Systems built by different teams, for different purposes, with different definitions of ‘out of stock’ will find every gap in your integration logic.

The only defense is building verification into the process itself — not trusting that your design covers everything.