Idempotency keys and CI/CD

Retry sqlflow commands safely with idempotency keys, handle the sign-in limit and exit codes, and run the CLI in a CI/CD job.

A script that creates or starts a pipeline can lose the answer, for example when a network connection drops after the control plane has already done the work. Running the command again might then do it twice. An idempotency key makes the retry safe: SQL Flow performs the work once, and answers a repeat with the original record.

This page also covers what to set up when sqlflow runs unattended in a CI/CD job.

Idempotency keys

An idempotency key is a string you choose that names one intended action, such as release-418-orders-start.

• It is 1 to 200 printable ASCII characters, without spaces, and is compared exactly.
• It belongs to the user who sends it, in the account the user works in.
• The CLI sends it only with the commands below. Pass it with --idempotency-key <key>.

pipeline reload doesn't take a key. --all and --idempotency-key can't be combined, because a key names one exact action: --idempotency-key identifies one exact operation and cannot be combined with --all.

What happens on a retry

1. The first command with a key does the work and records an operation with status accepted. The command prints the operation as a line of JSON, with "replayed":false.
2. A repeat of the same command with the same key does not do the work again. It prints the original operation with "replayed":true. For create, it prints reconciled pipeline '<name>' instead of created pipeline '<name>'.
3. The same key with a different request is refused with a 409 error. For start, pause and stop, the message reads This idempotency key was already used for a different dispatch payload. [idempotency_key_conflict]. For create and provision, the CLI shows only the status and the code idempotency_key_conflict.

key="release-418-orders-start"
sqlflow pipeline start orders --idempotency-key "$key"

start → orders [orders]
{"operation":{"id":"op-1f3a9c27b84d5e06","pipelineId":"orders","status":"accepted","createdAt":"2026-10-15T08:12:44+00:00","command":"start","agentIds":["agent-3f9c0a7e1b"],"commandIds":["c5a2e1d4"]},"replayed":false}

If that command times out and you don't know whether it took effect, run it again with the same key. You can also read the record without repeating the command:

sqlflow operation status --idempotency-key "$key"

See Operations commands.

Choose keys

• Build the key from the release or job and the action, not from the clock. A key made from the time of day differs on every retry, so it protects nothing.
• Use a new key for a new intent. The record is never cleaned up, so reusing orders-start months later for a new start repeats the old record instead of starting the pipeline.
• Use a different key for each pipeline and each command.

accepted means SQL Flow recorded the command and queued it. It doesn't mean the agent finished. To wait for the result, poll pipeline status.

Run the CLI in a CI/CD job

Before you begin

• A SQL Flow user for the job, with the User role, so the audit log shows who did what. Create it under Accounts, and sign in to the web app once to replace its temporary password. See Accounts and team.
• The sqlflow executable on the runner, and outbound HTTPS from the runner to the control plane.
• The control plane's certificate authority in the runner's trust store, if the control plane issues its own certificate. See HTTPS and certificates.
• The control plane address, the user's email and the user's password, kept in your CI secret store.

Configure the job

Set SQLFLOW_URL, SQLFLOW_OPERATOR and SQLFLOW_OPERATOR_PASSWORD from your CI system's secret store, as environment variables of the job. In GitHub Actions, for example, map three repository secrets to them in the job's env section. Don't pass the password with --operator-password.

Give database passwords the same way: put each in a secret, expose it as an environment variable, and pass its name with --source-password-env or --target-password-env. The password then never appears in the command line, the process list or the job log. Or use a secret reference so the password never leaves the agent's host.

Stay under the sign-in limit

Every command signs in on its own. The control plane allows 10 sign-ins a minute from one client address by default, and answers more with Too many requests from this client; retry later. [rate_limited]. A job that runs many commands in a minute, or several jobs that share one address, can reach the limit. Group the work into fewer commands, space the commands out, or retry after a minute.

Retry only commands that are safe to repeat: the ones that read, and the ones that carry an idempotency key. Don't blindly retry reload or resync, which rewrite target rows each time they run.

function Invoke-WithRetry {
    param([string[]]$Arguments)
    foreach ($attempt in 1..3) {
        sqlflow @Arguments
        if ($LASTEXITCODE -eq 0) { return }
        Start-Sleep -Seconds 60
    }
    throw "sqlflow $($Arguments -join ' ') failed after 3 attempts."
}

Invoke-WithRetry @('pipeline', 'start', 'orders', '--idempotency-key', "release-$env:BUILD_ID-orders-start")

Use exit codes

Every command returns an exit code, so a job can fail on it. See Output and exit codes. pipeline compare is built for gating:

sqlflow pipeline compare orders
status=$?
if [ "$status" -ne 0 ]; then
  echo "Compare did not come back clean (exit code $status)." >&2
  exit "$status"
fi

Exit code 0 means the target matches the source, 3 means drift, 4 means the result couldn't be confirmed, and 1 means the run failed.

Example: create and start a pipeline

This job saves a connection if it isn't saved yet, creates a pipeline, starts it, and waits until it reports healthy. It assumes the SQLFLOW_* variables, ORDERS_DB_PASSWORD and WAREHOUSE_DB_PASSWORD are set, and that the source connection orders-sql already exists.

$ErrorActionPreference = 'Stop'
$key = "release-$env:BUILD_ID-orders"

if (-not (sqlflow connection list | Select-String '^warehouse-pg ')) {
    sqlflow connection add warehouse-pg --engine postgres --host pg01.example.com --port 5432 --db warehouse --user sqlflow_writer
    if ($LASTEXITCODE -ne 0) { throw 'connection add failed' }
}

sqlflow pipeline create orders --source orders-sql --target warehouse-pg --tables dbo.Orders,dbo.OrderLines --agent orders-agent --source-password-env ORDERS_DB_PASSWORD --target-password-env WAREHOUSE_DB_PASSWORD --recreate-target --idempotency-key "$key-create"
if ($LASTEXITCODE -ne 0) { throw 'pipeline create failed' }

sqlflow pipeline start orders --idempotency-key "$key-start"
if ($LASTEXITCODE -ne 0) { throw 'pipeline start failed' }

foreach ($i in 1..30) {
    if (sqlflow pipeline status orders | Select-String ' healthy ') { return }
    Start-Sleep -Seconds 10
}
throw 'orders did not become healthy in 5 minutes.'

Each sqlflow pipeline status poll signs in again. A poll every 10 seconds uses 6 of the 10 sign-ins a minute that the control plane allows by default, so poll less often for a long wait, or when other jobs share the address.

See also

• Operations commands
• Pipelines commands
• Overview and authentication
• Database credentials and secret references