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

# Warehouse-native segments (zero-copy audiences)

> Author a read-only SQL SELECT that Orbit pushes down into your own BigQuery, Snowflake, or Redshift — only the member count ever crosses back, so PII stays resident in your warehouse.

# Warehouse-native segments (zero-copy audiences)

If a compliance rule, a data-residency posture, or plain warehouse discipline keeps your customer PII out of third-party stores, building an audience used to mean duplicating the membership data somewhere else anyway. Warehouse-native segments remove that trade-off: the membership SQL lives in **your** warehouse, Orbit executes it push-down, and the only thing that comes back is a number — the segment's member count. The matched rows never leave BigQuery, Snowflake, or Redshift.

Use this surface when the audience definition already exists as warehouse SQL (a dbt model, a purchases view, a finance-owned table) and you want Orbit to read the *size* without ingesting the *people*. When the membership rows themselves need to land in Orbit for activation, use the [in-Orbit CDP segments DSL](/guides/cdp-segments) instead — the comparison is in section 7.

## 1. The 30-second conceptual map

1. You author one read-only `SELECT` that projects a `user_id` membership column — one row per matched profile, in your warehouse's own dialect.
2. You POST it to Orbit with an encrypted connection credential.
3. On each refresh, Orbit wraps your body in `SELECT count(*) FROM (<your body>)` and runs it **inside your warehouse** over a read-only connection.
4. Your warehouse returns a single aggregate — the member count. That is the *only* value that crosses the boundary. Member rows are never fetched, never stored, never logged in Orbit.
5. Orbit records the count plus run status (`member_count`, `last_run_at`, `last_run_status`, `last_error`).

The privacy consequence: a warehouse-native segment answers "how many customers match" — and nothing else — so it is compatible with the strictest data-residency posture. Your DBA keeps full custody of the data; Orbit sees an integer.

## 2. Author the membership SELECT

The body must be a **single read-only statement**: it has to start with `SELECT` (or `WITH … SELECT`), and it must project the membership column `user_id`. The API rejects the rest at the edge, before anything is stored:

| Rejected                                                                          | Why                                                                        |
| --------------------------------------------------------------------------------- | -------------------------------------------------------------------------- |
| `SELECT * FROM …`                                                                 | No projected `user_id` membership column — the body must address a profile |
| `INSERT` / `UPDATE` / `DELETE` / `DROP` / `CREATE` / `MERGE` / `COPY` / `GRANT` … | DML/DDL keywords — membership 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               |

Check a body with the validate endpoint **before** persisting it — this is what the rule-builder preview in the dashboard calls, and it costs nothing:

```bash theme={null}
curl -X POST https://api.orbit.devotel.io/api/v1/cdp/warehouse-segments/validate \
  -H "X-API-Key: dv_live_sk_your_key_here" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT user_id FROM purchases"}'
```

The response is `{ "valid": true }` or `{ "valid": false, "reason": "…" }` with the exact rejection reason from the table above. Fix the body here, then save it with confidence.

## 3. Store the credential once — least-privilege

The connection blob is a JSON document encrypted at rest the moment you POST it; **no read path ever returns it** — GET responses carry only a `connection_configured: true` presence flag. Grant the connector identity SELECT-only access on exactly the tables/view the query touches:

* **BigQuery** — a Google service-account JSON (`type: "service_account"`). Grant it `bigquery.readonly`-scoped access (the connector mints an OAuth token for the query API with exactly that scope). The blob may be the raw SA JSON, or a wrapper `{ "project_id": "…", "service_account_json": { … } }` — `project_id` on the wrapper overrides the SA's own.
* **Snowflake** — key-pair auth: `{ "account": "xy12345.us-east-1", "username": "ORBIT_READER", "private_key": "-----BEGIN PRIVATE KEY-----\n…" }`. Create a dedicated user whose role holds `USAGE` on the warehouse/database/schema and `SELECT` on the membership tables — nothing more.
* **Redshift** — Postgres-wire: `{ "host": "…", "database": "…", "username": "…", "password": "…", "port": 5439 }`. Create a login restricted to `SELECT` on the referenced tables.

Because the executor additionally re-validates the body read-time and rejects destructive keywords at the API edge, the read-only warehouse role is defence-in-depth — but it is the authoritative boundary, so keep it minimal.

## 4. Create, poll, patch

Create the definition:

```bash theme={null}
curl -X POST https://api.orbit.devotel.io/api/v1/cdp/warehouse-segments \
  -H "X-API-Key: dv_live_sk_your_key_here" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "buyers_last_90d",
    "description": "Purchasers in the trailing 90 days (BigQuery).",
    "warehouse": "bigquery",
    "sql": "SELECT user_id FROM purchases.analytics WHERE purchase_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)",
    "connection": "{ \"type\": \"service_account\", \"project_id\": \"…\", \"client_email\": \"…\", \"private_key\": \"…\" }",
    "schedule_minutes": 1440
  }'
```

Response (201) carries the definition minus the credential, with run-status fields null until the first refresh:

```json theme={null}
{
  "id": "whseg_4b1f…",
  "name": "buyers_last_90d",
  "warehouse": "bigquery",
  "connection_configured": true,
  "schedule_minutes": 1440,
  "enabled": true,
  "member_count": null,
  "last_run_at": null,
  "last_run_status": null
}
```

* `GET /api/v1/cdp/warehouse-segments` lists all definitions (newest first), each with `member_count`, `last_run_at`, `last_run_status`, `last_error`.
* `GET /api/v1/cdp/warehouse-segments/:id` reads one.
* `PATCH /api/v1/cdp/warehouse-segments/:id` takes a partial body (`sql`, `connection`, `schedule_minutes`, `enabled`, `name`, `description`). The scheduler-owned run fields (`member_count`, `last_run_*`, `last_error*`) are never writable through the API — an operator cannot forge a green run to mask a broken query.
* `DELETE /api/v1/cdp/warehouse-segments/:id` removes the definition; refresh stops with it.

## 5. Refresh semantics

A scheduler evaluates each **enabled** definition against your warehouse and stores only the aggregate:

* **Cadence gate** — the tick runs every 15 minutes; a definition is *due* when `enabled && (last_run_at is null OR now − last_run_at ≥ schedule_minutes)`. Default `schedule_minutes` is 1440, the natural nightly cadence; set 60 for hourly or 10080 for weekly.
* **Zero-copy wrap** — the body is wrapped as `SELECT count(*) AS member_count FROM (<body>) AS orbit_segment` and returns at most one row.
* **Run status** — success writes `member_count` + `last_run_status: "ok"`; failure writes `last_run_status: "failed"` and a sanitized `last_error` code (driver strings are never echoed back, so no DSN leaks into the dashboard or an audit channel).
* **Defense in depth** — a stored definition is re-validated before every run (single SELECT, no comments, no chaining), so a hand-edited settings row cannot smuggle DML past the API edge; it is skipped-with-error instead.

## 6. Worked example — BigQuery buyers in the last 90 days

End to end, from an untested body to a production segment:

1. **Dry-run the body.** POST the candidate SQL to the `/validate` endpoint until `valid: true`.
2. **Save it.** POST `/api/v1/cdp/warehouse-segments` with the BigQuery service-account JSON (raw SA JSON or the `{ project_id, service_account_json }` wrapper) from a sandbox/dedicated connection.
3. **Poll.** `GET /api/v1/cdp/warehouse-segments/:id` — when `last_run_status` flips to `ok`, read `member_count` and compare it against a manual `SELECT count(*)` run directly in BigQuery. If it agrees, the definition is production-ready.
4. **Production.** Keep the same body against your production connection; store separate definitions (distinct `name`s) if you want sandbox and production side-by-side — a name clash returns a 409.

## 7. When to use the in-Orbit segments DSL instead

* **Membership rows must activate** — if downstream flows (journeys, campaigns, exports) need the *people*, not the count, build the audience with the [in-Orbit CDP segments DSL](/guides/cdp-segments) and ingest the source data through the CDP.
* **Warehouse-native is right** — when the definition already lives in the warehouse (dbt model, finance table), when residency rules bar copying PII, or when a nightly size-figure is all the activation layer needs.
* The two surfaces coexist: warehouse-native segments report a size; the DSL reports members. Pick per audience.

## 8. Common pitfalls

* **`SELECT *` rejected** — the body must project `user_id` explicitly. Re-shape it as `SELECT user_id FROM …` and re-validate.
* **Blocked credential** — a 400 `INVALID_CONNECTION` means the blob was not a JSON object; a failing run with a sanitized `last_error` usually means the warehouse role lacks SELECT on the touched table. Grant least-privilege correctly, then PATCH the `connection`.
* **Zero members from timezone drift** — `CURRENT_TIMESTAMP()` / `CURRENT_DATE` boundaries in your warehouse's session timezone can silently slide a 90-day window. Anchor boundaries to explicit UTC offsets (e.g. `TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)` in BigQuery, `DATEADD(day, -90, CURRENT_TIMESTAMP())` in Redshift) so DST and session-timezone settings cannot shrink the window to zero.
* **Duplicate name** — `name` is the stable operator handle and must be unique; a clash returns 409. Rename or PATCH the existing definition.
* **Update swallowed the SQL rule** — PATCH re-validates `sql` whenever it is present in the body, so a broken edit cannot overwrite a working definition.

## Related guides

* [CDP segments, computed traits, and activation](/guides/cdp-segments) — the in-Orbit DSL for when member rows must land in Orbit.
* [CDP reverse ETL to Snowflake and BigQuery](/guides/reverse-etl-warehouse-exports) — the opposite direction: pushing CDP profiles and events out to your warehouse.
* [API reference — CDP endpoints](/api-reference/endpoints/cdp) — generated per-operation shapes for `/api/v1/cdp/warehouse-segments`.