Copy a table 1:1

The simplest flow copies one entity into one table, unchanged. In a large migration most tables are like this, and each takes a few lines.

columns: "*"

From the pg-merge example, which copies a Postgres customers table into a reporting database:

# Loaded through `include:` in ferry.yaml, not by the flows/ convention.
flows:
  - name: customers
    from: app.public.customers
    sink: reporting
    mode: upsert
    to:
      customers:
        key: [id]
        columns: "*"            # 1:1 copy of every declared field

columns: "*" writes every field of the entity, with its name and type, in the order the source declares them. dataferry plan shows the result:

flow `customers`
  from:        app.public.customers (driver, key id)
  sink:        reporting, mode upsert
  strategy:    TxnWithState
  partitions:  about 50000 rows each (runtime.yaml), stored as `customers/<partition>`
  write order: customers
  targets:
    customers (key id)
      id     int64
      name   utf8
      email  utf8, nullable
  ddl (not run by plan):
    CREATE SCHEMA IF NOT EXISTS "public";
    CREATE TABLE "public"."customers" (
      "id" bigint NOT NULL,
      "name" text NOT NULL,
      "email" text,
      PRIMARY KEY ("id")
    );

The rules:

• "*" is allowed only in a flow with a single from: entity, on a target with no parent: and no unnest:.
• It can’t be combined with other columns in this release: to rename, convert or add one column, list them all.
• Types map from the source’s to the sink’s through dataferry’s canonical types (PostgreSQL, MongoDB). Copying from MongoDB works the same way, with the fields schema inference found.

Renaming, converting, filtering

Listing the columns gives you control over each:

      products:
        key: [id]
        columns:
          id: _id
          name: name
          price: {expr: price, type: "decimal(10,2)", allow_lossy: true}
          price_cents: int(round(price * 100))
          tag_count: int(coalesce(cardinality(tags), 0))

Here _id becomes id, price is narrowed to two decimal places, and two columns are computed. A flow where: would drop records, and a target where: rows (Transforms).

Copying into tables that exist

dataferry creates each target table that doesn’t exist, with the columns, types and primary key the plan shows. If a table already exists, it must fit: every column the flow writes must exist with a compatible type, and any column the flow doesn’t write must be nullable or have a default. A mismatch fails before anything is written, with every problem listed (PostgreSQL).

Two things to know before loading into a table that already holds rows:

• Rows with the same key are replaced, in either mode: under insert the sink deletes the target rows whose keys a partition writes, then inserts; under upsert it updates them in place (INSERT ... ON CONFLICT DO UPDATE). Rows with other keys are left alone. A target without key: can only be written under TxnWithState.
• Verification assumes empty targets. It compares whole tables with what the run wrote, so rows the run didn’t rewrite make it fail (Verification).

For repeated loads into the same tables, use mode: upsert and expect verification to report the rows already there that the run doesn’t rewrite.