Types of pipeline, and when to use each

A pipeline here is SQL steps, chained. That is one sentence and it is true of everything below — but "SQL steps, chained" covers four quite different jobs, and picking the wrong shape is the most common way a first pipeline becomes hard to live with.

This page is the four shapes, what each is for, and the question that tells you which one you have.

If you only want the question

Is the data already where it needs to be? If yes, you want a transform. If it is in another system, a transfer. If it is in a file, an ingest. If the answer is "it depends on what we find", a conditional.

1. Transform — reshape data that is already there

The common case, and the one to assume unless something rules it out. One database, several steps, each reading what the last one wrote.

Use it when your raw data has already landed somewhere and the job is to turn it into something people can query: cleaning types, joining sources, aggregating into a table a dashboard points at.

Shape: steps chained in order, usually ending in one that replaces a table. A nightly rebuild of a revenue mart from a staging layer is the canonical example — truncate, rebuild, validate.

Watch for: rebuilding everything every night when only yesterday changed. That is fine at a million rows and expensive at a hundred million. When it stops being fine, the fix is an incremental filter, not a different kind of pipeline.

Start at SQL Data Steps, then Pipelines to chain them.

2. Transfer — move data between systems

Two systems that cannot query each other. The data has to physically move, and it goes via a file in your own object storage.

Use it when the data you need is in a production database and the people who need it work in a warehouse. Or the reverse — warehouse results pushed back into an operational database so an application can read them.

Shape: an export step reads the source and writes Parquet to your bucket; a load step reads that file into the destination. Two steps per table, always, because the file path is derived from the step.

Watch for: the per-run row cap on buffered transfers, and type mismatches between dialects. Both are easier to handle at design time than after a truncated load.

Moving data between sources is the mechanics; Using the agent to move data is how to have it built for you.

3. Ingest — get a file into a table

Somebody has a CSV. It needs to be queryable, joined to real data, and not re-uploaded by hand every month.

Use it when the authoritative source is a spreadsheet export, a reference list, or a one-off extract from a system with no API worth using.

Shape: the file is loaded into a connected database as a table, and from there it is ordinary data — a transform pipeline can read it like anything else. In practice an ingest is usually the first step of a transform rather than a pipeline in its own right.

Watch for: treating a recurring manual upload as a permanent answer. If the same file arrives every month, the question to ask is whether the system producing it can be connected directly instead.

See Ingest Files.

4. Conditional — decide at runtime

The three above run the same way every time. Sometimes that is wrong: there is nothing to process, an upstream load has not finished, or the expensive step is only worth running when something actually changed.

Use it when a step should run sometimes, and the deciding factor is only known once the pipeline is already running.

Shape: a condition on the edge between two steps. You can branch on a value the previous step returned, its row count, its status, how long since it last succeeded, the time of day, or the day of week — combined with all or any.

Branching on a returned value is the one worth knowing. Have a step return a single summary row:

SELECT COUNT(*) AS NUM_RECS FROM staging_orders

then put NUM_RECS > 0 on the edge, and the expensive downstream work only runs when there is something to do. It needs Return data to pipeline enabled on that step — without it there is nothing stored to test, and the condition says so.

Watch for: using conditions where an assertion belongs. A condition decides whether to run; an assertion decides whether what ran was acceptable. "Skip if empty" is a condition. "Fail if empty" is an assertion. Reaching for the wrong one gives you a pipeline that silently does nothing and reports success.

These combine, and usually do

The four are not exclusive, and a real pipeline is often three of them in a row: ingest a file, transfer a table in from production, transform both into a mart, with a condition skipping the lot when nothing arrived.

Build it as one pipeline when the steps genuinely depend on each other, and as separate pipelines when they do not. The test is whether step 4 is wrong if step 1 did not run, or merely unchanged. Wrong means one pipeline. Unchanged means two, and two is easier to reason about at 3am.

What decides the trigger, not the type

Worth separating, because it is easy to conflate: how a pipeline runs is independent of which type it is. Any of the four can be run immediately, scheduled on CRON, or exposed as an API endpoint for another system to call. That is a deployment choice made after the pipeline exists, and changing it does not change the pipeline. See Deployments.

Likewise environments: the same pipeline runs against QA or PROD values without being rebuilt. See Deployments → Environments.