Zero-copy warehouse segment
Define audiences with the tables in your BigQuery, Snowflake or Databricks warehouse without copying customer data into Pushizo. The SELECT query you write runs in your warehouse; only the ID column you choose…
Define audiences with the tables in your BigQuery, Snowflake or Databricks warehouse without copying customer data into
Pushizo. The SELECT query you write runs in your warehouse; only the ID column you choose reaches Pushizo, and those
IDs are matched to your people and written as static segment membership. The segment is refreshed on a schedule or
manually and is used in campaign, journey and in-app audiences like any other segment. Available on Growth and above
(plan feature warehouse_segments, ADR-0091).
How it works
- In the dashboard, add a warehouse connection on Segments → Warehouse query (credentials are stored encrypted).
- Enter the query, the ID column and the cost cap; Estimate cost checks the query without running it.
- When the segment is saved, the first refresh runs in the background (worker). The query is wrapped in an outer query:
SELECT DISTINCT CAST(hs_src.`external_id` AS STRING) AS hs_identity
FROM (<your query>) AS hs_src
WHERE hs_src.`external_id` IS NOT NULL
LIMIT <max people + 1>Even if your query returns other columns, only hs_identity leaves the warehouse. IDs are matched only to this app's
people (external_id or user_id); a merged person resolves to the target person and unmatched IDs are skipped.
Membership is replaced in a single transaction: if a refresh fails, the previous membership is kept.
Connections
| Warehouse | Settings | Credential | API |
|---|---|---|---|
| BigQuery | project_id, optional location | Service account JSON (service_account_json) | jobs.query + getQueryResults, OAuth2 with a service account JWT |
| Snowflake | account, user, warehouse, optional database, schema, role | Key pair private key (private_key, unencrypted PKCS#8 PEM) | SQL API v2, key pair JWT |
| Databricks | host, warehouse_id, optional catalog, schema | Personal access token (access_token) | SQL Statement Execution API |
- Credentials are stored with envelope encryption and never appear in responses or logs; only a hint (service account email or masked token) is shown.
- Grant the warehouse account read-only access. Pushizo also validates the query, but the real safeguard is the permission in your warehouse.
- Test connection: a free dry run on BigQuery,
SELECT CURRENT_VERSION()on Snowflake,SELECT 1on Databricks. - At most 10 connections per app. A connection used by segments cannot be deleted.
Query rules
- A single statement starting with
SELECTorWITH; one trailing;is accepted. - Comments (
--,/* */,#), dollar-quoted text, multiple statements and write/session commands (INSERT,UPDATE,DELETE,MERGE,CREATE,DROP,COPY,CALL,EXECUTE,INTO…) are rejected. SnowflakeSYSTEM$and Databricksreflect/java_methodfunctions cannot be used. Text inside quotes does not affect the check. - The ID column is a column name in the query result (starts with a letter or underscore, at most 128 characters). Snowflake upper-cases unquoted names; write the column unquoted in your query.
- Rejected queries are also written to the query log (
status = rejected,reason).
Cost cap
| Limit | Default | Range | Enforcement |
|---|---|---|---|
| Timeout | 120 s | 10–600 s | BigQuery: jobs.cancel when time runs out; Snowflake: timeout (STATEMENT_TIMEOUT_IN_SECONDS); Databricks: cancel when time runs out |
| Max people | 1,000,000 | 1–1,000,000 | The outer query's LIMIT; if exceeded the refresh fails and membership is not truncated |
| BigQuery billed bytes | 10 GiB | 10 MiB–10 TiB | maximumBytesBilled: a job over the cap never runs (cost_limit_exceeded) |
| Snowflake warehouse size | none | X-Small … 6X-Large | Checked first with SHOW WAREHOUSES; if larger, the query does not run (warehouse_too_large) |
| Databricks row limit | max people + 1 | — | Statement Execution API row_limit |
Preview
- Estimate cost (
dry_run): the query does not run. The BigQuery dry-run estimate (free) is returned in bytes and compared with the cap; on Snowflake,EXPLAIN USING JSONgives the assigned bytes plus the warehouse size check; on Databricks,EXPLAINcompiles the query (no byte estimate). - Fetch sample (
sample): the cost is estimated first; if it is within the cap, the query runs with at most 20 IDs and the number matching your people is shown. On BigQueryLIMITdoes not reduce the bytes scanned; the sample query may be billed.
Refresh
- Interval: manual, or every 1, 6, 12 or 24 hours. The worker scheduler queues due segments every minute.
- A manual refresh and any change to the query, connection, ID column or ID type queue a refresh (response
202). While a request is pending no second job is added; a pending request older than 30 minutes can be requested again. - The refresh job is not retried (every attempt is a billed query); the error code is written to the segment's
last_errorand details to the query log. Segments of orgs without the plan feature are skipped by the scheduler. - Segment state (
refresh_state):queued,ok,failed,never.
| Error code | Meaning |
|---|---|
cost_limit_exceeded | The BigQuery maximumBytesBilled cap was exceeded |
warehouse_too_large | The Snowflake warehouse is larger than allowed |
timeout | Timed out; the statement/job was cancelled |
too_many_members | The max people limit was exceeded |
auth_failed, permission_denied | Credential or permission problem |
rejected | The warehouse rejected the query (e.g. missing column or table) |
connection_missing, plan_feature_disabled | No connection, or the plan feature is off |
API
Dashboard endpoints live under /internal/v1/apps/{app}/warehouse-segments/... and public endpoints under
/v1/apps/{app}/warehouse-segments/.... In the public API reading requires segments:read; connections, preview and
writes require the admin scope.
| Method | Path | Description |
|---|---|---|
GET | /options | Warehouses, ID types, Snowflake sizes, refresh intervals, limits |
GET, POST | /connections | List and create connections |
GET, PATCH, DELETE | /connections/{connectionId} | Connection details, update, delete |
POST | /connections/{connectionId}/test | Test a connection |
POST | /preview | dry_run or sample preview |
GET, POST | /segments | List and create warehouse segments |
PATCH | /segments/{segmentId} | Name, query, connection, ID column, limits, refresh interval |
POST | /segments/{segmentId}/refresh | Queue a refresh |
GET | /runs | Warehouse query log |
curl -X POST https://api.pushizo.com/v1/apps/$APP_ID/warehouse-segments/segments \
-H "Authorization: Bearer $PUSHIZO_API_KEY" -H "Content-Type: application/json" \
-d '{"name":"High-value customers","connection_id":"whc_...","query":"select external_id from analytics.customers where lifetime_value > 500","id_column":"external_id","id_type":"external_id","refresh_interval_minutes":1440,"limits":{"timeout_seconds":300,"max_bytes_billed":5368709120}}'Node SDK: pushizo.warehouseSegments.createConnection(...), .preview(...), .createSegment(...),
.refreshSegment(id), .runs(...).
Rule endpoints (PATCH /segments/{id} definition/type, POST /segments/{id}/refresh) return HS_BAD_REQUEST
(details.code = warehouse_segment) for a warehouse segment; name, description and delete are shared.
Local development and verification
With PROVIDER_MODE=mock warehouse requests never leave the machine: the mock driver returns the single-quoted strings
in your query as ID rows (... where external_id in ('u-1', 'u-2')), and the BigQuery dry-run estimate is 20 MiB.
Verification with real accounts (BigQuery service account, Snowflake key pair, Databricks token) must be done in your
environment.
SQL query and SQL segment
Define your audience with SQL when the rule builder is not enough: run a read-only SELECT query on the permitted views, see the result in a table and save the people in the result as a segment. A SQL segment is…
Companies (B2B accounts)
In B2B products, people belong to a company (an account or workspace): a billing reminder goes to the company's admin and a feature announcement to all of its users. In Pushizo a company is a separate object…