BettrData Customer Portal

Search

Home

Release Notes

User Docs

Customer Portal
Customer Portal
/
📖
User Docs
/User Guide
User Guide
/BD Select
BD Select
/
BD Segment Field Mapping Helper (Snowflake)

BD Segment Field Mapping Helper (Snowflake)

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.

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

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.

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};
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
;