Two data sources, one store — syncing WooCommerce with ERP and a catalog spreadsheet

I work on an online store running WooCommerce — several to tens of thousands of SKUs, weekly promotions, integration with a delivery platform, data from a POS system and a separate catalog file from the back office. One source of truth? In theory, yes. In practice — never.

I knew from the start that a single “universal importer” and admin CSV import would not work at this scale — from earlier e-commerce projects I know the WooCommerce API does not hold up under bulk meta and term updates. Instead I designed two independent pipelines, each for a different source and a different job. Below is the architecture and the specific problems: where they come from, what they break, and what I put in place to prevent them.

Context: scale and architecture

The store I work on has tens of thousands of products. The sync cron runs every few to several minutes — several data layers must stay aligned in that window:

• Stock from POS — the customer sees on the site what is physically available (or is deliberately hidden in Archive).
• Prices and promotions — including dates, omnibus requirements and reference price; a mismatch here ends in complaints or regulatory scrutiny.
• Product catalog — names, category tree, filter tags, descriptions, images; the layer the customer browses before adding to cart.
• Site visibility vs. shelf availability — a product can exist in ERP but not yet be sold online (or the other way around).
• Export to delivery platforms — stock and prices must match what the customer sees in the store.

WooCommerce is the presentation and sales layer, but it is not the source of truth for assortment. There are two sources of truth — each owns a different slice of data. That is why two scripts instead of one.

Two sources, two jobs

The pipelines run in parallel — CSV does not feed XML and vice versa. Both write to the same WooCommerce database, but each updates different product fields.

1. Catalog CSV file — “what we sell at all”

This script handles assortment structure — what the back office keeps in the catalog spreadsheet and what lands in the store as description, category and tree visibility. I run it less often than stock sync (the catalog changes slower than shelf levels), but each run touches hundreds or thousands of products at once.

What it does in practice:

• Fetch and conversion — file comes from FTP in Windows-1250, I convert to UTF-8 before anything hits the parser; without that Polish characters in categories break at the gate.
• Parsing — rows with ; separator, typical Excel or ERP export; each row is one product with GTIN, name, base price and category assignment.
• Identification by GTIN — barcode as primary key; internal SKU varies between systems.
• Create and update — new lines with a sensible price get a product card; existing ones get refreshed categories, tags (organic, frozen, 18+, vegan…), descriptions, images, brands.
• Archive — missing from CSV does not delete the product; it goes to Archive (soft delete) so URL and order history stay intact.
• Restore — back in the catalog file → pull from Archive and reassign the category saved earlier in metadata.

2. POS XML export — “how much is on the shelf and for how much”

The second script is the operational layer — checkout data that changes often: stock, current price, promotion, end date. Cron runs it every few minutes; XML files are tens of MB, so streaming parse was assumed from the start, not loading the whole file into RAM.

What it does in practice:

• Fetch from FTP — separate directory and file pattern from the catalog pipeline; both processes have their own lockfile so they do not write to the database at the same time.
• Streaming XML — XMLReader instead of SimpleXML/DOMDocument; I read record by record without holding the full tree in memory.
• Encoding — Latin II / CP1250 / ISO-8859-2 → UTF-8 block by block; POS rarely declares encoding correctly in the header.
• Commercial update — stock, prices, promotions, promotion dates, omnibus info, reference price; the only fields this script touches.
• Promotion mapping — symbols from POS (short codes) → WooCommerce attribute slugs; raw POS text does not go straight to the front end.
• Archive and restore — no stock and price → Archive; stock > 0 → category restored from saved metadata; new item only at checkout → create product in WooCommerce as fallback.

Division of responsibility

Aspect	Catalog CSV	POS XML export
Creating products	yes	yes (fallback)
Categories, tags, descriptions	yes	no
Images	yes	no
Stock	no	yes
Prices, promotions	no	yes
Archiving	missing from CSV	no stock/price

A product can be in the catalog but unavailable on the site (POS: stock = 0) — and vice versa. That comes from two independent business processes, not a sync bug. I assumed this mismatch from the start; Archive + saved _prod_cat metadata is part of the design, not a post-hoc fix.

Problems: causes, consequences, countermeasures

1. Slow import through the WooCommerce API

Cause: every update via update_post_meta(), wp_set_object_terms() or wc_get_product_id_by_sku() fires WooCommerce hooks, invalidates cache and recalculates term_taxonomy.count. With tens of thousands of SKUs that is hundreds of thousands of operations in one run.

Consequences: imports lasting hours, growing RAM usage, a window where the store is under heavy write load — cron timeouts and collisions with user traffic.

Countermeasure: I planned a batch SQL layer from the start. Field mapping was verified through the API first (faster to iterate on logic); production runs in batches of 200 products, INSERT ... ON DUPLICATE KEY UPDATE on postmeta, bulk DELETE + INSERT on term_relationships, one UPDATE on term_taxonomy.count at the end, transactions with three retries and rollback. Import drops from an hour to minutes because I bypass WC hooks where they are not needed.

2. Memory in a long-running PHP process

Cause: WordPress loads the full runtime; every get_post_meta() and product lookup adds to object cache. Iterating over the full catalog makes cache grow linearly until the script hits memory_limit.

Consequences: random crashes mid-import, incomplete transactions, products updated only partially.

Countermeasure: from earlier imports I knew per-product lookups had to go — preload metadata with one SQL query, runtime cache in PHP arrays (meta, terms, post data), every 10,000 products wp_cache_flush() + gc_collect_cycles(). For input files — streaming (generator/yield, XMLReader), because loading a 60 MB XML into memory is the same problem in a different shape.

3. Promotion time zones

Cause: POS stores promotion dates in local time (Europe/Warsaw), WooCommerce keeps _sale_price_dates_* as Unix timestamps in UTC. strtotime() with default UTC interprets local dates as UTC.

Consequences: promotions start and end about 2 hours too early or too late — visible at midnight and during DST, hard to diagnose without comparing timestamps.

Countermeasure: DateTimeImmutable with explicit Europe/Warsaw zone → timestamp conversion. I know this pattern from earlier promotion integrations; here I applied it in the XML parsing layer from the start, without relying on PHP’s default timezone.

4. Polish character encoding

Cause: CSV from ERP/Excel in Windows-1250, XML from POS in Latin II / ISO-8859-2. Sources do not declare encoding consistently and PHP defaults to UTF-8.

Consequences: mojibake in categories and descriptions, rejected rows, broken front-end filters (a “frozen” tag not attached despite a correct name in ERP).

Countermeasure: encoding detection from XML header + heuristics for CSV, streaming conversion block by block (1 MB), atomic file swap with rename(). Standard approach for Polish integrations — no exception here.

5. Product identification across systems

Cause: POS uses internal SKU, catalog file uses GTIN, sometimes one identifier is missing, sometimes XML has several barcodes per line.

Consequences: duplicate products, updates hitting the wrong row, “zombie” entries with no link between pipelines.

Countermeasure: GTIN as primary join key (barcode changes less often than internal SKU), fallback to SKU, with multiple EANs in XML — first sensible one (≥ 8 characters). Before import I build a GTIN → post_id index in one query instead of searching in a loop.

6. Promotions with non-standard values

Cause: POS sends a promotion symbol (short code) or free-text description. WooCommerce expects a taxonomy attribute slug — raw data lands straight in pa_promo.

Consequences: attributes with full marketing copy instead of filters, messy promotion presentation, unusable product list filters.

Countermeasure: whitelist of POS symbols → standardized attribute slugs; unknown symbol → clear the attribute instead of storing garbage. Mapping lives in script config, not ad hoc logic — from previous deployments I know POS raw data always needs a normalization layer.

7. Parallel import runs

Cause: cron every X minutes plus manual runs during debugging. Two processes write to the same tables at once.

Consequences: race conditions, double inserts, broken term_taxonomy.count, hard-to-reproduce intermediate states.

Countermeasure: lockfile with flock(LOCK_EX | LOCK_NB) — the second process sees the lock and exits cleanly. Same pattern in both scripts; it is the first thing I add to production cron jobs.

8. Overwriting external PIM data

Cause: some products have enriched data (images, names) from an external PIM provider. Catalog import overwrites title and thumbnail by default.

Consequences: loss of quality images and names, regression after every CSV sync cycle.

Countermeasure: source flag on the product — when set, I skip title and thumbnail in the catalog layer. CSV updates only what it owns (categories, tags, own descriptions).

9. Stale prices and stock after import

Cause: WooCommerce keeps aggregates in transients and lookup tables (wc_product_meta_lookup). Direct postmeta writes bypass the mechanisms that normally refresh them.

Consequences: front end shows old prices/stock despite correct DB data, price filters return wrong results.

Countermeasure: at the end of each catalog sync cycle I call:

wc_update_product_lookup_tables();
wc_scheduled_sales();
wc_delete_product_transients();
wc_delete_shop_order_transients();

Not a discovery — mandatory after every bulk import outside the WooCommerce API. Skipping this ends in “the store shows the wrong price” support tickets.

10. Catalog vs. stock mismatch

Cause: assortment in the CSV (what is offered) ≠ stock in POS (what is physically available). Two independent business cycles — back office updates catalog, checkout updates stock.

Consequences: product visible on site but unavailable; product at checkout with no store listing; broken expectations for customers and delivery platforms.

Countermeasure: Archive as soft delete (product stays in DB, loses visibility), save original category in _prod_cat before archiving, automatic restore when stock returns, create product from POS when item appears only at checkout. I rejected hard delete on purpose — URLs, order history and SEO stay intact.

Technical architecture (simplified)

Both pipelines start independently — they only share the destination (WooCommerce database) and write pattern (batch SQL); these are two parallel data streams.

Shared elements in both scripts:

• WordPress bootstrap via wp-load.php without theme — I need WC functions and DB access, not the front end.
• Lockfile (flock) — cron every X minutes plus manual runs; without a lock two processes overwrite each other’s data.
• Logging to file + error_log on errors — with tens of thousands of SKUs “it silently died halfway” is a nightmare to debug.
• File archival after processing (zip + FTP upload) — to know which export was last and replay state if something goes wrong.

What’s next

Sync works but needs maintenance — monitoring lag between POS and online visibility, cart price consistency after import, stock push to external platforms. Most of the traps above I already know from earlier deployments; here the point was to design for scale and two sources up front, not patch a single importer.

If you run a WooCommerce store with multiple data sources and feel that “CSV import” is not enough — you are probably right.