Reference Guide

Reference Guide#

2-Step Configuration#

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

Events and Logs#

To observe and troubleshoot app behavior, you can enable Logging and Event Tracing for your account and share the app logs with us. For more information, please check Snowflake Documentations below:

Stored Procedures#

VERIFY_SINGLE_ADDRESS#

The VERIFY_SINGLE_ADDRESS procedure validates and standardizes individual address components using the Global Address Verification API. It returns the verification results in JSON format or optional output table, supporting optional fields for flexible and accurate address validation.

There are 2 ways to verify an address in Native App Global Address Verification 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_ADDRESS_VERIFICATION. > Menu > Verify Single Address > Try It Now > Start Verifying.

Input Example#

  1. Enter the License Key and address you want to verify.

    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Try-Now.png

  2. Select Verify.

  3. Result will be displayed on the left sidebar.

Output Examples#

Verify a single address

Result will be displayed in JSON format.

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Try-Now-Result.png

Verify a single address 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.

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Try-Now-Default.png

Verify a single address with selected output table fields

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

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Try-Now-Selected-Fields.png

Verify a single address with ‘ALL’ output table fields

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

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Try-Now-All-Fields.png

SQL Script#

Manually call our procedures using a Snowflake SQL script.

You can find the installed application database in Projects > Worksheets > Databases.

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Sample-Structure.png
Setup and Sample Script#

  1. Select Projects > Worksheets on the left panel in your Snowflake account.

    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Script.png

  2. Select + to create a new SQL worksheet.

    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-Add-SQL-Worksheet.png

  3. Copy and paste the usage example below onto your worksheet to call the stored procedure, replace with your values.

    Check Input Parameters for more information.

    /******************************************************************
Global Address Verification - Single Address Usage Example
******************************************************************/
USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,ADDRESS_LINE1          => '22382 Avenida Empresa'
   ,LOCALITY               => 'Rancho Santa Margarita'
   ,ADMINISTRATIVE_AREA    => 'CA'
   ,POSTAL_CODE            => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
);

/* Result view, if Output Table is provided */
SELECT *
FROM <OUTPUT_TABLE_NAME>;

    Example of a stored procedure call from a SQL worksheet. Highlight the SQL statement then select Run button on the right top corner.

    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Sample.png
Input Parameters#

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

Parameter

Data Type

Description

Example

LICENSE

VARCHAR

Required.
The License Key with Credits issued by Melissa.

Get it here.

REPLACE_WITH_YOUR_LICENSE_KEY

OPTIONS

VARCHAR

Optional.
[OptionName:Parameter], separate by comma(,)

See more information about endpoint options.

USPreferredCityNames:on,
USStandardizationType:short

ORGANIZATION_NAME

VARCHAR

Optional.
The organization name associated with the address record.

LAST_NAME

VARCHAR

Optional. US Only.
For addresses missing a secondary like a suite or apartment, we can use the last name and append that information for residential addresses.

ADDRESS_LINE1

VARCHAR

Required.
The input field for the address.

This should contain the delivery address information (house number, thoroughfare, building, suite, etc.) but should not contain locality information (locality, administrative area, postal code, etc.) which have their own inputs.

22382 Avenida Empresa

ADDRESS_LINE2

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE3

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE4

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE5

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE6

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE7

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE8

VARCHAR

Optional.
The input field for the address.

DOUBLE_DEPENDENT_LOCALITY

VARCHAR

Optional.
The smallest population center data element.

This depends on the Locality and DependentLocality elements.

DEPENDENT_LOCALITY

VARCHAR

Optional.
Urbanization.

The smaller population center data element.
This depends on the Locality element. In terms of US Addresses, this element applies only to Puerto Rican addresses. It is used to break ties when a ZIP Code™ is linked to multiple instances of the same address.

LOCALITY

VARCHAR

Required.
City, Municipality.

The most common population center data element.

Rancho Santa Margarita

SUB_ADMINISTRATIVE_AREA

VARCHAR

Optional.
County.

The smallest geographic data element.

ADMINISTRATIVE_AREA

VARCHAR

Required.
State, Province.

The most common geographic data element.

CA

POSTAL_CODE

VARCHAR

Required.
ZIP, Postcode.

The complete postal code for a particular delivery point.
If all three elements are provided and the PostalCode is incorrect, it can be corrected from the data on the Locality and AdministrativeArea.

92688

SUB_NATIONAL_AREA

VARCHAR

Optional.
The administrative region within a country on an arbitrary level below that of the sovereign state.

COUNTRY

VARCHAR

Required.
The country name, abbreviation, or code.

OUTPUT_TABLE_NAME

VARCHAR

Optional.
The 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.

OUTPUT_TABLE_FIELDS

VARCHAR

Optional. Case-sensitive.

Only valid when OUTPUT_TABLE_NAME provided.

Fields in the output table; fields separated by comma(,) if not ‘All’.
Field name must match in one or all in an example list.
If omitted, see Default Output Fields.

Results,
FormattedAddress,
Organization,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
AddressLine5,
AddressLine6,
AddressLine7,
AddressLine8,
SubPremises,
DoubleDependentLocality,
DependentLocality,
Locality,
SubAdministrativeArea,
AdministrativeArea,
PostalCode,
PostalCodeType,
AddressType,
AddressKey,
SubNationalArea,
CountryName,
CountryISO3166_1_Alpha2,
CountryISO3166_1_Alpha3,
CountryISO3166_1_Numeric,
CountrySubdivisionCode,
Thoroughfare,
ThoroughfarePreDirection,
ThoroughfareLeadingType,
ThoroughfareName,
ThoroughfareTrailingType,
ThoroughfarePostDirection,
DependentThoroughfare,
DependentThoroughfarePreDirection,
DependentThoroughfareLeadingType,
DependentThoroughfareName,
DependentThoroughfareTrailingType,
DependentThoroughfarePostDirection,
Building,
PremisesType,
PremisesNumber,
SubPremisesType,
SubPremisesNumber,
PostBox,
Latitude,
Longitude,
DeliveryIndicator,
MelissaAddressKey,
MelissaAddressKeyBase,
PostOfficeLocation,
SubPremiseLevel,
SubPremiseLevelType,
SubPremiseLevelNumber,
SubBuilding,
SubBuildingType,
SubBuildingNumber,
UTC,
DST,
DeliveryPointSuffix,
CensusKey,
Extras,
JSON_Response

Output Examples#

Verify a single address

/******************************************************************
Global Address Verification - Single Address Usage Example
******************************************************************/
USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,ADDRESS_LINE1          => '22382 Avenida Empresa'
   ,LOCALITY               => 'Rancho Santa Margarita'
   ,ADMINISTRATIVE_AREA    => 'CA'
   ,POSTAL_CODE            => '92688'
   ,COUNTRY                => 'US'
);

Output will be displayed in JSON format.

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-JSON.png

Verify a single address 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. The JSON response will be inserted along side with Default Output Fields.

  • Call stored procedure VERIFY_SINGLE_ADDRESS.

    CALL VERIFY_SINGLE_ADDRESS(
  LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
 ,ADDRESS_LINE1          => '22382 Avenida Empresa'
 ,LOCALITY               => 'Rancho Santa Margarita'
 ,ADMINISTRATIVE_AREA    => 'CA'
 ,POSTAL_CODE            => '92688'
 ,COUNTRY                => 'US'
 ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-Default.png

  • Query output table.

    SELECT *
FROM <OUTPUT_TABLE_NAME>;
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-Default-Table-View.png

Verify a single address with selected output fields

2 default columns will be added automatically: RECORDID and TIMESTAMP.

  • Call stored procedure VERIFY_SINGLE_ADDRESS.

    CALL VERIFY_SINGLE_ADDRESS(
  LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
 ,ADDRESS_LINE1          => '22382 Avenida Empresa'
 ,LOCALITY               => 'Rancho Santa Margarita'
 ,ADMINISTRATIVE_AREA    => 'CA'
 ,POSTAL_CODE            => '92688'
 ,COUNTRY                => 'US'
 ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
 ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-Selected-Fields.png

  • Query output table.

    SELECT *
FROM <OUTPUT_TABLE_NAME>;
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-Selected-Fields-Table-View.png

Verify a single address with ‘ALL’ output table fields

  • Call stored procedure VERIFY_SINGLE_ADDRESS.

    CALL VERIFY_SINGLE_ADDRESS(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,ADDRESS_LINE1          => '22382 Avenida Empresa'
  ,LOCALITY               => 'Rancho Santa Margarita'
  ,ADMINISTRATIVE_AREA    => 'CA'
  ,POSTAL_CODE            => '92688'
  ,COUNTRY                => 'US'
  ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_FIELDS    => 'All'
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-All-Fields.png

  • Query output table.

    SELECT *
FROM <OUTPUT_TABLE_NAME>;
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Single-SQL-Result-All-Fields-Table-View.png

VERIFY_MULTIPLE_ADDRESSES#

The VERIFY_MULTIPLE_ADDRESSES procedure processes address records in batches, validating and standardizing key components using the Global Address Verification API.

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

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

Requirements#

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

Default Input Fields#

Column Name

Data Type

Description

RECORDID

NUMBER

Required.
Record identifier, primary key.

ORGANIZATION

VARCHAR

Optional.
The organization name associated with the address record.

LASTNAME

VARCHAR

Optional. US Only.
For addresses missing a secondary like a suite or apartment, we can use the last name and append that information for residential addresses.

ADDRESSLINE1

VARCHAR

Required.
The input field for the address.

This should contain the delivery address information (house number, thoroughfare, building, suite, etc.) but should not contain locality information (locality, administrative area, postal code, etc.) which have their own inputs.

ADDRESSLINE2

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE3

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE4

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE5

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE6

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE7

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE8

VARCHAR

Optional.
The input field for the address.

DOUBLEDEPENDENTLOCALITY

VARCHAR

Optional.
The smallest population center data element.

This depends on the Locality and DependentLocality elements.

DEPENDENTLOCALITY

VARCHAR

Optional. Urbanization.

The smaller population center data element.
This depends on the Locality element. In terms of US Addresses, this element applies only to Puerto Rican addresses. It is used to break ties when a ZIP Code™ is linked to multiple instances of the same address.

LOCALITY

VARCHAR

Required. City, Municipality.

The most common population center data element.

SUBADMINISTRATIVEAREA

VARCHAR

Optional. County.

The smallest geographic data element.

ADMINISTRATIVEAREA

VARCHAR

Required. State, Province.

The most common geographic data element.

POSTALCODE

VARCHAR

Required. ZIP, Postcode.

The complete postal code for a particular delivery point.
If all three elements are provided and the PostalCode is incorrect, it can be corrected from the data on the Locality and AdministrativeArea.

SUBNATIONALAREA

VARCHAR

Optional.
The administrative region within a country on an arbitrary level below that of the sovereign state.

COUNTRY

VARCHAR

Required.
The country name, abbreviation, or code.

SQL Script#

You can find the installed application database in Projects > Worksheets > Databases.

../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-SQL-Sample-Structure.png
Setup and Sample Script#

The step-by-step example below shows how to use our stored procedure VERIFY_MULTIPLE_ADDRESSES 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 NUMBER(38,0) autoincrement start 1 increment 1 noorder
    ,OrganizationName VARCHAR DEFAULT ('')
    ,Address1 VARCHAR DEFAULT ('')
    ,Address2 VARCHAR DEFAULT ('')
    ,Address3 VARCHAR DEFAULT ('')
    ,Address4 VARCHAR DEFAULT ('')
    ,Address5 VARCHAR DEFAULT ('')
    ,Address6 VARCHAR DEFAULT ('')
    ,Address7 VARCHAR DEFAULT ('')
    ,Address8 VARCHAR DEFAULT ('')
    ,City VARCHAR DEFAULT ('')
    ,County VARCHAR DEFAULT ('')
    ,State VARCHAR DEFAULT ('')
    ,PostalCode VARCHAR DEFAULT ('')
    ,Country 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.

  • Step 1 - Grant Required Privileges

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

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

  • 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_ADDRESS_VERIFICATION.CORE;

 /* Set input parameters, replace with your values */
 SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
 SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
 SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';

 /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
 /* For optional columns, uncomment/comment each line for the needed one. */
 CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
 SELECT
    RecId                                           AS RECORDID
    --,COALESCE(MY_LASTNAME_COL, '')                AS LASTNAME
    ,COALESCE(OrganizationName, '')                 AS ORGANIZATION
    ,COALESCE(Address1, '')                         AS ADDRESSLINE1
    ,COALESCE(Address2, '')                         AS ADDRESSLINE2
    ,COALESCE(Address3, '')                         AS ADDRESSLINE3
    ,COALESCE(Address4, '')                         AS ADDRESSLINE4
    ,COALESCE(Address5, '')                         AS ADDRESSLINE5
    ,COALESCE(Address6, '')                         AS ADDRESSLINE6
    ,COALESCE(Address7, '')                         AS ADDRESSLINE7
    ,COALESCE(Address8, '')                         AS ADDRESSLINE8
    --,COALESCE(MY_DOUBLEDEPENDENTLOCALITY_COL, '') AS DOUBLEDEPENDENTLOCALITY
    --,COALESCE(MY_DEPENDENTLOCALITY_COL, '')       AS DEPENDENTLOCALITY
    ,COALESCE(City, '')                             AS LOCALITY
    ,COALESCE(County, '')                           AS SUBADMINISTRATIVEAREA
    ,COALESCE(State, '')                            AS ADMINISTRATIVEAREA
    ,COALESCE(PostalCode, '')                       AS POSTALCODE
    --,COALESCE(MY_SUBNATIONALAREA_COL, '')         AS SUBNATIONALAREA
    ,COALESCE(Country, '')                          AS COUNTRY
 FROM IDENTIFIER($INPUT_TABLE_NAME);

  • Step 3 - Call stored procedure VERIFY_MULTIPLE_ADDRESSES to verify your data

    Check Input Parameters for more information.

    /* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,OPTIONS                => $OPTIONS
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

/* Output table view */
SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
Full SQL Script
/**********************************************************************
Global Address Verification - Multiple Addresses Usage Example
**********************************************************************/
/* Grant usage on input table to the application, replace with your values.*/
GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;

USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';

/* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
/* For optional columns, uncomment/comment each line for the needed one. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
   RecId                                           AS RECORDID
   --,COALESCE(MY_LASTNAME_COL, '')                AS LASTNAME
   ,COALESCE(OrganizationName, '')                 AS ORGANIZATION
   ,COALESCE(Address1, '')                         AS ADDRESSLINE1
   ,COALESCE(Address2, '')                         AS ADDRESSLINE2
   ,COALESCE(Address3, '')                         AS ADDRESSLINE3
   ,COALESCE(Address4, '')                         AS ADDRESSLINE4
   ,COALESCE(Address5, '')                         AS ADDRESSLINE5
   ,COALESCE(Address6, '')                         AS ADDRESSLINE6
   ,COALESCE(Address7, '')                         AS ADDRESSLINE7
   ,COALESCE(Address8, '')                         AS ADDRESSLINE8
   --,COALESCE(MY_DOUBLEDEPENDENTLOCALITY_COL, '') AS DOUBLEDEPENDENTLOCALITY
   --,COALESCE(MY_DEPENDENTLOCALITY_COL, '')       AS DEPENDENTLOCALITY
   ,COALESCE(City, '')                             AS LOCALITY
   ,COALESCE(County, '')                           AS SUBADMINISTRATIVEAREA
   ,COALESCE(State, '')                            AS ADMINISTRATIVEAREA
   ,COALESCE(PostalCode, '')                       AS POSTALCODE
   --,COALESCE(MY_SUBNATIONALAREA_COL, '')         AS SUBNATIONALAREA
   ,COALESCE(Country, '')                          AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,OPTIONS                => $OPTIONS
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

/* Output table view */
SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
Input Parameters#

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

Parameter

Data Type

Description

Example

LICENSE

VARCHAR

Required.
The License Key with Credits issued by Melissa.

Get it here.

REPLACE_WITH_YOUR_LICENSE_KEY

OPTIONS

VARCHAR

Optional.
[OptionName:Parameter], separate by comma(,)

See more information about endpoint options.

USPreferredCityNames:on,
USStandardizationType:short

INPUT_TABLE_NAME

VARCHAR

Required.
Name of the input table.

IN_DB.IN_SCHEMA.INPUT_TABLE_NAME

OUTPUT_TABLE_NAME

VARCHAR

Required.
The 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.

OUTPUT_TABLE_NAME

OUTPUT_TABLE_FIELDS

VARCHAR

Optional. Case-sensitive.

Fields in the output table; fields separated by comma(,) if not ‘All’.
Field name must match in one or all in an example list.
If omitted, see Default Output Fields.

Results,
FormattedAddress,
Organization,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
AddressLine5,
AddressLine6,
AddressLine7,
AddressLine8,
SubPremises,
DoubleDependentLocality,
DependentLocality,
Locality,
SubAdministrativeArea,
AdministrativeArea,
PostalCode,
PostalCodeType,
AddressType,
AddressKey,
SubNationalArea,
CountryName,
CountryISO3166_1_Alpha2,
CountryISO3166_1_Alpha3,
CountryISO3166_1_Numeric,
CountrySubdivisionCode,
Thoroughfare,
ThoroughfarePreDirection,
ThoroughfareLeadingType,
ThoroughfareName,
ThoroughfareTrailingType,
ThoroughfarePostDirection,
DependentThoroughfare,
DependentThoroughfarePreDirection,
DependentThoroughfareLeadingType,
DependentThoroughfareName,
DependentThoroughfareTrailingType,
DependentThoroughfarePostDirection,
Building,
PremisesType,
PremisesNumber,
SubPremisesType,
SubPremisesNumber,
PostBox,
Latitude,
Longitude,
DeliveryIndicator,
MelissaAddressKey,
MelissaAddressKeyBase,
PostOfficeLocation,
SubPremiseLevel,
SubPremiseLevelType,
SubPremiseLevelNumber,
SubBuilding,
SubBuildingType,
SubBuildingNumber,
UTC,
DST,
DeliveryPointSuffix,
CensusKey,
Extras,
JSON_Response

Output Examples#

Some common examples are shown below.

Verify multiple addresses with the default output fields

  • Call the stored procedure to verify multiple addresses with Default Output Fields.

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET OPTIONS                 = '<OptionName_1:Parameter,OptionName_2:Parameter>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<OUTPUT_TABLE_NAME>';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
   RECID                                   AS RECORDID,
   --COALESCE(LASTNAME, '')                AS LASTNAME,
   --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
   COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
   COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
   COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
   COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
   COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
   COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
   COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
   COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
   --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
   --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
   COALESCE(CITY, '')                      AS LOCALITY,
   --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
   COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
   COALESCE(POSTALCODE, '')                AS POSTALCODE,
   --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
   COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,OPTIONS                => $OPTIONS
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-Default-Output.png

  • Query output table.

    SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-Default-Output-Table-View.png

Verify multiple addresses with selected output fields

  • Call stored procedure to verify multiple addresses from an input table with selected output fields.

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS     = '<Field_1,Field_2>';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
    RECID                                   AS RECORDID,
    --COALESCE(LASTNAME, '')                AS LASTNAME,
    --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
    COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
    COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
    COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
    COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
    COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
    COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
    COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
    COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
    --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
    --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
    COALESCE(CITY, '')                      AS LOCALITY,
    --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
    COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
    COALESCE(POSTALCODE, '')                AS POSTALCODE,
    --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
    COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
     LICENSE                => $LICENSE
    ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
    ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
    ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
    -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
    -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-Selected-Output.png

  • Query output table.

    SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-Selected-Output-Table-View.png

Verify multiple addresses with ‘ALL’ output fields

  • Call stored procedure to verify multiple addresses from an input table with ‘All’ output fields.

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS     = 'All';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
    RECID                                   AS RECORDID,
    --COALESCE(LASTNAME, '')                AS LASTNAME,
    --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
    COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
    COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
    COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
    COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
    COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
    COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
    COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
    COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
    --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
    --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
    COALESCE(CITY, '')                      AS LOCALITY,
    --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
    COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
    COALESCE(POSTALCODE, '')                AS POSTALCODE,
    --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
    COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
     LICENSE                => $LICENSE
    ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
    ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
    ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-All-Output.png

  • Query output table.

    SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
    ../../_images/Snowflake-GAV-Stored-Procedures-Verify-Multiple-All-Output-Table-View.png

DROP_OUTPUT_TABLE#

After the creation, output table is only allowed for:

  • Select.

    SELECT TOP 100 *
FROM <OUTPUT_TABLE_NAME>
ORDER BY (RECORDID);

  • Insert.

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

  • Drop.

    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 GLOBAL_ADDRESS_VERIFICATION.CORE.DROP_OUTPUT_TABLE('<OUTPUT_TABLE_NAME>');

Default Output Fields#

If OUTPUT_TABLE_FIELDS is an empty string or not provided, output table will have a default schema as below.

Column Name

Data Type

RECORDID

NUMBER

MELISSADDRESSKEY

VARCHAR

MELISSADDRESSKEYBASE

VARCHAR

RESULTS

VARCHAR

FORMATTEDADDRESS

VARCHAR

COUNTRYNAME

VARCHAR

LATITUDE

VARCHAR

LONGITUDE

VARCHAR

JSON_RESPONSE

VARCHAR

TIMESTAMP

TIMESTAMP_NTZ

Versions and Updates#

Check for current version#

Select Data Products > Apps, the current version of the app will be on display.

../../_images/Snowflake-GAV-Download-App-Step4.png


Or, run this query in Projects > Worksheets.

SHOW APPLICATIONS;

Updates#

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

However, you will 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 Address Verification: Snowflake, please visit here.

Interpreting Results#

The Results field returned back by the service will tell all the status, error and change information about the output address returned back to you. They are returned as a comma-delimited string of 4-character alpha-numeric codes, e.g. AC02,AS01 or AE01.

The results are split into four main categories:

AV Codes

  • AV2X denotes an address that has been verified to the highest possible level with the reference data.

  • AV1X denotes a partially verified address, but NOT to the highest level possible with the reference data.

  • The second number indicates the level of verification. There are five levels:

    ../../_images/GAV-Reference-Guide-Interpreting-Results-AV-Codes.png

AE Codes

These are error codes that will tell you what type of error we encountered.

AC Codes

These are change codes that will tell you which address pieces we changed from the input to the output. They include field that were blank in the input and added in the output.

GS & GE Codes

For the users who get geocoding information, this will tell you to what precision level the latitude and longitude appended are.

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.

Using Result Codes Programmatically#

The concept behind the result codes are that the service will include a full list of delimited result codes, many of which may not be important to you. As a user, you should decide what codes define a good address for you and use a string.Contains search to look for those codes. If you find it, you have a good address. If you don’t, then you have a bad address. You can also cascade them into 3 or more condition layers. Here is a common example:

Result Code Web Service Error Example:

If(Results.Contains("AV23") or Results.Contains("AV24") or Results.Contains("AV25"))
{ /*Handle Good Address*/ }
Else if(Results.Contains("AV22") or Results.Contains("AV14") or Results.Contains("AV13"))
{ /*Handle Suspect Address*/ }
Else
{ /*Handle Bad Address*/ }

Code

Description

Recommendation

AS09

The address is in a non-supported country.

Contact your CSR to add new countries.

AV25

The address has been verified to the SubPremise (Suite) or PO Box Level, which is the highest level possible with the reference data.

Good

AV24

The address has been verified to the Premise (House or Building) Level, which is the highest level possible with the reference data.

Good

AV23

The address has been verified to the Thoroughfare (Street) Level, which is the highest level possible with the reference data.

Good

AV22

The address has been verified to the Locality (City) Level, which is the highest level possible with the reference data.

Medium. Only locality data was available, odds on quality is dependent on source of input.

AV14

The address has been partially verified to the Premise (House or Building) Level, which is NOT the highest level possible with the reference data.

Medium. Most of the address was good, just bad/missing suite.

AV13

The address has been partially verified to the Thoroughfare (Street) Level, which is NOT the highest level possible with the reference data.

Medium. Most of the address was good, house number not verified.

AV12

The address has been partially verified to the Locality (City) Level, which is NOT the highest level possible with the reference data.

Bad. Could not verify the address but found a good locality value.

AV11

The address has been partially verified to the Administrative Area (State) Level, which is NOT the highest level possible with the reference data.

Bad. Could not verify the address but found a good administrative area value.
Contents