As Snowflake is used more and more frequently as the central datawarehouse users often find themselves with duplicate copies of customer information in different tables. This guide shows how to use the FullContact for Snowflake Native Application to unify different pieces of customer data using the FullContact PersonID to have a single, accurate view of how the customer is engaging with your brand.

With your install we will provide you some synthetic data that will join to our Identity Graph to allow you to test out the application. By the end of the walkthrough, we're going to show you how several profiles of a fictional customer named Willow Underwood, will now be able to be merged into a single record of that customer. To see a more in depth example with other sample datasets, make sure to check out the YouTube product walkthrough that is a companion to this guide.

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

The FullContact for Snowflake Native App is available in the Snowflake Marketplace

  1. Visit the "Apps" section of Snowsight from your Snowflake Account

Install App

  1. Under the "Recently Shared with You" section, click the "Get" button next to the FullContact Native Identity Application
  2. Choose the warehouse to use to run the install scripts (an XS warehouse works fine).
  3. Expand the Options dropdown, where you are able to name the application anything you would like. We have named the app "FC_NATIVE_APP". This is the app name we use in the SQL queries we provide post installation of the application in a Snowsight worksheet to help you complete the installation.

  1. Click the Get button to install the application. The Get button will be deactivated while installing. There is not currently a loader that displays to give a sense for how the installation is progressing - the installers typically complete within 1-2 minutes.
  2. Once installation is complete, you will see a confirmation modal.

  1. Click the Open button, which will open a worksheet pre-populated with the queries you will need to complete the installation.

Once installed there are a few more steps that need to be completed before the FullContact for Snowflake application can function. Follow these instructions by pasting and running the following SQL in a new SQL worksheet.

  1. Create and grant access to API INTEGRATION

The API INTEGRATION is used to check your license key, allowed usage, and report usage summary counts back to FullContact. Your raw data never leaves Snowflake as the app executes natively and FullContact will not have access to your raw data unless you decide to share it with us using a normal secure share.

CREATE API INTEGRATION IF NOT EXISTS FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS
    API_PROVIDER = aws_api_gateway
    API_AWS_ROLE_ARN = 'arn:aws:iam::966965295085:role/cx-blizzard-snowflake'
    API_ALLOWED_PREFIXES = ('https://um58694ui8.execute-api.us-east-1.amazonaws.com/blizzard-default/v3')
    ENABLED = true;

-- Grant access to allow a specific user or role to use this application
GRANT USAGE ON INTEGRATION FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS TO APPLICATION FC_NATIVE_APP;
  1. Allow application to create a dedicated database for input/output tables.
GRANT CREATE DATABASE on account to APPLICATION FC_NATIVE_APP;
  1. Install and define the EXTERNAL FUNCTIONS that the application needs to run.
-- Install the EFs (external functions) that the app needs to run:
-- install EFs
CALL FC_NATIVE_APP.APP_SCHEMA.CREATE_EF_GET_DATAPACKS('FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS');
CALL FC_NATIVE_APP.APP_SCHEMA.CREATE_EF_GET_OAUTH_GROUP_LIST('FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS');
CALL FC_NATIVE_APP.APP_SCHEMA.CREATE_EF_GET_OAUTH_GROUP_MEMBER_LIST('FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS');
CALL FC_NATIVE_APP.APP_SCHEMA.CREATE_EF_GET_PRODUCT_USAGE('FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS');
CALL FC_NATIVE_APP.APP_SCHEMA.CREATE_EF_PUT_USAGE('FC_API_INT_FULLCONTACT_IDENTITY_SOLUTIONS');

In order to use the FullContact for Snowflake Application you need a license (API) key. Follow these instructions to get one for free and use the generated key in the next sections.

  1. Visit the FullContact for Snowflake Offer Page

Platform Offer

  1. Enter your business email address and click "Create Account". Once complete you should see a page directing you to check your email.

Platform Signup

  1. Check your email to find a "Welcome to FullContact" email. Click the "Verify This Email Address" button to setup your initial password

Verify Email

  1. Choose your new Password and complete the short "Tell Us About Your Business Form"
  2. Once the form is complete you will be logged into the FullContact Platform. Click the "API Keys" menu option on the left hand side.

API Keys

  1. Click the "+ Generate API Key" button, verify your phone number via SMS then name your key (we used Snowflake Key in this example)

Name Key

  1. Click the copy button to copy the value of the key into a secure location for the next step (a notepad or file would work)

Copy Key

Before we can run Resolve to unify the sample customer data we need to setup a database and schema to hold the output tables and need to create a SEMANTIC VIEW to help the FullContact application understand how to interpret the different columns in the data.

Follow the steps below and copy and paste the SQL to your SQL Worksheet.

  1. Create output schema
CREATE DATABASE FC_QUICKSTART;
CREATE SCHEMA FC_QUICKSTART.OUTPUT;
  1. Create the Semantic Input view. Run the following stored procedure. It will scan the sample input dataset and output additional SQL that you will need to copy/paste/run into your worksheet.
CALL FC_NATIVE_APP.APP_SCHEMA.GET_SQL_CREATE_INPUT_VIEW(
'FC_NATIVE_APP.SAMPLE_DATA.CUST_JOURNEY_PURCHASE_DEMO', -- input table name
'FC_QUICKSTART.OUTPUT.CUST_JOURNEY_PURCHASE_SEMANTIC',  -- output view name
'RECORD_ID');                                           -- name of column to treat as RECORD_ID
  1. Copy the results of the previous SPROC and run it (it should be something similar to the below)
-- This view create statement contains predicted aliases for columns based on data in each column.
-- Please review the statement and modify it as needed before using it as input to the RESOLVE stored procedure.
-- Accepted column names for the RESOLVE stored procedure can be found in the documentation linked below.
-- https://docs.fullcontact.com/docs/snowflake-native-resolve
CREATE OR REPLACE VIEW FC_QUICKSTART.OUTPUT.CUST_JOURNEY_PURCHASE_SEMANTIC AS SELECT
 RECORD_ID AS RECORD_ID,
 FIRST_NAME,
 LAST_NAME,
 PHONE_NUMBER,
 CITY,
 PURCHASE_CHANNEL,
 ZIP_CODE,
 ADDRESS_LINE_1,
 ADDRESS_LINE_2,
 CUSTOMER_NUMBER,
 LIFETIME_VALUE,
 EMAIL_1,
 STATE
FROM FC_NATIVE_APP.SAMPLE_DATA.CUST_JOURNEY_PURCHASE_DEMO;
GRANT USAGE ON DATABASE FC_QUICKSTART TO APPLICATION FC_NATIVE_APP;
GRANT USAGE ON SCHEMA FC_QUICKSTART.OUTPUT TO APPLICATION FC_NATIVE_APP;
GRANT SELECT ON VIEW FC_QUICKSTART.OUTPUT.CUST_JOURNEY_PURCHASE_SEMANTIC TO APPLICATION FC_NATIVE_APP;

At this point you should have your SEMANTIC view FC_QUICKSTART.OUTPUT.CUST_JOURNEY_PURCHASE_SEMANTIC created.

These steps are where the magic happens.

We provide some sample data with the application install that you can use for this initial test. Copy, paste and run the following SQL to take the sample data and run it through the FullContact Resolve process (which standardizes it and joins it to the FullContact Identity Graph) and assign Person IDs (PIDs)

  1. Run the RESOLVE_WITH_API_KEY SPROC (replace the REPLACEWITHYOURAPIKEY string below with the API you created in the FullContact platform in the previous step).
-- Call the RESOLVE SPROC to resolve and assign PIDs to sample data
CALL FC_NATIVE_APP.APP_SCHEMA.RESOLVE_WITH_API_KEY(
'FC_QUICKSTART.OUTPUT.CUST_JOURNEY_PURCHASE_SEMANTIC',    	-- semantic input view
'REPLACEWITHYOURAPIKEY',                    				-- api key
null);                                        				-- [OPTIONAL] output table name. If null, the output table name will be the name of your semantic input view table with `_RESOLVE_RESULTS` appended

The results will be stored in a table in the FC_NATIVE_APP_IO.RESOLVE_OUT schema. If not provided, the output table name will be the name of your semantic input view table with _RESOLVE_RESULTS appended to the end. In this case, FC_NATIVE_APP_IO.RESOLVE_OUT.CUST_JOURNEY_PURCHASE_SEMANTIC_RESOLVE_RESULTS

  1. View the results, making note of the PIDs column.
SELECT * FROM FC_NATIVE_APP_IO.RESOLVE_OUT.CUST_JOURNEY_PURCHASE_SEMANTIC_RESOLVE_RESULTS LIMIT 10;
  1. For each call, you can view some summary metrics on how your records were resolved.
SELECT * FROM FC_NATIVE_APP.METRICS.FC_RESOLVE_METRICS;
  1. Note how the different versions of Willow were all consolidated into the same PersonID
SELECT * FROM FC_NATIVE_APP_IO.RESOLVE_OUT.CUST_JOURNEY_PURCHASE_SEMANTIC_RESOLVE_RESULTS WHERE first_name = 'Willow';

By following this guide you learned how to use the FullContact for Snowflake application to unify disparate customer data through the Fullcontact PersonID. This application reads, standardizes and joins your dataset to the FullContact Identity Graph all without your data leaving the confines of Snowflake or being shared with FullContact.

If you want to learn more about FullContact for Snowflake check out the official documentation or contact an expert to learn more about the different products FullContact offers and how it can help you better connect to your customers.

What we've covered