Tuning Incremental Sync Config

1---
2name: tuning-incremental-sync-config
3description: >
4  Change the sync configuration of an existing data warehouse schema — switch sync_type, pick a different
5  incremental_field, set primary_key_columns, choose cdc_table_mode, or change sync_frequency. Use when the user
6  asks "switch my orders table from full refresh to incremental", "this table is syncing too slowly / too
7  frequently", "I need to pick a different incremental column", "set up CDC for this Postgres table", or when
8  diagnosis of a failing sync pointed to an incremental-field or PK misconfiguration.
9---
10 
11# Tuning incremental sync config
12 
13A sync's configuration lives on the `ExternalDataSchema` and can be changed any time via
14`external-data-schemas-partial-update`. Most changes are non-destructive (take effect on the next sync), but a few
15(switching sync_type, changing primary keys) require careful handling to avoid corrupting the synced data.
16 
17## When to use this skill
18 
19- The user wants to change how an already-connected table is synced
20- A diagnosis flagged the incremental field or primary key as wrong
21- The table is syncing too often / not often enough
22- Switching an incremental table to CDC (or vice versa)
23- The source table was changed on the other side (new columns, dropped columns) and the sync config needs to catch up
24 
25If the user is setting up a brand-new source, use `setting-up-a-data-warehouse-source` instead — configuration is
26chosen at creation time there.
27 
28## Available tools
29 
30| Tool                                                   | Purpose                                                                   |
31| ------------------------------------------------------ | ------------------------------------------------------------------------- |
32| `external-data-schemas-retrieve`                       | Current sync_type, incremental_field, PKs, sync_frequency                 |
33| `external-data-schemas-incremental-fields-create`      | Refresh candidate incremental fields from the live source                 |
34| `external-data-schemas-partial-update`                 | Apply the config change                                                   |
35| `external-data-schemas-reload`                         | Trigger a sync with the new config                                        |
36| `external-data-schemas-resync`                         | Wipe and re-import from scratch when the change invalidates existing data |
37| `external-data-schemas-delete-data`                    | Drop the synced table while keeping the schema entry                      |
38| `external-data-sources-check-cdc-prerequisites-create` | Pre-flight Postgres CDC (only when switching to/from CDC)                 |
39| `external-data-sources-webhook-info-retrieve`          | Current webhook state (when switching to/from sync_type=webhook)          |
40| `external-data-sources-create-webhook-create`          | Register a webhook after switching a schema to sync_type=webhook          |
41| `external-data-sources-update-webhook-inputs-create`   | Rotate a webhook signing secret                                           |
42| `external-data-sources-delete-webhook-create`          | Unregister webhook when switching schemas off sync_type=webhook           |
43 
44## The fields you can tune
45 
46From the partial-update endpoint:
47 
48| Field                    | Values                                                                                           | Notes                                                              |
49| ------------------------ | ------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------ |
50| `sync_type`              | `full_refresh`, `incremental`, `append`, `cdc`, `webhook`                                        | Source must support the target type — check via incremental-fields |
51| `incremental_field`      | Column name from the source                                                                      | Must appear in `incremental_fields` list for the schema            |
52| `incremental_field_type` | `datetime`, `date`, `timestamp`, `integer`, `numeric`, `objectid`                                | Must match the column's real type                                  |
53| `primary_key_columns`    | Array of column names                                                                            | Required for CDC. Used for upsert dedup on incremental             |
54| `cdc_table_mode`         | `consolidated`, `cdc_only`, `both`                                                               | Only meaningful when sync_type=cdc                                 |
55| `sync_frequency`         | `1min`, `5min`, `15min`, `30min`, `1hour`, `6hour`, `12hour`, `24hour`, `7day`, `30day`, `never` | Applies to all non-CDC types                                       |
56| `sync_time_of_day`       | `HH:MM:SS`                                                                                       | When sync_frequency is daily/weekly-scale                          |
57| `should_sync`            | `true` / `false`                                                                                 | Pause the schema without deleting it                               |
58 
59## Workflow
60 
61### Step 1 — Read the current config
62 
63Always start with `external-data-schemas-retrieve({id})`. Understanding the current state prevents mistakes like
64"fixing" an incremental_field that's actually correct.
65 
66Note:
67 
68- Current `sync_type`, `incremental_field`, `incremental_field_type`, `primary_key_columns`
69- Current `status` (don't tune a schema that's currently `Running` — wait or cancel first)
70- `last_synced_at` (so you can tell if the next sync worked)
71- `latest_error` if present (the error often tells you exactly what to change)
72 
73### Step 2 — If changing sync_type or incremental_field, refresh candidates
74 
75Call `external-data-schemas-incremental-fields-create({id})`. Even though the operation name says "create", it
76re-reads the source and returns the current candidate fields — use it to confirm the field you want to set actually
77exists on the source and which sync types are now available for this table.
78 
79The response:
80 
81```text
82{
83  "incremental_fields": [{"field": "updated_at", "type": "datetime", ...}, ...],
84  "incremental_available": true,
85  "append_available": true,
86  "cdc_available": true,
87  "full_refresh_available": true,
88  "detected_primary_keys": ["id"],
89  "available_columns": [...]
90}
91```
92 
93If your target `incremental_field` isn't in the list, tell the user — they need to either pick a different field or
94change the source table to add one.
95 
96### Step 3 — Apply the change
97 
98Call `external-data-schemas-partial-update({id}, {...changed fields})`.
99 
100**Only send the fields that are actually changing.** Partial update means unspecified fields stay as they are.
101 
102Examples:
103 
104```json
105// Switch from full_refresh to incremental
106{
107  "sync_type": "incremental",
108  "incremental_field": "updated_at",
109  "incremental_field_type": "datetime"
110}
111 
112// Change sync frequency to hourly
113{"sync_frequency": "1hour"}
114 
115// Fix wrong PK on a CDC table
116{"primary_key_columns": ["tenant_id", "order_id"]}
117 
118// Pause a schema
119{"should_sync": false}
120```
121 
122### Step 4 — Decide whether existing data is still valid
123 
124This is the step that's easy to get wrong. Some config changes invalidate the synced data; others don't.
125 
126**Changes that DON'T invalidate existing data:**
127 
128- `sync_frequency`, `sync_time_of_day` — scheduling only
129- `should_sync` — on/off
130- `cdc_table_mode` in most cases — next sync will start writing to the new shape, but historical consolidated rows
131  stay valid
132- Switching between `incremental` and `full_refresh` with the same `incremental_field` — next sync just re-runs
133  fresh
134- Switching to or from `sync_type: "webhook"` — the synced data stays valid; only the ingestion path changes.
135  Remember to register or unregister the webhook (see sections below) alongside the sync_type change.
136 
137**Changes that MAY invalidate existing data and need a resync:**
138 
139- Changing `incremental_field` to a different column — the high-water mark is from the old column and won't match.
140  Without a resync you'll miss rows that were updated between the two fields' histories.
141- Changing `primary_key_columns` — existing rows may be deduplicated incorrectly against new PK definitions.
142- Switching from `full_refresh` to `append` — the existing rows don't have the version-history shape that append
143  expects.
144- Switching from `append` to `full_refresh` — opposite problem; you'll end up with duplicate historical versions.
145- Switching to/from `cdc` — the table shape changes fundamentally.
146 
147When the change invalidates data, the clean flow is:
148 
1491. `external-data-schemas-partial-update` with the new config
1502. Warn the user this is destructive
1513. `external-data-schemas-resync` to wipe and re-import under the new config
152 
153Or equivalently, `external-data-schemas-delete-data` → `external-data-schemas-reload`. `delete-data` + `reload` is
154cleaner when the table is large and the user wants to start from zero.
155 
156### Step 5 — Trigger and confirm
157 
158For non-destructive changes, call `external-data-schemas-reload({id})` to pick up the new config immediately rather
159than waiting for the schedule.
160 
161Wait a moment, then `external-data-schemas-retrieve({id})` to confirm `status = Running` then `Completed`. Report
162`last_synced_at` and any new `latest_error`.
163 
164## Specific common changes
165 
166### Switching full_refresh → incremental
167 
1681. `incremental-fields-create` to confirm the desired field exists and `incremental_available: true`.
1692. `partial-update`: `{sync_type: "incremental", incremental_field, incremental_field_type}`.
1703. No data wipe needed — next sync just switches strategy. If the source is growing fast, the next incremental sync
171   is the cheap one.
172 
173### Switching incremental → cdc (Postgres only)
174 
1751. Run `external-data-sources-check-cdc-prerequisites-create` on the parent source. Only proceed if `valid: true`.
1762. `incremental-fields-create` to confirm `cdc_available: true` and see `detected_primary_keys`.
1773. `partial-update`: `{sync_type: "cdc", primary_key_columns: [...], cdc_table_mode: "consolidated"}`.
1784. **Resync required** — CDC tables have a different shape. Trigger `external-data-schemas-resync` after the update.
179   Warn the user this wipes existing data.
180 
181### Fixing a stale incremental field after schema drift
182 
183Source dropped the `updated_at` column. Sync has been failing with "column does not exist".
184 
1851. `incremental-fields-create` to see what fields remain.
1862. Pick a replacement (or switch to `full_refresh` if none are suitable).
1873. `partial-update` with the new field + type (or new sync_type).
1884. `reload` to retry.
189 
190### Changing primary keys on a CDC table
191 
1921. `partial-update`: `{primary_key_columns: [...]}`.
1932. **Resync required** — existing CDC tombstones and upsert keys won't match the new PK definition, leading to row
194   duplication or missed updates.
1953. `resync`, warn the user.
196 
197### Changing sync_frequency
198 
1991. `partial-update`: `{sync_frequency: "1hour"}`.
2002. No reload needed — the next scheduled sync picks up the new cadence. Or reload manually if the user wants to
201   confirm nothing broke.
202 
203### Switching a schema to `sync_type: "webhook"`
204 
205Only works for sources that implement `WebhookSource` (today: Stripe) and tables where `supports_webhooks: true`
206from `incremental-fields-create`.
207 
2081. `incremental-fields-create` to confirm `supports_webhooks: true` for the table.
2092. `partial-update`: `{sync_type: "webhook"}`.
2103. If the source doesn't already have a webhook registered (check with `webhook-info-retrieve`), call
211   `external-data-sources-create-webhook-create({source_id})` to register it.
2124. No resync required — the schema's existing bulk-synced data stays, and the webhook becomes the primary ingestion
213   path once the next reconciliation finishes.
2145. Keep `sync_frequency` set (e.g. `24hour`) — it acts as a safety-net reconciliation in case any webhook delivery
215   is missed.
216 
217### Switching off `sync_type: "webhook"`
218 
2191. `partial-update`: `{sync_type: "incremental"}` (or whatever bulk type is appropriate) with the required
220   `incremental_field` + `incremental_field_type`.
2212. If **no other schemas** on the source are still using `sync_type: "webhook"`, call
222   `external-data-sources-delete-webhook-create({source_id})` to unregister. Leaving an orphaned webhook
223   registered on the source side just means events will be received and dropped — not harmful, but messy.
2243. If other schemas on the source are still on webhook, leave the webhook registered — it's shared across all
225   webhook-type schemas on the source.
226 
227### Rotating a webhook signing secret
228 
229The source's signing secret (e.g. Stripe's `whsec_...`) was rotated, and payloads are now failing signature
230verification.
231 
2321. Grab the new secret from the source's dashboard.
2332. `external-data-sources-update-webhook-inputs-create({source_id}, {inputs: {signing_secret: "whsec_..."}})`.
2343. No reload needed — the next inbound webhook payload will verify against the new secret.
235 
236### Pausing a schema
237 
2381. `partial-update`: `{should_sync: false}`. Schema stops syncing but stays configured.
2392. To resume later: `partial-update`: `{should_sync: true}`, then `reload` for an immediate run.
240 
241## Important notes
242 
243- **Read before you write.** Always retrieve the current config first. `partial-update` doesn't complain if you set a
244  field to the value it already had, but you might be about to change something you didn't realize was already set.
245- **Not every sync_type is available on every schema.** The `incremental-fields-create` response tells you what's
246  available _right now_, which can be different from what was available at creation (e.g. CDC may have been
247  enabled for the team since).
248- **Wipe when the shape changes.** Switching sync strategy often changes the physical table. If you don't resync,
249  you'll be mixing row shapes and queries will return garbage.
250- **CDC needs prerequisites.** Never switch to `sync_type: "cdc"` without running `check-cdc-prerequisites-create`
251  first. The sync will just fail immediately.
252- **Don't touch a Running schema.** If the schema is currently running, either wait for it to finish or
253  `external-data-schemas-cancel` before applying the change. Updating config mid-sync can leave the incremental
254  high-water mark inconsistent.
255- **Sync frequency is cheap to change.** Encourage experimentation there. Sync_type and incremental_field are
256  expensive to change — encourage care.
257- **Webhooks are registered at the source level, not the schema level.** Multiple webhook-type schemas on the same
258  source share one webhook registration. Only delete the webhook when the _last_ webhook-type schema on that
259  source is being switched away, otherwise other schemas stop receiving pushes.
260