Transfer Tool

TransferTool

Move rows from one data backend to another in a single atomic playbook step.

Source: src/tools/transfer.rs Added: initial release (postgres/http/duckdb); Snowflake↔Postgres added in v3.10.0 (tools#65, closes noetl/ai-meta#99).

---

Supported directions

Source	Target	Notes
postgres	postgres	Direct COPY-style via INSERT batches
http	postgres	HTTP response body → PG table
duckdb	postgres	In-process DuckDB → PG
postgres	duckdb	PG → in-process DuckDB
snowflake	postgres	SF→PG with type coercion (v3.10.0)
postgres	snowflake	PG→SF with generated INSERTs (v3.10.0)

---

Config shape

A tool: transfer step declares source and target endpoint objects plus an optional mode.

- name: transfer_sf_to_pg
  tool: transfer
  config:
    source:
      type: snowflake
      auth: "sf_test"          # keychain alias — worker resolves this
      query: "SELECT id, name, value, created_at, metadata FROM my_table"
    target:
      type: postgres
      auth: "pg_noetl_k8s"     # keychain alias
      table: "public.my_table"
      mode: replace            # truncate before INSERT (default: append)

Endpoint fields

Field	Required	Description
type	yes	Backend kind: snowflake, postgres, duckdb, http
auth	yes	Keychain credential alias (string). The worker resolves this before dispatch and injects the connection fields.
query	source only	SQL to run against the source (snowflake/postgres/duckdb source).
table	target only	Destination table name (schema-qualified if needed).
mode	target, optional	append (default) or replace (truncates the target table before writing).

Credential resolution

The worker pre-resolves source.auth and target.auth at dispatch time via the same auth_alias mechanism all other tools use. The resolved credential fields (for Snowflake: account, user, warehouse, role, database, schema, private_key, public_key, password; for Postgres: host, port, dbname, user, password, sslmode) are flattened into the endpoint config object via #[serde(flatten)] extra before the transfer tool sees it.

Playbook authors write only the alias string:

auth: "sf_test"

The tool never receives a bare alias — by the time TransferTool::execute runs, the object already contains the resolved connection fields. See noetl/worker wiki — auth-alias for the full field-map reference.

---

Snowflake↔Postgres specifics

SF→PG: type coercion

Snowflake's SQL REST API returns every cell as a string, regardless of the declared column type. The transfer tool solves this in two steps:

1. Type lookup. Before writing, the tool queries the target Postgres table's information_schema.columns to get the udt_name for each column.
2. Cast injection. Each placeholder in the INSERT statement is written as $n::text::<udt_name> so Postgres performs the string-to-typed cast. For example, id with udt_name = int4 becomes $1::text::int4.

Timestamp reformat. Snowflake's internal TIMESTAMP_TZ format is <unix_epoch_seconds>.<nanoseconds> <tz_offset_minutes>, not a standard datetime string. The tool detects this format and reformats to RFC3339 before passing the value to the ::text::timestamptz cast. Without this step, the PG cast fails with a parse error.

PG→SF: generated INSERTs

The tool reads rows from the Postgres source using the configured query, then generates individual SQL-escaped INSERT INTO <table> VALUES (...) statements and sends them to Snowflake via SnowflakeTool's statement API. String values are single-quoted with internal quotes escaped; numeric and boolean values are written bare.

mode: replace

When mode: replace is set on the target:

• Postgres target: TRUNCATE TABLE <table> CASCADE runs before the first INSERT batch.
• Snowflake target: TRUNCATE TABLE <table> runs before the generated INSERTs.

The default (append) skips the truncate.

---

Result envelope

The tool returns the standard {status, data} shape:

{
  "status": "COMPLETED",
  "data": {
    "rows_transferred": 5,
    "source": "snowflake",
    "target": "postgres"
  }
}

rows_transferred is the count of rows moved.

---

Related

• Snowflake-Tool — the Snowflake SQL REST client the transfer tool reuses internally (SnowflakeTool::query_rows was added in v3.10.0 to serve the transfer tool).
• noetl/worker wiki — auth-alias — how the worker resolves credential aliases before dispatch.
• noetl/ai-meta#99 — umbrella tracking the Snowflake↔Postgres transfer work.