Reference Guide

Reference Guide#

Application Configuration#

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

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. 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 the app, default name is GLOBAL_ADDRESS_VERIFICATION.

  • Choose 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 procedure in <APP_NAME>.CORE schema.

Setup and Sample Script#

  1. Add a new SQL Worksheet in your workspace.

  2. 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.<OUTPUT_TABLE_NAME>;
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

Examples#

Verify a 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 will be displayed in JSON format.

{
  "Version": "9.4.1.1232",
  "TransmissionReference": "{\"product\":\"SnowflakeNativeApps\",\"version\":\"3.12.4.0\"}",
  "TransmissionResults": "",
  "TotalRecords": "1",
  "Records": [
    {
      "RecordID": "1",
      "Results": "AC16,AS01,AV24,GS05",
      "FormattedAddress": "22382 Avenida Empresa;Rancho Santa Margarita CA  92688-2112",
      "Organization": "",
      "AddressLine1": "22382 Avenida Empresa",
      "AddressLine2": "",
      "AddressLine3": "",
      "AddressLine4": "",
      "AddressLine5": "",
      "AddressLine6": "",
      "AddressLine7": "",
      "AddressLine8": "",
      "SubPremises": "",
      "DoubleDependentLocality": "",
      "DependentLocality": "",
      "Locality": "Rancho Santa Margarita",
      "SubAdministrativeArea": "Orange",
      "AdministrativeArea": "CA",
      "PostalCode": "92688-2112",
      "PostalCodeType": "",
      "AddressType": "S",
      "AddressKey": "92688211282",
      "SubNationalArea": "",
      "CountryName": "United States of America",
      "CountryISO3166_1_Alpha2": "US",
      "CountryISO3166_1_Alpha3": "USA",
      "CountryISO3166_1_Numeric": "840",
      "CountrySubdivisionCode": "US-CA",
      "Thoroughfare": "Avenida Empresa",
      "ThoroughfarePreDirection": "",
      "ThoroughfareLeadingType": "",
      "ThoroughfareName": "Avenida Empresa",
      "ThoroughfareTrailingType": "",
      "ThoroughfarePostDirection": "",
      "DependentThoroughfare": "",
      "DependentThoroughfarePreDirection": "",
      "DependentThoroughfareLeadingType": "",
      "DependentThoroughfareName": "",
      "DependentThoroughfareTrailingType": "",
      "DependentThoroughfarePostDirection": "",
      "Building": "",
      "PremisesType": "",
      "PremisesNumber": "22382",
      "SubPremisesType": "",
      "SubPremisesNumber": "",
      "PostBox": "",
      "Latitude": "33.637562",
      "Longitude": "-117.606887",
      "DeliveryIndicator": "B",
      "MelissaAddressKey": "8008006245",
      "MelissaAddressKeyBase": "",
      "PostOfficeLocation": "",
      "SubPremiseLevel": "",
      "SubPremiseLevelType": "",
      "SubPremiseLevelNumber": "",
      "SubBuilding": "",
      "SubBuildingType": "",
      "SubBuildingNumber": "",
      "UTC": "UTC-08:00",
      "DST": "Y",
      "DeliveryPointSuffix": "",
      "CensusKey": "060590320531002",
      "Extras": {}
    }
  ]
}

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 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>'
);

Verify a single address with selected output fields

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

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>'
);

Verify a single address with ‘ALL’ output table fields

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'
);

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 procedure in <APPL_NAME>.CORE schema.

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) NOT NULL PRIMARY KEY
   ,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 ('')
);

    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 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 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 DISTINCT
    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
   ,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 OUTPUT.<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 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 DISTINCT
   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
   ,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 OUTPUT.<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

DUPLICATE_CHECK

BOOLEAN

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

Some common examples are shown below.

Verify multiple addresses with the default output fields

CALL VERIFY_MULTIPLE_ADDRESSES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,INPUT_TABLE_NAME       => '<INPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
);

Verify multiple addresses with selected options and output fields

CALL VERIFY_MULTIPLE_ADDRESSES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,OPTIONS                => '<OptionName1:Parameter,OptionName2:Parameter>'
  ,INPUT_TABLE_NAME       => '<INPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
);

Verify multiple addresses with ‘ALL’ output fields

CALL VERIFY_MULTIPLE_ADDRESSES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,INPUT_TABLE_NAME       => '<INPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_FIELDS    => 'All'
);

Verify multiple addresses with duplicate RecordID check enabled

CALL VERIFY_MULTIPLE_ADDRESSES(
   LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
  ,INPUT_TABLE_NAME       => '<INPUT_TABLE_NAME>'
  ,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 CORE.DROP_OUTPUT_TABLE('<APP_NAME>.OUTPUT.<OUTPUT_TABLE_NAME>');

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.

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#

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 Native App Global Address Verification - 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:

    ../../_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 Cloud API 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