PushizoDocs
Analytics & data

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

TableTypeCursorNote
deliveriesincrementalupdated_atA new row every time the status changes; for the current status, take the largest updated_at per id
delivery_eventsincrementaloccurred_atsent, delivered, opened, clicked …
eventsincrementalreceived_atCustom events; occurred_at is the client time
conversionsincrementalattributed_atConversions and attribution
userssnapshot—Once a day (on the scheduled run)
subscriptionssnapshot—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 returns 409.
  • 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

  1. Create a service account in Google Cloud; grant it the BigQuery Data Editor and BigQuery Job User roles on the project.
  2. Create a JSON key for the service account and paste its contents into the field in the wizard.
  3. 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).

  1. 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
  2. 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;
  3. In the wizard, enter the account identifier (myorg-myaccount or xy12345.eu-central-1), user, database, schema, warehouse, optional role and the contents of rsa_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, plus s3:DeleteObject for 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

SourcesourcePreset (config.preset)CredentialsReading
Postgrespostgresalloydb, cloud_sql, greenplum, materialize, motherduckpasswordSELECT
MySQLmysqlsinglestorepasswordSELECT
SQL Serversqlserversynapsepassword (SQL authentication)SELECT
ClickHouseclickhouse—passwordSELECT (HTTP, readonly=1)
Trinotrinostarburst_enterprise, starburst_galaxypasswordSELECT
Amazon Athenaathena—AWS keySELECT
Snowflakesnowflake—private keySELECT
BigQuerybigquery—service accountSELECT
Elasticsearchelasticsearch—API key or user + passwordindex + search_after
S3s3—AWS keyCSV or NDJSON (up to 5 MB)
Google Sheetsgoogle_sheets—service accountrange
Apache Kafkakafkaconfluentoptional SASLtopic
Google Pub/Subpubsub—service accountpull 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 credentials field 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).

On this page