Reference Guide

Reference Guide#

2-Step Configuration #

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

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:

Fill in the Verification Form on the Native App interface.
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 #

Enter the License Key and address you want to verify.
Select Verify.
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 Ouput 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 #

Select Projects > Worksheets on the left panel in your Snowflake account.
Select + to create a new SQL worksheet.

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 #

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. \|br\| The License Key with Credits issued by Melissa. Get it here.	MY_LICENSE_KEY
OPTIONS	VARCHAR	Optional. \|br\| [OptionName:Parameter], separate by colon(,) See more information about OPTIONS	USPreferredCityNames:on, USStandardizationType:short
ORGANIZATION_NAME	VARCHAR	Optional. \|br\| The organization name associated with the address record.
LAST_NAME	VARCHAR	Optional. US Only. \|br\| 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. \|br\| 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. \|br\| The input field for the address.
ADDRESS_LINE3	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESS_LINE4	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESS_LINE5	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESS_LINE6	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESS_LINE7	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESS_LINE8	VARCHAR	Optional. \|br\| The input field for the address.
DOUBLE_DEPENDENT_LOCALITY	VARCHAR	Optional. \|br\| The smallest population center data element. This depends on the Locality and DependentLocality elements.
DEPENDENT_LOCALITY	VARCHAR	Optional. \|br\| 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. \|br\| City, Municipality. The most common population center data element.	Rancho Santa Margarita
SUB_ADMINISTRATIVE_AREA	VARCHAR	Optional. \|br\| County. The smallest geographic data element.
ADMINISTRATIVE_AREA	VARCHAR	Required. \|br\| State, Province. The most common geographic data element.	CA
POSTAL_CODE	VARCHAR	Required. \|br\| 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. \|br\| The administrative region within a country on an arbitrary level below that of the sovereign state.
COUNTRY	VARCHAR	Required. \|br\| The country name, abbreviation, or code.
OUTPUT_TABLE_NAME	VARCHAR	Optional. \|br\| 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. \|br\| Only valid when OUTPUT_TABLE_NAME provided. Fields in the output table; fields separates by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, see Default Ouput Fields.	Results, \|br\| FormattedAddress, \|br\| Organization, \|br\| AddressLine1, \|br\| AddressLine2, \|br\| AddressLine3, \|br\| AddressLine4, \|br\| AddressLine5, \|br\| AddressLine6, \|br\| AddressLine7, \|br\| AddressLine8, \|br\| SubPremises, \|br\| DoubleDependentLocality, \|br\| DependentLocality, \|br\| Locality, \|br\| SubAdministrativeArea, \|br\| AdministrativeArea, \|br\| PostalCode, \|br\| PostalCodeType, \|br\| AddressType, \|br\| AddressKey, \|br\| SubNationalArea, \|br\| CountryName, \|br\| CountryISO3166_1_Alpha2, \|br\| CountryISO3166_1_Alpha3, \|br\| CountryISO3166_1_Numeric, \|br\| CountrySubdivisionCode, \|br\| Thoroughfare, \|br\| ThoroughfarePreDirection, \|br\| ThoroughfareLeadingType, \|br\| ThoroughfareName, \|br\| ThoroughfareTrailingType, \|br\| ThoroughfarePostDirection, \|br\| DependentThoroughfare, \|br\| DependentThoroughfarePreDirection, \|br\| DependentThoroughfareLeadingType, \|br\| DependentThoroughfareName, \|br\| DependentThoroughfareTrailingType, \|br\| DependentThoroughfarePostDirection, \|br\| Building, \|br\| PremisesType, \|br\| PremisesNumber, \|br\| SubPremisesType, \|br\| SubPremisesNumber, \|br\| PostBox, \|br\| Latitude, \|br\| Longitude, \|br\| DeliveryIndicator, \|br\| MelissaAddressKey, \|br\| MelissaAddressKeyBase, \|br\| PostOfficeLocation, \|br\| SubPremiseLevel, \|br\| SubPremiseLevelType, \|br\| SubPremiseLevelNumber, \|br\| SubBuilding, \|br\| SubBuildingType, \|br\| SubBuildingNumber, \|br\| UTC, \|br\| DST, \|br\| DeliveryPointSuffix, \|br\| CensusKey, \|br\| Extras, \|br\| JSON_Response \|br\|

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 Ouput 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>;
```

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>;
```

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>;
```

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. \|br\| Record identifier, primary key.
ORGANIZATION	VARCHAR	Optional. \|br\| The organization name associated with the address record.
LASTNAME	VARCHAR	Optional. US Only. \|br\| 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. \|br\| 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. \|br\| The input field for the address.
ADDRESSLINE3	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESSLINE4	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESSLINE5	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESSLINE6	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESSLINE7	VARCHAR	Optional. \|br\| The input field for the address.
ADDRESSLINE8	VARCHAR	Optional. \|br\| The input field for the address.
DOUBLEDEPENDENTLOCALITY	VARCHAR	Optional. \|br\| The smallest population center data element. This depends on the Locality and DependentLocality elements.
DEPENDENTLOCALITY	VARCHAR	Optional. Urbanization. \|br\| 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. \|br\| The most common population center data element.
SUBADMINISTRATIVEAREA	VARCHAR	Optional. County. \|br\| The smallest geographic data element.
ADMINISTRATIVEAREA	VARCHAR	Required. State, Province. \|br\| The most common geographic data element.
POSTALCODE	VARCHAR	Required. ZIP, Postcode. \|br\| 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. \|br\| The administrative region within a country on an arbitrary level below that of the sovereign state.
COUNTRY	VARCHAR	Required. \|br\| 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 neccessary 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 Parameter 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 Parameter #

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. \|br\| The License Key with Credits issued by Melissa. Get it here.	MY_LICENSE_KEY
OPTIONS	VARCHAR	Optional. \|br\| [OptionName:Parameter], separate by colon(,) See more info about OPTIONS.	USPreferredCityNames:on, USStandardizationType:short
INPUT_TABLE_NAME	VARCHAR	Required. \|br\| Name of the input table
OUTPUT_TABLE_NAME	VARCHAR	Required. \|br\| 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. \|br\| Fields in the output table; fields separates by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, see Default Ouput Fields.	Results, \|br\| FormattedAddress, \|br\| Organization, \|br\| AddressLine1, \|br\| AddressLine2, \|br\| AddressLine3, \|br\| AddressLine4, \|br\| AddressLine5, \|br\| AddressLine6, \|br\| AddressLine7, \|br\| AddressLine8, \|br\| SubPremises, \|br\| DoubleDependentLocality, \|br\| DependentLocality, \|br\| Locality, \|br\| SubAdministrativeArea, \|br\| AdministrativeArea, \|br\| PostalCode, \|br\| PostalCodeType, \|br\| AddressType, \|br\| AddressKey, \|br\| SubNationalArea, \|br\| CountryName, \|br\| CountryISO3166_1_Alpha2, \|br\| CountryISO3166_1_Alpha3, \|br\| CountryISO3166_1_Numeric, \|br\| CountrySubdivisionCode, \|br\| Thoroughfare, \|br\| ThoroughfarePreDirection, \|br\| ThoroughfareLeadingType, \|br\| ThoroughfareName, \|br\| ThoroughfareTrailingType, \|br\| ThoroughfarePostDirection, \|br\| DependentThoroughfare, \|br\| DependentThoroughfarePreDirection, \|br\| DependentThoroughfareLeadingType, \|br\| DependentThoroughfareName, \|br\| DependentThoroughfareTrailingType, \|br\| DependentThoroughfarePostDirection, \|br\| Building, \|br\| PremisesType, \|br\| PremisesNumber, \|br\| SubPremisesType, \|br\| SubPremisesNumber, \|br\| PostBox, \|br\| Latitude, \|br\| Longitude, \|br\| DeliveryIndicator, \|br\| MelissaAddressKey, \|br\| MelissaAddressKeyBase, \|br\| PostOfficeLocation, \|br\| SubPremiseLevel, \|br\| SubPremiseLevelType, \|br\| SubPremiseLevelNumber, \|br\| SubBuilding, \|br\| SubBuildingType, \|br\| SubBuildingNumber, \|br\| UTC, \|br\| DST, \|br\| DeliveryPointSuffix, \|br\| CensusKey, \|br\| Extras, \|br\| JSON_Response \|br\|

Output Examples #

Verify a table of addresses with the default output fields #

Call the stored procedure to verify multiple addresses with Default Ouput 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 a table of 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 a table of 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 Ouput 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.
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 #

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:
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.

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	Input Country not enabled for License	Contact your CSR to add new countries.
AV25	Full Verification to SubPremise	Good
AV24	Full Verification to House Number	Good
AV23	Full Verification to Thoroughfare. House Number data not available.	Good
AV22	Full Verification to Locality.	Medium. Only locality data was available, odds on quality is dependent on source of input.
AV14	Address Verified to the House Number. Issue found with SubPremise.	Medium. Most of the address was good, just bad/missing suite.
AV13	Address Verified to the Thoroughfare. Issue found with House Number.	Medium. Most of the address was good, house number not verified.
AV12	Address Verified to the Locality. Could not verify any delivery address data.	Bad. Could not verify the address but found a good locality value.
AV11	Address Verified to the Administrative Area only.	Bad. Could not verify the address but found a good administrative area value.

For the full list of all possible result codes, please visit here.

Reference Guide

Contents

Reference Guide#

2-Step Configuration #

Events and Logs #

Stored Procedures #

VERIFY_SINGLE_ADDRESS #

Verification Form #

Input Example #

Output Examples #

Verify a single address #

Verify a single address and insert the result to an output table #

Verify a single address with selected output table fields #

Verify a single address with ‘ALL’ output table fields #

SQL Script #

Setup and Sample Script #

Input Parameters #

Output Examples #

Verify a single address #

Verify a single address and insert the result into an output table #

Verify a single address with selected output fields #

Verify a single address with ‘ALL’ output table fields #

VERIFY_MULTIPLE_ADDRESSES #

Requirements #

Default Input Fields #

SQL Script #

Setup and Sample Script #

Input Parameter #

Output Examples #

Verify a table of addresses with the default output fields #

Verify a table of addresses with selected output fields #

Verify a table of addresses with ‘ALL’ output fields #

DROP_OUTPUT_TABLE #

Default Ouput Fields #

Versions and Updates #

Check for current version #

Updates #

Result Codes #

Interpreting Results #

Using Result Codes Programmatically #