Overview

Mapping of fields in Audience Builder can be a monumental task if you are dealing with hundreds of fields. To help with this, we have created a stored procedure to autocreate much of the fields in a temporary table.

Prerequisites

The user that will create and run the stored procedure will need enough permissions to do it successfully. This is a minimum set of permissions to do this is below. The assumption is that the tables and views that the stored procedure will access are in the same database and schema. You may need to add more grants to select from.

use database {TARGET_DATABASE};
use schema {TARGET_SCHEMA};
use warehouse SOME_WAREHOUSE;

------------------------------------------------------------------------------------------
-- Add Table Generator Permissions to Role
------------------------------------------------------------------------------------------

GRANT USAGE ON DATABASE {TARGET_DATABASE} TO ROLE {TARGET_ROLE};
GRANT USAGE ON SCHEMA {TARGET_DATABASE}.{TARGET_SCHEMA} TO ROLE {TARGET_ROLE};
GRANT SELECT ON ALL TABLES IN SCHEMA {TARGET_DATABASE}.{TARGET_SCHEMA} TO ROLE {TARGET_ROLE};
GRANT SELECT ON ALL VIEWS IN SCHEMA {TARGET_DATABASE}.{TARGET_SCHEMA} TO ROLE {TARGET_ROLE};
GRANT CREATE PROCEDURE ON SCHEMA {TARGET_DATABASE}.{TARGET_SCHEMA} TO ROLE {TARGET_ROLE};

The variable to fill in are noted below

TARGET_DATABASE: The database where the source tables and view are, and where the procedure will be created

Example: BETTRDATA

TARGET_SCHEMA: The schema where the source tables and view are, and where the procedure will be create

Example: STAGING

TARGET_ROLE: The role of the user to create and run the stored procedure

Once this is done, the user should be able to create and run the stored procedure. The script below shows the logic

USE ROLE BETTRDATA_AUDIENCE_BUILDER_ROLE;
USE DATABASE {TARGET_DATABASE};
USE SCHEMA {TARGET_SCHEMA};
USE WAREHOUSE COMPUTE;

CREATE OR REPLACE PROCEDURE GENERATE_FIELD_VALUES(
    table_name STRING,
    field_list ARRAY
)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
var js_field_list = arguments[1];
var table_name = arguments[0];

// Construct the SQL query for unpivoting with distinct values
var sql_query = `
    CREATE OR REPLACE TEMPORARY TABLE temp_field_values AS
    SELECT DISTINCT field_name, field_value
    FROM (
        SELECT ` + js_field_list.map(field => `TO_VARCHAR("${field}") AS "${field}"`).join(', ') + `
        FROM ` + table_name + `
    ) t
    UNPIVOT(field_value FOR field_name IN (` + js_field_list.map(field => `"${field}"`).join(', ') + `))
`;

// Execute the query to create the temp table
var stmt = snowflake.createStatement({sqlText: sql_query});
stmt.execute();

return 'Temporary table temp_field_values created successfully with distinct field values. You can now display or download its contents.';
$$;


/*
Parameters
- First parameter is the table name
- Second parameter is list of columns in that table that will add to the
It generates a temporary table that we will use for the field definition for 
audience builder
*/
CALL {TARGET_DATABASE}.{TARGET_SCHEMA}.GENERATE_FIELD_VALUES('DEMO_DATA_VIEW', ARRAY_CONSTRUCT('CHILD_PRESENT','CITY','AGE','DWELLING_TYPE','EST_AGE'));

/*
Select this table and download the data as a CSV, which matches the definition 
csv we use for the audience builder fields
*/
select field_name "name",
       field_name "label",
       'selection-multi' "displayType",
       'Default' "categories",
       '' "validations.min",
       '' "validations.max",
       field_value "validations.value",
       field_value "validations.value_label",
       '' "validations.minDate",
       '' "validations.maxDate"
from temp_field_values order by FIELD_NAME
;

Calling the stored will return a set of rows that matches the format of the data-source-fields.csv that can be downloaded and uploaded to an Audience Builder data source fields dialog. Download this as a CSV file.

Example Output

NAME	LABEL	DISPLAYTYPE	CATEGORIES	validations.min	validations.max	validations.value	validations.value_label	validations.minDate	validations.maxDate
AGE	AGE	selection-multi	Default			36	36
AGE	AGE	selection-multi	Default			64	64
AGE	AGE	selection-multi	Default			80	80
AGE	AGE	selection-multi	Default			89	89
AGE	AGE	selection-multi	Default			71	71

Additional Steps

You will need to change the label and categories to match to your preference, but that will be a matter of selecting the relevant column values and replacing.

BD Segment Field Mapping Helper (Snowflake)

Overview

Prerequisites

Example Output

Additional Steps