Parts Found From Fragments and Typos for a Replacement Parts Distributor

The challenge

The distributor's website is where technicians, repair shops and consumers look for the exact part they need among millions of items. Search ran directly on SQL Server and matched part numbers only from their first character. One typo or a missing dash returned nothing, and a phrase such as “ice maker” gave unreliable results.

Part numbers made the problem harder. Standard full-text search treats each part number as one whole word, so a fragment from the middle of the number could not find the part. The catalog also had to stay current. Prices, stock and descriptions changed through the day, across more than 14 million product and model records.

What we delivered

SquareShift built the search platform on Elasticsearch in Elastic Cloud Serverless on Google Cloud. Separate Logstash pipelines keep products, models, brands and curated search terms in step with SQL Server. Each pipeline picks up only changed rows, with times converted to UTC. A look-back window catches records updated during a run, and each record keeps its SQL key, so none is duplicated.

A layered query ranks exact phrases first, then matches on all terms, then close matches for misspellings, and breaks ties by product demand. A full part number, with or without dashes, returns one result so the site can go straight to the part page. Fragments of every part number are now indexed in advance, which replaced costly wildcard queries.

A second pipeline exports search history to Google BigQuery every day, where the reporting team already works. The new search was refined on a test version of the website against real searches before it went live. Later changes to the index structure went onto new indexes built beside the old ones, so live search was not disrupted. Every change shipped with step-by-step documentation.

The outcome

Customers now find parts from any fragment of a part number or description, and search tolerates typos. A full part number takes them straight to the part page, and popular parts rank higher. In testing, search on part-number fragments ran about 35% faster than with wildcard queries.

More than 14 million records match SQL Server exactly, with no duplicates and no manual full rebuilds. Daily search data in BigQuery shows the business what customers look for and which searches return nothing. The client's own team can now run and tune the platform from its step-by-step documentation.