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.
The FullContact for Snowflake Native App is available in the Snowflake Marketplace
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.
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;
GRANT CREATE DATABASE on account to APPLICATION FC_NATIVE_APP;
EXTERNAL FUNCTIONSthat 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.
Snowflake Keyin this example)
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.
CREATE DATABASE FC_QUICKSTART; CREATE SCHEMA FC_QUICKSTART.OUTPUT;
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
-- 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
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)
RESOLVE_WITH_API_KEYSPROC (replace the
REPLACEWITHYOURAPIKEYstring 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,
SELECT * FROM FC_NATIVE_APP_IO.RESOLVE_OUT.CUST_JOURNEY_PURCHASE_SEMANTIC_RESOLVE_RESULTS LIMIT 10;
SELECT * FROM FC_NATIVE_APP.METRICS.FC_RESOLVE_METRICS;
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.