Sample Snowflake External Function

A collection of sample Snowflake functions.

Sample Snowflake External Function

Method: Tokenization

Type: ALPHA

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

VARCHAR

16M (16,777,216 bytes)

4K (4,096 bytes)

CHAR

STRING

TEXT

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_ALPHA ( val varchar ) 
  RETURNS varchar 
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using an ALPHA data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_ALPHA"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_ALPHA ( val varchar ) 
  RETURNS varchar 
  NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using an ALPHA data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_ALPHA"}]}'
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

 

Sample EF Calls:

SELECT PTY_PROTECT_ALPHA ('Hello World')

SELECT PTY_UNPROTECT_ALPHA('rfDtw sLMJK');

 

Snowflake Masking Policy example:

create or replace masking policy alpha_policy as (val string) returns string -> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_ALPHA(val) 
 else val 
end;

alter table pii_data modify column field01 set masking policy alpha_policy; 
alter table pii_data modify column field01 unset masking policy;

Method: Tokenization

Type: NUMERIC

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

NUMBER

 

 

DECIMAL

INTEGER

DOUBLE

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_NUMERIC ( val number ) 
  RETURNS number 
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using a NUMERIC data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_NUMERIC"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_NUMERIC ( val number) 
  RETURNS number 
  NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using a NUMERIC data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_NUMERIC"}]}'
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

 

Sample EF Calls:

SELECT PTY_PROTECT_NUMERIC ('123456789');

SELECT PTY_UNPROTECT_NUMERIC ('752513497');

 

Snowflake Masking Policy example:

create or replace masking policy num_policy as (val number) returns number -> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_NUMERIC(val) 
 else val 
end;

alter table pii_data modify column field02 set masking policy num_policy; 
alter table pii_data modify column field02 unset masking policy;

Method: Tokenization

Type: DATE YYYY-MM-DD

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

DATE (any supported format)

10 bytes

10 bytes

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_DATEYYYYMMDD ( val date ) 
  RETURNS date 
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using a Date data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_DATEYYYYMMDD"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_DATEYYYYMMDD ( val date ) 
  RETURNS date 
  NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using a Date data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_DATEYYYYMMDD"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

Sample EF Calls:

SELECT PTY_PROTECT_DATEYYYYMMDD ('2020-12-31');

SELECT PTY_UNPROTECT_DATEYYYYMMDD('0653-06-01');

SELECT PTY_PROTECT_DATEYYYYMMDD ('31-DEC-2020');*

SELECT PTY_UNPROTECT_DATEYYYYMMDD('01-JUN-0653');*

SELECT PTY_PROTECT_DATEYYYYMMDD('12/31/2020');*

SELECT PTY_UNPROTECT_DATEYYYYMMDD('06/01/0653');*

SELECT PTY_PROTECT_DATEYYYYMMDD (current_date);

 

Snowflake Masking Policy example:

create or replace masking policy date_policy as (val date) returns date -> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_DATEYYYYMMDD (val) 
 else val 
end;

alter table pii_data modify column field11 set masking policy date_policy; 
alter table pii_data modify column field11 unset masking policy;
**\***: Automatic cast to YYYY-MM-DD, no need to make any conversions. The output is always in the YYYY-MM-DD format

Cutover Dates of the Proleptic Gregorian Calendar: no issues (no conversions performed by Snowflake)

Method: Tokenization

Type: DATETIME

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

DATE

10 bytes

29 bytes

DATETIME

29 bytes

TIMESTAMPNTZ*

TIMESTAMP_NTZ*

TIMESTAMP WITHOUT TIME ZONE*

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_DATETIME ( val timestamp ) 
  RETURNS timestamp 
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using a TIMESTAMP data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_DATETIME"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_DATETIME ( val timestamp ) 
  RETURNS timestamp 
  NOT NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using a TIMESTAMP data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_DATETIME"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

Sample EF Calls:

SELECT PTY_PROTECT_DATETIME('2010-10-25');

SELECT PTY_UNPROTECT_DATETIME('0845-04-04');

SELECT PTY_PROTECT_DATETIME('2010-10-25 10:45:33');

SELECT PTY_UNPROTECT_DATETIME('0845-04-04 10:45:33');

SELECT PTY_PROTECT_DATETIME('2010-10-25 10:45:33.123');

SELECT PTY_UNPROTECT_DATETIME('0845-04-04 10:45:33.123');

SELECT PTY_PROTECT_DATETIME(current_date);

SELECT PTY_PROTECT_DATETIME(cast(current_timestamp as TIMESTAMPNTZ));

 

Snowflake Masking Policy example:

create or replace masking policy datetime_policy as (val timestampntz) returns timestampntz -> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_DATETIME (val) 
 else val 
end;

alter table pii_data modify column field12 set masking policy datetime_policy; 
alter table pii_data modify column field12 unset masking policy;
**\***: Default TIMESTAMP in Snowflake includes Time Zone – not supported by Protegrity’s DATETIME data element

Method: Tokenization

Type: DECIMAL

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

NUMBER(N,M)

38 digits

36 digits

NUMERIC(N,M)*

DECIMAL(N,M)*

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_DECIMAL ( val NUMBER(38,6) ) 
  RETURNS NUMBER(38,6) 
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using a DECIMAL data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_DECIMAL"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_DECIMAL ( val NUMBER(38,6) ) 
  RETURNS NUMBER(38,6) 
  NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using a DECIMAL data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_DECIMAL"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

Sample EF Calls:

SELECT PTY_PROTECT_DECIMAL (12345678.99);

SELECT PTY_UNPROTECT_DECIMAL (21872469.760000);

 

Snowflake Masking Policy example:

create or replace masking policy decimal_policy as (val NUMBER(38,6)) returns NUMBER(38,6)-> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_DECIMAL (val) 
 else val 
end;

alter table pii_data modify column field13 set masking policy decimal_policy; 
alter table pii_data modify column field13 unset masking policy;
**\***: Synonymous with NUMBER

Method: Tokenization

Type: INTEGER

 

Snowflake Data Types

Snowflake Max Size

Protegrity Max Size

NUMBER

38 digits

2 bytes

4 bytes

8 bytes

NUMERIC*

INT*

INTEGER*

BIGINT*

SMALLINT*

TINYINT*

BYTEINT*

 

External Function Sample Definitions:

CREATE SECURE EXTERNAL FUNCTION PTY_PROTECT_INTEGER ( val NUMBER ) 
  RETURNS NUMBER
  NULL 
  IMMUTABLE 
  COMMENT = 'Protects using an INTEGER data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"PROTECT","data_element":"TOK_INTEGER"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

CREATE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_INTEGER ( val NUMBER ) 
  RETURNS NUMBER 
  NOT NULL 
  IMMUTABLE 
  COMMENT = 'Unprotects using an INTEGER data element'  
  API_INTEGRATION = REPLACE_WITH_YOUR_API_INTEGRATION_ID 
  HEADERS =(  
    'X-Protegrity-HCoP-Rules'=
    '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"TOK_INTEGER"}]}'    
  ) 
  CONTEXT_HEADERS = ( current_user, current_timestamp, current_account ) 
  AS '<AWS API GATEWAY URL>/SF_CUSTOMER';

Sample EF Calls:

SELECT PTY_PROTECT_INTEGER (123456789);

SELECT PTY_UNPROTECT_INTEGER (1104108887);

 

Snowflake Masking Policy example:

create or replace masking policy int_policy as (val NUMBER ) returns NUMBER -> 
 case 
 when current_role() in ('ACCOUNTADMIN') then PTY_UNPROTECT_INTEGER (val) 
 else val 
end;

alter table pii_data modify column field14 set masking policy int_policy; 
alter table pii_data modify column field14 unset masking policy;
**\***: Synonymous with NUMBER, except that precision and scale cannot be specified \(i.e. always defaults to NUMBER\(38, 0\)\)

**Recommended approach for protecting whole numbers fields in Snowflake

When values are…then use the following Data Element:
Between -32768 and 32767INTEGER (2 bytes)
Between -2147483648 and 2147483647INTEGER (4 bytes)
Between -9223372036854775808 and 9223372036854775807INTEGER (8 bytes)
< -9223372036854775808 or > 9223372036854775807DECIMAL

When in doubt, use DECIMAL for any numeric range.


Last modified : January 21, 2026