Sync modes
How sanda reads a source on each run and writes it to your warehouse, and how to pick the right mode for each table.
A sync mode says what happens to one table on every run. It is a pair of decisions:
- How sanda reads the source. Read the whole table, or read only the rows that are new or changed since the last run.
- How sanda writes the warehouse table. Replace what is there, add to it, or keep one row per key.
There are five modes, and you choose one per stream. This page explains the idea and helps you choose. The sync modes reference lists each mode precisely.
Full refresh and incremental
The two families answer the first decision.
Full refresh reads the whole table on every run. It needs no setup, and the result always reflects the source as it is now. The cost is that every run reads and writes everything, so a big table takes longer each time it grows.
Incremental reads only the rows whose cursor has moved since the last run. The first run reads everything, as any first run must, and later runs move only what changed, which is fast even for a very large table. In return an incremental mode needs a cursor: a column that moves forward whenever a row is created or changed, such as updated_at. It cannot see a row that has been deleted at the source, because a deleted row has no cursor to read.
The five modes
| Mode | Reads | Writes | Needs |
|---|---|---|---|
| Full refresh · Overwrite | The whole table | Replaces the table | Nothing |
| Full refresh · Overwrite + Deduped | The whole table | Replaces the table, one row per key | A primary key |
| Full refresh · Append | The whole table | Adds a full copy beside the earlier ones | Nothing |
| Incremental · Append | New and changed rows | Adds them | A cursor |
| Incremental · Append + Deduped | New and changed rows | Keeps the latest version of each key | A cursor and a primary key |
The default is Full refresh · Overwrite. It is the one mode every table supports that needs nothing answered, and a table that is replaced whole on every run is the easiest to reason about. You move a table to an incremental mode when it is big enough to be worth the extra setup.
The picker offers only the modes a source supports for each table. Some tables cannot be read incrementally, because the source gives no way to tell what changed.
How to choose
Start from what the table is.
| The table is | Use | Because |
|---|---|---|
| Small and changes in place: a chart of accounts, a contact list, a price list | Full refresh · Overwrite | It always matches the source, and deleted rows disappear. Reading it whole is cheap |
| Large, with rows that are created and then edited: invoices, orders, tickets | Incremental · Append + Deduped | Each run moves only what changed, and the table holds the latest version of each row |
| Large and only ever grows: events, log lines, transactions that never change | Incremental · Append | Each run adds the new rows and nothing is rewritten |
| Something whose past you want to keep: a stock level, a balance, a price | Full refresh · Append | Each run adds a snapshot, so you can see how the table looked on any run |
| Whole, but the source may hand back the same key more than once | Full refresh · Overwrite + Deduped | You get the whole table with one row per key |
A few things settle most choices.
- If you are unsure, leave it on the default. You can change a stream's mode at any time from Edit streams. The change applies from the next sync.
- Check for a cursor you trust. An incremental mode is only as good as its cursor. Pick a column that is set when a row is created and updated whenever it changes. A cursor that is not updated on every edit means those edits are missed.
- Deletes need a full refresh. A cursor cannot see a row that has been removed, so an incremental table keeps rows that no longer exist at the source. A source read by its database's change log is the exception: it reports deletions as a flag on the row instead of removing it. If deleted rows must disappear, use a full refresh mode that replaces the table.
- Snapshots grow. Full refresh · Append adds the whole table on every run. On a large table that adds up fast, and it counts toward your storage.
What each mode does to history and deletes
| Mode | A row edited at the source | A row deleted at the source |
|---|---|---|
| Full refresh · Overwrite | Shows its new value | Disappears at the next run |
| Full refresh · Overwrite + Deduped | Shows its new value | Disappears at the next run |
| Full refresh · Append | Each run holds the value it had then | The earlier copies still hold it. It is missing from later copies |
| Incremental · Append | Lands again as a new row. The earlier version stays | Stays in the table |
| Incremental · Append + Deduped | The table holds its latest version | Stays in the table |
Things worth knowing
- Full refresh replaces a table in one step. For a short time after a run that replaces a table, before sanda has attached the new copy, a question over the table returns a message that it has no rows attached right now, rather than an empty answer. It clears when sanda next checks the run. See Troubleshoot connections.
- Each successful run is metered as one run, however many rows it moves. A full refresh does more work in your warehouse than an incremental run on the same table, and takes longer.
- Modes are per stream. One connection can mix them: a small lookup table on the default and a large fact table on incremental.
- Connections created before all five modes were offered keep what they had. A full refresh overwrote, and an incremental deduplicated when the table had a key.
Something unclear or out of date? Tell us, and we will fix the page.