Derived Properties

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.
• You need the Derived Property Manager or Entities Admin role to create, edit, or delete derived properties.

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.

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.

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_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.

Comparison operators depend on the property's data type:

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.

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.

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:

1
entity "account" {
2
  name        = "Account"
3
  table_ref   = "ENTITIES.ENTITY_COMMON.ACCOUNTS"
4
  primary_key = "ID"
5

6
  # ── String functions ──────────────────────────────────────────
7

8
  derived_property full_name {
9
    name        = "Full Name"
10
    description = "Concatenate first and last name with a space"
11
    function    = "concat"
12

13
    arguments {
14
      values    = ["account.FIRST_NAME", "account.LAST_NAME"]
15
      delimiter = " "
16
    }
17
  }
18

19
  derived_property account_display_id {
20
    name        = "Account Display ID"
21
    description = "Concatenate prefix, account number, and suffix"
22
    function    = "concat"
23

24
    arguments {
25
      values    = ["ACC-", "account.ACCOUNT_NUMBER", "-US"]
26
      delimiter = ""
27
    }
28
  }
29

30
  derived_property account_prefix {
31
    name        = "Account Prefix"
32
    description = "First 3 characters of account number"
33
    function    = "substring"
34

35
    arguments {
36
      property = "account.ACCOUNT_NUMBER"
37
      start    = "1"
38
      length    = "3"
39
    }
40
  }
41

42
  # ── Number functions ──────────────────────────────────────────
43

44
  derived_property balance_with_tax {
45
    name        = "Balance with Tax"
46
    description = "Add balance and a fixed tax amount"
47
    function    = "add"
48

49
    arguments {
50
      operands = ["account.BALANCE", "25.50"]
51
    }
52
  }
53

54
  derived_property net_balance {
55
    name        = "Net Balance"
56
    description = "Subtract fees and penalties from balance"
57
    function    = "subtract"
58

59
    arguments {
60
      operands = ["account.BALANCE", "account.FEE", "account.PENALTY"]
61
    }
62
  }
63

64
  derived_property total_with_tax {
65
    name        = "Total with Tax"
66
    description = "Multiply balance by tax rate"
67
    function    = "multiply"
68

69
    arguments {
70
      operands = ["account.BALANCE", "1.08"]
71
    }
72
  }
73

74
  derived_property monthly_payment {
75
    name        = "Monthly Payment"
76
    description = "Annual amount divided by 12 months"
77
    function    = "divide"
78

79
    arguments {
80
      dividend = "account.ANNUAL_AMOUNT"
81
      divisor  = "12"
82
    }
83
  }
84

85
derived_property rounded_up_score {
86
    name        = "Rounded Up Score"
87
    description = "Round score up to nearest integer"
88
    function    = "ceiling"
89

90
    arguments {
91
      property = "account.RAW_SCORE"
92
    }
93
  }
94

95
  derived_property completed_years {
96
    name        = "Completed Years"
97
    description = "Round tenure down to nearest integer"
98
    function    = "floor"
99

100
    arguments {
101
      property = "account.TENURE_YEARS"
102
    }
103
  }
104

105
  derived_property rounded_balance {
106
    name        = "Rounded Balance"
107
    description = "Round balance to 2 decimal places"
108
    function    = "round"
109

110
    arguments {
111
      property  = "account.BALANCE"
112
      precision = 2
113
    }
114
  }
115

116
  derived_property credit_utilization_pct {
117
    name        = "Credit Utilization %"
118
    description = "Balance as a percentage of credit limit"
119
    function    = "percentage"
120

121
    arguments {
122
      numerator   = "account.BALANCE"
123
      denominator = "account.CREDIT_LIMIT"
124
      precision   = 2
125
    }
126
  }
127

128
  # ── Date functions ────────────────────────────────────────────
129

130
  derived_property account_creation_year {
131
    name        = "Account Creation Year"
132
    description = "Year extracted from creation date"
133
    function    = "extract_date_part"
134

135
    arguments {
136
      property = "account.CREATED_DATE"
137
      part     = "year"
138
    }
139
  }
140

141
  derived_property account_age_days {
142
    name        = "Account Age (Days)"
143
    description = "Days since account was created"
144
    function    = "date_difference"
145

146
    arguments {
147
      start_date = "account.CREATED_DATE"
148
      end_date   = "current_date()"
149
      unit       = "day"
150
    }
151
  }
152

153
  derived_property days_until_renewal {
154
    name        = "Days Until Renewal"
155
    description = "Days from now until renewal date"
156
    function    = "date_difference"
157

158
    arguments {
159
      start_date = "current_date()"
160
      end_date   = "account.RENEWAL_DATE"
161
      unit       = "day"
162
    }
163
  }
164

165
 derived_property renewal_reminder_date {
166
    name        = "Renewal Reminder Date"
167
    description = "30 days before renewal date"
168
    function    = "date_add"
169

170
    arguments {
171
      property = "account.RENEWAL_DATE"
172
      amount   = -30
173
      unit     = "day"
174
    }
175
  }
176

177
  derived_property coverage_end_date {
178
    name        = "Coverage End Date"
179
    description = "One year after enrollment"
180
    function    = "date_add"
181

182
    arguments {
183
      property = "account.ENROLLMENT_DATE"
184
      amount   = 1
185
      unit     = "year"
186
    }
187
  }
188

189
  # ── Conditional function ──────────────────────────────────────
190

191
  derived_property risk_level {
192
    name        = "Risk Level"
193
    description = "Assess risk based on credit score"
194
    function    = "case_when"
195

196
    arguments {
197
      conditions = [
198
        { when = "account.CREDIT_SCORE >= 750", then = "high" },
199
        { when = "account.CREDIT_SCORE >= 650", then = "medium" },
200
        { when = "account.CREDIT_SCORE >= 550", then = "low" }
201
      ]
202
      else = "very low"
203
    }
204
  }
205
}

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.

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.

1
derived_property days_until_reminder {
2
    name        = "Days Until Reminder"
3
    description = "Days from today until the renewal reminder date"
4
    function    = "date_difference"
5

6
    arguments {
7
      start_date = "current_date()"
8
      end_date   = "account.renewal_reminder_date"   # derived property
9
      unit       = "day"
10
    }
11
  }

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:

1
derived_property utilization_tier {
2
  name        = "Utilization Tier"
3
  description = "Bucket credit utilization into tiers"
4
  function    = "case_when"
5

6
  arguments {
7
    conditions = [
8
      { when = "account.credit_utilization_pct >= 70", then = "high" },
9
      { when = "account.credit_utilization_pct >= 30", then = "medium" }
10
    ]
11
    else = "low"
12
  }
13
}

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.

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.

• 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.

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:

1
SET segment_retl_schema = '__segment_reverse_etl';
2
SET segment_connection_role = 'SEGMENT_LINKED_ROLE';
3

4
-- Grant schema usage
5
GRANT USAGE ON SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role);
6

7
-- Allow creating views
8
GRANT CREATE VIEW ON SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role);
9

10
-- Allow replacing and dropping views
11
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role);
12
GRANT OWNERSHIP ON FUTURE VIEWS IN SCHEMA identifier($segment_retl_schema) TO ROLE identifier($segment_connection_role);

These are the permission roles and their capabilities:

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.