Tables & columns

Every selected table compiles to one mapping rule: a target name, a set of included columns, a primary key, a create mode and a schema-change setting. Rules match on the source table name in declaration order, and a table with no matching rule is dropped. This page covers every knob on that rule.

The default is 1:1

When you select a table, Data Sync maps it straight through. Every discovered column is included, every column keeps its name, the target takes the source table's bare name, and the primary key is whatever the source declared. A sync where you never open step 4 is a complete, valid sync.

That matters for how you should read the rest of this page: you are configuring exceptions. There is no mapping to write out, no schema to declare, nothing to keep in step with your source. Adding a column to the source adds it at the sink; the only columns you have to think about are the ones you want treated differently.

Renaming

Renaming happens in two places, because tables and columns are decided at different points in the wizard.

Expanding a table in step 4 shows one row per discovered column. Each row carries a checkbox controlling whether the column is included, the source column name, a badge with the source type as the connector reported it, a PK badge on columns that are part of the detected primary key, and a target-name box that is empty until you rename.

Above the column list sit the two per-table controls: Schema changes, which decides what happens when the source gains or alters a column mid-stream, and Primary key, which overrides the detected key for this target. Both apply to the whole table, not to a single column.

The type shown against each column is the source’s own type, not the sink’s. The sink type is chosen when the target table is created, or checked against the existing table when you choose Use existing.

Dropping columns

Unchecking a column drops it — and it is worth being precise about where. The column is dropped before it leaves the source. It is not read and forwarded and then discarded at the sink; it does not travel through the runner, it is not written to any batch, and it never appears in a log line.

Two consequences follow, and they are the two reasons to use this.

• PII containment. If a column must not leave the source network, unchecking it is the control that enforces that, not a downstream view or a sink-side grant. Nothing is copied that you can later be asked to delete.
• Narrowing wide tables. A hundred-column table where you need eleven of them costs a hundred columns of read, serialise, transfer and write on every single change. Dropping the rest cuts snapshot time, CDC bytes and sink storage in one move.

Primary keys

The target's primary key defaults to the primary key Data Sync detected on the source table during discovery. The per-table Primary key control overrides it, and you can name more columns than the source key contains — a composite target key is fine.

One rule catches people out: overrides name output columns. If you renamed customer_id to account_id, the key override must say account_id. The override is applied to the mapped row, after renaming, not to the source row.

Why the key matters

The key is not decoration. It is the thing that makes a sink write idempotent, and idempotency is the entire basis of Data Sync's exactly-once posture: there is no distributed transaction across source and sink, so correctness on recovery comes from replaying a bounded window of changes into a sink that converges. An UPDATE replayed against a keyed target is a no-op the second time. Deletes need the key too — without one there is no way to identify the row to remove.

Derived columns

A derived column — the console calls the control + Calculated field — is an output column whose value is an expression evaluated once per row, in the runner, as the row passes through the mapping layer. It exists at the sink and has no counterpart at the source.

Expressions are compiled once when the sync starts and type-checked against the discovered source schema. A misspelled column name or a function applied to the wrong type fails at that point, with the sync refusing to start, rather than at three in the morning halfway through a stream.

To add one, choose Calculated field, name the output column, write the expression, and leave the type on Auto unless you need to pin it.

What an expression can read

Expressions read source columns and the _meta.* namespace, and nothing else.

Function catalog

Operators

Types

An expression produces one of STRING, INT, DOUBLE, DECIMAL, BOOL, DATE, TIMESTAMP or JSON. The type selector defaults to Auto (inferred), which takes the type the compiler worked out from the expression itself. Leave it there unless you need to pin the output — for instance forcing DECIMAL on a money column so the auto-created target does not land on a floating-point type.

Nulls and failures

Null propagates SQL-style: if an input is null, the result is null. The exceptions are the functions whose whole job is handling nulls — concat, coalesce, is_null, iif and nullif — which are defined on null inputs and behave accordingly.

Worked examples

Money in currency units. The source stores an integer number of cents and every consumer divides by a hundred. Do it once, name the output amount and pin the type to DECIMAL so the auto-created target is exact rather than floating point.

round(amount_cents / 100, 2)

A soft-delete flag. The source marks deletion by setting a deleted_at timestamp, but the downstream dashboard filters on a boolean. Name the output is_deleted and leave the type on Auto (inferred) — the comparison already produces BOOL.

!is_null(deleted_at)

A display label with a fallback chain. Users may have a display name, or only a username, or neither. ?? takes the first non-null; cast_string makes the numeric id a valid last resort, because the branches of a coalescing chain have to agree on a type.

display_name ?? username ?? cast_string(user_id)

A normalised join key. Two systems hold the same country code with different casing and stray whitespace. Normalising on the way in means the downstream join does not have to.

upper(trim(country_code))

Metadata columns

The + Metadata column control adds an output column sourced directly from the _meta.* namespace — no expression to write. It is the same set of fields listed above: ingest_time, commit_time, op, phase, source_table, source_name, lsn and txn_id.

These are what make a replicated table auditable. commit_time lets you measure end-to-end freshness at the sink instead of trusting a dashboard. op and lsn together let you reconstruct why a row looks the way it does. phase tells you whether a row arrived in the backfill or on the stream, which is the first question worth asking when a backfilled table disagrees with the source.

If you want a metadata value combined with something else — a truncated commit date, a concatenated provenance key — use a calculated field instead and reference _meta.* in the expression.

Fan-in: combining tables

Combine into one table, set in step 3, folds several source tables into a single target. The usual case is a source that was sharded or time-partitioned for operational reasons — events_2024, events_2025, or one table per tenant — where the warehouse wants one table.

Every member of a combined set carries a _source_table provenance column recording which source table the row came from. That column is forced into the target's primary key, and you cannot opt out of it.

The reason is collision. Sharded tables almost always restart their identifiers, so events_2024 and events_2025 both contain an id of 1. Keyed on id alone, the second row to arrive would silently overwrite the first, and the overwrite would happen again on every replay. With _source_table in the key the two rows are distinct, upserts stay idempotent, and deletes address exactly one row.

Create mode

Each table target is either created by Data Sync or already yours.

Under Use existing, validation checks that the target exists and that the mapped output — every included column, every rename, every derived and metadata column, and the primary key — can actually be written to it. If it cannot, the sync fails at start rather than issuing DDL to make it fit. A dry run does not catch this, because a dry run never touches the sink.

Per-table schema-change policy

Sources change. The per-table Schema changes control decides what this table does when the source gains a column mid-stream.

This control is only one input to a larger model. Whether a change is backward compatible or breaking, which type widenings are recognised, and how the sync-level widen / approve / fail policy interacts with it are all covered in Schema changes. The one rule to carry over: a breaking change always routes through approval, whatever any policy says.