SQL traits

A SQL trait is a computed trait authored as warehouse SQL instead of the JSON predicate DSL. You store one read-only SELECT that returns (user_id, trait_value) rows against your own BigQuery, Snowflake, or Redshift; Orbit runs it on the schedule you set and stamps each row’s value into the matching contact’s traits map under your trait name. The value then behaves like any other attribute: segmentable, personalizable, exportable. This surface is for SQL-forward and dbt-forward teams — the same people who already model everything else in the warehouse. If that is not you, the computed traits DSL covers the same outcome without SQL.

1. Choose between the four trait engines

Orbit now carries four ways to derive a trait. Pick deliberately:

Engine	Author with	Runs against	Best when
Computed traits DSL	a deterministic JSON predicate	contact facts already in Orbit	The rule is a comparison over recorded facts — “LTV over 500 and seen this week”. No LLM, reproducible previews.
AI traits	a fixed score schema filled by an LLM	unstructured signals	The signal cannot be written as a predicate — churn narrative, next-best-action inference.
Tag autopilot	free-form language, LLM-proposed tags	unstructured signals	Discovery — exploring candidate tags, not a targeting primitive.
SQL traits (this page)	a warehouse `SELECT`	your BigQuery / Snowflake / Redshift	The trait already exists as warehouse SQL — a dbt model, a finance-owned LTV view — and you want Orbit to adopt it rather than rebuild it in the DSL.

A concrete decision tree:

Is the data you need already in Orbit (contact facts, custom fields)? Author it as a DSL predicate — cheapest to run, easiest to preview.
Is the source a warehouse table your analytics team already maintains? Author it as a SQL trait — Orbit reuses the model where it lives instead of a hand-ported copy.
Does the trait need model judgment, not data? Use an LLM surface (AI traits or the autopilot).

SQL traits also fit when the audience itself is warehouse-native: pair them with warehouse-native segments so both the trait values and the segment membership are defined in the same place.

2. Prerequisites

A reachable warehouse. The trait runs against BigQuery, Snowflake, or Redshift through the same shared read path the reverse ETL warehouse exports use. You supply the connection credential per definition (section 4), encrypted at rest.
An operator role. Creating, updating, and deleting definitions requires owner, admin, or developer. Reads accept the broader analyst and marketer set.
A scalar query. Each definition returns exactly two columns: user_id (the identity the value stitches to — it matches the contact’s external id) and trait_value (the value written into traits[<name>]). One row per profile.

3. Author the SELECT

The body must be a single read-only statement. The API rejects the rest at the edge, before anything is stored:

Rejected	Why
`INSERT` / `UPDATE` / `DELETE` / `DROP` / `CREATE` / `MERGE` / `COPY` / `GRANT` …	DML/DDL keywords — trait SQL is read-only
Two statements chained with `;`	Only a single statement is allowed (one trailing terminator is tolerated)
`--` or `/* … */` comments	A classic way to smuggle a second clause past a keyword scan
A query that omits `user_id` or `trait_value`	The executor reads exactly these two columns; without `user_id` no profile can be addressed

Example — BigQuery, an LTV tier

Bucket customers into a tier from a purchases view your analytics team already owns:

WITH ltv AS (
  SELECT user_id, SUM(amount_usd) AS total
  FROM analytics.orders
  WHERE ordered_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY user_id
)
SELECT user_id,
  CASE
    WHEN total >= 1000 THEN 'gold'
    WHEN total >= 250 THEN 'silver'
    ELSE 'bronze'
  END AS trait_value
FROM ltv

Example — Redshift, computed read-only

The same shape over the Postgres wire. The executor runs Redshift reads inside a READ ONLY transaction, so a side-effecting body cannot execute even if it were stored:

SELECT user_id,
  CASE
    WHEN SUM(o.grand_total) >= 50000 THEN 'tier_enterprise'
    WHEN SUM(o.grand_total) >= 5000 THEN 'tier_business'
    ELSE 'tier_starter'
  END AS trait_value
FROM dbt_marts.customer_orders o
GROUP BY user_id

Keep the values compact and stable — the string you return is the segment key everywhere downstream.

4. Create a definition

POST the body, a name, the warehouse kind, and the connection credential. The name becomes the traits[<name>] key, so it must be a lowercase identifier (starts with a letter; letters, digits, underscores after). Duplicate names come back 409.

curl -X POST https://api.orbit.devotel.io/api/v1/cdp/sql-traits \
  -H "X-API-Key: dv_live_sk_your_key_here" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "ltv_tier",
    "description": "Annual LTV bucketed gold/silver/bronze",
    "warehouse": "bigquery",
    "sql": "WITH ltv AS (SELECT user_id, SUM(amount_usd) AS total FROM analytics.orders WHERE ordered_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY) GROUP BY user_id) SELECT user_id, CASE WHEN total >= 1000 THEN '"'"'gold'"'"' WHEN total >= 250 THEN '"'"'silver'"'"' ELSE '"'"'bronze'"'"' END AS trait_value FROM ltv",
    "connection": "{\"type\":\"service_account\",\"project_id\":\"my-project\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\n...\",\"client_email\":\"orbit-reader@my-project.iam.gserviceaccount.com\"}",
    "schedule_minutes": 1440,
    "enabled": true
  }'

Field by field:

name (required) — the trait key. Stamped as traits.ltv_tier on each matched contact.
warehouse (required) — bigquery, snowflake, or redshift.
sql (required) — the read-only body, up to 20,000 characters.
connection (required) — a JSON credential blob, encrypted at rest the moment it lands; no read path ever returns it (see section 7 for rotation).
schedule_minutes (optional, default 1440) — how often the runner re-evaluates. Set the daily default unless the trait genuinely needs a tighter loop.
enabled (optional, default true) — a disabled definition persists but no longer evaluates; values it already stamped stay in place.

A 400 with code INVALID_SQL means the body failed the read-only guard — the rejection reason names the exact keyword or missing column. A 400 INVALID_CONNECTION means the credential blob is not a JSON object.

5. Prevalidate before you persist

Rule-builder previews and your own editor both go through the validate endpoint. It applies the same guard as create, writes nothing, and costs nothing:

curl -X POST https://api.orbit.devotel.io/api/v1/cdp/sql-traits/validate \
  -H "X-API-Key: dv_live_sk_your_key_here" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT user_id, CASE WHEN total >= 1000 THEN '"'"'gold'"'"' ELSE '"'"'bronze'"'"' END AS trait_value FROM ltv"}'

The response is { "valid": true } or { "valid": false, "reason": "…" }. Iterate here until the body passes; then POST it with confidence. Validation is deliberately conservative — it is an edge guard, and the executor is the authoritative boundary (read-only warehouse role, statement timeout) — so a body that passes validation can still fail at run time (a missing table, a revoked grant). Watch the run-status fields after the first cycle.

6. Materialization cadence

Nothing computes at create time. A scheduled runner sweeps every enabled definition on a 15-minute outer tick and evaluates the ones that are due — never run yet, or whose last run is at least schedule_minutes old. For each due definition it:

Re-reads the definition and re-validates it (a hand-edited body that fails validation is marked failed, never executed).
Runs your SELECT against the warehouse over a read-only connection, capped at 50,000 rows per run.
Stitches each row to a contact: user_id is matched against the contact’s external id, and last row wins when a user_id appears more than once.
Writes trait_value into traits[<name>] with a JSON merge — every sibling trait already on the contact is preserved. Only existing, non-deleted contacts are updated; the runner never creates orphan contacts.
Records the outcome on the definition — last_run_at, last_run_status, last_run_rows, and a sanitized last_error on failure. Those fields are operator-read-only: an operator cannot forge a green run to mask a broken query.

Because the run is a full recompute (not a delta), a contact whose value changed picks up the new value on the next cycle, and a row that disappears from your query result simply stops being rewritten — the previous value remains until your warehouse stops returning the old rows.

7. Rotate the credential per definition

Each definition carries its own connection blob. To rotate, PATCH the definition with the fresh credential — a dedicated, SELECT-only warehouse identity per definition limits blast radius:

curl -X PATCH https://api.orbit.devotel.io/api/v1/cdp/sql-traits/sqltrait_9f2c1d4a7b3e9f2c1d4a7b3e9f2c1d4a7b3e \
  -H "X-API-Key: dv_live_sk_your_key_here" \
  -H "Content-Type: application/json" \
  -d '{"connection": "{\"type\":\"service_account\",\"project_id\":\"my-project\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\n...\",\"client_email\":\"orbit-reader@my-project.iam.gserviceaccount.com\"}"}'

You can rotate only the credential — every other field is independently optional, and scheduler-owned run-status fields are never writable through the API. Recommended shapes by kind, same as the warehouse-native segments guide:

BigQuery — a service-account JSON scoped to bigquery.readonly.
Snowflake — key-pair auth: { "account": "…", "username": "…", "private_key": "…" } for a dedicated user with SELECT only.
Redshift — Postgres-wire: { "host": "…", "database": "…", "username": "…", "password": "…", "port": 5439 } for a login restricted to SELECT.

Because the credential is never returned by any read path, rotation is the only way to change it — there is no “reveal” to be careful with.

8. Production checklist

Before you leave a SQL trait running unattended:

The query is read-only in spirit, not just keyword-clean. Prefer a dedicated reporting view or dbt model over raw operational tables — it keeps the trait stable when schema underneath shifts.
user_id is the right identity. It must match the value the contact carries as its external id; join through your identity mapping in the warehouse if the raw table carries a different key.
The credential identity is SELECT-only and scoped to exactly the tables/view the query touches.
Validate first, then create. Run the body through POST /cdp/sql-traits/validate, then persist it.
Check the first run. After one cycle, GET the definition and confirm last_run_status: "ok" and a plausible last_run_rows. A failed run’s last_error is sanitized but specific enough to act on (missing table, revoked grant, timeout).
Size the schedule. The default daily loop fits most traits; set schedule_minutes lower only when downstream journeys genuinely act on the fresher value.
Segment on the stamped value. traits.ltv_tier equals gold behaves exactly like a DSL-stamped tag — filter on it in segments, branch on it in journeys, export it with the profile.

Once materialized, a SQL trait is indistinguishable from any other contact attribute — the only difference is where the rule lives, and that difference is exactly the point: your warehouse stays the source of truth for the model, and Orbit adopts it rather than copies it.

WhatsApp

Guides

Messaging Controls

Audience & Support Tools

SQL traits: author traits in warehouse SQL

SQL traits

1. Choose between the four trait engines

2. Prerequisites

3. Author the SELECT

Example — BigQuery, an LTV tier

Example — Redshift, computed read-only

4. Create a definition

5. Prevalidate before you persist

6. Materialization cadence

7. Rotate the credential per definition

8. Production checklist

​SQL traits

​1. Choose between the four trait engines

​2. Prerequisites

​3. Author the SELECT

​Example — BigQuery, an LTV tier

​Example — Redshift, computed read-only

​4. Create a definition

​5. Prevalidate before you persist

​6. Materialization cadence

​7. Rotate the credential per definition

​8. Production checklist

SQL traits

1. Choose between the four trait engines

2. Prerequisites

3. Author the SELECT

Example — BigQuery, an LTV tier

Example — Redshift, computed read-only

4. Create a definition

5. Prevalidate before you persist

6. Materialization cadence

7. Rotate the credential per definition

8. Production checklist