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).
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.
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 NAMEand the connector's guide list its apply operations (Google Ads: the*Service.mutate_*methods and the conversion uploads).- Each row of
valid_conversionsbecomes 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
- Checked. A row that cannot be sent fails on its own and is never sent (codes below).
- 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. - Sent in chunks. Each chunk is marked in flight before its call (write-ahead).
- 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. - 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:
| Status | Meaning | On the next run |
|---|---|---|
pending | Committed, not sent yet | Sent |
in_flight | Its call was cut short: nobody knows if the vendor applied it | See Unknown outcomes |
applied | The vendor accepted it | Never sent again |
failed | Rejected, or never sendable | Stays 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
| Key | What it does | Default |
|---|---|---|
chunk_size | Rows per call | The operation's max_chunk (also the maximum) |
on_row_error | fail: a rejected row makes the run exit 1 (see rejected rows). continue: rejected rows are kept as failed without failing the run | fail |
nulls | A NULL field: error fails the row, skip_field leaves the field out, clear clears the field at the vendor | error |
on_unknown | Rows whose outcome is unknown: hold stops for you to decide, reconcile asks the connector (see unknown outcomes) | hold |
allow_remove | Lets 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:
| Code | Why |
|---|---|
INVALID_ACTION | Its op is not one of the operation's actions |
REMOVE_NOT_ALLOWED | Its op is destructive and the output does not set allow_remove: true |
NULL_VALUE | A field is NULL under nulls: error |
Supported connectors
| Connector | What an output sends | Guide |
|---|---|---|
google_ads | Any *Service.mutate_* (create, update, remove) and the conversion uploads | Google Ads |
microsoft_ads | Any *Service.Add*, Update*, Delete*, and offline conversions | Microsoft Advertising |
meta_ads | Any object's create_* edge, api_update, api_delete, and Conversions API events | Meta Ads |
openai_ads, linkedin_ads, amazon_ads, apple_ads, restapi | Any 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
| Key | What it does | Default |
|---|---|---|
path | The endpoint; {{ row.<column> }} puts a row's value in it, e.g. /campaigns/{{ row.id }} | Required |
method | POST, PUT, PATCH or DELETE | POST |
json | The body. {{ row }} is the whole row and {{ row.<column> }} one value; {{ rows }} sends the chunk's rows in one request | The row itself |
params, headers | The query string and extra headers; may use {{ row.<column> }} | None |
records | {path: ...}: the part of the response kept as each row's result | The 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_sizeto set how many rows are sent between progress lines. - One request per chunk, when
jsonholds{{ rows }}, for examplejson: {elements: "{{ rows }}"}. Every row of the chunk gets the call's outcome. Withrecords: {path: results}, row n gets item n of that list. A single row's value ({{ row.<column> }}) cannot be used then. - Retries.
PUTandDELETEare idempotent, so they followpolicy.retry.POSTandPATCHare sent once; a request cut short keeps the rows sent before it and holds the rest (unknown outcomes). - The output's
policydefaults to the connection'spolicy(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):
| Policy | What happens to the rejected rows |
|---|---|
abandon (default) | Nothing is sent again; the rejected rows stay failed. |
retry | The 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. |
block | The 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), orstreamwright duckdb state skip JOB JOB_ID: mark them failed withUNKNOWN_OUTCOME.
on_unknown: reconcileasks the connector what already exists, where it can.
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:.
| Key | What it does | Default |
|---|---|---|
sql | One SELECT over the results table (below) | Every row |
message | The line; {column} names a column of the SELECT | |
level | debug, info or warning | info |
max_rows | Lines to log, at most 1000; then one ... and N more row(s) line | 20 |
The results table is named after the output:
| Column | Holds |
|---|---|
| The output's columns | Each row as the output's SQL made it (gclid, campaign.name, ...) |
outcome.status | applied or failed |
outcome.error_code, outcome.error_message | Why a failed row failed: the vendor's code and message (for Google Ads, the field it names) |
outcome.result | JSON: what the vendor returned for an applied row (for example outcome.result->>'$.resource_name') |
outcome.row | The 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_sizeagainst the operation's maximum, and every policy value. - Dry runs belong to the vendor, as an argument of its operation (Google Ads:
validate_only: truechecks every row and changes nothing). To offer one, declare a boolean config input and pass it on, asvalidate_onlyin 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.