The configuration script lets users create profiles that store the locations of the application's input data.
The application requires at least one profile per matching function (aka "template"). Users may create multiple profiles per template to satisfy different use cases.
Configure Translate
Run the following statements as INSTALLER_ROLE or USER_ROLE.
DO NOT RUN AS-IS. Fill in the required settings marked as application_name and consumer_defined.
Allow the application to access the consumer data required to perform matching. For each consumer-defined database and schema set in the user_settings, run the following statements. This can be modified depending on the particular scenario (e.g., object types, schema vs. object-level permissions, etc.).
PLEASE NOTE: This action is needed for all new tables added after initial execution.
USE ROLE <CONSUMER_DEFINED_ROLE>;
USE WAREHOUSE <WAREHOUSE_NAME>;
-- Grant statement example
GRANT USAGE
ON DATABASE <consumer_defined_db>
TO APPLICATION <consumer_defined_application>;
GRANT USAGE
ON SCHEMA <consumer_defined_db>.<consumer_defined_schema>
TO APPLICATION <consumer_defined_application>;
GRANT SELECT
ON ALL TABLES IN SCHEMA <consumer_defined_db>.<consumer_defined_schema>
TO APPLICATION <consumer_defined_application>;Set locations of consumer-defined resources
PLEASE NOTE: The setting_profile is important because it allows you to maintain multiple configuration sets for each template. For instance, you can have various profiles for clear_email_match, each storing different dataset locations to use with this template, such as TEST, DEV, DEFAULT, etc. You can then pass the profile as a parameter when calling the match template, as detailed in Match data with the Translate app.
Pass-through fields (consumer_*_passthrough_field_1 and consumer_*_passthrough_field_2) are optional and allow additional columns from the consumer dataset to be included in the output alongside matched IDs. Pass-through fields must also be specified in the request dimensions using the c. prefix (e.g. c.my_field).
Consumer-defined settings can alternatively be passed as runtime parameters directly in the match request; runtime parameters always take precedence over user settings. See the runtime parameters section below for a list of supported parameter names.
If the passthrough parameters are set in the DEFAULT setting profile, the respective function will use the default setting if the setting_profile parameter is not provided, even if it is not included in the parameters list during execution.
clear_email_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_clear_email_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_clear_email_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_clear_email_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_clear_email_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_clear_email_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_clear_email_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;device_id_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_devices_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_devices_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_devices_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_devices_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_devices_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_devices_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;hashed_email_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_devices_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_devices_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_devices_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_devices_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_devices_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_devices_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;hmid_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_hmid_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_hmid_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_hmid_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_hmid_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_hmid_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_hmid_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;ip_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_ip_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_ip_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_ip_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_ip_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_ip_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_ip_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;phone_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_phone_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_phone_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_phone_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_phone_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_phone_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_phone_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;waterfall_match
MERGE INTO <application_name>.local.user_settings settings
USING (
SELECT *
FROM (
VALUES
('consumer_waterfall_db', '<consumer_defined_db>', 'DEFAULT'),
('consumer_waterfall_schema', '<consumer_defined_schema>', 'DEFAULT'),
('consumer_waterfall_table', '<consumer_defined_table>', 'DEFAULT'),
('consumer_waterfall_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_hmid_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_device_id_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_ip_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_phone_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_email_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
('consumer_waterfall_email_type_field', '<consumer_defined_column_name>', 'DEFAULT')
) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
INSERT (setting_name, setting_value, setting_profile)
VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
UPDATE SET settings.setting_value = input.setting_value;