Data warehouse export
Pushizo regularly exports delivery, event, conversion, user and subscription data to your own data warehouse: BigQuery, Snowflake, Databricks, Redshift or S3 (Parquet). The first run exports all historical data,…
Pushizo regularly exports delivery, event, conversion, user and subscription data to your own data warehouse:
BigQuery, Snowflake, Databricks, Redshift or S3 (Parquet). The first run exports all historical data; later runs export only new and
changed rows (docs/adr/0030-event-streams-ve-veri-ambari.md, docs/adr/0049-depo-aktarimi.md).
The reverse direction is set up from the same page: data in database, data warehouse, file and streaming sources is written to user properties or
custom events (/v1/apps/{appId}/reverse-etl). Details: Importing from a warehouse.
Management: dashboard Developer → Data warehouse (organization owner/admin) or the public API
/v1/apps/{appId}/warehouse-exports (admin-scoped API key).
Tables
| Table | Type | Cursor | Note |
|---|---|---|---|
deliveries | incremental | updated_at | A new row every time the status changes; for the current status, take the largest updated_at per id |
delivery_events | incremental | occurred_at | sent, delivered, opened, clicked … |
events | incremental | received_at | Custom events; occurred_at is the client time |
conversions | incremental | attributed_at | Conversions and attribution |
users | snapshot | — | Once a day (on the scheduled run) |
subscriptions | snapshot | — | Device tokens and precise location are not exported |
Extra columns on every row: _run_id (run ID) and _exported_at (export time, UTC). In snapshot
tables, select the most recent snapshot with max(_exported_at). Read the column list and schema versions with
GET /v1/apps/{appId}/warehouse-exports/tables.
-- BigQuery: current status of deliveries
SELECT * EXCEPT(rn) FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
FROM `proje.pushizo.deliveries`
) WHERE rn = 1;When the schema changes, columns are only added (at the end, nullable); the destination table is updated on the next run.
Scheduling and reliability
- Every 15 minutes (
every_15_minutes): at minutes 00, 15, 30 and 45 of the hour; hourly: 5 minutes past every hour; daily: 00:15 UTC. You can trigger a run manually with "Run now" (POST …/warehouse-exports/{id}/run); if a run is pending or in progress, or the export is paused, it returns409. - Data is read up to 5 minutes before the run starts (so late-committing transactions are not missed; the rest arrives on the next run).
- Transient errors (network, timeout, 429, 5xx) are retried at 1, 2 and 4 minute intervals. An interrupted run resumes from the last written chunk and does not produce duplicate rows (idempotent writes per chunk).
- After 3 consecutive failed runs, an email is sent to the organization owners/admins and an audit log entry
(
warehouse_export.failing) is created. Because the last successful cursor is preserved, no data is lost once the problem is fixed. - Very large initial exports are split into chunks: after 20 minutes the run saves its progress and finishes partially, and the rest starts automatically within minutes.
BigQuery
- Create a service account in Google Cloud; grant it the BigQuery Data Editor and BigQuery Job User roles on the project.
- Create a JSON key for the service account and paste its contents into the field in the wizard.
- Enter the project ID, dataset (created if it doesn't exist) and location (
EU,US,europe-west3…).
Loading uses NDJSON load jobs (no streaming charges). Tables are partitioned daily on _exported_at; JSON
fields are of type JSON.
curl -X POST "$API/v1/apps/$APP_ID/warehouse-exports" -H "Authorization: Bearer $HS_KEY" -H 'content-type: application/json' -d '{
"name": "BigQuery daily",
"destination": "bigquery",
"config": { "project_id": "my-project", "dataset": "pushizo", "location": "EU" },
"credentials": { "service_account_json": "<service account JSON contents, as text>" },
"tables": ["deliveries", "events", "conversions"],
"schedule": "daily"
}'Snowflake
Prerequisite: Pushizo does not create databases, schemas or warehouses (it only creates tables and adds columns). The target database, schema and warehouse must exist in Snowflake before you use the wizard:
CREATE DATABASE IF NOT EXISTS ANALYTICS;
CREATE SCHEMA IF NOT EXISTS ANALYTICS.PUSHIZO;
CREATE WAREHOUSE IF NOT EXISTS LOAD_WH WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;If they don't exist, the connection test and runs fail permanently with a permission_denied error (object does not exist or
no permission).
- Create a key pair (unencrypted PKCS#8):
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub - Assign the public key to the user and grant permissions:
ALTER USER HS_LOADER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh…'; GRANT USAGE ON WAREHOUSE LOAD_WH TO ROLE HS_LOADER_ROLE; GRANT USAGE ON DATABASE ANALYTICS TO ROLE HS_LOADER_ROLE; GRANT USAGE, CREATE TABLE ON SCHEMA ANALYTICS.PUSHIZO TO ROLE HS_LOADER_ROLE; - In the wizard, enter the account identifier (
myorg-myaccountorxy12345.eu-central-1), user, database, schema, warehouse, optional role and the contents ofrsa_key.p8.
Tables are created with CREATE TABLE IF NOT EXISTS; JSON fields are VARIANT and timestamps are TIMESTAMP_TZ.
S3 (Parquet)
- Enter the bucket, region, key prefix and access key (only
s3:PutObject, pluss3:DeleteObjectfor the connection test, is needed). For compatible storage such as Cloudflare R2 or MinIO, you can specify a custom endpoint (internal network addresses are not accepted). - File layout (Hive partitioning,
dt= export day in UTC):<prefix>/table=events/dt=2026-10-15/part-<run>-0000.parquet - Files are GZIP-compressed Parquet; Athena, Spark, DuckDB, BigQuery external tables and pandas/pyarrow read them directly:
-- DuckDB SELECT count(*) FROM read_parquet('s3://hs-lake/pushizo/table=events/dt=*/*.parquet', hive_partitioning = true);
Importing from a warehouse
| Source | source | Preset (config.preset) | Credentials | Reading |
|---|---|---|---|---|
| Postgres | postgres | alloydb, cloud_sql, greenplum, materialize, motherduck | password | SELECT |
| MySQL | mysql | singlestore | password | SELECT |
| SQL Server | sqlserver | synapse | password (SQL authentication) | SELECT |
| ClickHouse | clickhouse | — | password | SELECT (HTTP, readonly=1) |
| Trino | trino | starburst_enterprise, starburst_galaxy | password | SELECT |
| Amazon Athena | athena | — | AWS key | SELECT |
| Snowflake | snowflake | — | private key | SELECT |
| BigQuery | bigquery | — | service account | SELECT |
| Elasticsearch | elasticsearch | — | API key or user + password | index + search_after |
| S3 | s3 | — | AWS key | CSV or NDJSON (up to 5 MB) |
| Google Sheets | google_sheets | — | service account | range |
| Apache Kafka | kafka | confluent | optional SASL | topic |
| Google Pub/Sub | pubsub | — | service account | pull subscription |
A preset is not a separate driver; it fills in defaults on top of the same protocol. For example, the Materialize port is 6875, and MotherDuck is its own Postgres endpoint (pg.<region>-aws.motherduck.com, user postgres, database md:, password is the access token). Microsoft Fabric is not available in this version.
Mapping. mapping.external_id_column is required. For the event destination, event_name or event_name_column is required; for query-based sources, cursor_column is also required. The cursor is compared as text, so choose a value with a fixed format (ISO date, zero-padded number). For query-based sources with the event destination, provide occurred_at_column: otherwise the event time is the read time, and a page that is interrupted and re-run can produce duplicate events.
Query. It must be a single SELECT; comments, a second statement and write commands are rejected. Pushizo wraps the query to add the cursor and limit, so do not put ORDER BY or LIMIT in your query. A separate database user with read-only permissions is recommended.
TLS. Connections must be encrypted with certificate verification. If the server certificate is not signed by a public CA, provide the CA as PEM in the config.ca_certificate field (Postgres, MySQL, SQL Server). When connecting to Cloud SQL and AlloyDB over a public IP, the certificate name won't match, so only the provided CA is verified.
Streaming sources
Kafka and Pub/Sub can be read in batches every 5 minutes (schedule: "every_5_minutes"); this option is rejected for other sources. At most 5,000 records are read per run; the rest is left for the next run. Delivery is at-least-once: because the source record's ID and publish time determine the event ID, re-reading does not produce duplicate events. For the user property destination, the same value is written again.
The message value must be a JSON object. Messages that aren't are skipped; Avro and Schema Registry are not supported. The external ID is a field in the message; use kafka_key for the Kafka message key. If occurred_at_column is not provided, the event time is the message's publish time.
Kafka. config.brokers (host:port), config.topic, config.start (earliest or latest; earliest if empty). Offsets are not committed to the cluster; they are stored per partition in Pushizo. You still need read permission on the topic and access to the pushizo-rtl-* consumer group. For Confluent Cloud, use preset: "confluent", mechanism: "plain", with the API key as the username and the API secret as the password. Unencrypted connections are rejected in production.
Pub/Sub. config.project_id, config.subscription (pull subscription). Grant the service account roles/pubsub.subscriber on the subscription. Messages are acknowledged after the rows are written. A connection test does not consume messages.
Security
- Credentials are stored with envelope encryption and never appear in API responses or audit logs; only a non-secret summary is shown (service account email, Snowflake key fingerprint, masked access key).
- To change credentials, send the
credentialsfield again when editing. - In local development with
PROVIDER_MODE=mock, BigQuery/Snowflake requests do not go out to the network (mock transport); S3 is a real client in every mode (RustFS locally).
Event Streams: webhook body template and event filter
Event Streams lets you transform the body of outgoing webhooks (docs/webhooks.md) into the format the receiver expects and receive only the events you care about (ADR-0030). In the dashboard: Developer → Webhooks → endpoint…
Exports and imports (CSV)
CSV export jobs (users, subscriptions, events, deliveries), signed download links, Pushizo and legacy provider CSV imports, error reports and retention periods.