# Transformation Security ## 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:** ```sql 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:** ```sql 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:** ```sql {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 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.datapancake.com/core-concepts/attribute-metadata/attribute-metadata-details/transformation-security.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.