Skip to main content

Streams

A stream is one dataset a source produces, such as campaigns or daily metrics. Each file in streams/ defines one stream. Add a stream when you want one more dataset from the same system.

In short

A stream is fetch -> shape -> write: requests fetch records, transform shapes them with DuckDB SQL, and export writes them.

  1. 1. Fetchrequests

    Read records from an API, files or a database. The connector signs in, pages and retries.

  2. 2. Shapetransform

    Reshape records with DuckDB SQL: filter, join, cast types, compute columns.

  3. 3. Writeexport

    Write files, DuckDB or DuckLake, Singer or dlt, or send rows back to an ad platform or REST API.

streams/campaigns.yaml
requests: # fetch
- name: raw_campaigns
operation:
path: /v1/accounts/{{ config.account_id }}/campaigns
method: GET
transform: # shape
mode: page
steps:
- name: cleaned_campaigns
select: |
SELECT
(record->>'id')::BIGINT AS id,
record->>'name' AS name,
(record->>'total')::DECIMAL(18,2) AS total,
record->>'status' AS status
FROM raw_campaigns
WHERE status = 'ACTIVE'
export: # write
campaigns:
step: cleaned_campaigns
primary_key: [id]
  • raw_campaigns calls the API through the source's connection. The connector handles sign-in, paging and retries.
  • cleaned_campaigns casts types, renames fields and keeps active campaigns.
  • campaigns is the dataset the run writes, keyed by id.

Requests: fetch​

requests read raw records. Each request has a name and an operation (what to invoke). A REST request names a path; an SDK or database request names a service and a method.

requests:
- name: raw_campaigns # a REST request
operation:
path: /v1/accounts/{{ config.account_id }}/campaigns
method: GET
- name: raw_orders # a database or SDK request
operation:
service: database
method: query
arguments:
query: SELECT id, total::VARCHAR AS total FROM orders WHERE updated_at >= $since
params: { since: "{{ window.start }}" }
Connector typeoperation keys
REST APIpath, method, params, headers, json, records, paginator
SDK or databaseservice, method, arguments

Values such as $since are bound parameters. They are passed to the database separately, never pasted into the query text.

A stream's own policy: (how: retry, rate_limit, timeout, on_error) replaces the source's defaults for its requests.

Async reports​

Some APIs build reports in the background: you start a report, check it until it is ready, then fetch it. Use async_job instead of operation for such a request. Ad platforms such as Microsoft Ads, Meta Ads and Amazon Ads work this way.

streams/daily_report.yaml
requests:
- name: raw_report
async_job:
submit: # 1. start the report
operation:
path: /v1/reports
method: POST
json: { start: "{{ window.start }}", end: "{{ window.end }}" } # see Windows below
poll: # 2. check it until it is ready
operation:
path: /v1/reports/{{ submit.id }}
every: 30s
timeout: 1h
done_when: { path: status, equals: COMPLETED }
fail_when: { path: status, equals: FAILED }
download: { url: "{{ poll.url }}", format: csv, compression: gzip } # 3. read the file
  • submit starts the report. Its response is {{ submit.* }}, so {{ submit.id }} is the report's id.
  • poll runs every 30 seconds until status is COMPLETED. FAILED, or no result after an hour, fails the request like an API error.
  • download reads the finished file. Each CSV row becomes one record, read by the transform like any request.
KeyWhat it doesRequired / default
submit.operationStarts the report. Its response is {{ submit.* }}Required
poll.operationChecks the report. Its response is {{ poll.* }}Required
poll.every, poll.timeoutTime between checks, and how long to wait in all (30s, 3h)Required
poll.done_whenWhen the report is ready: { path, equals } or { path, in: [...] }Required
poll.fail_whenWhen the report has failed, in the same formOptional
downloadFetch a file: url, format (csv, jsonl or json), compression (none, zip or gzip)One of download or results
resultsRead the results with an operation, paged like a request (with records and paginator)One of download or results

Each part's operation takes the same keys as a request's: an HTTP request for REST connectors, or a call (service, method, arguments) for SDK connectors. Each ads connector page has a report template.

Good to know:

  • Presigned downloads: the file URL is fetched without the connection's sign-in or headers, because report links (such as S3 links) carry their own access.
  • No data is not an error: an empty download URL, or an empty file, gives 0 rows.
  • Plain responses: a submit response that is a single value (just an id) is {{ submit.result }}.
  • The poll's service: in a call, poll.operation.service defaults to the submit's. Set it when the poll reads another object (Meta Ads polls the report run, not the account).
  • In jobs: a DuckDB or Spark call step can take the same async_job.
  • Logs: each job logs at INFO when it is submitted (with its request id), at every status check that is not done yet, and when it is done (with the number of checks and the time taken). JSON logs carry them as job_request, job_status and checks.

Transform: shape​

transform steps reshape records with DuckDB SQL: filter, cast types, compute columns, and join a stream's requests. Each request is a table named after it. The record is in its JSON column record; read a field with record->>'field'.

modeSteps run...Use it for
pageOn each page of records, as it arrives. Exactly one request.Shaping records page by page, with flat memory use
runOnce, over everything the run read. One or more requests.Joining requests, partitions that come from a step, run totals

A step can read its own stream's requests and earlier steps only, not other streams. To drive one stream from another's rows, use a from_stream partition (see Partitions).

Export: write​

export names the datasets to write and their keys. Each export becomes a file, a table or a Singer stream in the output you choose at run time.

export:
campaigns:
step: cleaned_campaigns
primary_key: [id]
file_name: "{{ config.client }}/campaigns_{{ today }}.csv" # optional: the file's path in a file output
KeyWhat it does
stepThe step whose rows are written
primary_keyOptional. Key columns, used to de-duplicate and merge
file_nameOptional. The export's path inside DIR when the run writes files (--output csv:DIR, jsonl:DIR, tsv:DIR or parquet:DIR). Folders are allowed. Other outputs ignore it.

file_name is a template. It can use {{ export }}, {{ source }}, {{ today }}, {{ timestamp }}, {{ format }} (the output's extension) and {{ config.NAME }}. Which name wins:

  1. The run's --file-name.
  2. The export's own file_name.
  3. The default in source.yaml, such as export: { file_name: "{{ source }}/{{ export }}_{{ today }}.{{ format }}" }.
  4. With none of these, <export>.<date>.<time>.<unique>.<ext>.

Windows and batches​

An incremental stream reads one window of days at a time, so a daily run reads only new data. Declare incremental and use {{ window.start }} and {{ window.end }} in a request:

incremental:
cursor_field: date
start: "{{ config.start_date }}"
window: 1d
requests:
- name: raw_metrics
operation:
path: /v1/metrics
method: GET
params:
from: "{{ window.start }}"
to: "{{ window.end }}"
KeyWhat it does
cursor_fieldThe export column that marks how far the stream has read
startThe first day to read: today, -7d, 2026-01-01, or a reference such as {{ config.start_date }}
windowOptional. Days per window, at least 1d. Without it, one window runs from the start up to today
lookbackOptional. Days before the bookmark to read again, such as 3d

The run saves a bookmark per stream (and per partition) in its state. The next run continues from the day after it.

A batch reads many ids per request. A request partition with batch_size binds a list of up to that many ids, one list per request:

partitions:
- {name: ids, from: raw_orders, field: id, batch_size: 500}

Partitions: one request per value​

A partition repeats a stream's requests once per value, read as {{ partition.NAME }}.

partitions:
- name: account_id
values: "{{ config.account_ids }}" # a list input
requests:
- name: raw_accounts
operation:
path: /v1/accounts/{{ partition.account_id }}
method: GET
PartitionOne request per...
valuesItem of a list, or a list input such as {{ config.account_ids }}
from_stream + fieldDistinct value of a field in another stream's records (that stream runs first)
from + field (on a request)Distinct value in an earlier request or step of the same stream; add batch_size to send lists

See also​

  • Sources: source.yaml, inputs and client settings.
  • Outputs: where exports are written.
  • CLI: --stream, --state and --file-name.