DuckDB jobs
A DuckDBJob is one YAML file that fetches data, shapes it with SQL, and writes the results to tables, files, message brokers or vendor APIs. It runs in an in-process DuckDB, so you need no cluster. Use a job when you need several steps, joins across systems, or state between runs. A SparkJob reads the same keys on Spark.
connections say where, steps fetch and shape, outputs write. Every call to the outside world names
a connection (where), an operation (what) and a policy (how).
A minimal job
kind: DuckDBJob
version: 1
name: analytics_orders
description: Load high-value orders into a DuckDB warehouse.
spec:
config:
min_amount: { type: number, default: 50.0 }
connections:
raw_data:
provider: files
roots: [data] # relative to this job's folder
warehouse:
provider: duckdb
path: analytics.duckdb
steps:
- name: raw_orders
connection: raw_data
read: parquet
path: orders.parquet
materialize: view
- name: high_value_orders
materialize: table
sql: |
SELECT order_id, customer_id, amount
FROM raw_orders
WHERE amount >= {{ config.min_amount }}
outputs:
- name: fact_orders
connection: warehouse
step: high_value_orders
table: fact_orders
mode: merge
primary_key: [order_id]
runtime:
threads: 4
memory_limit: 1GB
streamwright validate jobs/analytics/orders.yaml
streamwright run jobs/analytics/orders.yaml --set min_amount=100
What it does:
- Fetch:
raw_ordersreadsdata/orders.parquetas a view. - Shape:
high_value_ordersfilters it with SQL, using the config inputmin_amount. - Write: the output
fact_ordersmerges the rows into the warehouse table byorder_id. - Each run is one batch. Add
runtime.stateto keep a history of batches, so a failed run can resume.
Job structure
| Key | What it holds |
|---|---|
kind, version, name, description | kind: DuckDBJob; name is the job's name (also its name in the state store) |
spec | Typed inputs: config, secrets, input (caller rows), lookups |
connections | Where: named places to read from and write to |
steps | Fetch and shape: views and tables built from SQL, file and database reads, API calls, broker messages |
outputs | Write: where the steps' rows go: tables, files, brokers, vendor APIs |
runtime | DuckDB settings, limits, the state store, fan-out |
Inputs
| Key | What it does |
|---|---|
spec.config | Typed values from --set NAME=VALUE (lists as a,b,c) or --config FILE. Templates read {{ config.NAME }} |
spec.secrets | Typed secrets, from --secrets FILE or STREAMWRIGHT_SECRET_<NAME> |
spec.input | Rows the caller hands to the run (--input), read as the table input. See Input payloads |
spec.lookups | Values fetched at run time. See Lookups and fan-out |
Each input takes type, items, required and default. Optional checks (enum, pattern, minimum, maximum,
max_length) run on every value before the run starts. Use them on any input that reaches SQL text.
A secret may appear only in a connection's credential keys. See Accounts & secrets.
Connections
Each connection names a provider plus that provider's keys.
| Provider | Needs |
|---|---|
duckdb (a database file) | Built in |
files (local roots), s3, gcs, azure_blob, postgres, mysql, deltalake | The connector, for example sw connectors -i files |
kafka, sqs | sw connectors -i kafka, sw connectors -i sqs |
An API used by a call step or an apply output (for example google_ads) | The connector itself (sw connectors -i google_ads) |
Steps
Each step builds a view or table. Later steps and outputs read it by its name. The run order comes from the SQL and
the {{ step.column }} references, not from the list order.
| Step | Recognized by | Use it to |
|---|---|---|
sql | sql: with no connection or source | Shape earlier steps with DuckDB SQL |
read | connection + read: | Read files or a database table |
| Call | connection + operation: (or async_job:) | Call an API through a connector, or run a report job |
| Source | source + stream | Run a stream of a StreamWright source folder |
| Consume | a broker connection + operation: {service: message, method: consume} | Read a batch of Kafka or SQS messages |
Any step can also log its rows and take a policy.
SQL steps
A sql step runs DuckDB SQL over earlier steps. It is kept as a view (default) or a table (materialize:), like
high_value_orders in the minimal job. To read a table of a duckdb connection, qualify it
(wh.main.orders) or use a read: table step.
Read steps
| Reads | Keys |
|---|---|
Files: read: parquet, csv, json or auto | path, options, schema_mode, row_index, columns, column_groups, path_pattern, vocabulary, on_unmapped_column |
| Databases | table or query |
path may be a {{ step.column }} reference. A read pins its list of files in the batch, so a retry reads the
same files.
Call steps
A call step asks a connector to run an operation: and loads what comes back. The operation is either:
- a call,
{service, method, arguments}, for SDK connectors such as Google Ads, or - an HTTP request,
{path, method, params, headers, json, records, paginator}, for REST API connectors such asrestapi.
- name: raw_campaigns
connection: ads_api # provider: google_ads
operation:
service: GoogleAdsService
method: search_stream
arguments:
customer_id: "{{ config.customer_id }}"
query: "SELECT campaign.id, campaign.name FROM campaign LIMIT 50"
sql: |
SELECT record->>'$.campaign.id' AS campaign_id,
record->>'$.campaign.name' AS campaign_name
FROM records
- The
recordstable. The step's optionalsqlreads the tablerecords: one JSON column,record, one row per record. Withoutsql, the records' top-level keys become the columns. - Use
->>. It returns text (cast it for other types).->keeps JSON, so a CSV output writes it quoted. - Partitions.
partitions: [{from, field, name}]runs the call once per distinct value offieldin the stepfrom. The call reads the value as{{ partition.NAME }}. These values are not columns ofrecords. - HTTP requests. The connection holds the authentication and the
http:defaults (base_url,headers,paginator, ...). Each REST connector's page has a complete job.
Async reports
A call step can run a report job: use async_job: instead of operation: (one or the other, not both). It takes the
same keys as in a source (Async reports): submit, poll, and exactly one of
results or download. The records land in records like any call step's.
- name: report
connection: ads # provider: microsoft_ads
async_job:
submit:
operation:
service: ReportingService
method: SubmitGenerateReport
arguments:
request:
ReportRequest:
"@type": CampaignPerformanceReportRequest
Aggregation: Daily
Format: Csv
ExcludeReportHeader: true
ExcludeReportFooter: true
Columns: [TimePeriod, AccountId, CampaignId, CampaignName, Impressions, Clicks, Spend]
Scope: {AccountIds: ["{{ config.account_id }}"]}
Time: {PredefinedTime: Yesterday}
poll:
operation: {method: PollGenerateReport, arguments: {request: {ReportRequestId: "{{ submit.result }}"}}}
every: 15s
timeout: 30m
done_when: {path: Status, equals: Success}
fail_when: {path: Status, equals: Error}
download: {url: "{{ poll.ReportDownloadUrl }}", format: csv, compression: zip}
sql: SELECT record->>'CampaignId' AS campaign_id, (record->>'Impressions')::BIGINT AS impressions FROM records
- Each part's
operationis what the connector takes: a call for SDK connectors (Microsoft Ads, Meta Ads), an HTTP request for REST connectors (Amazon Ads,restapi). - Call steps have no incremental windows (
{{ window.* }}works only in sources). Set the range with{{ config.* }}, a relative range of the API (such asPredefinedTime), or step partitions. - Inline or a source? Inline suits a one-off, self-contained job. Use a source stream and a source step when the report is reused, read incrementally (windows, lookback, bookmarks) or shared by several jobs.
Source steps
A source step runs one stream of a source folder (relative to the job's folder). Its records load like a call step's.
- name: raw_events
source: ../../sources/readers/files_demo
stream: events
Consume steps
A consume step reads a batch of messages into the table input. Its optional sql shapes them. A job has at most
one consume step.
- name: orders
connection: bus # provider: kafka
operation:
service: message
method: consume
arguments: {destination: orders, group_id: orders-loader, from: earliest, max_wait: 5s}
sql: |
SELECT message_id, key, json_extract_string(body, '$.id') AS order_id, headers['source'] AS origin
FROM input
| Part | What it holds |
|---|---|
arguments | The connector's (Kafka: destination, group_id, from, options; SQS: the queue URL as destination, visibility_timeout), plus max_messages and max_wait |
The input table | message_id, key, body (the payload as text), headers (a map), timestamp |
Messages are acknowledged only after the outputs commit, so a failed batch keeps them for its retry. To run more than one batch per run, see batch modes.
Logging rows
Any step can log its rows, each batch, so you can check what a job did.
- name: skipped_actions
sql: SELECT i.name, e.resource_name FROM input AS i JOIN existing_actions AS e USING (name)
log:
message: "conversion action {name} already exists ({resource_name}): not created"
max_rows: 50
| Key | What it does | Default |
|---|---|---|
message | Required. {column} placeholders are filled from each row | |
level | debug, info or warning | info |
max_rows | Lines to log (1 to 1000), then one ... and N more row(s) line | 20 |
Lines go to the streamwright.steps logger, redacted. A placeholder for a missing column fails the step. Under
streamwright serve, only the row count is logged.
Policy
Steps and outputs take policy:, with the same keys as a source stream. A key that does not apply where you put it
is a validation error, never silently ignored.
| Key | What it does | Works on |
|---|---|---|
retry: {max_attempts, backoff, max_delay, codes} | Retries a transient failure | Read and call steps, outputs that write or apply |
rate_limit: {requests, per} | Throttles calls | Call steps, apply outputs |
page_size | Pages a call | Call steps |
timeout | Bounds each call, read or write (seconds, or 30s) | sql, read and call steps, outputs that write |
on_error | fail (default) fails the batch; skip and warn let it go on (warn also logs) | sql, read and call steps, outputs that write |
Consume steps and publish outputs take none of these keys.
Outputs
Each output names a connection, and a step or an inline sql. There are three kinds:
- Writes to tables or files (below).
- Apply outputs send rows to vendor APIs (reverse ETL).
- Publish outputs send events to Kafka or SQS.
| Writer | Modes | Other keys |
|---|---|---|
Tables (duckdb, postgres, mysql) | append, overwrite, merge | table, primary_key |
Files (files, s3, gcs, azure_blob) | overwrite, error | path, format (csv, tsv, json, jsonl, parquet), file_name, layout, split_by, partition_by (Hive folders) |
Delta Lake (deltalake) | append, overwrite, merge | condition, when_matched, when_not_matched, when_not_matched_by_source, target_predicate, partition_by, schema_evolution, replace_where or whole_table |
A write needs a mode (overwrite, never replace). A key the writer does not read is an error. Modes that are
not safe to repeat (table append, files error) get a warning.
File names
Without file_name, path is the file. With it, path is a folder and layout adds subfolders. split_by: [col]
writes one file per distinct value, and the names may use {col}.
outputs:
- name: save_orders
connection: lake
step: orders
path: exports/orders
format: csv
mode: overwrite
layout: "{today:%Y/%m}" # exports/orders/2026/11/
file_name: "orders_{today:%Y%m%d}.csv" # orders_20261102.csv
| Placeholder | Renders |
|---|---|
{today}, {today:%Y-%m-%d} | The batch's UTC date (default %Y%m%d) |
{now}, {batch_ts}, {now:%Y%m%d%H%M%S} | The batch's UTC time (default %Y%m%dT%H%M%SZ) |
{today-1d:%Y%m%d}, {now+2h:%H} | A time with offsets (s m h d w; combinable: -1d+6h) |
{batch_id} | The batch's job id |
{seq} | The split's 5-digit number (00001) |
{col}, {col:%Y%m} | A split_by column's value, reduced to [A-Za-z0-9._-] |
Times are pinned when the batch starts, so a retry writes the same names. file_name needs a placeholder and no
/. For a fixed name, set the file as path.
Apply outputs
An apply output sends its rows to a vendor's API, one operation per row, and keeps each row's outcome. Its
operation: names a connector's apply operation (for example Google Ads
ConversionUploadService.upload_click_conversions), and it takes apply: instead of mode. A completed batch is
never sent again. See Apply outputs.
Publish outputs
An output with operation: {service: message, method: publish} on a Kafka or SQS connection sends each row as one
JSON event.
- name: orders_loaded
connection: bus
operation:
service: message
method: publish
arguments: {destination: orders-loaded, key: order_id}
sql: SELECT order_id, origin FROM orders
arguments:destination; for Kafka,key(the column that keys each event); for SQS,group_id.- Delivery: at least once, after the batch's writes commit. Each event has a dedupe key (Kafka: the
dedupe-keyheader). - Limit: at most
runtime.limits.eventsper output per batch. - Events about what was written: select from
_commits(output,table_name,version,rows,batch_id) and_file_errors(step,path,reason).
Runtime and state
runtime sets DuckDB's resources, limits and the state store.
| Key | What it does | Default |
|---|---|---|
threads, memory_limit, temp_directory | DuckDB's settings | DuckDB's |
timeout_seconds | Fails a batch that runs longer | |
limits.events · limits.apply_rows | Most events per publish output · rows per apply output, per batch | 10000 · 100000 |
limits.reference_values · limits.read_groups | Most values of a {{ step.column }} reference · read groups of one file read | 10000 · 200 |
state | The state store (below) | none: nothing is kept |
fan_out | Runs the whole job once per item (account). See Lookups and fan-out | |
log.labels | Labels on every log line of the run. See logging options |
State store
The state store keeps each batch's status, pinned inputs and write receipts. So a failed run resumes where it stopped, and an output that already has a receipt is not written again.
runtime:
state:
sqlite: state.db
failed_job_policy: retry
| Key | What it does | Default |
|---|---|---|
sqlite · connection | A SQLite file, or a duckdb, postgres or mysql connection of the job | |
schema | The schema of the state tables (with connection) | streamwright |
ttl_seconds | How long a run's lease lasts before it must be renewed | 300 |
apply_migrations | Let the run create or upgrade the state tables (for development) | false |
failed_job_policy | What the next run does with a failed batch: abandon, retry or block (below) | abandon |
max_job_attempts | With retry: how many times a batch may start in all | 3 |
job_key | A batch with this key runs once per scope (--job-key overrides it) | |
scope | Separate history and lease per value, for example per client | "" |
lock | Tokens shared across scopes, so two runs never work on the same item (below) |
Migrations are a deploy step. A run never changes the state tables itself: it fails while a migration is
pending. Run streamwright duckdb state migrate JOB when you deploy, or use --apply-state-migrations in development.
Failed batches
The scope's next run handles a failed batch first, as failed_job_policy says:
| Policy | The next run | Suits |
|---|---|---|
abandon (default) | Gives it up and runs new work | Snapshot jobs. Keyed jobs should use retry |
retry | Resumes it with its pinned inputs, up to max_job_attempts, then acts as block | Incremental and broker jobs |
block | Stops (exit 1) until you run state retry or state abandon | Jobs where a person must look at every failure |
Scopes and locks
Each run holds a lease, so one run per scope works at a time. Set scope to run one job for many clients side by
side. When clients share items (an account two clients both list), lock the list input:
runtime:
state:
sqlite: state.db
scope: "client:{{ config.client }}"
lock:
each: "{{ config.accounts }}" # exactly one list input
as: account
token: "account:{{ account }}"
policy: prune # fail (default) | wait | prune
When a token is held:
lock.policy | The run |
|---|---|
fail (default) | Stops (exit 1) |
wait | Retries until lock.timeout |
prune | Runs only the free items. None left: skipped, exit 0 |
For one shared resource, use lock: {token: report}.
Broker batch modes
A job with a consume step runs one batch per run by default.
| Option | Runs batches |
|---|---|
--once (default) | One: unfinished batches resume, then one new batch |
--drain | Until a poll finds no message |
--available-now | Until the messages there at the start are processed (SQS runs as --drain) |
--follow [--interval 10s] | Until interrupted (exit 130), pausing after an empty poll |
--max-messages N (default 500) caps a batch. 0 only resumes unfinished batches.
Commands
streamwright validate | plan | run JOB send a DuckDBJob to this engine. Validation never needs secret values.
streamwright run orders.yaml --secrets secrets.yaml # run; unfinished batches resume first
streamwright duckdb state list orders.yaml # list batches
streamwright duckdb state retry orders.yaml JOB_ID # resume a failed batch, then run
streamwright duckdb state ACTION JOB looks after batches:
| Action | Does |
|---|---|
list · status · show JOB_ID | List batches · the scope's newest batch · one batch, with its pinned inputs and receipts |
retry JOB_ID · abandon JOB_ID | Resume a failed batch, then run · give it up |
resend JOB_ID · skip JOB_ID | Apply rows with an unknown outcome: send again on the next run · mark failed. See Apply outputs |
leases · purge · migrate · check | Lease tokens · delete old finished batches · create or upgrade the state tables · exit 1 while a migration is pending |
See the CLI reference for every option and state action.
Other ways to run
The same job file runs from the CLI (streamwright run or sw run), or as a service:
streamwright serve takes HTTP requests and streamwright worker runs them. To run the same steps on
Spark, write kind: SparkJob (SparkJob).
Folders and overlays
Keep one folder per system and one file per job, named for what it produces (analytics/orders.yaml, with
name: analytics_orders). Put environment overlays, such as orders.local.yaml, beside the job and pass them with
--overlay. An overlay is merged onto the job; a null deletes a key.
streamwright run jobs/analytics/orders.yaml --overlay jobs/analytics/orders.local.yaml
See also
- Lookups and fan-out: fetch credentials at run time and run once per account.
- Input payloads: hand a run its rows.
- Apply outputs: send rows to vendor APIs.
- Serving jobs over HTTP: run jobs through a REST API.
- SparkJob: the same manifest on Spark.