Lookups and fan-out
A lookup fetches an input's value when the job runs, instead of you passing it up front: from a database, a
command, a Python function or AWS Secrets Manager. Fan-out (runtime.fan_out) then runs the whole job once per
item, such as an account. Together they let one job serve one account, several, or every account of a tenant.
Both engines support this (kind: DuckDBJob and kind: SparkJob); a SparkJob runs its accounts one at a time.
spec.lookups says where values come from, lookup: {from, field} binds an input to one, and runtime.fan_out
runs the job once per row.
Example
spec:
config:
tenant_id: {type: string, required: false}
account_ids: {type: list, items: string, required: false}
account_id: {type: string, lookup: {from: account, field: account_id}} # the pass's row
login_customer_id: {type: string, lookup: {from: account, field: manager_id}}
secrets:
client_secret: {type: string, lookup: {from: app_secrets, field: credentials.clientSecret}} # once per run
refresh_token: {type: string, lookup: {from: account, field: refresh_token}} # per account
lookups:
accounts:
type: database # database | command | function | aws_secretsmanager
connection: creds_db # a postgres, mysql, duckdb or sqlite connection of the job
query: |
SELECT account_id, manager_id, refresh_token_enc FROM accounts
WHERE status = 'active' AND (CAST(:tenant_id AS text) IS NULL OR tenant_id = :tenant_id)
AND (CAST(:account_ids AS text) IS NULL OR account_id IN :account_ids)
params: {tenant_id: "{{ config.tenant_id }}", account_ids: "{{ config.account_ids }}"}
rows: many # one (default: exactly one row) | many (for fan_out)
secret_fields: [refresh_token_enc]
transforms: # per row, when its pass starts: function | command (stdin) | builtin
refresh_token: {type: function, function: "acme.crypto:decrypt",
arguments: {ciphertext: "{{ row.refresh_token_enc }}"}}
app_secrets:
type: command # an argument list, never a shell
command: [secrets-cli, read, ads/app]
format: auto # auto | text | json | json_string
runtime:
fan_out: {over: accounts, as: account, concurrency: 4, on_failure: continue, on_empty: fail}
state:
sqlite: state.db
scope: "account:{{ config.account_id }}" # required with fan_out: each account keeps its own state
What it does:
- The
accountslookup selects the active accounts, filtered bytenant_idoraccount_idswhen you give them. fan_outruns the job once per row (a pass), four at a time. In each pass,account_id,login_customer_idandrefresh_tokencome from that row;refresh_tokenis decrypted by a Python function first.client_secretcomes from a command, once per run, and is shared by every pass.- Each pass keeps its own state, because the scope names the account.
Run it for one account, several, or a whole tenant:
sw run job.yaml --set account_ids=a1 --allow-lookup-exec # one account
sw run job.yaml --set account_ids=a1,a2 --allow-lookup-exec # several
sw run job.yaml --set tenant_id=t1 --allow-lookup-exec --summary # every active account of a tenant
sw spark run job.yaml --set tenant_id=t1 --allow-lookup-exec # Spark (forwarded to the driver)
sw duckdb state list job.yaml --scope account:a1 # one account's batches
How it works
- An input binds a lookup with
lookup: {from: NAME, field: PATH}, inspec.configorspec.secrets. - An explicit value wins:
--set,--config,--secretsorSTREAMWRIGHT_SECRET_*. A lookup that nothing needs never runs. - Lookups run before anything connects. A lookup that reads the fan-out item runs once per pass; the others run once per run.
- With
fan_out, each pass is a complete run in its own scope, with its ownfailed_job_policy, attempts and leases. One account's failure does not stop the others.
Binding an input
| Key | What it does | Default |
|---|---|---|
from | A lookup of spec.lookups, or the fan-out item (its as name) | |
field | A dot path or column in the value: credentials.clientId, items[0].id. It also reads into text that holds JSON. | The whole value |
Lookup types
type | Keys | Notes |
|---|---|---|
database | connection (required), query (required), params, rows, secret_fields, transforms | connection is a postgres, mysql, duckdb or sqlite connection of the job. rows: one (default) expects exactly one row; rows: many returns a list, for fan_out. |
command | command (required), stdin, timeout (default 30s) | command is an argument list, never a shell. Pass sensitive values on stdin, not in the arguments. Needs --allow-lookup-exec. |
function | function (required, module:function), arguments | Needs --allow-lookup-exec. |
aws_secretsmanager | secret_id (required), region |
Every lookup also takes format, which says how its value is read:
format | Reads the value as |
|---|---|
auto (default) | JSON when it is JSON (a JSON string holding JSON is unwrapped), else text |
text | Text, never parsed |
json | JSON |
json_string | JSON serialized inside a JSON string |
Query parameters
:name parameters are bound from params. After IN, a list expands to (?, ?, ...) and a missing one becomes
(NULL); elsewhere a missing one is NULL. Cast it where the database must know its type, for example
CAST(:ids AS text) IS NULL on PostgreSQL.
Transforms
Transforms compute a value from each row of a database lookup, for example to decrypt or decode it. They run when the row is used (when its pass starts), and their input and output are masked.
Transform type | Keys |
|---|---|
function | function (module:function), arguments ({{ row.COLUMN }} is the row's column); needs --allow-lookup-exec |
command | command, stdin (for example {{ row.encrypted_token }}), timeout; needs --allow-lookup-exec |
builtin | apply (base64, base64url, hex or json) and value (for example {{ row.b64_secret }}) |
Templates
A lookup's templates read {{ config.* }}, {{ secrets.* }} and the fan-out item's {{ <as>.COLUMN }}.
Transforms also read {{ row.COLUMN }}.
Fan-out
| Key | What it does | Default |
|---|---|---|
over | Required. A rows: many lookup, or a list input of spec.config | |
as | The item's name; inputs bind it with lookup: {from: <as>, field: COLUMN} | account |
key | The row's column that names a pass | The first column |
concurrency | Passes at once (--fan-out-concurrency N) | 1 |
on_failure | continue: the other items still run; stop: no new pass starts after a failure (--fan-out-on-failure) | continue |
on_empty | No item: fail (exit 1) or skip (exit 0, a warning) | fail |
runtime.state.scope must name the item, so each pass keeps its own history.
The run exits 0 when every pass succeeds, else 1. Each failed pass logs a WARNING line, and --summary lists
passes (item, status, job ids, outputs, error).
Good to know
- Allow code to run. Command and function lookups and transforms need
--allow-lookup-exec; without it the run exits 2. - Secrets stay masked. Every value bound to a secret, every
secret_fieldscolumn, and every transform's input and output is masked in logs, errors, summaries and state. A retry looks the credentials up again. - Database drivers. A database lookup opens its connection with a Python DB-API driver (PostgreSQL:
psycopg, MySQL:pymysql), separate from the connector's engine adapter. When the driver is missing,validatewarns (driver-not-installed, an error with--strict), andrunstops before any lookup, naming what to install. A connection that only lookups use needs no engine adapter. - Validation checks what a binding names, that
overis arows: manylookup or a list input, and that the scope names the item. - State actions (
sw duckdb state ...) run only the lookups the state connection needs. - Serving.
streamwright servenever lets a caller set an input that has alookup:: a caller's value would skip the lookup and could name any account.
See also
- DuckDB jobs: scopes, locks and failed batches.
- Accounts & secrets.