Using External Functions - Resolve & Enrich

Welcome

This guide will walk you through how to use FullContact’s external functions to access our Resolve & Enrichment endpoints directly from within Snowflake.

If you run into any challenges working through this guide, please send an email to your FullContact customer success manager or to [email protected] for assistance.

Setup

If you haven't already, you must first configuration the overall application that contains each of the individual functionalities.

View the Application Setup Documentation

External Function Overviews

The FullContact External Functions FC_MAPRESOLVE and FC_ENRICH are UDFs that instruct Snowflake to call the FullContact API endpoints. When using these functions, your data leaves Snowflake. The function calls out to a FullContact API, and the data is then returned as a VARIANT (json like) object that can be parsed out.

Note: You will need a FullContact API Key in order to use this integration. Each function has a designated argument for including the API Key.

Function Mapping

Resolve CRM data w/ out of appended PersonIds and RecordIds

SELECT *, get_path(SRC, 'personIds') AS "PersonId", get_path(SRC, 'recordIds') AS "RecordId"
FROM
(SELECT *, FULLCONTACT_IDENTITY_SOLUTIONS.APP_SCHEMA.FC_MAPRESOLVE(EMAIL1,
                [EMAIL1,EMAIL2], -- emails
                PHONE1, -- phone
                [PHONE1,PHONE2,PHONE3,PHONE4], -- phones
                null, -- name object
                null, -- location object
                null, -- placekey
                null, -- profiles array
                null, -- maids array
                null, -- recordid
                null, -- liNonId
                null, -- panoramaId
                null, -- generatePID boolean
                '{{API_KEY}}'   -- auth
                 ) as SRC FROM TEST_CONSUMER_INPUT.PUBLIC.JR_CRM_DB)
WHERE get_path(SRC, 'personIds') is not null;

If you are wanting to insert the results back into a table, start by creating the table. These are the values and datatypes you will want to use. Note in our create statement we are showing an example of the fully qualified path for our table - DATABASE.SCHEMA.TABLE. You should insert your own values for a new or existing database and an appropriate table.

--Creation of Table
CREATE OR REPLACE TABLE SALES_SANDBOX.PUBLIC.BLACE_CRM_RESOLVE_TEST_OUT(
  FIRSTNAME string,
  LASTNAME string,
  GENDER string,
  ADDRESS1 string,
  CITY string,
  EMAIL1 string,
  EMAIL2 string,
  PHONE1 string,
  PHONE2 string,
  CRMGUID string,
  UNIQUEID string,
  PersonId string,
  RecordId string);

With the table created, we're ready to insert the results into the table.

--Insert into table (column size must match)
INSERT INTO SALES_SANDBOX.PUBLIC.BLACE_CRM_RESOLVE_TEST_OUT
  SELECT FIRSTNAME, LASTNAME, GENDER, ADDRESS1, CITY, EMAIL1, EMAIL2, PHONE1, PHONE2, CRMGUID, UNIQUEID, get_path(SRC, 'personIds') AS "PersonId", get_path(SRC, 'recordIds') AS "RecordId"
FROM
(SELECT *, FULLCONTACT_IDENTITY_SOLUTIONS.APP_SCHEMA.FC_MAPRESOLVE(EMAIL1,
                [EMAIL1,EMAIL2], -- emails
                PHONE1, -- phone
                [PHONE1,PHONE2,PHONE3,PHONE4], -- phones
                null, -- name object
                null, -- location object
                null, -- placekey
                null, -- profiles array
                null, -- maids array
                null, -- recordid
                null, -- liNonId
                null, -- panoramaId
                null, -- generatePID boolean
                '{{API_KEY}}'   -- auth
                 ) as SRC FROM TEST_CONSUMER_INPUT.PUBLIC.JR_CRM_DB)
WHERE get_path(SRC, 'personIds') is not null;

Enrich CRM data w/ output of appended MAIDs and SHA1 HEMs

The data available to return to Snowflake through the Enrich external function will depend on the enrichment bundles you have purchased through FullContact. We've included an example below for a customer who has purchased media amplification data and is receiving MAIDs and SHA1 HEMs.

SELECT *, get_path(SRC, 'details.emails[0].sha1') as SHA1_1, get_path(SRC, 'details.emails[1].sha1') as SHA1_2, get_path(SRC, 'details.identifiers.maids[0].id') as MAID_1, get_path(SRC, 'details.identifiers.maids[1].id') as MAID_2
FROM
(SELECT *, FULLCONTACT_IDENTITY_SOLUTIONS.APP_SCHEMA.FC_ENRICH(EMAIL1,
                [EMAIL1,EMAIL2], -- emails
                PHONE1, -- phone
                [PHONE1,PHONE2,PHONE3,PHONE4], -- phones
                null, -- name object
                null, -- location object
                null, -- placekey
                null, -- profiles array
                null, -- maids array
                null, -- recordid
                null, -- personId
                null, -- liNonId
                null, -- panoramaId
                null, -- condifidenceLevel varchar
                null, -- infer boolean
                null, -- dataFilter array
                '{{API_KEY}}'   -- auth
                 ) AS SRC FROM TEST_CONSUMER_INPUT.PUBLIC.JR_CRM_DB)
WHERE "SHA1_1" is not null
OR "MAID_1" is not null

Closing

That is it, you are ready to start integrating FullContact’s external functions for Resolve and Enrich into other data processing workflows.

We’re always looking for feedback on the products we develop for customers. Please send feedback on ways to enhance this application for your usage to [email protected].

Updated about 1 year ago