--- "@context": https://schema.org "@type": TechArticle "@id": https://www.twilio.com/docs/segment/unify/data-graph/derived-properties#article headline: Derived Properties description: Create calculated values by applying functions to existing entity properties in the Data Graph. url: https://www.twilio.com/docs/segment/unify/data-graph/derived-properties inLanguage: en dateModified: 2026-10-14T20:29:41.000Z author: "@type": Organization name: Twilio Developer Education Team publisher: "@type": Organization name: Twilio --- # Derived Properties > \[!IMPORTANT] > > Derived Properties is available as a Public Beta product and the information contained in this document is subject to change. This means that some features arn't yet implemented and others may be changed before the product is declared as Generally Available. Public Beta products aren't covered by a Twilio SLA. Derived properties let you create calculated values by applying functions to existing entity properties in the Data Graph. You can define a derived property on an entity and then reuse it in any downstream resource without warehouse transformations or duplicating columns. ## Prerequisites Before using derived properties: * Your warehouse must grant Segment additional write permissions for views (a saved query that behaves like a table). See [Warehouse permissions](#warehouse-permissions-and-roles). * You need the Derived Property Manager or Entities Admin role to create, edit, or delete derived properties. ## Supported functions Derived properties support string, number, date, and conditional functions. Anywhere a function accepts an entity property, you can also use a derived property from the same entity. ### String functions | Function | Description | Arguments | | ----------- | ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `concat` | Combines up to 5 values into one string. | `values`: up to 5 entity properties, derived properties, or literal strings. Non-string values are cast to string. Null values are skipped. | | `substring` | Extracts part of a string. | `property`: an entity or derived property (non-string values are cast to string). `start`: 1-indexed position where extraction begins. `length` (optional): number of characters to extract. Leave empty to extract to the end. | If `start` exceeds the string length, Segment returns an empty string. If `length` extends past the end of the string, extraction runs to the end of the string. For example, `start = 3` and `length = 4` on `ACC-12345` returns `C-12`. ### Number functions | Function | Description | Arguments | | ----------------------------- | ------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `add`, `subtract`, `multiply` | Performs arithmetic at the record level. | `operands`: array of up to 5 number-type entity properties, derived properties, or literal numbers. Segment skips null values. Results return full precision. | | `divide` | Divides one value by another. | `dividend` and `divisor` (2 operands only). Division by zero returns null. | | `ceiling` | Rounds up to the nearest integer (for example, 12.1 returns 13, -12.7 returns -12). | `property`: a number-type entity or derived property. | | `floor` | Rounds down to the nearest integer (for example, 12.9 returns 12, -12.1 returns -13). | `property`: a number-type entity or derived property. | | `round` | Rounds to a set number of decimal places. | `property`: a number-type entity or derived property. `precision` (optional): decimal places, 0 or greater. Defaults to 0 (12.7 returns 13). | | `percentage` | Calculates (numerator / denominator) × 100. | `numerator` and `denominator`: a number-type entity property, derived property, or literal number. `precision` (optional): decimal places, 0 or greater. Defaults to 0. Division by zero returns null. | `ceiling`, `floor`, `round`, and `percentage` return null when the input is null. `round` and `percentage` follow your warehouse's default rounding for values exactly halfway between two numbers. Most supported warehouses round half away from zero (2.5 becomes 3, and -2.5 becomes -3). ### Date functions | Function | Description | Arguments | | ------------------- | ---------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `extract_date_part` | Extracts a component from a date or timestamp. Returns a number. | `property`: a date/timestamp entity or derived property. `part`: `year`, `month`, or `day`. Month returns 1 to 12, day returns 1 to 31, and year returns the full year (for example, 2026). | | `date_add` | Adds or subtracts a period of time. Returns a date. | `property`: a date/timestamp entity or derived property, or `current_date()`. `amount`: an integer. Use a negative value to subtract. `unit`: `year`, `month`, or `day`. | | `date_difference` | Returns `end_date - start_date` as a number. | `start_date` and `end_date`: a date/timestamp entity or derived property, a date from the date picker, or `current_date()` (today's date at runtime). `unit`: `year`, `month`, or `day`. | * `date_difference` returns a negative value if `end_date` is before `start_date`. * When `date_add` lands on a day that doesn't exist in the target month (for example, January 31 plus 1 month), the result follows your warehouse's date arithmetic. Most warehouses return the last day of the month. ### Conditional function | Function | Description | Arguments | | ----------- | ------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `case_when` | Returns a value based on the first matching condition, evaluated top to bottom. | `when`: a comparison using entity or derived properties (up to 10 conditions per clause and up to 10 clauses). `then`: the value to return (a literal, entity property, or derived property). `else`: required fallback value if no condition matches. | Comparison operators depend on the property's data type: | Data type | Operators | | --------- | --------------------------------------------------------------------------------------------------- | | String | `=`, `!=`, `is null`, `is not null` | | Number | `>`, `<`, `>=`, `<=`, `=`, `!=`, `is null`, `is not null` | | Date | `>`, `<`, `>=`, `<=`, `=`, `!=`, `is null`, `is not null`, `current_date()` (using the date picker) | If every `then` value and the `else` value reference properties of the same data type (for example, all dates), the derived property takes that type. If the outputs mix data types, all outputs cast to strings. The UI shows an information box when this happens. Keep output types consistent if you need numeric or date operators downstream. ## Create a derived property in the Data Graph UI To create a derived property in the Data Graph UI: 1. In the Data Graph, select the entity you want to add a property to. 2. Select the **Derived columns** tab. 3. Click **Create derived column**. 4. Configure the derived property: * **Name** (required): Enter a name that is unique within the entity, is 150 characters or fewer, and doesn't match a source property column name. * **Description** (optional): Explains the purpose and usage, is 150 characters or fewer. The description appears in tooltips downstream. * **Function type**: String, Number, Date, or Conditional. * **Function**: For example `concat`, `add`, `extract_date_part`, or `case_when`. 5. Review the **expression preview**, which shows the resulting expression as you build. 6. Click **Create column**. Segment lists derived properties in the derived columns table. * Segment orders derived properties alphabetically by default. You can sort by **Name**, **Data Type**, and **Function**. * Search covers **Name** and **Description**. * The table shows each property's data type and who created it. ## Create a derived property in the Data Graph spec You can also define derived properties in the advanced Data Graph editor. Add a `derived_property` block inside an entity block. Each block needs a name, a description, a function from the supported functions list, and the arguments that function requires. When you define a derived property in the Data Graph spec, follow these rules for referencing properties: * Reference entity properties as `entity.COLUMN_NAME`, for example `account.BALANCE`. * Reference another derived property on the same entity by its slug, for example `account.credit_utilization_pct`. * Referenced properties must exist on the entity. * Segment rejects circular references on save. The following example defines derived properties for each function type: ```hcl entity "account" { name = "Account" table_ref = "ENTITIES.ENTITY_COMMON.ACCOUNTS" primary_key = "ID" # ── String functions ────────────────────────────────────────── derived_property full_name { name = "Full Name" description = "Concatenate first and last name with a space" function = "concat" arguments { values = ["account.FIRST_NAME", "account.LAST_NAME"] delimiter = " " } } derived_property account_display_id { name = "Account Display ID" description = "Concatenate prefix, account number, and suffix" function = "concat" arguments { values = ["ACC-", "account.ACCOUNT_NUMBER", "-US"] delimiter = "" } } derived_property account_prefix { name = "Account Prefix" description = "First 3 characters of account number" function = "substring" arguments { property = "account.ACCOUNT_NUMBER" start = "1" length = "3" } } # ── Number functions ────────────────────────────────────────── derived_property balance_with_tax { name = "Balance with Tax" description = "Add balance and a fixed tax amount" function = "add" arguments { operands = ["account.BALANCE", "25.50"] } } derived_property net_balance { name = "Net Balance" description = "Subtract fees and penalties from balance" function = "subtract" arguments { operands = ["account.BALANCE", "account.FEE", "account.PENALTY"] } } derived_property total_with_tax { name = "Total with Tax" description = "Multiply balance by tax rate" function = "multiply" arguments { operands = ["account.BALANCE", "1.08"] } } derived_property monthly_payment { name = "Monthly Payment" description = "Annual amount divided by 12 months" function = "divide" arguments { dividend = "account.ANNUAL_AMOUNT" divisor = "12" } } derived_property rounded_up_score { name = "Rounded Up Score" description = "Round score up to nearest integer" function = "ceiling" arguments { property = "account.RAW_SCORE" } } derived_property completed_years { name = "Completed Years" description = "Round tenure down to nearest integer" function = "floor" arguments { property = "account.TENURE_YEARS" } } derived_property rounded_balance { name = "Rounded Balance" description = "Round balance to 2 decimal places" function = "round" arguments { property = "account.BALANCE" precision = 2 } } derived_property credit_utilization_pct { name = "Credit Utilization %" description = "Balance as a percentage of credit limit" function = "percentage" arguments { numerator = "account.BALANCE" denominator = "account.CREDIT_LIMIT" precision = 2 } } # ── Date functions ──────────────────────────────────────────── derived_property account_creation_year { name = "Account Creation Year" description = "Year extracted from creation date" function = "extract_date_part" arguments { property = "account.CREATED_DATE" part = "year" } } derived_property account_age_days { name = "Account Age (Days)" description = "Days since account was created" function = "date_difference" arguments { start_date = "account.CREATED_DATE" end_date = "current_date()" unit = "day" } } derived_property days_until_renewal { name = "Days Until Renewal" description = "Days from now until renewal date" function = "date_difference" arguments { start_date = "current_date()" end_date = "account.RENEWAL_DATE" unit = "day" } } derived_property renewal_reminder_date { name = "Renewal Reminder Date" description = "30 days before renewal date" function = "date_add" arguments { property = "account.RENEWAL_DATE" amount = -30 unit = "day" } } derived_property coverage_end_date { name = "Coverage End Date" description = "One year after enrollment" function = "date_add" arguments { property = "account.ENROLLMENT_DATE" amount = 1 unit = "year" } } # ── Conditional function ────────────────────────────────────── derived_property risk_level { name = "Risk Level" description = "Assess risk based on credit score" function = "case_when" arguments { conditions = [ { when = "account.CREDIT_SCORE >= 750", then = "high" }, { when = "account.CREDIT_SCORE >= 650", then = "medium" }, { when = "account.CREDIT_SCORE >= 550", then = "low" } ] else = "very low" } } } ``` ## Chain derived properties A derived property can reference other derived properties on the same entity. Anywhere a function accepts an entity property, you can select a derived property instead. The referenced property's output data type determines which functions and operators you can use with it. For example, a `date_add` output is a date, so it can feed `date_difference` or `extract_date_part`. When you chain derived properties, follow these rules: * A derived property can only reference derived properties on the same entity. * Circular references aren't allowed, whether direct or through another derived property. * Each derived property in a chain counts toward the limit of 100 derived properties per entity. * The expression preview shows the full resolved expression, including referenced derived properties. ### Example: Days until a renewal reminder is due 1. **Renewal Reminder Date** (`date_add`): `RENEWAL_DATE` minus 30 days. Returns a date. 2. **Days Until Reminder** (`date_difference`): from `current_date()` to Renewal Reminder Date, in days. Returns a number. ```hcl derived_property days_until_reminder { name = "Days Until Reminder" description = "Days from today until the renewal reminder date" function = "date_difference" arguments { start_date = "current_date()" end_date = "account.renewal_reminder_date" # derived property unit = "day" } } ``` ### Example: Credit utilization tier You can calculate a credit utilization percentage, then use a second derived property to bucket that percentage into risk tiers. 1. **Credit Utilization %** (`percentage`): `BALANCE` / `CREDIT_LIMIT` × 100, with 2 decimal places. 2. **Utilization Tier** (`case_when`): Credit Utilization % ≥ 70 returns "high", ≥ 30 returns "medium", otherwise "low". In the Data Graph spec, the second property references the first by slug: ```hcl derived_property utilization_tier { name = "Utilization Tier" description = "Bucket credit utilization into tiers" function = "case_when" arguments { conditions = [ { when = "account.credit_utilization_pct >= 70", then = "high" }, { when = "account.credit_utilization_pct >= 30", then = "medium" } ] else = "low" } } ``` ## Use derived properties After you create a derived property, it's available in the audience builder, conditional splits, and destination tag enrichments. * **Audience builder**: When you filter on an entity, you can choose its standard properties or its derived properties. Preview works as it does for standard properties. A tooltip shows the derived property's description and expression preview. * **Conditional splits**: Derived properties appear in split criteria as selectable properties under their entity. * **Destination tag enrichments**: A derived property appears in tag enrichments only when the tag level is the entity that the property belongs to. Derived property values are dynamic. Enter filter values as you would for standard properties. The exception is the conditional (`case_when`) function type, where the builder and splits surface sample values combining any literal values with the sample values of the entity properties used in the `case_when` function. ## Manage derived properties ### View downstream dependencies Segment shows where each derived property is being used downstream: in audiences, conditional splits, tag enrichments, and other derived properties that reference it. This helps you understand the full dependency chain before making changes. ### Edit or delete * If a downstream resource references a derived property, editing or deleting it shows a warning with the list of impacted audiences. Deletion warnings show the full dependency list, regardless of your access to those resources. * You can't delete a derived property that other derived properties reference. Remove or update the dependent properties first. * Segment blocks edits that change a property's output data type if the new type is incompatible with a dependent derived property. * Deleting a derived property that an audience references causes that audience to fail on its next run. ## Warehouse permissions and roles ### Warehouse permissions Derived properties need write access to your warehouse to create and replace view (a saved query that behaves like a table) tables. The standard Data Graph connection uses read-only permissions, so your data engineering team must grant additional permissions before you create derived properties. Segment creates and manages all derived property views within Segment's existing `__segment_reverse_etl` schema. This is the same private schema used for Reverse ETL operations. Segment grants `OWNERSHIP` access only on the views Segment creates within that schema. Segment doesn't require ownership of the schema itself or access to any other objects (such as tables) within it. Segment won't require write access to any of the schemas containing your customer data. Segment manages views as follows: * When you create derived properties on an entity, Segment creates a single non-materialized view (for example, `account_enriched`) in the `__segment_reverse_etl` schema. * The views reference your source tables using fully-qualified names, so your original tables and views remain completely untouched. * Segment evaluates the view each time it queries the data. Data isn't copied or stored separately. * When you add, modify, or delete derived properties, Segment updates the view definition in a single transaction in the `__segment_reverse_etl` schema. * If you remove all derived properties from an entity, Segment drops the view. Run the following SQL to create and manage views in this schema: ```sql SET segment_retl_schema = '__segment_reverse_etl'; SET segment_connection_role = 'SEGMENT_LINKED_ROLE'; -- Grant schema usage GRANT USAGE ON SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role); -- Allow creating views GRANT CREATE VIEW ON SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role); -- Allow replacing and dropping views GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role); GRANT OWNERSHIP ON FUTURE VIEWS IN SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role); ``` ### Permission roles These are the permission roles and their capabilities: | Role | What it can do | | ------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Entities Read-Only | Can view entities, properties, and derived properties. Segment disables the **Create column** button and derived properties display in view-only mode. | | Derived Property Manager | Can view entities and properties and create, edit, and delete derived properties, including viewing dependency information. Doesn't grant the ability to modify source entities, properties, connections, or schema. Can't edit the Data Graph. | | Entities Admin | Has full view and edit access to all entity models, connection details, and derived property capabilities. | ## Best practices Keep these best practices in mind when using derived properties: * Write descriptions. Descriptions appear in downstream tooltips next to the expression preview so others can understand a property before they use it. * Check dependencies before you edit or delete. Use the dependency view to see which audiences, splits, tags, and derived properties reference it. * Use a naming convention. Names must be unique within an entity and can't match source column names. Consistent names make properties easier to find and reuse. * Build complex logic in steps. Chain derived properties instead of packing everything into one `case_when`. You can validate and reuse intermediate properties. * Round for readability. Use `round` or `percentage` with a `precision` value when a number appears in customer-facing messaging or when you compare a number to a fixed threshold. * Keep `case_when` output types consistent. Mixed output types cast to string, which removes numeric and date operators downstream. * Split responsibilities. Data engineers can pre-build common derived properties, and marketers with the Derived Property Manager role can create their own without full Data Graph access.