Environments and Promotion

Purpose#

An environment is a named slot inside a SQL Pipelines project — dev, staging, prod — that decides which physical databases a task talks to. The task definition (its SQL, its load mode, its keys) is written once; the environment supplies the connectors underneath it.

That split gives you three things:

• The same pipeline can run against a scratch database and a production database without editing a line of SQL.
• Production environments can be marked protected, which removes write and run rights from Developers.
• Moving a task from one environment to another is an explicit, reviewable promotion with a field-level diff and a typed confirmation.

Prerequisites#

• A SQL Pipelines project. See SQL pipelines.
• Data connectors already created for the databases each environment will use. See Data connectors.

Required role#

The global admin role is an implicit Owner on every project.

Note. connection.bind and task.run are environment-gated: a Developer holds them in unprotected environments only. task.promote is Maintainer-or-higher and is always evaluated against the environment you are promoting into, not the one you are promoting from.

Working with environments#

Understand what exists already#

When you create a project, exactly one environment is created for you: dev. It is the project default and it is not protected. Every other environment is something you add deliberately.

Environments are per project. Two projects both named their environment prod; those are two unrelated rows with their own connector bindings.

Create an environment#

1. Open the project and go to its Environments tab.
2. Choose New environment.
3. Fill in the fields (see Environment fields).
4. Save.

If you name the environment with a production-class name, the protected flag is switched on by default. You can turn it off, but do so knowingly — see What protected actually does.

Bind connectors by role#

An environment on its own does nothing. It becomes useful when you bind connectors to it by role:

• source — where tasks read from.
• destination — where tasks write to.

1. Open the environment.
2. In Connector bindings, pick a role.
3. Choose one of the existing data connectors.
4. Save.

A task declares which role it needs, never which connector. At run time the runner resolves the role against the environment the run is happening in.

Resolution is fail-loud. If a task needs the source role and the environment has no connector bound to source, the run fails immediately with a message that names both the environment and the missing role. There is no fallback to the project default, no fallback to dev, and no "last known good" connector. A pipeline that cannot prove where its data is coming from does not run.

What protected actually does#

The protected flag does exactly two things. Both are worth knowing precisely, because a lot of operational safety rests on them.

1. It removes six write capabilities from Developers.

In a protected environment a project member with the Developer role loses:

• task.create
• task.update
• task.delete
• task.run
• connection.bind
• schedule.manage

The denial carries the reason protected_environment, so the message you see distinguishes "you are not allowed to do this at all" from "you are not allowed to do this here". Maintainers and Owners keep all six in every environment.

2. It makes job runs preflight their connectors.

A job run targeting a protected environment is refused before any task executes unless every connector role that the job's tasks require resolves in that environment. The check happens up front, as a batch: you get a refusal naming what is missing, not a half-finished pipeline with three tables loaded and the fourth failed.

Warning. Turning off protected on a production environment silently re-grants those six capabilities to every Developer on the project and drops the job preflight. Treat the flag as a production control, not a convenience toggle.

Promoting a task between environments#

Promotion copies a task definition from one environment to its twin in another. It is the supported way to move a pipeline from dev to staging to prod.

Run a promotion#

1. Open the task in its current environment.
2. Choose Promote.
3. Pick the target environment.
4. Read the diff. You get a field-level comparison of 30 definition fields between the task as it exists here and the twin as it exists there (or a full "will be created" view if no twin exists yet).
5. Type the target environment's name, exactly into the confirmation box.
6. Confirm.

Without the typed confirmation the promotion is preview-only: you can look at the diff as often as you like and nothing is written. This is deliberate — the diff is meant to be read, and a single-click promotion is not a review.

What the diff covers#

The 30 compared fields are the task's definition:

• Identity and description: name, description
• Shape: task type, source role, destination role
• Logic: SQL, destination schema, destination table
• Load behaviour: load mode, unique/business keys, incremental column, history columns
• Hooks: pre-SQL, post-SQL
• Operational settings: enabled, timeout, retry count, retry delay, on_row_error, on_schema_drift

Deliberately excluded from both the diff and the copy:

• Connector foreign keys — the whole point is that the target environment supplies its own connectors through its role bindings.
• environment_id — the target environment's own id.
• Row ids and other primary keys.

What promotion does not do#

Promotion identifies the twin task by a shared promotion key, then creates it if it is absent or updates it if it is present.

Promotion does not promote orchestration. Job membership is not copied. Schedules are not copied. If a task belongs to a job in dev and runs nightly there, promoting it to prod gives you the task in prod and nothing else — you still add it to the prod job and attach a prod schedule yourself.

This is intentional: orchestration is usually the part that differs most between environments, and silently copying a schedule into production is the kind of surprise that pages someone at 03:00.

Read the parity panel#

The project's parity panel answers two questions across all environments at once:

• Which environments have this task? Gaps show you what has not been promoted yet.
• Which environments have diverged? A task present in two environments but differing in any of the 30 compared fields is flagged, so a hotfix applied directly in prod and never back-ported to dev does not stay invisible.

Sample and dev runs#

A sample run is a bounded run of a task. It exists so you can exercise a pipeline against real data without loading the whole source table and without disturbing production state.

The most important thing to understand: sample mode is a per-run flag, not a property of an environment. There is no "sample environment". You choose it when you start a run, and any environment can host one.

Start a sample run#

1. Open the task and choose Run.
2. Switch on Sample run.
3. Pick a bound:
   • row_cap — stop after N source rows. Default 1000, maximum 1,000,000.
   • time_window — restrict the source SELECT to a time window. This requires the task to have an incremental column; without one, the window has nothing to filter on and the option is unavailable.
4. Run.

Either bound is pushed down into the source SELECT — the runner limits what it reads, rather than reading everything and throwing rows away afterwards.

What makes a sample run safe#

• It does not advance watermarks. The next normal run picks up exactly where it would have without the sample.
• It does not write to the applied-loads ledger. Sample loads are not counted as having been applied, so they neither satisfy nor skip a real load.
• It is labelled SAMPLE in the run logs and throughout the UI, so nobody mistakes a bounded run for a full one when reading history.
• A rerun or retry of a sample run stays a sample. The flag travels with the run; you cannot accidentally escalate a sample into a full load by hitting retry.

Note. table_copy tasks support row_cap only. A straight table copy has no SQL body and no incremental predicate to hang a time window on.

Field reference#

Environment fields#

Connector binding fields#

Sample-run fields#

Failure modes#

See also#

• SQL pipelines — the tasks that environments host
• Jobs — orchestration, and the protected-environment preflight
• Schedules — attaching schedules to jobs
• Load modes
• RBAC