Skip to main content

​
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 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: 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:
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:
Response (201) carries the definition minus the credential, with run-status fields null until the first refresh:
  • 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 names) 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 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.