setting-up-a-data-warehouse-source

What this skill does

# Setting up a data warehouse source

Use this skill when the user wants to connect an external data source to PostHog's data warehouse for the first time.
The setup has a specific three-step flow (wizard → db-schema → create) — skipping steps leads to failed sources and
confused users.

## When to use this skill

- The user wants to connect a new source: "connect Stripe", "import my Postgres orders table", "sync Hubspot contacts"
- The user isn't sure what source types PostHog supports
- The user has credentials but doesn't know how to structure the `schemas` payload
- The user wants guidance on which sync method to pick per table

## Available tools

| Tool                                                   | Purpose                                                                          |
| ------------------------------------------------------ | -------------------------------------------------------------------------------- |
| `external-data-sources-wizard`                         | Discover which source types exist and what fields each needs                     |
| `external-data-sources-db-schema`                      | Validate credentials and list tables with available sync methods per table       |
| `external-data-sources-create`                         | Create the source — requires a `schemas` array built from the db-schema response |
| `external-data-sources-check-cdc-prerequisites-create` | Postgres CDC pre-flight check (optional, only for Postgres CDC)                  |
| `external-data-sources-webhook-info-retrieve`          | Check if a source supports webhooks and whether one has been registered          |
| `external-data-sources-create-webhook-create`          | Register a webhook with the external service after source creation               |
| `external-data-sources-update-webhook-inputs-create`   | Supply the signing secret manually when auto-registration failed                 |
| `external-data-sources-list`                           | After creation, confirm the source is listed and see its initial status          |
| `external-data-schemas-list`                           | See per-table sync status once the source is created                             |

## The three-step flow

Every source setup follows the same shape. Don't try to shortcut to `external-data-sources-create` — you need the
db-schema response to build a valid `schemas` payload.

```text
         ┌────────────────────┐
         │ 1. wizard          │  What source types exist? What fields does each need?
         └────────┬───────────┘
                  ▼
         ┌────────────────────┐
         │ 2. db-schema       │  Validate creds. List tables + available sync methods per table.
         └────────┬───────────┘
                  ▼
         ┌────────────────────┐
         │ 3. create          │  Send source_type + credentials + schemas[] to actually create.
         └────────────────────┘
```

## Workflow

### Step 1 — Discover the source type

Call `external-data-sources-wizard` (no params). The response is a dict keyed by source type. Each entry describes:

- `name` — the canonical source_type string you'll pass to later calls (e.g. `"Postgres"`, `"Stripe"`, `"Hubspot"`).
- `label` / `caption` — human-readable.
- `fields` — the config fields needed (host, port, database, api_key, client_id/secret, ...). Each has `name`,
  `type` (input, password, switch, select, file-upload), and `required`.
- `featured`, `unreleasedSource` — use to gauge readiness. Skip sources marked `unreleasedSource: true` unless the
  user explicitly asked for a preview.

Match the user's request to a source. If they said "Postgres", look up `Postgres`. If they said something ambiguous
like "database", present the top relevant matches (Postgres, MySQL, MongoDB, BigQuery, Snowflake, Redshift) and let
them pick.

For OAuth-based sources (Hubspot, Salesforce, Google Ads), the wizard entry hints at an OAuth flow. These typically
need the user to authorize in the PostHog UI rather than pasting credentials — explain this and direct them to the
source setup page rather than trying to collect tokens in chat. OAuth is about _authentication_, not about how data
flows; OAuth sources still use polling bulk sync, not webhooks.

Gather the required credentials from the user. Never ask for more fields than the wizard entry says are required —
asking for an unnecessary `port` when the source doesn't need one confuses users.

### Step 2 — Validate credentials and discover tables

Call `external-data-sources-db-schema` with `source_type` plus all credential fields. This does two things at once:

1. Validates the credentials against the live source. Returns 400 with a `message` if anything is wrong (bad host,
   wrong password, permission denied). Show the error verbatim — it's often actionable ("password authentication
   failed for user 'x'").
2. If valid, returns an array of table entries. Each entry:

```text
{
  "table": "orders",
  "should_sync": false,
  "rows": 1_250_000,
  "incremental_available": true,   # can do sync_type=incremental
  "append_available": true,        # can do sync_type=append
  "cdc_available": true,           # can do sync_type=cdc  (null = not enabled for team)
  "supports_webhooks": false,      # can do sync_type=webhook for real-time push
  "incremental_fields": [          # candidates: usually updated_at, created_at, id
    {"field": "updated_at", "type": "datetime", "label": "updated_at", ...},
    {"field": "created_at", "type": "datetime", ...},
    {"field": "id", "type": "integer", ...}
  ],
  "detected_primary_keys": ["id"],
  "available_columns": [{"field": "id", "type": "integer", "nullable": false}, ...],
  "description": "..."
}
```

Present this to the user. Don't dump the raw JSON — summarize: which tables were found, row counts, and the default
sync method recommendation per table (see [sync-type decision guide](./references/sync-types.md)).

### Step 3 — Confirm per-table sync configuration

For each table the user wants to sync, pick a sync_type. See the
[sync-type decision guide](./references/sync-types.md) for detailed rules, but the short version is:

- **Small / dimension tables (<50k rows, no natural ordering column):** `full_refresh` — simple and always correct.
- **Large tables with an `updated_at` / `modified_at`:** `incremental` — much cheaper per sync.
- **Append-only immutable tables (logs, events):** `append` if available — preserves history.
- **Postgres with CDC enabled and you need near-real-time:** `cdc` — requires primary keys and Postgres prerequisites.
- **Sources that support webhooks (currently Stripe):** for near-real-time ingestion set `sync_type: "webhook"` on
  the tables where `supports_webhooks: true`, then register the webhook as a post-create step (see step 6 below).
  Tables that don't support webhooks on the same source still need a bulk sync_type.

For each schema that will use `incremental`/`append`/`cdc`, you also need:

- `incremental_field` — which column to track for high-water-mark ordering. Pick from the `incremental_fields` list
  returned by db-schema. Prefer `updated_at` over `created_at` (updated_at catches late-arriving updates;
  created_at misses them). For integer-only tables, use the monotonically increasing primary key.
- `incremental_field_type` — must match the chosen field's type (`datetime`, `timestamp`, `date`, `integer`,
  `numeric`, `objectid`).
- `primary_key_columns` — required for CDC. Use `detected_primary_keys` from db-schema.

### Step 4 — Pick a good prefix

The source's `prefix` is prepended to table names in HogQL. Tables end up as `{prefix}_{table_name}`.

- Default to the source type lowercased if there's only one source of that type: `stripe`, `postgres`.
- If the user already has a Postgres source, pick something distinguishing: `postgres_prod`, `postgres_analytics`.
- Use lowercase, underscore-separated. The prefix becomes part of every HogQL query the user writes.

Confirm the pr