Using the agent to move data

Copying a table from one system into another is tedious rather than hard. You need an export that reads the source, a file in between, a load that writes the destination, and the two halves agreeing about the filename. Moving data between sources documents all of it, and you can build one by hand from that page.

This page is about not doing that. Describe the move in a sentence and the Analyst works out the shape — but it is a conversation rather than a command, and the parts it asks about are the parts worth your attention.

Say it the way you'd say it to a colleague

"Move the staging invoices and subscriptions tables from the Snowflake demo connector into BigQuery every night."

That is enough. You do not need to know that it becomes two steps per table, that a bucket sits in the middle, or what the export SQL looks like.

What you should expect back is not a finished pipeline. It is a plan with questions attached, and the questions are the useful part.

What it works out without being told

Three things it establishes before proposing anything, each of which is a mistake you would otherwise make at 2am.

Which tables you actually meant. Asked for "staging invoices", it found two candidates and stopped:

STAGING.STG.STG_INVOICES & STAGING.STG.STG_SUBSCRIPTIONS (the transformed staging layer)

RAW.STRIPE.INVOICES & RAW.STRIPE.SUBSCRIPTIONS (the raw Stripe tables)

Before I plan the transfer, which tables did you mean?

Both are reasonable readings of the same phrase. Guessing would have been worse than asking, and this is the cheapest moment to be wrong.

Whether the route exists. Not every pair of systems can be connected in both directions — what a source can export and what a destination can load are separate capabilities, recorded per engine. It checks that the route you asked for is one the product can actually build before planning it.

Whether the volume fits. Buffered transfers are capped per run, and it sized the job against the real tables rather than assuming:

Mechanism: Buffered (via GCS staging) — capped at 200,000 rows/run. Your tables are ~6K invoices and ~400 subscriptions, well within that limit.

If your tables had been over the cap, that is the sentence that tells you before the first run truncates something.

What you still decide

Three things it will ask, because nothing in your schema answers them:

  • The destination dataset or schema. It knows the connector; it does not know where in it you want the tables to land.
  • Which connector, when more than one could serve.
  • How often, if you did not say.

Answer in plain text. Naming the objects exactly — STAGING.STG.STG_INVOICES rather than "the invoices one" — removes the only guess it would otherwise have to make.

Read the plan before you say yes

The plan arrives as a table, and it is short enough to actually check:

# Role Step Connector
1 Export STG_INVOICES export Snowflake → GCS
2 Load STG_INVOICES load GCS → BigQuery (dc_test.STG_INVOICES)
3 Export STG_SUBSCRIPTIONS export Snowflake → GCS
4 Load STG_SUBSCRIPTIONS load GCS → BigQuery (dc_test.STG_SUBSCRIPTIONS)

Four steps for two tables, because one table per export step — the file path is derived from the step, so two tables sharing one would overwrite each other.

Three things worth checking in that table, every time:

  1. The arrows. Each load should end at your destination, not at the staging bucket. GCS → GCS means the plan is copying the bucket to itself.
  2. The fully-qualified names. The dataset and table on the load legs are where your data is actually going to appear.
  3. The step count. Two per table. A missing load leg means an export that writes a file nobody reads.

This is not ceremony. A plan is cheap to correct in a sentence and expensive to correct once four steps exist and a schedule is firing — which is the whole reason the agent proposes before it builds.

The questions are a feature, not slowness

It would be easy to read "which tables did you mean?" as the agent failing to get on with it. The alternative is a confident guess, four steps built against the wrong tables, and a nightly job quietly copying raw Stripe rows over your transformed ones. Answer the question; it costs a sentence.

What happens when you approve

The four steps are created as ordinary SQL data steps and wired into one pipeline, which you can then open, read and edit like anything else you built yourself. Nothing about an agent-built transfer is a special object: it is the same export and load steps described in Moving data between sources, and the same versioning and assertions apply.

Each load uses a build → swap → drop pattern, so a run that fails partway leaves the previous table in place rather than an empty one.

Where it gets harder

Honest limits, so you know when to stop asking and start reading:

  • Large tables. The buffered cap is per run. Above it you want an incremental filter — a WHERE on a date column — rather than a full copy, and deciding the right watermark is a judgement about your data that the agent cannot make for you.
  • Type mismatches. Dialects disagree about numerics and timestamps. The load leg is ordinary SQL and you can cast in it.
  • Anything with a bucket you don't own. A transfer stages through your own storage connector, so there must be one. See Credentials.