Aggregating auto-parts catalogs from several distributors sounds like a plumbing problem until you actually try it. Once you are ingesting feeds from four different suppliers, each with its own format, encoding, and idea of what a "product" is, the hard part stops being the download and becomes reconciliation. Here are the lessons that stuck after building a catalog that now spans more than 180,000 SKUs.
Every supplier speaks a different dialect
One supplier ships a 1.4M-row comma-separated file over SFTP with space-padded columns. Another gives you a UTF-8-BOM CSV plus a separate stock-only file that refreshes more often. A third has no feed at all and has to be scraped. A fourth hands you a Latin-1 snapshot with tri-state stock (Yes / Low / No) and no barcodes anywhere.
The practical takeaway: normalize aggressively at the edge and keep the raw parse dumb. We stream each feed straight into an UNLOGGED staging table with COPY, then do a single set-based upsert. Trying to be clever in the row parser (or pushing parsing into SQL) was consistently slower than a plain streaming parser feeding a bulk load, because the workload is I/O-bound, not CPU-bound.
Matching products across suppliers is the whole game
The moment two suppliers sell the same part, you want to show cost, margin, and stock side by side. That requires deciding they are the same part. GTIN/UPC is the strongest signal, but barcodes are messy: some are junk (too short), some suppliers omit them entirely, and manufacturer part numbers collide across brands.
We settled on honest, explicit match tiers instead of one fuzzy score:
- exact — both suppliers carry a valid GTIN-14 and they agree.
- verified — a GTIN backfilled from an authoritative echo (a product page UPC).
- probable — manufacturer part number plus brand agreement, no barcode.
- none — single supplier, no cross-match.
Normalization (stripping, casing, check-digit handling) is the actual contract here, so it lives in exactly one place and is unit-tested. A backfilled or MPN-based link never gets to masquerade as a hard dual-barcode match. Keeping the confidence label honest is what keeps buyers from seeing two unrelated parts merged into one listing.
Dropship fulfilment needs a fast stock path
Listing a part you cannot ship is worse than not listing it. A daily full feed is fine for pricing but too stale for inventory, so the stock-only refresh runs on a much tighter cadence and a 5-minute oversell guard pushes quantity zero to every sales channel the instant a supplier goes out of stock.
If you are curious what this looks like as a live storefront, it powers Vinyasa Automotive. The engineering lesson generalizes to any multi-source catalog: keep parsing dumb, make matching explicit and testable, and treat inventory freshness as a first-class latency problem.
Top comments (0)