Skip to main content

​
CDP reverse ETL to Snowflake and BigQuery

The CDP records, segments, and computed traits you build in Devotel Orbit are only half the value loop — a team running a warehouse still needs the data to LAND there nightly without hand-rolled pipelines. Reverse ETL is the second leg: a push of CDP profiles and events out to the warehouse you already own, with per-row idempotency (delete-then-insert on id) so re-runs never double-write. Supported destinations: BigQuery (via the /api/v1/cdp/reverse-etl surface), and for non-Google warehouses — Snowflake, Redshift, self-managed Postgres, Databricks, and ClickHouse — the /api/v1/cdp/reverse-etl/warehouse/:warehouse surface. The two surfaces share one cadence model and one credentials envelope.

​
1. What gets mirrored

Each run pushes only the rows that changed since the destination’s last successful run:
  • Contacts — keyed on updated_at, so edits roll forward without a full-snapshot re-ship. Exported set includes identifiers, attributes, tags, lifecycle stage, aggregates, and DNC / block flags.
  • CDP events — keyed on received_at, so append-only event rows show up in the destination once per run.
  • Opt-in mirrors (BigQuery only, expand the export with PATCH /api/v1/cdp/reverse-etl):messages, conversations, usage_events, and call_logs deltas. First provision the destination table, then flip the flag — the destination 404s otherwise.
  • Field mappings — optional per-table exclude / rename map, applied row-by-row before push (field_mappings in the config); the id column is locked so the dedup key always survives.

​
2. Snowflake setup

The connector speaks Snowflake’s SQL REST API with a key-pair RS256 JWT — no snowflake-sdk on the worker, and no password travels.
  1. Create a Snowflake service user for the export (e.g. ORBIT_SVC), grant it the role that can write to the destination database / schema.
  2. Generate an RSA key-pair and register the public half:
  3. In Snowflake, bind the exported public fingerprint to the service user:
  4. PATCH the warehouse config — only the key pair and the namespace go over the wire; values are validated, credentials encrypted at rest (credentials_encrypted) and never returned by GET:
  5. Watch for status: GET /api/v1/cdp/reverse-etl/sync-runs reports the last run per destination — ok, failing, stale, never_run, or disabled.

​
3. BigQuery setup

The BigQuery connector uses a Google service-account JSON (encrypted at rest on PATCH). It is the simplest destination because the OAuth + streaming insertAll form is the single-document contract.
  1. Create a Google Cloud service account with BigQuery Data Editor on the target project + dataset.
  2. Fetch a JSON key for it (a type: "service_account" blob).
  3. PATCH the settings body; service_account_json is encrypted at rest and never returned by GET — GET returns only service_account_configured:
  4. For each opt-in dataset (export_messages, export_conversations, export_usage_events, export_call_logs) provision the corresponding BigQuery table first — the insert fails loudly otherwise, and the destination flips to failing with the missing-table error visible in GET /api/v1/cdp/reverse-etl/sync-runs.

​
4. Sync cadence, and how the cursor works

  • Two run gates per destination: the 02:00 UTC settle window (so the last writers of the day get a two-hour grace before the snapshot) and a per-destination sync_interval_minutes in the [60, 30-days-minutes] range. The default — omitting sync_interval_minutes — reproduces the historical nightly cadence (23h).
  • Cursor (is idempotent by design): the destination’s last_run_at advances only on a successful run. A pod crash mid-export, a SIGTERM, or a transient Cloud SQL blip means the next run re-reads from the same last_run_at, so you never miss the delta and you never see doubles.
  • Backfill: a PATCH-set backfill_since (ISO instant, or epoch for a full-history replay) resends an older window on the next run. The replay is idempotent — delete-then-insert on id — so a one-time catch-up never leaves duplicate rows.
  • Sync-now: request an on-demand run with POST /api/v1/cdp/reverse-etl-sync-now (or set sync_now_requested on the config); it overrides cadence without lowering the export floor.
  • Idempotent upsert: every batch writes delete-then-insert keyed on id, so a re-run can never surface duplicates downstream.

​
5. Operational verification

  1. Confirm destination health — GET /api/v1/cdp/reverse-etl/sync-runs — after the first scheduled run completes; the returned object reports per-warehouse last_run_at, row counts, and a derived health indicator (ok / failing / stale / never_run / disabled).
  2. Look at last_run_rows_contacts and last_run_rows_events — they should be non-zero once any contacts / events exist in the delta window.
  3. When a destination goes failing, the response carries a sanitized last_error code (config_or_credential, tenant_client_open, export_stream) — never the raw driver message — so the error panel is safe to share with an audit channel.
  4. Watch consecutive_failures — the operator UI paints a warning when it exceeds zero; the same envelope feeds the shared alerting (this guide does not block on the shared alert path, it is a plain count in the GET response).

​
What stays internal

  • Credentials are encrypted at rest under the enc:v1: envelope; GET returns only a credentials_configured boolean.
  • Per-run last_error records a coarse enum (as above), not the raw driver text — a malformed-credential fix never leaks an internal DSN into the dashboard.
  • Reverse ETL is not a generic ETL tool: only contacts + cdp_events flow by default, and the messages / conversations / usage_events / call_logs arms need the explicit opt-in on the BigQuery surface.