Snowflake 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 get the FullContact Snowflake app from the Snowflake marketplace and complete initial configuration.

FullContact Snowflake App Quickstart Guide

Warehouse Size

You should only use XS for best performance. High concurrency can cause API gateway issues so limit warehouse size to S or XS only.

External Function Overviews

The FullContact External Functions FC_MAPRESOLVE and FC_ENRICH 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

FC_MAPRESOLVE calls the /v3/identity.mapresolve endpoint
FC_ENRICH calls the /v3/person.enrich endpoint

Resolve CRM Data to Get PersonIds and RecordIds

Testing the Resolve with Static Values

Once the native app is installed from the Snowflake marketplace, you can test the FC_MAPRESOLVE function using a simple select statement and static input values, like email.

SELECT FC_NATIVE_APP.APP_CONFIG.FC_MAPRESOLVE(
        '[email protected]',  -- email VARCHAR
        NULL,                         -- emails ARRAY
        NULL,                         -- phone VARCHAR
        NULL,                         -- phones ARRAY
        NULL,                         -- name OBJECT
        NULL,                         -- location OBJECT
        NULL,                         -- placeKey VARCHAR
        NULL,                         -- profiles ARRAY
        NULL,                         -- maids ARRAY
        NULL,                         -- recordId VARCHAR
        NULL,                         -- liNonId VARCHAR
        NULL,                         -- panoramaId VARCHAR
        FALSE,                        -- generatePid BOOLEAN
        '{{API_KEY}}'                 -- FullContact API Key VARCHAR
    ) as "RESOLVE_OUTPUT";

Resolving CRM data from a Snowflake Table

To resolve CRM data from a Snowflake table, simply pass appropriate column references to the external function. In the example below, email and address data from DEV.PUBLIC.CRM_DATA is being resolved and the results are being written to a new table DEV.PUBLIC.RESOLVED_CRM_DATA

NOTE - if your table exceeds a million rows, batch queries into subsets of 1M for best performance.

-- create table with first million
CREATE OR REPLACE TABLE DEV.PUBLIC.RESOLVED_CRM_DATA AS
    (SELECT
         t.RECORD_ID,
         FC_NATIVE_APP.APP_CONFIG.FC_MAPRESOLVE(
                 NULL,                                          -- email VARCHAR
                 IFF(
                         t.EMAIL_1 IS NULL AND t.EMAIL_2 IS NULL,
                         NULL,
                         ARRAY_CONSTRUCT_COMPACT(t.EMAIL_1, t.EMAIL_2)
                 ),                                             -- emails ARRAY
                 t.PRIMARY_PHONE_NUMBER,                        -- phone VARCHAR
                 NULL,                                          -- phones ARRAY
                 NULL,                                          -- name OBJECT
                 NULL,                                          -- location OBJECT
                 NULL,                                          -- placeKey VARCHAR
                 NULL,                                          -- profiles ARRAY
                 NULL,                                          -- maids ARRAY
                 NULL,                                          -- recordId VARCHAR
                 NULL,                                          -- liNonId VARCHAR
                 NULL,                                          -- panoramaId VARCHAR
                 FALSE,                                         -- generatePid BOOLEAN
                 '{{API_KEY}}'                                  -- FullContact API Key VARCHAR
         ) AS "RESOLVE_OUTPUT"
     FROM DEV.PUBLIC.CRM_DATA t
     WHERE t.RECORD_ID BETWEEN 1 AND 1000000);
     
-- continue inserting with next million etc...
INSERT INTO DEV.PUBLIC.RESOLVED_CRM_DATA (RECORD_ID, RESOLVE_OUTPUT)
SELECT
    t.RECORD_ID,
    FC_NATIVE_APP.APP_CONFIG.FC_MAPRESOLVE(
        NULL,                                                    -- email VARCHAR
        IFF(
                t.EMAIL_1 IS NULL AND t.EMAIL_2 IS NULL,
                NULL,
                ARRAY_CONSTRUCT_COMPACT(t.EMAIL_1, t.EMAIL_2)
        ),                                                       -- emails ARRAY
        t.PRIMARY_PHONE_NUMBER,                                  -- phone VARCHAR
        NULL,                                                    -- phones ARRAY
        NULL,                                                    -- name OBJECT
        NULL,                                                    -- location OBJECT
        NULL,                                                    -- placeKey VARCHAR
        NULL,                                                    -- profiles ARRAY
        NULL,                                                    -- maids ARRAY
        NULL,                                                    -- recordId VARCHAR
        NULL,                                                    -- liNonId VARCHAR
        NULL,                                                    -- panoramaId VARCHAR
        FALSE,                                                   -- generatePid BOOLEAN
        '{{API_KEY}}'                                            -- FullContact API Key VARCHAR
    ) AS RESOLVE_OUTPUT
FROM DEV.PUBLIC.CRM_DATA t
WHERE t.RECORD_ID BETWEEN 1000001 AND 2000000;

Here is another example that that creates a results table first. Then parses the personId and recordId from the resolve output variant and inserts into the created output table.

 -- create table in advance
CREATE OR REPLACE TABLE DEV.PUBLIC.RESOLVED_CRM_OUTPUT(
  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);

-- resolve, parse, and insert
INSERT INTO DEV.PUBLIC.RESOLVED_CRM_OUTPUT
SELECT FIRSTNAME,
       LASTNAME,
       GENDER,
       ADDRESS1,
       CITY,
       EMAIL1,
       EMAIL2,
       PHONE1,
       PHONE2,
       CRMGUID,
       UNIQUEID,
       get_path(SRC, 'personIds') AS "PERSON_ID",
       get_path(SRC, 'recordIds') AS "RECORD_ID"
FROM (SELECT *,
             FC_NATIVE_APP.APP_CONFIG.FC_MAPRESOLVE(
                     EMAIL1,
                     [EMAIL1,EMAIL2],
                     PHONE1,
                     [PHONE1,PHONE2,PHONE3,PHONE4],
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     '{{API_KEY}}'
             ) AS SRC
      FROM DEV.PUBLIC.CRM_DATA)
WHERE get_path(SRC, 'personIds') IS NOT NULL;

Enriching CRM Data

The data available to return to Snowflake through the FC_ENRICH external function will depend on the enrichment bundles you have purchased through FullContact. Other than input arguments differing slightly, usage is identical to FC_MAPRESOLVE

CREATE OR REPLACE TABLE TEST_DB.PUBLIC.MY_CUSTOMER_EMAILS_ENRICHED AS
    (SELECT e.email AS "EMAIL",
            FC_NATIVE_APP.APP_CONFIG.FC_ENRICH(e.email,             --email VARCHAR
                                               NULL,                -- emails ARRAY
                                               NULL,                -- phone VARCHAR
                                               NULL,                -- phones ARRAY
                                               NULL,                -- name OBJECT
                                               NULL,                -- location OBJECT
                                               NULL,                -- placeKey VARCHAR
                                               NULL,                -- profiles ARRAY
                                               NULL,                -- maids ARRAY
                                               NULL,                -- recordId VARCHAR
                                               NULL,                -- personId VARCHAR
                                               NULL,                -- liNonId VARCHAR
                                               NULL,                -- panoramaId VARCHAR
                                               NULL,                -- confidenceLevel VARCHAR
                                               NULL,                -- infer VARCHAR
                                               NULL,                -- dataFilter VARCHAR
                                               '{{API_KEY}}'        -- FullContact API Key VARCHAR
            ) AS "ENRICH_OUTPUT"
     FROM TEST_DB.PUBLIC.MY_CUSTOMER_EMAILS e
     ORDER BY e.email
     LIMIT 1000000 OFFSET 1000000);

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