match tables with intermediate mapping table (fuzzy joins with similar strings)

Question

I'm using BigQuery. I have two simple tables with "bad" data quality from our systems. One represents revenue and the other production rows for bus journeys. I need to match every journey to a revenue transaction but I only have a set of fields and no key and I don't really know how to do this matching.

This is a sample of the data:

Revenue

Year, Agreement, Station_origin, Station_destination, Product
2020, 123123, London, Manchester, Qwerty

Journeys

Year, Agreement, Station_origin, Station_destination, Product
2020, 123123, Kings Cross, Piccadilly Gardens, Qwer
2020, 123123, Kings Cross, Victoria Station, Qwert
2020, 123123, London, Manchester, Qwerty

Every station has a maximum of 9 alternative names and these are stored in a "station" table.

Stations

Station Name, Station Name 2, Station Name 3,...
London, Kings Cross, Euston,...
Manchester, Piccadilly Gardens, Victoria Station,...

I would like to test matching or joining the tables first with the original fields. This will generate some matches but there are many journeys that are not matched. For the unmatched revenue rows, I would like to change the product name (shorten it to two letters and possibly get many matches from production table) and then station names by first change the station_origin and then station_destination. When using a shorter product name I could possibly get many matches but I want the row from the production table with the most common product. Something like this:

1. Do a direct match. That is, I can use the fields as they are in the tables.
2. Do a match where the revenue.product is changed by shortening it to two letters. substr(product,0,2)
3. Change the rev.station_origin to the first alternative, Station Name 2, and then try a join. The product or other station are not changed.
4. Change the rev.station_origin to the first alternative, Station Name 2, and then try a join. The product is changed as above with a substr(product,0,2) but rev.station_destination is not changed.
5. Change the rev.station_destination to the first alternative, Station Name 2, and then try a join. The product or other station are not changed.

I was told that maybe I should create an intermediate table with all combinations of stations and products and let a rank column decide the order. The station names in the station's table are in order of importance so "station name" is more important than "station name 2" and so on.

I started to do a query with a subquery per rank and do a UNION ALL but there are so many combinations that there must be another way to do this.

Don't know if this makes any sense but I would appreciate any help or ideas to do this in a better way. Cheers, Cris

Answer 1

To implement a complex joining strategy with approximate matching, it might make more sense to define the strategy within JavaScript - and call the function from a BigQuery SQL query.

For example, the following query does the following steps:

• Take the top 200 male names in the US.
• Find if one of the top 200 female names matches.
• If not, look for the most similar female name within the options.

Note that the logic to choose the closest option is encapsulated within the JS UDF fhoffa.x.fuzzy_extract_one(). See https://medium.com/@hoffa/new-in-bigquery-persistent-udfs-c9ea4100fd83 to learn more about this.

WITH data AS (
  SELECT name, gender, SUM(number) c
  FROM `bigquery-public-data.usa_names.usa_1910_2013` 
  GROUP BY 1,2
), top_men AS (
  SELECT * FROM data WHERE gender='M'
  ORDER BY c DESC LIMIT 200
), top_women AS (
  SELECT * FROM data WHERE gender='F'
  ORDER BY c DESC LIMIT 200
)

SELECT name male_name, 
  COALESCE(
    (SELECT name FROM top_women WHERE name=a.name)
    , fhoffa.x.fuzzy_extract_one(name, ARRAY(SELECT name FROM top_women))
  ) female_version
FROM top_men a