Sync modes

The five ways sanda can read a source and write it to your warehouse, with what each needs and how each treats edits, deletes and history.

For owners and admins

View as Markdown

A sync mode is set per stream. It says how sanda reads the table at the source and how it writes the table in your warehouse. For advice on choosing, see Sync modes in the connections guide.

The modes at a glance

Mode	Needs a cursor	Needs a primary key	What it does
Full refresh · Overwrite (default)	No	No	Reads the whole table on every run and replaces what landed last time.
Full refresh · Overwrite + Deduped	No	Yes	Reads the whole table on every run, replaces what landed and keeps one row per primary key.
Full refresh · Append	No	No	Reads the whole table on every run and adds it beside the earlier copies: a history of snapshots.
Incremental · Append	Yes	No	Reads only the rows whose cursor has moved since the last run, and adds them.
Incremental · Append + Deduped	Yes	Yes	Reads only the rows whose cursor has moved, and keeps the latest version of each primary key.

A cursor is a column that moves forward whenever a row is created or changed, such as updated_at. A primary key is the column, or columns, that identify one row. Both are chosen in the stream picker unless the source has already decided them.

Full refresh · Overwrite

Shown in stream lists as full refresh. Needs nothing. This is the default.

Reads the whole table on every run and replaces what landed last time.

Edits at the source: the table shows the new value after the next run.
Deletes at the source: the row is gone after the next run, because the whole table is replaced.
History: none. Only the result of the latest run exists.

It is the default for any stream you have not chosen a mode for, because every table supports it and it needs nothing answered. Where a table does not offer it, the picker opens on the first mode the table does offer.

Full refresh · Overwrite + Deduped

Shown in stream lists as full refresh · dedup. Needs a primary key.

Reads the whole table on every run, replaces what landed, and keeps one row per primary key.

Edits at the source: the table shows the new value after the next run.
Deletes at the source: the row is gone after the next run.
History: none.

Use it when a source can return the same key more than once and you want a single row for each.

Full refresh · Append

Shown in stream lists as full refresh · append. Needs nothing.

Reads the whole table on every run and adds it beside the earlier copies: a history of snapshots.

Edits at the source: each run's copy holds the value the row had at that run.
Deletes at the source: the earlier copies still hold the row. It is missing from the copies that follow.
History: one full copy per run. The table grows by the size of the source on every run, and the storage counts toward your warehouse limit.

Each landed row carries a loading column, whose name starts with an underscore, that records when it was extracted. It is what tells one run's copy from the next.

Incremental · Append

Shown in stream lists as incremental. Needs a cursor.

Reads only the rows whose cursor has moved since the last run, and adds them. The first run has nothing to compare with, so it reads the whole table.

Edits at the source: the changed row lands again as a new row, and the earlier version stays.
Deletes at the source: a cursor cannot see a row that is no longer there, so the row stays in the table. The exception is a source read by its database's change log, which reports a deletion as a flag on the row.
History: every version of every row that a run has seen.

Incremental · Append + Deduped

Shown in stream lists as incremental · dedup. Needs a cursor and a primary key.

Reads only the rows whose cursor has moved, and keeps the latest version of each primary key.

Edits at the source: the table holds the latest version.
Deletes at the source: the row stays, for the same reason as Incremental · Append.
History: the table holds one row per key, not every version.

What a mode needs

Need	Modes that ask for it	Where it comes from
A cursor	Incremental · Append, Incremental · Append + Deduped	You pick a column. The picker marks the source's suggestion. A source can also decide the cursor itself, and then the cell reads source-defined
A primary key	Full refresh · Overwrite + Deduped, Incremental · Append + Deduped	You pick a column. A source can declare the key itself, and then the cell shows it

The picker refuses to save while a stream that is on lacks what its mode needs, and says how many streams are missing a cursor or a key.

Modes a source offers

Each table offers only the modes the source supports for it. A table that supports only one shows it as fixed text. When a source reports nothing about a table, all five are offered.

A mode applies from the next sync after you save it. Changing a mode does not run anything by itself.

Older connections

Before sanda offered all five modes, a stream was either "full refresh" or "incremental", and sanda chose how to write it. A stream saved that way keeps doing what it did: a full refresh overwrote, and an incremental deduplicated when the table had a key (its own or the source's) and appended otherwise. Open Edit streams to move it to one of the five.

What is the same in every mode

The table lands as raw.<connection>__<stream>, whichever mode reads it.
Every landed table carries a few loading columns whose names start with an underscore. They stay out of the semantic fluid.
A run that replaces a table is followed by a short window, until sanda has attached the new copy, in which a question over the table returns a message that it has no rows attached right now. See Troubleshoot connections.
Each successful run is metered as one run, however many rows it moves.

Something unclear or out of date? Tell us, and we will fix the page.