> ## Documentation Index
> Fetch the complete documentation index at: https://docs.orbit.devotel.io/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL traits: author traits in warehouse SQL

> Define a computed trait as a read-only SQL SELECT against your own BigQuery, Snowflake, or Redshift — Orbit runs it on your schedule and materializes each row's value into contacts.traits.

# 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](/guides/computed-traits) 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](/guides/computed-traits) | 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](/guides/ai-decisioning)            | 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:

1. **Is the data you need already in Orbit** (contact facts, custom fields)? Author it as a DSL predicate — cheapest to run, easiest to preview.
2. **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.
3. **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](/guides/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](/guides/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:

```sql theme={null}
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:

```sql theme={null}
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.

```bash theme={null}
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:

```bash theme={null}
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:

1. Re-reads the definition and re-validates it (a hand-edited body that fails validation is marked failed, never executed).
2. Runs your `SELECT` against the warehouse over a read-only connection, capped at 50,000 rows per run.
3. 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.
4. 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.
5. 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:

```bash theme={null}
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](/guides/warehouse-native-segments):

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