The problem
With a very large catalogue, even a small percentage of wrong prices means a lot of products. Some were showing the wrong price on the storefront. Each product can be supplied from several sources, and the price customers see should come from the preferred source that actually has stock. Over time, some prices had drifted away from that rule.
The tempting fix was to correct the affected products by hand and move on. That would have made the symptom disappear for a week. I wanted to know why prices were drifting, so the fix would hold.
Finding the cause
- I wrote down the business rule precisely: rank stock sources in the business’s priority order, count only sources with stock, and exclude products that follow different pricing rules.
- I turned that rule into a ranked SQL query using window functions, so every product’s correct price could be calculated and compared with the live one.
- Comparing the two showed which products were wrong and, more importantly, the pattern behind them. The drift came from two specific data paths that were overwriting the preferred price with a default one.
Fixing it safely
- A correction script built to be trusted. It processes products in batches, records successes and failures to separate files, and saves its progress, so it can be stopped and resumed without redoing or skipping work.
- A monitoring query that can be re-run during and after the fix to show exactly how many products are still inconsistent.
- Staging first. Every step ran on staging before production.
Making it stick
A one-off clean-up only lasts until the next import, so I automated it. A scheduled job now runs every evening on production and staging. It finds inconsistent prices, resets them to the correct source, refreshes the systems that depend on it, and writes timestamped logs and a list of affected products, so the business can audit every change.
What I would do next
The scheduled job is a safety net, not the end state. Because the analysis identified the exact import and update paths that cause the drift, the next step is to correct the pricing rule inside those paths, so wrong prices are never written in the first place. The nightly job then becomes a monitor that should find nothing.
Fix the cause, not the symptom, and make the fix visible enough that people can trust it.
