Native App Global Phone: Snowflake Reference Guide

Application Configuration

Follow the instruction to get the app and details about installation in Native App Installation.

Events and Logs

Note

An ACCOUNTADMIN or equivalent role is required to enable Events and Logs

Important

We highly recommend enabling Events and Logs to facilitate troubleshooting in case of any issues.

To observe and troubleshoot app behavior, you can enable Logging and Event Tracing for your account and share the app logs with us.

SELECT
   RESOURCE_ATTRIBUTES:"snow.application.name"::STRING as APP_NAME
   ,RECORD
   ,RECORD_ATTRIBUTES
   ,RECORD_TYPE
   ,TIMESTAMP
FROM <YOUR_EVENT_TABLE>
WHERE RECORD_TYPE LIKE 'SPAN_EVENT%'
   AND APP_NAME = '<YOUR_APP_NAME>'
ORDER BY TIMESTAMP DESC
limit 100;

For more information, please check Snowflake Documentations below:

• Set up event tracing for an app as a consumer

• Enable event sharing for an app

Stored Procedures

VERIFY_SINGLE_PHONE

Note

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

The VERIFY_SINGLE_PHONE procedure validates and standardizes individual phone using the Global Phone. It returns the verification results in JSON format or optional output table, supporting optional fields for flexible and accurate phone validation.

There are 2 ways to verify an phone in Native App Global Phone for Snowflake:

1. Fill in the Verification Form on the Native App interface.

2. Manually run a Snowflake SQL script to call the stored procedure.

Verification Form

In your Snowflake account, select Data Products » Apps » GLOBAL_PHONE > Menu » Verify Single Phone » Try It Now » Verification Form.

Input Example

1. Enter the License Key and phone number you want to verify.

   

2. Select Verify.

3. Result will be displayed on the left sidebar.

Output Examples

Some common output examples are shown below.

Verify a single phone number

Result will be displayed in JSON format.

Verify a single phone number and insert the result to an output table

You can choose to insert the result to an output table for later use with our Default Output Fields.

Verify a single phone number with selected output table fields

You can choose which fields to be included in the output table.

Verify a single phone number with ‘ALL’ output table fields

You can choose to have All fields to be included in the output table.

SQL Script

Manually call our procedures using a Snowflake SQL script. You can find the installed procedure in <APP_NAME>.CORE schema.

Syntax

CALL VERIFY_SINGLE_PHONE(
  LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,PHONENUMBER           => '<YOUR_PHONE_NUMBER>'
  ,COUNTRY               => '<YOUR_COUNTRY_CODE>'
  [ optional parameters ...]
);

Input Parameters

Input parameters when calling stored procedure VERIFY_SINGLE_PHONE.

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key with Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
PHONENUMBER	VARCHAR	Required. Phone number need to be verified.	949-858-3000
COUNTRY	VARCHAR	Required. The country code.	US
OPTIONS	VARCHAR	Value: OptionName:Parameter,..., separate by comma(,) See more information about endpoint options. If omitted, Default Input Options will be applied.	TimeToWait:5
COUNTRYOFORIGIN	VARCHAR	The country of origin code.	US
OUTPUT_TABLE_NAME	VARCHAR	The output table name. Value: Blank input will return a JSON response. YOUR_OUTPUT_TABLE_NAME: A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	YOUR_OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Only valid when OUTPUT_TABLE_NAME provided. Value: Blank input will return Default Output Fields. All: Parse all available response fields. This will significantly increase the writing time to output table. We recommend to use the default or specify the output fields. FieldName,...: Case-sensitive, separate by comma(,). See all available response fields. (Additional fields for Snowflake app): InPhoneNumber InCountry InCountryOfOrigin JSON_Response Timestamp

Output Examples

Some common output examples are shown below.

• Verify a single phone number

• Verify a single phone number and insert the result into an output table

• Verify a single phone number with selected output fields

• Verify a single phone number with ‘ALL’ output table fields

Verify a single phone number

CALL VERIFY_SINGLE_PHONE(
    LICENSE             => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PHONENUMBER         => '949-858-3000'
   ,COUNTRY             => 'US'
);

Result is displayed in JSON format.

Verify a single phone number and insert the result into an output table

If OUTPUT_TABLE_NAME is provided, a new table will be created if not already existed in the same application database with Default Output Fields.

CALL VERIFY_SINGLE_PHONE(
    LICENSE             => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PHONENUMBER         => '949-858-3000'
   ,COUNTRY             => 'US'
   ,OUTPUT_TABLE_NAME   => '<OUTPUT_TABLE_NAME>'
);

Verify a single phone number with selected output fields

Attention

OUTPUT_TABLE_FIELDS is only valid when OUTPUT_TABLE_NAME is provided.

CALL VERIFY_SINGLE_PHONE(
    LICENSE             => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PHONENUMBER         => '949-858-3000'
   ,COUNTRY             => 'US'
   ,OUTPUT_TABLE_NAME   => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS => '<Field_1,Field_2>'
);

Verify a single phone number with ‘ALL’ output table fields

Attention

OUTPUT_TABLE_FIELDS is only valid when OUTPUT_TABLE_NAME is provided.

CALL VERIFY_SINGLE_PHONE(
    LICENSE             => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PHONENUMBER         => '949-858-3000'
   ,COUNTRY             => 'US'
   ,OUTPUT_TABLE_NAME   => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS => 'All'
);

VERIFY_MULTIPLE_PHONES

Note

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

The VERIFY_MULTIPLE_PHONES procedure processes phone number records in batches, validating and standardizing key components using the Global Phone.

It can handle tables of any size, returning the results in a specified output table and supports optional fields for flexible, accurate phone validation.

Requirements

To use this feature, you need to prepare an input table in Snowflake beforehand referencing our default input fields below.

Default Input Fields

Attention

Input table must have all the Required columns. Otherwise, you will get a “NoneType” error.

Column Name	Data Type	Description
RECORDID	NUMBER	Required. Record identifier, primary key.
PHONENUMBER	VARCHAR	Required. The phone number to be verified.
COUNTRY	VARCHAR	Required. The country code.
COUNTRYOFORIGIN	VARCHAR	Required. Empty string is accepted. The country of origin code.

SQL Script

You can find the installed procedure in <APP_NAME>.CORE schema.

Syntax

CALL VERIFY_MULTIPLE_PHONES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,INPUT_TABLE_NAME       => '<YOUR_INPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_NAME      => '<YOUR_OUTPUT_TABLE_NAME>'
  [ optional parameters ...]
);

Input Parameters

Below is the input parameters information for calling stored procedure VERIFY_MULTIPLE_PHONES.

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key with Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
INPUT_TABLE_NAME	VARCHAR	Required. Name of input table.	DATABASE.SCHEMA.INPUT_TABLE
OUTPUT_TABLE_NAME	VARCHAR	Required. The output table name. Value: YOUR_OUTPUT_TABLE_NAME: A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	YOUR_OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Value: Blank input will return Default Output Fields. All: Parse all available response fields. This will significantly increase the writing time to output table. We recommend to use the default or specify the output fields. FieldName,...: Case-sensitive, separate by comma(,). See all available response fields. (Additional fields for Snowflake app): InPhoneNumber InCountry InCountryOfOrigin JSON_Response Timestamp
OPTIONS	VARCHAR	Value: OptionName:Parameter,..., separate by comma(,) See more information about endpoint options. If omitted, Default Input Options will be applied.	TimeToWait:5
DUPLICATE_CHECK	BOOLEAN	Enable or disable several checks for duplicate RecordID. - FALSE (Default): No action will be taken. The app will process records coming from the input source as is. - TRUE: Enable several checks for duplicate RecordID. When enabled, the process will stop when a duplicate RecordID is detected. No request will be sent to the service or output inserted to a table. Runtime will be significantly reduced.	TRUE

Examples

Output tables will be created in OUTPUT schema of the application database if not already existed.

The step-by-step example below shows how to use our stored procedure VERIFY_MULTIPLE_PHONES with the default values. Replace with your values.

• Step 0 - Prepare an input table.

  Assume that the input table has the signature below:

  CREATE TABLE IF NOT EXISTS <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> (
     RecId INT IDENTITY
     ,PhoneNumber VARCHAR DEFAULT ('')
     ,Country VARCHAR DEFAULT ('')
     ,CountryOfOrigin VARCHAR DEFAULT ('')
  ) CLUSTER BY (RecId);
  

  This input table has different column names from the Default Input Fields. Mapping column names in Step 2 is necessary for the program to get the correct parameters.

Attention

Make sure your input table contains unique RecordID before running the next script. See more about Batch Processing Best Practices.

• Step 1 - Grant Required Privileges.

  Ensure the application has the necessary access to the input table. Replace placeholders with your actual values.

  /**********************************************************************
  Global Phone - Verify Multiple Phone Numbers Usage Example
  **********************************************************************/
  /* Grant usage on input table to the application, replace with your values.*/
  GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_PHONE;
  GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_PHONE;
  GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_PHONE;
  

• Step 2 - Map Input Columns with the Default Input Fields.

  Set the required parameters and map your input columns to our Default Input Fields.
  

  You can skip the mapping step if your input table matches our Default Input Fields exactly.

  USE GLOBAL_PHONE.CORE;
  
  /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
  CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
  SELECT
     RecId                            AS RECORDID
     ,COALESCE(PhoneNumber, '')       AS PHONENUMBER
     ,COALESCE(Country, '')           AS COUNTRY
     ,COALESCE(CountryOfOrigin, '')   AS COUNTRYOFORIGIN
  FROM <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>;
  

Note

Default Settings will be used if OPTIONS and/or OUTPUT_TABLE_FIELDS is empty.

• Step 3 - Call stored procedure VERIFY_MULTIPLE_PHONES to verify your data.

  Check Input Parameters for more information.

  /* Call the verify procedure */
  CALL VERIFY_MULTIPLE_PHONES(
      LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
     ,OPTIONS                => '<OptionName_1:Parameter,OptionName_2:Parameter>'
     ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
     ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
     ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
  );
  
  /* Output table view */
  SELECT *
  FROM OUTPUT.<OUTPUT_TABLE_NAME>
  ORDER BY (RECORDID)
  LIMIT 100;
  

  Full SQL Script

  /**********************************************************************
  Global Phone - Verify Multiple Phone Numbers Usage Example
  **********************************************************************/
  /* Grant usage on input table to the application, replace with your values.*/
  GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_PHONE;
  GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_PHONE;
  GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_PHONE;
  
  USE GLOBAL_PHONE.CORE;
  
  /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
  CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
  SELECT
     RecId                            AS RECORDID
     ,COALESCE(PhoneNumber, '')       AS PHONENUMBER
     ,COALESCE(Country, '')           AS COUNTRY
     ,COALESCE(CountryOfOrigin, '')   AS COUNTRYOFORIGIN
  FROM <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>;
  
  /* Call the verify procedure */
  CALL VERIFY_MULTIPLE_PHONES(
      LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
     ,OPTIONS                => '<OptionName_1:Parameter,OptionName_2:Parameter>'
     ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
     ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
     ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
  );
  
  /* Output table view */
  SELECT *
  FROM OUTPUT.<OUTPUT_TABLE_NAME>
  ORDER BY (RECORDID)
  LIMIT 100;
  

Some common output examples are shown below.

• Verify multiple phone numbers with the default output fields

• Verify multiple phone numbers with selected output fields

• Verify multiple phone numbers with ‘ALL’ output fields

• Verify multiple phone numbers with duplicate RecordID check enabled

Verify multiple phone numbers with the default output fields

CALL VERIFY_MULTIPLE_PHONES(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,INPUT_TABLE_NAME       => '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
);

Verify multiple phone numbers with selected output fields

CALL VERIFY_MULTIPLE_PHONES(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,OPTIONS                => '<OptionName_1:Parameter,OptionName_2:Parameter>'
   ,INPUT_TABLE_NAME       => '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
);

Verify multiple phone numbers with ‘ALL’ output fields

CALL VERIFY_MULTIPLE_PHONES(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,INPUT_TABLE_NAME       => '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => 'All'
);

Verify multiple phone numbers with duplicate RecordID check enabled

CALL VERIFY_MULTIPLE_PHONES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,INPUT_TABLE_NAME       => '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>'
  ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
  ,DUPLICATE_CHECK        => TRUE
);

DROP_OUTPUT_TABLE

Use stored procedure DROP_OUTPUT_TABLE if you wish to remove the table from the application database. Only tables created by the procedure call can be dropped.

CALL DROP_OUTPUT_TABLE('<OUTPUT_TABLE_NAME>');

---

Default Settings

Default Input Options

If input OPTIONS is empty, default input options will be:

Parameter	Value
OPTIONS	VerifyPhone:Express, CallerID:FALSE, DefaultCallingCode:<blank>, EnableBatchSuggestion:FALSE, TimeToWait:2

These are our recommended service options for general batch processing. See more information about endpoint options here.

Additional output columns available only with CallerID:TRUE

• CallerID (US/CA only)

Default Output Fields

If input OUTPUT_TABLE_FIELDS is empty, output table will have a default schema as below.

Column Name	Data Type
RECORDID	NUMBER
INPHONENUMBER	VARCHAR
INCOUNTRY	VARCHAR
INCOUNTRYOFORIGIN	VARCHAR
RESULTS	VARCHAR
PHONENUMBER	VARCHAR
COUNTRYABBREVIATION	VARCHAR
INTERNATIONALPHONENUMBER	VARCHAR
CARRIER	VARCHAR
PHONEINTERNATIONALPREFIX	VARCHAR
PHONECOUNTRYDIALINGCODE	VARCHAR
PHONENATIONPREFIX	VARCHAR
PHONENATIONALDESTINATIONCODE	VARCHAR
PHONESUBSCRIBERNUMBER	VARCHAR

Output Tables

Output tables created during the process are owned by the Application. Therefore, their usage is limited to the operations listed below.

• Select, Delete, Truncate.

  SELECT *
  FROM <OUTPUT_TABLE_NAME>;
  

  DELETE FROM <OUTPUT_TABLE_NAME>
  WHERE RECORDID IS NULL;
  

  TRUNCATE TABLE <OUTPUT_TABLE_NAME>;
  

• Insert.

  By calling the same stored procedure without changing the input structure, new records will be inserted to the same table.

• Drop.

  By calling DROP_OUTPUT_TABLE procedure.

Versions and Updates

Check for current version

SHOW APPLICATIONS;

Updates

When Melissa releases a new version of the Native App, your installed application will get updated automatically.

If your version is not the latest, you might need to reinstall the app’s functionality after the upgrade is complete, as described in Application Configuration.

Result Codes

For the full list of result codes returned by Native App Global Phone: Snowflake, please visit here.

Interpreting Results

Result codes yield more granular information about a given phone number. They are returned as a comma-delimited string of 4-character alpha-numeric codes, e.g. PS01,PS07,PS18 or PS07,PE04.

SE## and GE## Codes

The SE## and GE## codes (Transmission Service Error and General Transmission Error) are used to signify more general errors, and are returned under the key TransmissionResults in the outermost level of our responses.

PE## and PS## Codes

Global Phone Cloud API returns back a string of PSXX result codes in the “Results” field of the response. These result codes provide users with information about the response from the service.

In almost every case, one of the values of the “Results” string will be one of the following:

Code	Description	Recommendation
PS20	Number exists within a block of registered phone numbers.	Low Confidence - This number exists within a block of registered phone numbers that the service checks against.
PS22	Number was verified against current dialing equipment.	High Confidence - This number was verified against current dialing equipment. This result code will only be returned if the VerifyPhone option in the initial request to the service is set to Premium. (ex: &opt=VerifyPhone:Premium).

With these result codes users can easily build logic for what to do with good valid phone numbers and easily distinguish good phone numbers from bad phone numbers.

There are also PEXX codes that can be returned which indicate there was an error with a part of the requested phone number.