Target Schema and Data Policies

schemaPolicy and writeMode control what DBConvert Streams does when the target already has tables or rows.

Use this page when:

target tables may already exist
target tables may already contain data
you need to choose between safe-stop, append, refresh, or upsert behavior

These fields apply to MySQL and PostgreSQL database targets. Other target types accept only the mode defaults.

In the UI, these options are shown with friendly labels. The API values are listed in the reference tables below.

Defaults

Mode	Schema policy	Write mode
Load	Fail if exists (safe default)	Fail if not empty (safe default)
CDC	Validate existing	Upsert (recommended for CDC)

mode=cdc supports writeMode=upsert (default) or writeMode=append. Any other value is rejected at startup.

Common policy pairs

Situation	Schema policy	Write mode
Target schema managed outside the stream	Validate existing	Fail if not empty (Load) or Upsert (CDC)
Add only missing tables	Create missing only	Fail if not empty (safe default)
Initial Load + CDC into a new target	Create missing only	Upsert
Rebuild selected target tables from scratch	Drop and recreate	Append
Refresh data in existing tables without recreating them	Validate existing	Truncate and load

Schema policy values

Label in UI	API value	Description in UI	Notes
Fail if exists (safe default)	`fail_if_exists`	Raises an error if any target table already exists. Safest option for first-time runs.	No DDL is executed.
Validate existing	`validate_existing`	Uses existing target tables as-is. Schema compatibility will be verified.	No DDL is executed. See checks below.
Create missing only	`create_missing_only`	Creates only tables that don't exist yet. Existing tables are left unchanged.	No drop or alter is done on existing tables.
Drop and recreate	`drop_and_recreate`	Drops and recreates all target tables from source schema. All existing target data will be lost.	MySQL disables foreign key checks during the drop step. PostgreSQL uses `DROP TABLE ... CASCADE`.

Create missing only behavior

Use Create missing only when DBConvert Streams should create target tables that are not present yet, while leaving existing target tables under operator control.

This policy:

creates only selected target tables that are missing
validates existing selected target tables instead of replacing them
does not alter, drop, truncate, or clean existing target tables
still applies the selected write mode after structure creation

For Initial Load + CDC, pair Create missing only with Upsert when starting with a new target. DBConvert Streams can create missing tables first, then verifies that the selected target tables are empty before copying existing source rows. Existing tables must still be compatible and empty for the first bootstrap copy.

Validate existing checks

Startup fails when

Check	What it means
Missing table or column	The target schema does not contain the required objects.
Incompatible or narrower mapped type	The target type cannot safely store the mapped source values.
Source nullable vs target NOT NULL mismatch	Source data may contain nulls that the target would reject.
Required key contract issue	CDC or `upsert` needs keys that are missing or incompatible on the target.

Warnings are emitted for

Difference	Why it stays non-blocking
Compatible type differences	The target type differs, but remains compatible with the mapped source type.
Source NOT NULL vs target nullable	The target is less strict than the source.
Default differences	Default values differ after normalization.
Primary key definition differences when a key is not required	Key shape differs, but the selected mode does not require it for correctness.
Index, foreign key, or check constraint differences	Structural differences exist outside the required compatibility contract.
Collation or charset differences	Text settings differ, but the table can still be used.

Write mode values

Label in UI	API value	Description in UI	Notes
Fail if not empty (safe default)	`fail_if_not_empty`	Raises an error if any target table already contains data. Safest option for first-time loads.	Uses a fast `LIMIT 1` check rather than a full row count.
Append	`append`	Adds source data to existing rows using plain INSERT.	Existing rows stay untouched. Duplicate key conflicts surface as errors. The writer also emits a startup warning when reruns are likely to hit existing primary or unique keys.
Truncate and load	`truncate_and_load`	Deletes all existing rows before loading. Target tables are emptied first.	MySQL uses child-first truncate order. PostgreSQL uses `TRUNCATE ... CASCADE`.
Upsert	`upsert`	Updates matching rows by primary key, inserts new ones. Safe for reruns.	Requires primary keys.

Write modes for CDC

CDC streams support two write modes:

Write mode	Behavior	Best for
Upsert (default)	Matches rows by primary key. Inserts new rows, updates existing ones.	Most CDC workloads. Safe for reruns and restarts.
Append	Uses plain INSERT. No duplicate checking.	Append-only tables (e.g. event logs, audit trails) where rows are never updated or deleted. Faster than upsert because it skips the primary key lookup on each write.

When to use append for CDC:

The source table is insert-only (no UPDATEs or DELETEs).
You want maximum write throughput and are willing to accept duplicate-key errors if the stream is restarted.
The target table has no primary key or unique constraints that could cause conflicts.

When to stick with upsert:

The source issues UPDATEs or DELETEs (upsert handles all three event types correctly).
You need idempotent reruns (e.g. replaying from a WAL position).
The target table has primary or unique keys.

Structure-Only Runs

When skipData=true or the stream is started with structureOnly=true, schemaPolicy is still applied and writeMode is ignored.

API examples

Load mode with safe defaults:

{
  "mode": "load",
  "target": {
    "id": "conn_target",
    "spec": {
      "db": {
        "database": "analytics",
        "schemaPolicy": "validate_existing",
        "writeMode": "fail_if_not_empty"
      }
    }
  }
}

CDC mode with append (insert-only workload):

{
  "mode": "cdc",
  "target": {
    "id": "conn_target",
    "spec": {
      "db": {
        "database": "analytics",
        "writeMode": "append"
      }
    }
  }
}

Verification

After you start the stream:

confirm the selected schema policy and write mode appear as expected in the saved stream configuration
if you use validate_existing, review startup warnings before treating the run as production-ready
if you use append, watch for duplicate-key warnings before rerunning the same load

Database Structure Options — tables, indexes, and foreign key creation strategies
Stream Configuration Guide
Stream Configuration Reference
Load Mode
CDC Mode