Moving data between sources

A transfer copies tables from one connected source into another — a production database into a warehouse, a warehouse into Postgres, either one into your own bucket.

It is two data steps in one pipeline, and that is the only part worth understanding before you build one:

  1. an export step reads the source and writes Parquet into your bucket
  2. a load step reads that file and writes it into the destination

Ask the Analyst and it writes both for you — "move the actor and film tables into Postgres every night" is enough. The rest of this page is what it builds, so you can read it, change it, or write it yourself.

Why it goes through a bucket

Even when both ends are databases. Two reasons, and neither is paperwork:

  • Nothing passes through our memory. For PostgreSQL, AlloyDB and MySQL sources, the export streams straight from the source into Parquet. There is no row limit, because the rows never gather anywhere.
  • The file is a checkpoint. If the load fails at 02:00, the retry reads the file that was already written rather than querying your source again.

So a storage connector is required. If your organization has none, add one before you start.

The export step

Source: the database. Destination: your bucket.

SELECT * FROM src.public.actor

Two things are not obvious and both matter:

  • Read the source through src. The query runs inside DuckDB with your database attached under that alias. Without the prefix you are querying nothing.
  • Write a plain SELECT. The COPY … TO around it is added for you, and so is the file path. Do not write your own — the load step reads the path this one wrote, and two steps that each name the file can disagree by one character and move nothing while reporting success.

One table per export step. The file path is derived from the step, so two tables in one step would write over each other. Three tables means three export steps and three load steps.

The load step

Source: your bucket. Destination: the database.

-- 1. Build the new data beside the live table, which keeps serving.
CREATE OR REPLACE TABLE dest.public.actor__dc_new AS
SELECT * FROM 'gs://your-bucket/_dc_transfer/<step>/prod/data.parquet';

-- 2. Swap. Only now does the live table change.
ALTER TABLE IF EXISTS dest.public.actor RENAME TO actor__dc_old;
ALTER TABLE dest.public.actor__dc_new RENAME TO actor;

-- 3. Drop the previous generation.
DROP TABLE IF EXISTS dest.public.actor__dc_old;
  • Write the destination through dest. Same reason as src, with a sharper failure: an unqualified CREATE TABLE actor writes into DuckDB's own in-memory catalogue, which is discarded when the step ends. The step succeeds, reports rows, and moves nothing.
  • Build first, swap last. The target keeps serving its old rows for the whole load. If the load fails, the live table is untouched — which on a nightly pipeline is the difference between a failed run and an outage.

Wire them into one pipeline

Export first, load second, with the load depending on the export. The order is the point: the load reads the file the export just wrote. A load step scheduled on its own re-reads whatever the last successful export left behind, which will be yesterday's data.

Then deploy the pipeline on a schedule.

What it will cost you

Every run moves every row — there is no incremental mode yet. So the monthly volume is the size of the tables times the number of runs: a nightly transfer of 50 GB is about 1.5 TB a month against your plan's allowance.

The Analyst tells you this figure before you save the schedule, using the table sizes on the Schemas page. If it is more than you want to move, a less frequent schedule is the only lever today.

Which sources can do what

Source Export Note
PostgreSQL, AlloyDB, MySQL Streams No size limit
BigQuery, Snowflake, Databricks, SQL Server, Redshift Buffered Read through the connector, capped at 200,000 rows per run

Loading is narrower: a transfer can write into PostgreSQL, AlloyDB and MySQL. Other engines can be a source today but not a destination.