Skip to main content

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.

In short

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 accounts lookup selects the active accounts, filtered by tenant_id or account_ids when you give them.
  • fan_out runs the job once per row (a pass), four at a time. In each pass, account_id, login_customer_id and refresh_token come from that row; refresh_token is decrypted by a Python function first.
  • client_secret comes 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​

  1. An input binds a lookup with lookup: {from: NAME, field: PATH}, in spec.config or spec.secrets.
  2. An explicit value wins: --set, --config, --secrets or STREAMWRIGHT_SECRET_*. A lookup that nothing needs never runs.
  3. Lookups run before anything connects. A lookup that reads the fan-out item runs once per pass; the others run once per run.
  4. With fan_out, each pass is a complete run in its own scope, with its own failed_job_policy, attempts and leases. One account's failure does not stop the others.

Binding an input​

KeyWhat it doesDefault
fromA lookup of spec.lookups, or the fan-out item (its as name)
fieldA 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​

typeKeysNotes
databaseconnection (required), query (required), params, rows, secret_fields, transformsconnection 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.
commandcommand (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.
functionfunction (required, module:function), argumentsNeeds --allow-lookup-exec.
aws_secretsmanagersecret_id (required), region

Every lookup also takes format, which says how its value is read:

formatReads the value as
auto (default)JSON when it is JSON (a JSON string holding JSON is unwrapped), else text
textText, never parsed
jsonJSON
json_stringJSON 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 typeKeys
functionfunction (module:function), arguments ({{ row.COLUMN }} is the row's column); needs --allow-lookup-exec
commandcommand, stdin (for example {{ row.encrypted_token }}), timeout; needs --allow-lookup-exec
builtinapply (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​

KeyWhat it doesDefault
overRequired. A rows: many lookup, or a list input of spec.config
asThe item's name; inputs bind it with lookup: {from: <as>, field: COLUMN}account
keyThe row's column that names a passThe first column
concurrencyPasses at once (--fan-out-concurrency N)1
on_failurecontinue: the other items still run; stop: no new pass starts after a failure (--fan-out-on-failure)continue
on_emptyNo 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_fields column, 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, validate warns (driver-not-installed, an error with --strict), and run stops before any lookup, naming what to install. A connection that only lookups use needs no engine adapter.
  • Validation checks what a binding names, that over is a rows: many lookup 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 serve never lets a caller set an input that has a lookup:: a caller's value would skip the lookup and could name any account.

See also​