Skip to main content

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.

In short

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​

jobs/analytics/orders.yaml
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_orders reads data/orders.parquet as a view.
  • Shape: high_value_orders filters it with SQL, using the config input min_amount.
  • Write: the output fact_orders merges the rows into the warehouse table by order_id.
  • Each run is one batch. Add runtime.state to keep a history of batches, so a failed run can resume.

Job structure​

KeyWhat it holds
kind, version, name, descriptionkind: DuckDBJob; name is the job's name (also its name in the state store)
specTyped inputs: config, secrets, input (caller rows), lookups
connectionsWhere: named places to read from and write to
stepsFetch and shape: views and tables built from SQL, file and database reads, API calls, broker messages
outputsWrite: where the steps' rows go: tables, files, brokers, vendor APIs
runtimeDuckDB settings, limits, the state store, fan-out

Inputs​

KeyWhat it does
spec.configTyped values from --set NAME=VALUE (lists as a,b,c) or --config FILE. Templates read {{ config.NAME }}
spec.secretsTyped secrets, from --secrets FILE or STREAMWRIGHT_SECRET_<NAME>
spec.inputRows the caller hands to the run (--input), read as the table input. See Input payloads
spec.lookupsValues 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.

warning

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.

ProviderNeeds
duckdb (a database file)Built in
files (local roots), s3, gcs, azure_blob, postgres, mysql, deltalakeThe connector, for example sw connectors -i files
kafka, sqssw 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.

StepRecognized byUse it to
sqlsql: with no connection or sourceShape earlier steps with DuckDB SQL
readconnection + read:Read files or a database table
Callconnection + operation: (or async_job:)Call an API through a connector, or run a report job
Sourcesource + streamRun a stream of a StreamWright source folder
Consumea 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​

ReadsKeys
Files: read: parquet, csv, json or autopath, options, schema_mode, row_index, columns, column_groups, path_pattern, vocabulary, on_unmapped_column
Databasestable 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 as restapi.
- 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 records table. The step's optional sql reads the table records: one JSON column, record, one row per record. Without sql, 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 of field in the step from. The call reads the value as {{ partition.NAME }}. These values are not columns of records.
  • 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 operation is 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 as PredefinedTime), 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
PartWhat it holds
argumentsThe connector's (Kafka: destination, group_id, from, options; SQS: the queue URL as destination, visibility_timeout), plus max_messages and max_wait
The input tablemessage_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
KeyWhat it doesDefault
messageRequired. {column} placeholders are filled from each row
leveldebug, info or warninginfo
max_rowsLines to log (1 to 1000), then one ... and N more row(s) line20

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.

KeyWhat it doesWorks on
retry: {max_attempts, backoff, max_delay, codes}Retries a transient failureRead and call steps, outputs that write or apply
rate_limit: {requests, per}Throttles callsCall steps, apply outputs
page_sizePages a callCall steps
timeoutBounds each call, read or write (seconds, or 30s)sql, read and call steps, outputs that write
on_errorfail (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:

WriterModesOther keys
Tables (duckdb, postgres, mysql)append, overwrite, mergetable, primary_key
Files (files, s3, gcs, azure_blob)overwrite, errorpath, format (csv, tsv, json, jsonl, parquet), file_name, layout, split_by, partition_by (Hive folders)
Delta Lake (deltalake)append, overwrite, mergecondition, 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
PlaceholderRenders
{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-key header).
  • Limit: at most runtime.limits.events per 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.

KeyWhat it doesDefault
threads, memory_limit, temp_directoryDuckDB's settingsDuckDB's
timeout_secondsFails a batch that runs longer
limits.events · limits.apply_rowsMost events per publish output · rows per apply output, per batch10000 · 100000
limits.reference_values · limits.read_groupsMost values of a {{ step.column }} reference · read groups of one file read10000 · 200
stateThe state store (below)none: nothing is kept
fan_outRuns the whole job once per item (account). See Lookups and fan-out
log.labelsLabels 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
KeyWhat it doesDefault
sqlite · connectionA SQLite file, or a duckdb, postgres or mysql connection of the job
schemaThe schema of the state tables (with connection)streamwright
ttl_secondsHow long a run's lease lasts before it must be renewed300
apply_migrationsLet the run create or upgrade the state tables (for development)false
failed_job_policyWhat the next run does with a failed batch: abandon, retry or block (below)abandon
max_job_attemptsWith retry: how many times a batch may start in all3
job_keyA batch with this key runs once per scope (--job-key overrides it)
scopeSeparate history and lease per value, for example per client""
lockTokens 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:

PolicyThe next runSuits
abandon (default)Gives it up and runs new workSnapshot jobs. Keyed jobs should use retry
retryResumes it with its pinned inputs, up to max_job_attempts, then acts as blockIncremental and broker jobs
blockStops (exit 1) until you run state retry or state abandonJobs 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.policyThe run
fail (default)Stops (exit 1)
waitRetries until lock.timeout
pruneRuns 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.

OptionRuns batches
--once (default)One: unfinished batches resume, then one new batch
--drainUntil a poll finds no message
--available-nowUntil 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:

ActionDoes
list · status · show JOB_IDList batches · the scope's newest batch · one batch, with its pinned inputs and receipts
retry JOB_ID · abandon JOB_IDResume a failed batch, then run · give it up
resend JOB_ID · skip JOB_IDApply rows with an unknown outcome: send again on the next run · mark failed. See Apply outputs
leases · purge · migrate · checkLease 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​