Condition Insert based Fuzzy Logic in Snowflake

user_17867413 0
Viewed 56

I have a table as below

Company Name ID
Facebook 32
Google 33
Apple 44

So If I get a new record with Company name "Facebook Inc" or "Facebook Company" it should ignore else it should insert. What should be condition for logic?

insert into Table a where? (Fuzzy logic)

snowflake-cloud-data-platform
1 Answers

For the logic described in the question a simple way of solving this is with a merge:

  • Apply the "fuzzy logic" to look for a match. In this case it's a regex that compares the first word of the string: regexp_substr(a.company, '^[^ ]+') = regexp_substr(b.company, '^[^ ]+')
  • If matched, do nothing (with and false).
  • If not matched, insert:
merge into companies a
using (
    select 'Facebook Inc' company, 10 id
) as b on regexp_substr(a.company, '^[^ ]+') = regexp_substr(b.company, '^[^ ]+')
when matched and false then update set a.id = b.id
when not matched then insert (company, id) values (b.company, b.id)

Setup:

create or replace temp table companies as
select $1::string company, $2::int id
from values ('Google', 1), ('Facebook', 2), ('Apple', 3);

If you want to define a more complex "fuzzy logic", please start a new question.

user_1324380
Related