AI-Powered Organization Name Matching as a SQL Query: Native Application on Snowflake

Once the native application is added to your Snowflake account with a one-click install, the following SQL query is all you need to identify and cluster similar and inconsistently-spelled organization names in any Snowflake data table:

SELECT org,interzoid_org_match_app.core.org_simkey(org) AS org_key
FROM organizations
ORDER BY org_key;

Overview

Interzoid's Organization Matching Snowflake Native Application addresses the issues of inconsistency and redundancy in company, institution, or organization names within data tables. These issues can otherwise hinder accurate data analysis, customer communication, data-driven processes, AI model effectiveness, and other data-centric activities.

By leveraging specialized algorithms, machine learning, extensive knowledge bases, and fine-tuned AI models, the application generates a canonical key (a textual string) using Interzoid's Organization Matching API. This key helps identify and match "similar" organization name data values, whether within a single table or across multiple tables, using simple SQL statements on the Snowflake data platform. The functionality is made available pre-integrated as a "User-Defined Function" (UDF) and can be accessed as part of a SQL statement. Examples of similarity keys are provided below.

Features

• Generation of similarity keys helps to enable significant data quality improvement by identifying inconsistent data, discovering duplicate records, addressing matching challenges across joins, and more.
• Snowflake's Native Application platform makes it easy and fast to get started identifying data consistency and quality issues in any Snowflake table.
• Simply install the app from Snowflake Marketplace, obtain an API key from Interzoid, provide it as a "Secret" using Snowflake SQL, and you're off and running.

Snowflake Marketplace Installation

1. Log in to your Snowflake account.
2. Navigate to the Snowflake Marketplace.
3. Search for "Interzoid Org Match".
4. Once located, click "Get" and follow the Snowflake installation instructions to install the native application to your Snowflake account for use.

Initial Setup

1. Obtain your Interzoid API License Key by registering at www.interzoid.com.
2. Enter the API License Key in the Snowflake Secrets Manager using the Security icon in the upper right on the installed Application's main page. On the 'Privileges' tab, click 'Add' next to the API License Key object, enter your API key, and click 'Configure'.
3. In the same security section, click on the 'Connections' tab and click 'Review' on the api.interzoid.com connection, and then 'Connect'. This will complete the setup of the application and the external API access. Copy the (database.schema.secret) value of the 'Authentication with Interzoid API Key' field in this Connections tab of the Security panel, as your GRANT statement values for secret authorization may be different in the configuration script below.
4. Run the following configuration script in a Snowflake SQL Worksheet. This script sets the correct privileges, and then creates a test database and schema as an example to try out use of the API within the created User Defined Function (UDF). This script can be customized to provide authorization and access to your own databases, schemas, and tables so you can immediately begin performing data quality analysis and increasing match rates on your own data.

USE APPLICATION interzoid_org_match_app;

GRANT USAGE ON DATABASE INTERZOID_ORG_MATCH_APP_APP_DATA TO APPLICATION interzoid_org_match_app;

GRANT USAGE ON SCHEMA INTERZOID_ORG_MATCH_APP_APP_DATA.CONFIGURATION TO APPLICATION interzoid_org_match_app;

-- Use your 'Authentication with Interzoid API Key' (database.schema.secret) as described above in the initial setup
GRANT USAGE ON SECRET INTERZOID_ORG_MATCH_APP_APP_DATA.CONFIGURATION.INTERZOID_ORG_MATCH_APP_INTERZOID_AUTH_KEY TO APPLICATION interzoid_org_match_app;

Running the Interzoid Org Match App

The following initializes the native application, which then enables the execution of the sample SQL statements using the Interzoid similarity key functionality. This demonstrates how the function can be called a single value at a time.

CALL interzoid_org_match_app.org_match_core.init_app(PARSE_JSON('{
        "secret_name": "interzoid_auth_key",
        "external_access_integration_name": "interzoid_external_access_integration",
    }'));

SELECT interzoid_org_match_app.org_match_core.org_simkey('Snowflake');
SELECT interzoid_org_match_app.org_match_core.org_simkey('snoflake inc');
SELECT interzoid_org_match_app.org_match_core.org_simkey('SNOW FLAKE CORP');

SELECT interzoid_org_match_app.org_match_core.org_simkey('Microsoft Inc');
SELECT interzoid_org_match_app.org_match_core.org_simkey('microsot');
SELECT interzoid_org_match_app.org_match_core.org_simkey('MICROS0FT');

SELECT interzoid_org_match_app.org_match_core.org_simkey('IBM');
SELECT interzoid_org_match_app.org_match_core.org_simkey('Intl businessmachines');

SELECT interzoid_org_match_app.org_match_core.org_simkey('Amazon.com');
SELECT interzoid_org_match_app.org_match_core.org_simkey('Amazon Incorp.');

Similarity Key Examples

These are examples of the algorithmically and AI model-based similarity key strings used to identify inconsistent organization name data. These keys can be used to identify, match, and cluster similar organization names, either within one table or across multiple tables. Note that similar organization names generate the same similarity key, which can then be used to match and cluster similar data.

Snowflake -> zPXpUnLwdW0UtBh8kNE4gA6DgwFBNzkm0djgmnyjgaM
snoflake inc -> zPXpUnLwdW0UtBh8kNE4gA6DgwFBNzkm0djgmnyjgaM
SNOWFLAKE CORP.	-> zPXpUnLwdW0UtBh8kNE4gA6DgwFBNzkm0djgmnyjgaM

Microsoft Inc -> xUhcrilUNsRiCthe7rXkIupHiCbhhgyLrKNAcXruwoA
microsot -> xUhcrilUNsRiCthe7rXkIupHiCbhhgyLrKNAcXruwoA
MICROS0FT -> xUhcrilUNsRiCthe7rXkIupHiCbhhgyLrKNAcXruwoA

IBM -> edplDLsBWcH9Sa7ZECaJx8KiEl5lvMWAa6ackCA4azs
Intl businessmachines -> edplDLsBWcH9Sa7ZECaJx8KiEl5lvMWAa6ackCA4azs

Amazon.com -> tyGzXZjfZUqhgqt6mqNZF8MCsn-QQV1NJbysxSTB7aI
Amazon Incorp. -> tyGzXZjfZUqhgqt6mqNZF8MCsn-QQV1NJbysxSTB7aI

Entire Snowflake SQL Table Example

Another way to try is to create a simple table inserting these company names randomly into a table to demonstrate usage of the similarity key generation function within a SQL statement on the Snowflake platform:

create database test_db;
create schema test_db.test_schema;

CREATE TABLE test_db.test_schema.organizations (org TEXT);

INSERT INTO test_db.test_schema.organizations (org) VALUES
     ('Snowflake'),
     ('MICROS0FT'),
     ('IBM Corp'),
     ('Amazon Incorp.'),
     ('microsot'),
     ('snoflake Inc'),
     ('Amazon.com'),
     ('Intl businessmachines'),
     ('SNOW FLAKE LLC.'),
     ('Microsoft Inc');

After the table is created, the following query generates a similarity key for each organization name using the Interzoid API behind-the-scenes. Sorting by the generated similarity key will then cluster similar organization names next to each other.

-- Use your 'Authentication with Interzoid API Key' (database.schema.secret) as described above in the initial setup
GRANT USAGE ON SECRET INTERZOID_ORG_MATCH_APP_APP_DATA.CONFIGURATION.INTERZOID_ORG_MATCH_APP_INTERZOID_AUTH_KEY TO APPLICATION interzoid_org_match_app;

GRANT USAGE ON DATABASE test_db TO APPLICATION interzoid_org_match_app;
GRANT USAGE ON SCHEMA test_db.test_schema TO APPLICATION interzoid_org_match_app;

USE APPLICATION interzoid_org_match_app;

SELECT org,interzoid_org_match_app.org_match_core.org_simkey(org) AS org_key
FROM test_db.test_schema.organizations
ORDER BY org_key;

Snowflake query results:

Sorting by similarity key clusters similar organization names together.

From here, the flexibility and possibilities are endless with your own data, including duplicate identification, using multiple-column matching, custom business logic, joins that overcome data inconsistency, and more.