Picker and Preview
The computed attribute editor helps you build an attribute without remembering model, column or attribute names, and lets you check its values before you save it.
Insert anything with /
In the Formula and Advanced: SQL boxes, type / (or press Ctrl+Space) to open a list of what you can insert. Keep typing to narrow it, use the arrow keys to move, and press Enter to insert.
You can search the way you think: /safe (divide), /coalesce (default when empty), or several words of a name such as /lifetime sp. In Advanced: SQL, /sum and /count also find a sum or count over a related model; a Formula has no sum or count of its own, so there they only find computed attributes and columns whose names match, and, if you can create computed attributes, offer to create a new one.
In a Formula
| You can insert | Example |
|---|---|
| Another computed attribute on the same model | lifetime_value |
| A column of the parent model | parent.signup_date |
| A function, with the cursor placed on its first input | Days since a date, Divide (no error on zero), If / then / else, Round |
| + New computed attribute | Builds a missing input in a side panel, saves it and inserts it |
The attributes a formula uses are picked up from what you type — there is nothing to tick. Write names as they are; don’t wrap them in {{ }}.
In Advanced: SQL
| You can insert | Example |
|---|---|
| A column of the parent model | parent.customer_id |
| A count, sum or other value from a related model | Sum · orders → amount |
| A function | Round, Default when empty |
A value from a related model is inserted as a complete subquery, already joined to the current customer, with the cursor left just before its closing ). Type src. there to list that model’s columns — for example to add AND src.status = 'completed'. To continue after the subquery instead, move the cursor past the ) first.
On ClickHouse, related models are not offered here: ClickHouse cannot read the current customer (parent) inside a subquery, so such a subquery fails. Build a value from a related model as an Aggregation, Occurrence or List attribute instead, and combine it with others in a Formula.
Use Advanced: SQL only when the other types can’t express what you need.
Preview before saving
Click Preview values to compute the attribute in your warehouse without saving it. You see:
- Up to ten sample records with their values
- How many records have a value and how many don’t
- The number of distinct values, for text, number, true/false and date values
- The lowest and highest value, for text, number and date values
- The most common values, for text and true/false values
- The type the warehouse returns — the Result Type is set from it. If you change the definition afterwards, a note says the type came from an earlier preview until you preview again or change it back.
Preview also catches problems that only show up with real data, such as a subquery that returns several rows for some customers, or a join that gives everyone the same value. Snowflake and Databricks refuse a subquery that might return several rows before running it; Preview explains that error the same way. Preview is available while creating an attribute and on its detail page.
Previewing a saved attribute as it is needs permission to read computed attributes. Previewing a new attribute, or unsaved changes to an existing one, needs permission to edit them.
Reading a model two steps away
Aggregation, Occurrence, List and List of objects can read a model that is two relationships from the parent — for example products (via orders) for “favourite product category”. Pick it from Source model. Columns of the model in between appear as orders › created_at, so you can filter or order by them.
A model is offered two steps away only when:
- the second relationship is defined from the model in between (orders → products, not products → orders);
- it is not a many-to-many relationship;
- its keys are plain columns, or an array of values on the model in between (such as
orders.product_ids).
Such a source reads one row per order and product, so each order repeats once for every product it holds. Where that would change the answer, the columns of the model in between are not offered:
- Sum and Average — an order of three products would be added three times;
- Most frequent and Least frequent in Occurrence;
- List, unless Each value once is ticked;
- List of objects made only of their columns — include at least one column of the model read.
To use them that way, pick the model in between (orders) as the source. Count, Min, Max, First and Last are not affected.
Two-step models are not offered in the Advanced: SQL picker.
Good to know
- Sum and Average offer only number columns.
- A Count is
0for a customer with no related records, so an audience on “order count = 0” finds them. Sum, Average, Min and Max stay empty. - A List applies its Limit and Sort — “the last 3 orders” returns the three most recent. Tick Each value once to get, say, the last 3 different categories. On Redshift, which has no array type, a List with a Limit or Sort column is refused.
- Renaming an attribute updates the formulas that use it. Merge tags and store feeds that refer to it by name are not updated — change those by hand.
- Continue stays disabled until the definition is complete. For Formula and Advanced: SQL, that only means the expression is not empty — use Preview values to check it runs.