Transformation Security

Configuration for attribute transformations during materialization, semantic layer expressions, column naming, null handling, schema consolidation, and security policies including row-level access and

Overview

Transformation & Security metadata controls transformations during materialization, semantic layer views, and security policies. Configure business logic and security here.

---

Column Naming

Alias Name (ALIAS_NAME)

• Custom column name alias for Dynamic Table column

• Editable - Overrides POLYMORPHIC_ATTRIBUTE_NAME

• Example: customer_email instead of email_str

Code Generated Column Name (CODE_GENERATED_COLUMN_NAME)

• Final column name used in generated SQL

• Uses ALIAS_NAME if provided, otherwise POLYMORPHIC_ATTRIBUTE_NAME

• Read-only - Automatically generated

• Reference this name in virtual attribute expressions and view expressions

---

Materialization Transformations

Transformation Type (TRANSFORMATION_TYPE)

• Values: 'No Transformation', 'SQL Expression'

• Editable

No Transformation:

• Uses attribute as-is with NULL_VALUE_EXPRESSION (if provided)

• Default behavior

SQL Expression:

• Applies TRANSFORMATION_EXPRESSION

• NULL_VALUE_EXPRESSION not applicable

Null Value Expression (NULL_VALUE_EXPRESSION)

• SQL expression for handling null/missing values

• Default: NULL

• Editable - Only applies when TRANSFORMATION_TYPE = 'No Transformation'

• Can use {attribute_name} placeholder

Examples:

NULL
COALESCE({attribute_name}, 'N/A')
CASE WHEN {attribute_name} IS NULL THEN 'Unknown' ELSE {attribute_name} END

---

Transformation Expression (TRANSFORMATION_EXPRESSION)

• SQL expression for Dynamic Table column when TRANSFORMATION_TYPE = 'SQL Expression'

• Editable - Can use {attribute_name} placeholder

• Applied during materialization

Examples:

UPPER({attribute_name})
TRY_TO_NUMBER({attribute_name})
CASE WHEN {attribute_name} IS NULL THEN 'Unknown' WHEN {attribute_name} = '' THEN 'Empty' ELSE {attribute_name} END
TRIM(UPPER({attribute_name}))
{quantity} * {unit_price}

Transformation Expression Comment (TRANSFORMATION_EXPRESSION_COMMENT)

• Documentation for transformation expression

• Editable

---

Schema Consolidation

Unify attributes across different paths using regex and SQL expressions.

Schema Insert Regular Expression Search (SCHEMA_INSERT_REGULAR_EXPRESSION_SEARCH)

• Regex pattern for attribute consolidation

• Editable - Used to find portions of paths to replace

• Works with SCHEMA_INSERT_SQL_EXPRESSION to produce unified paths

Schema Insert SQL Expression (SCHEMA_INSERT_SQL_EXPRESSION)

• SQL expression for attribute consolidation

• Editable - Uses regex matches to produce unified attribute paths

• Applied during code generation

Use cases: Unifying similar attributes with different paths, normalizing path variations, consolidating polymorphic variations.

---

Semantic Layer Configuration

Additional transformations on top of materialized Dynamic Table columns for analytics.

Include In Semantic Layer (INCLUDE_IN_SEMANTIC_LAYER)

• Boolean for including in semantic layer views

• Editable - TRUE = appears in views, FALSE = excluded

Semantic Layer Expression (SEMANTIC_LAYER_EXPRESSION)

• Additional transformation for semantic layer views

• Editable - Applied on top of materialized columns

• {attribute_name} placeholder refers to Dynamic Table column

• Can reference other columns using CODE_GENERATED_COLUMN_NAME

Examples:

{attribute_name}
{attribute_name} || ' - ' || {other_column_name}
SUM({array_column_name})
{price} * {quantity}

Semantic Layer Expression Comment (SEMANTIC_LAYER_EXPRESSION_COMMENT)

• Documentation for view transformation

• Editable

Semantic Layer Alias Name (SEMANTIC_LAYER_ALIAS_NAME)

• Custom alias for semantic layer views

• Editable - Overrides materialized column name in views

---

Security Policy Integration

Include In Security Row Level Policy (INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY)

• Boolean for row-level security policy integration

• Editable - TRUE = included in row access policy

• Requires Security Policy Integration product tier

Masking Policy Name (MASKING_POLICY_NAME)

• Name of column masking policy to apply

• Editable - Policy must exist in Snowflake

• Applied to column in generated Dynamic Tables

• Requires Security Policy Integration product tier

Masking Policy Parameters (MASKING_POLICY_PARAMETERS)

• Parameters for masking policy

• Editable - Comma-separated list of additional columns to pass as parameters

• Format depends on policy definition

---

Merge Polymorphic Versions

Feature to automatically merge numeric polymorphic versions (int, float, str) into either float or string version.

How it works:

• Creates SQL expressions using COALESCE to handle type variations

• Updates INCLUDE_IN_SEMANTIC_LAYER flags appropriately

• Materialized columns remain separate (only semantic layer expressions updated)

Merge options:

• Merge Into Float - Consolidates int, float, str into float version

• Merge Into String - Consolidates int, float, str into string version

When to use: Multiple numeric polymorphic versions represent the same data; want to simplify semantic layer.

---

Common Scenarios

Data cleaning during materialization:

• Set TRANSFORMATION_TYPE = 'SQL Expression'

• Write TRANSFORMATION_EXPRESSION: TRIM(UPPER({attribute_name}))

• Document in TRANSFORMATION_EXPRESSION_COMMENT

Default values for missing data:

• Set TRANSFORMATION_TYPE = 'No Transformation'

• Set NULL_VALUE_EXPRESSION: COALESCE({attribute_name}, 'default_value')

View-level calculations:

• Set INCLUDE_IN_SEMANTIC_LAYER = TRUE

• Write SEMANTIC_LAYER_EXPRESSION: {price} * {quantity}

• Set SEMANTIC_LAYER_ALIAS_NAME for calculated field

Applying masking policies:

• Create masking policy in Snowflake

• Set MASKING_POLICY_NAME to policy name

• Set MASKING_POLICY_PARAMETERS if needed

Row-level security:

• Set INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY = TRUE for relevant attributes

• Configure row access policy in Snowflake

• Policy applied during code generation