PPactDocs
Documentation

Integrations > Data warehouse sync

Two-way sync between Pact and Snowflake, BigQuery, Databricks, Redshift, or Postgres

Data warehouse sync

Stream Pact changes into your data warehouse in real time, and pull warehouse-modeled data back into Pact records. One connection drives both directions, configured at Admin → Warehouse → Sync (/admin/warehouse/sync).

Supported warehouses: Snowflake, Google BigQuery, Databricks, Amazon Redshift, and PostgreSQL (including Neon). Redshift and Postgres are live with no extra setup; Snowflake, BigQuery, and Databricks require their connector enabled on your workspace.

Outbound — Pact → warehouse

  1. Connect a warehouse. On the sync dashboard, click Connect a warehouse, pick your warehouse kind, and enter the connection details. Credentials are stored encrypted, per workspace — never in a shared config.
    • Postgres / Redshift: a connection string, or host / port / database / user / password.
    • Snowflake: account, user, password (or key-pair), warehouse, database, role.
    • BigQuery: a service-account JSON key and project.
    • Databricks: server hostname, HTTP path, and an access token.
  2. Choose which tables to export. Per-table opt-in keeps cost and privacy under your control — export only contacts and deals, or everything. Alongside the CRM core (companies, contacts, opportunities, sequences, segments, journeys, campaigns, consent decisions, audit log), the analytics-shaped tables sync too: deals and both stage-history ledgers, the unified activity ledger, meeting notes and intelligence, call-recording metadata (audio bytes never leave object storage), campaign sends, customer-health snapshots, voice-campaign budgets and spend, enrichment runs, and the raw tracking-event stream.
  3. Pick a PII mode. Personally-identifiable columns can be written verbatim, dropped, or one-way hashed (so you can still join on identity without the raw value leaving Pact).
  4. Backfill. On first connect, run a one-time historical export so your warehouse starts in sync. Schema is created automatically with typed columns; new Pact fields are added to the warehouse tables as they appear.
  5. Stream. Every insert, update, and delete then flows to the warehouse — sub-second on the premium tier, batched on free and starter to keep warehouse credits low. Lag and row counts are visible on the dashboard.

Inbound — warehouse → Pact (reverse ETL)

Pull modeled data back into Pact records — product-usage stats, marketing attribution, customer-health scores, or membership for a segment computed in your warehouse.

  1. New sync. Choose the source warehouse and write a SQL query against it.
  2. Map columns. Map each result column to a Pact field, and choose the target object: contacts, accounts, or a segment.
  3. Match. Pick the field rows are matched on (e.g. email). Matched records are updated; you can optionally insert unmatched rows.
  4. Go incremental. Set a watermark column (such as updated_at) so each run only pulls rows that changed — far cheaper than re-scanning the whole table.
  5. Schedule. Run manually, hourly, or daily. Every run records rows read, written, and failed, with errors surfaced inline.

Observability & audit

The sync dashboard shows both directions: warehouse health, CDC lag, recent export and reverse-ETL runs, and per-run row counts. Every sync attempt is written to the audit log with the caller, timestamp, and row counts for SOC 2 evidence.

Cost

Warehouse credits are the main cost, and they scale with write volume and query frequency — not table size. Batched tiers keep warehouses asleep; incremental watermarks keep reverse-ETL scans small. Set a monthly budget when connecting (or later) and Pact pauses CDC automatically when the modeled projection crosses it — the dashboard shows rows synced this month and the estimated cost per connection, so a surprise bill can't build up silently.

Agent access (MCP)

Three MCP tools cover the sync surface for AI agents: list_warehouse_connections (read-only status, lag, cost), configure_warehouse (settings only — PII mode, table filter, budget, pause/resume), and trigger_warehouse_sync (backfill, CDC flush, or a reverse-ETL run). Credentials can never be supplied through MCP — connections are created in the admin UI, where secrets are entered once and stored encrypted.

Was this page helpful?