PushizoDocs
Users & segments

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

  1. In the dashboard, add a warehouse connection on Segments → Warehouse query (credentials are stored encrypted).
  2. Enter the query, the ID column and the cost cap; Estimate cost checks the query without running it.
  3. 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

WarehouseSettingsCredentialAPI
BigQueryproject_id, optional locationService account JSON (service_account_json)jobs.query + getQueryResults, OAuth2 with a service account JWT
Snowflakeaccount, user, warehouse, optional database, schema, roleKey pair private key (private_key, unencrypted PKCS#8 PEM)SQL API v2, key pair JWT
Databrickshost, warehouse_id, optional catalog, schemaPersonal 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 1 on Databricks.
  • At most 10 connections per app. A connection used by segments cannot be deleted.

Query rules

  • A single statement starting with SELECT or WITH; 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. Snowflake SYSTEM$ and Databricks reflect/java_method functions 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

LimitDefaultRangeEnforcement
Timeout120 s10–600 sBigQuery: jobs.cancel when time runs out; Snowflake: timeout (STATEMENT_TIMEOUT_IN_SECONDS); Databricks: cancel when time runs out
Max people1,000,0001–1,000,000The outer query's LIMIT; if exceeded the refresh fails and membership is not truncated
BigQuery billed bytes10 GiB10 MiB–10 TiBmaximumBytesBilled: a job over the cap never runs (cost_limit_exceeded)
Snowflake warehouse sizenoneX-Small … 6X-LargeChecked first with SHOW WAREHOUSES; if larger, the query does not run (warehouse_too_large)
Databricks row limitmax 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 JSON gives the assigned bytes plus the warehouse size check; on Databricks, EXPLAIN compiles 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 BigQuery LIMIT does 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_error and 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 codeMeaning
cost_limit_exceededThe BigQuery maximumBytesBilled cap was exceeded
warehouse_too_largeThe Snowflake warehouse is larger than allowed
timeoutTimed out; the statement/job was cancelled
too_many_membersThe max people limit was exceeded
auth_failed, permission_deniedCredential or permission problem
rejectedThe warehouse rejected the query (e.g. missing column or table)
connection_missing, plan_feature_disabledNo 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.

MethodPathDescription
GET/optionsWarehouses, ID types, Snowflake sizes, refresh intervals, limits
GET, POST/connectionsList and create connections
GET, PATCH, DELETE/connections/{connectionId}Connection details, update, delete
POST/connections/{connectionId}/testTest a connection
POST/previewdry_run or sample preview
GET, POST/segmentsList and create warehouse segments
PATCH/segments/{segmentId}Name, query, connection, ID column, limits, refresh interval
POST/segments/{segmentId}/refreshQueue a refresh
GET/runsWarehouse 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.

On this page