> ## Documentation Index > Fetch the complete documentation index at: https://docs.orbit.devotel.io/llms.txt > Use this file to discover all available pages before exploring further. # CDP reverse ETL to Snowflake and BigQuery > Mirror contacts and event streams out of the CDP into your own warehouse — Snowflake, BigQuery, Redshift, self-managed Postgres, Databricks, or ClickHouse — on an operator-set cadence, with per-row idempotency encrypted at rest. # 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: ```bash theme={null} openssl genrsa -out /tmp/orbit-sfkey.pem 2048 openssl rsa -in /tmp/orbit-sfkey.pem -pubout -outform DER | \ openssl dgst -sha256 -binary | openssl enc -base64 -A # Copy the SHA256 base64 fingerprint (Snowflake expects this exact form): # SHA256: ``` 3. In Snowflake, bind the exported public fingerprint to the service user: ```sql theme={null} ALTER USER ORBIT_SVC SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9...'; ``` 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: ```bash theme={null} curl -X PATCH https://api.orbit.devotel.io/api/v1/cdp/reverse-etl/warehouse/snowflake \ -H "X-API-Key: dv_live_sk_your_key_here" \ -H "Content-Type: application/json" \ -d '{ "enabled": true, "account": "xy12345.us-east-1", "username": "ORBIT_SVC", "database": "MARKETING", "schema_name": "PUBLIC", "warehouse_name": "ANALYTICS_WH", "role": "DATA_WRITER", "table_prefix": "orbit_", "sync_interval_minutes": 1380, "credentials": "-----BEGIN PRIVATE KEY-----\n..." }' ``` 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`: ```bash theme={null} curl -X PATCH https://api.orbit.devotel.io/api/v1/cdp/reverse-etl \ -H "X-API-Key: dv_live_sk_your_key_here" \ -H "Content-Type: application/json" \ -d '{ "enabled": true, "project_id": "tenants-warehouse-prod", "dataset_id": "orbit_cdp", "table_prefix": "orbit_", "export_messages": true, "export_conversations": false, "service_account_json": "{ \"type\": \"service_account\", ... }" }' ``` 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. ## Related guides * [CDP segments and computed traits](/guides/cdp-segments) — the source-leg of the value loop; build the audience before you push it. * [API reference — CDP endpoints](/api-reference/endpoints/cdp) — the generated per-operation shapes for `/api/v1/cdp/reverse-etl*` and `/api/v1/cdp/reverse-etl/warehouse/*`.