Skip to main content

Reverse ETL: apply outputs

An apply output sends a job's rows to a vendor's API, one operation per row, and records what happened to each row. Use it to send conversions or events, or to create, update and remove objects such as campaigns. Both engines support it (kind: DuckDBJob and kind: SparkJob).

In short

Each row is sent in a chunk, its outcome (applied or failed) is stored, and a re-run never sends an applied row again.

Example​

This job takes conversions from the caller, keeps those with a value, and uploads them to Google Ads.

conversions.yaml
kind: DuckDBJob
version: 1
name: upload_conversions
description: Upload offline click conversions to a Google Ads account.

spec:
config:
customer_id: {type: string, required: true, pattern: "^[0-9]{10}$"}
validate_only: {type: boolean, default: false}
secrets:
developer_token: {type: string, required: true}
client_id: {type: string, required: true}
client_secret: {type: string, required: true}
refresh_token: {type: string, required: true}
input: # the rows to send, from --input
columns:
gclid: {type: string, required: true, max_length: 200}
conversion_action: {type: string, required: true}
conversion_date_time: {type: string, required: true}
conversion_value: {type: number, minimum: 0}
currency_code: {type: string, enum: [USD, EUR, GBP]}

connections:
ads_api:
provider: google_ads
developer_token: "{{ secrets.developer_token }}"
client_id: "{{ secrets.client_id }}"
client_secret: "{{ secrets.client_secret }}"
refresh_token: "{{ secrets.refresh_token }}"

steps:
- name: valid_conversions # each row becomes one conversion upload
sql: SELECT * FROM input WHERE conversion_value > 0

outputs:
- name: upload_conversions
connection: ads_api # where
step: valid_conversions # or sql:
operation: # what
service: ConversionUploadService
method: upload_click_conversions
arguments: {customer_id: "{{ config.customer_id }}", validate_only: "{{ config.validate_only }}"}
apply: # how rows are sent and judged
chunk_size: 2000
on_row_error: fail
policy: # how calls retry and how fast they go
retry: {max_attempts: 5, backoff: exponential}
rate_limit: {requests: 5, per: 1s}
log: # optional: a line per failed row, once the rows are sent
sql: SELECT gclid, outcome.error_code AS code FROM upload_conversions WHERE outcome.status = 'failed'
message: "gclid {gclid} rejected: {code}"
level: warning

runtime:
state:
sqlite: state.db # keeps every row's outcome between runs
failed_job_policy: retry
# once: create the state tables
streamwright duckdb state migrate conversions.yaml --set customer_id=1234567890
# a dry run: Google checks the rows and records none
streamwright run conversions.yaml --secrets secrets.yaml --input conversions.json \
--set customer_id=1234567890 --set validate_only=true
# the upload
streamwright run conversions.yaml --secrets secrets.yaml --input conversions.json --set customer_id=1234567890
# every row's status, attempts, result and error
streamwright duckdb state show conversions.yaml JOB_ID --set customer_id=1234567890

What it does:

  • operation: names the vendor operation, the way a call step names a read: a connection of a connector, and {service, method, arguments}. streamwright connectors NAME and the connector's guide list its apply operations (Google Ads: the *Service.mutate_* methods and the conversion uploads).
  • Each row of valid_conversions becomes one conversion upload, sent in chunks of 2,000 rows per call.
  • Every row's outcome is stored in the state store, and failed rows are logged.
  • With failed_job_policy: retry, the next run sends only the rejected rows again (see Rejected rows).

Row lifecycle​

  1. Checked. A row that cannot be sent fails on its own and is never sent (codes below).
  2. Pinned. The rows are committed to the batch's outbox together with the batch, with the output's rendered arguments. A retry applies the same rows with the same arguments.
  3. Sent in chunks. Each chunk is marked in flight before its call (write-ahead).
  4. Judged. After the call, every row's outcome is stored: what the vendor returned (for example the new campaign's resource_name), or its error code and message. A row the vendor rejects fails alone (Google Ads partial failures). A call the vendor rejects as a whole fails all its rows.
  5. Done. A completed batch is never applied again. With --job-key, a given piece of work runs once.

Delivery is at least once, and honest about it: when a call is cut short, its rows are not marked applied or failed. They stay in flight until the outcome is known (Unknown outcomes).

Each row has a status, shown by streamwright duckdb state show JOB JOB_ID with its attempts, result and error:

StatusMeaningOn the next run
pendingCommitted, not sent yetSent
in_flightIts call was cut short: nobody knows if the vendor applied itSee Unknown outcomes
appliedThe vendor accepted itNever sent again
failedRejected, or never sendableStays failed, unless the batch is retried (below)

With on_row_error: fail (the default), a rejected row makes the run exit 1. The outcomes are kept either way.

Options​

Apply settings​

KeyWhat it doesDefault
chunk_sizeRows per callThe operation's max_chunk (also the maximum)
on_row_errorfail: a rejected row makes the run exit 1 (see rejected rows). continue: rejected rows are kept as failed without failing the runfail
nullsA NULL field: error fails the row, skip_field leaves the field out, clear clears the field at the vendorerror
on_unknownRows whose outcome is unknown: hold stops for you to decide, reconcile asks the connector (see unknown outcomes)hold
allow_removeLets rows use the operation's destructive actions (op: remove)false

An apply output's policy: takes retry and rate_limit.

Row columns​

Each column is a field of the vendor's object; the connector's guide says which (campaign.name, network_settings.target_google_search, ...). When the operation has several actions (create, update, remove), a column op picks the row's action; the default is the first.

Before anything is sent, a row fails on its own, and is never sent, when:

CodeWhy
INVALID_ACTIONIts op is not one of the operation's actions
REMOVE_NOT_ALLOWEDIts op is destructive and the output does not set allow_remove: true
NULL_VALUEA field is NULL under nulls: error

Supported connectors​

ConnectorWhat an output sendsGuide
google_adsAny *Service.mutate_* (create, update, remove) and the conversion uploadsGoogle Ads
microsoft_adsAny *Service.Add*, Update*, Delete*, and offline conversionsMicrosoft Advertising
meta_adsAny object's create_* edge, api_update, api_delete, and Conversions API eventsMeta Ads
openai_ads, linkedin_ads, amazon_ads, apple_ads, restapiAny endpoint of the API, as an HTTP request (below)Connectors

REST API connectors​

A REST API connector's output sends HTTP requests instead of naming a service and method. Its operation: is a request, like a call step's, with the connection's base URL, headers and sign-in:

outputs:
- name: send_events
connection: ads_api # a REST API connector, e.g. provider: linkedin_ads
step: events
operation:
path: /rest/conversionEvents # added to the connection's http.base_url
method: POST # POST (default), PUT, PATCH or DELETE
apply:
on_row_error: continue
KeyWhat it doesDefault
pathThe endpoint; {{ row.<column> }} puts a row's value in it, e.g. /campaigns/{{ row.id }}Required
methodPOST, PUT, PATCH or DELETEPOST
jsonThe body. {{ row }} is the whole row and {{ row.<column> }} one value; {{ rows }} sends the chunk's rows in one requestThe row itself
params, headersThe query string and extra headers; may use {{ row.<column> }}None
records{path: ...}: the part of the response kept as each row's resultThe whole response

How rows are sent:

  • One request per row (the default). A row the API rejects (a 4xx status) fails alone, with the status as its error code. Use chunk_size to set how many rows are sent between progress lines.
  • One request per chunk, when json holds {{ rows }}, for example json: {elements: "{{ rows }}"}. Every row of the chunk gets the call's outcome. With records: {path: results}, row n gets item n of that list. A single row's value ({{ row.<column> }}) cannot be used then.
  • Retries. PUT and DELETE are idempotent, so they follow policy.retry. POST and PATCH are sent once; a request cut short keeps the rows sent before it and holds the rest (unknown outcomes).
  • The output's policy defaults to the connection's policy (rate limit and retries).

Rejected rows​

Under on_row_error: fail, a rejected row fails its batch. The scope's next run deals with that batch as runtime.state.failed_job_policy says (handle a failed batch):

PolicyWhat happens to the rejected rows
abandon (default)Nothing is sent again; the rejected rows stay failed.
retryThe retry sends again only the batch's failed rows of its on_row_error: fail outputs. If they are rejected again, the batch fails again. The next run retries it while it has been started fewer than runtime.state.max_job_attempts times.
blockThe run stops until state retry JOB JOB_ID or state abandon JOB JOB_ID. state retry ignores the outputs' apply: settings, so it sends every failed row of the batch again.

On any retry:

  • applied rows are never sent again;
  • rows in flight follow unknown outcomes;
  • rows you skipped (UNKNOWN_OUTCOME) stay failed;
  • a row keeps its attempts and last error until it is sent again.

Fix the cause first (the data, or the vendor account): a retry sends the same pinned rows with the same arguments.

Unknown outcomes​

A call cut short leaves its rows in flight: nobody knows whether the vendor applied them. This happens when the run stops mid-call, or when a transient error outlasts the retries.

  • An idempotent operation (one the vendor deduplicates, like conversion uploads) is sent again by the next run.
  • Otherwise, on_unknown: hold (the default) stops the run with exit 1 and names the batch. Check the vendor, then either:
    • streamwright duckdb state resend JOB JOB_ID: send them again on the next run (they may be applied twice), or
    • streamwright duckdb state skip JOB JOB_ID: mark them failed with UNKNOWN_OUTCOME.
  • on_unknown: reconcile asks the connector what already exists, where it can.
warning

A call of a non-idempotent operation is sent once: policy.retry does not apply to it. A call that timed out may have been applied, so it is never resent within the run.

Logs and results​

Run log​

Each call logs its chunk and time on streamwright.output. The output ends with one applied: line with the batch's totals:

output upload: call 1/3: 2,000 row(s), 1,998 applied, 2 failed, 1.4 s
applied: job_id=..., output=upload, rows=4998, failed=2, calls=3, duration_ms=4210

The run summary's outputs counts the applied rows.

Result log​

Once a run has sent a batch's rows, the output's log: logs a line per row of the results, like a step's log:. Each line is output NAME: MESSAGE on the streamwright.output logger. It works the same on DuckDBJob and SparkJob, whatever the connector. Only apply outputs take log:.

KeyWhat it doesDefault
sqlOne SELECT over the results table (below)Every row
messageThe line; {column} names a column of the SELECT
leveldebug, info or warninginfo
max_rowsLines to log, at most 1000; then one ... and N more row(s) line20

The results table is named after the output:

ColumnHolds
The output's columnsEach row as the output's SQL made it (gclid, campaign.name, ...)
outcome.statusapplied or failed
outcome.error_code, outcome.error_messageWhy a failed row failed: the vendor's code and message (for Google Ads, the field it names)
outcome.resultJSON: what the vendor returned for an applied row (for example outcome.result->>'$.resource_name')
outcome.rowThe row's number among the output's rows in the batch, from 1

For example, a count per error code:

log:
sql: |
SELECT outcome.error_code AS code, count(*) AS rows, min(gclid) AS example
FROM upload_conversions WHERE outcome.status = 'failed' GROUP BY 1
message: "{code}: {rows} conversion(s) rejected, e.g. gclid {example}"
level: warning
Good to know about log:
  • {outcome.status} and {outcome.result.resource_name} reach into the struct without selecting it; {{ batch.id }} is the batch.
  • The SELECT runs in an in-memory DuckDB that holds only the results: no files, no network.
  • The lines cover the rows this run sent; a row resent by a retry is logged again. They are redacted with the run's secrets and cut to 1,000 characters.
  • A log: that cannot run is a warning, never a failure: the rows were already sent.
  • Under streamwright serve (no row values in logs) it logs only how many rows the SELECT returned.

Good to know​

  • Row cap. runtime.limits.apply_rows (default 100000) caps the rows of one output per batch, so a runaway join fails the batch before any call.
  • Validation checks the operation against the installed vendor library (a typo gets a "did you mean"), its arguments, apply.chunk_size against the operation's maximum, and every policy value.
  • Dry runs belong to the vendor, as an argument of its operation (Google Ads: validate_only: true checks every row and changes nothing). To offer one, declare a boolean config input and pass it on, as validate_only in the example does. The CLI stays vendor-neutral.

See also​

  • Input payloads: rows the caller hands to the run, a common source for apply outputs. Rows can also come from files a job reads (S3, a local folder), shaped with SQL.
  • DuckDB jobs: runtime, state and failed batches.