Snowflake App: Identity Resolution and Enrichment

Example: 

SETUP EVENT TABLE

--Create event db/schema
create database <my_event_db>;
create schema <my_event_db>.<my_event_schema>;

--Create event table
create event table <my_event_db>.<my_event_schema>.<my_events_log>;

--Set the account level event table
alter account set event_table=<my_event_db>.<my_event_schema>.<my_events_log>;

--Check active event table
show parameters like 'event_table' in account;

--Share application log events with provider
alter application <native_app_name> set share_events_with_provider = TRUE;

--Validate sharing flag
desc application <native_app_name>;

grant usage on database <my_db> to application <native_app_name>;

grant usage on schema <my_db>.<my_schema> to application <native_app_name>;

grant select on table <my_db>.<my_schema>.<my_table> to application <native_app_name>;

    |     

    ├── REMOVE_TABLE()

    |      `-- Description: Procedure to remove tables created in the APP_DATA schema.

    | 

    └── VMS_ENRICHMENT()

           `-- Description: Procedure to add VMS keys, identity and attribute data.

Procedure: VMS_ENRICHMENT

This procedure in the Snowflake Native App allows for the execution of VMS Enrichment process based on a set of defined parameters. Below is the detailed usage of APP_UTL.VMS_ENRICHMENT.

Syntax

To call this procedure, use the following syntax:

call <native_app_name>.APP_UTL.VMS_ENRICHMENT({ 

  'input_table':'<my_db>.<my_schema>.<my_table>', 

  'output_table':'<my_output_table>', 

  'product_name':'<product_name>', 

  'method':'<method>', 

  'name':['<full_name>'], 

  'dob':'<dob>', 

  'address':'<address>', 

  'city':'<city>', 

  'state':'<state>', 

  'zip':'<zip>', 

  'phone':'<phone>', 

  'email':'<email>', 

  'pid_key':'<vms_pid>', 

  'hhid_key':'<vms_hhid>' 

});

Examples

VMS Keying:

--VMS Keying process call

call <native_app_name>.APP_UTL.VMS_ENRICHMENT({

  'input_table':'marketing_db.leads.customers',

  'output_table': 'customer_output_01',

  'product_name':'tci attributes',

  'method':'keying',

  'name':['full_name'],

  'dob':'dob',

  'address':'address',

  'city':'city',

  'state':'state',

  'zip':'zip',

  'phone':'phone',

  'email':'email'

});

This call will execute the keying process with the specified parameters, using the data from the specified <input_table>, and create an output table named app_data.customer_output_01. The output table will include the input data along with the associated VMS Keys found and assigned match level.

VMS Enrichment:

--VMS Enrichment process call

call <native_app_name>.APP_UTL.VMS_ENRICHMENT({

  'input_table':'app_data.customer_output_01',

  'output_table': 'customer_output_01_en',

  'product_name':'tci attributes',

  'method':'enrichment',

  'pid_key':'PID'

});

This call will execute the procedure with the specified parameters, using the data from the specified <input_table> with VMS Keys and create a table named app_data.customer_output_01_en. The output table will include the input data along with the associated TCI attribute data found for each VMS PID.

VMS Plus:

--VMS Plus process call

call <native_app_name>.APP_UTL.VMS_ENRICHMENT({

  'input_table':'marketing_db.leads.audience_data',

  'output_table': 'audience_data_insights',

  'product_name':'tci attributes',

  'method':'plus',

  'name':['full_name'],

  'address':'address',

  'city':'city',

  'state':'state',

  'zip':'zip',

  'phone':'phone',

  'pid_key':'PID',

  'hhid_key':'HHID',

});