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.
A stream is fetch -> shape -> write: requests fetch records, transform shapes them with DuckDB SQL, and
export writes them.
- 1. Fetch
requestsRead records from an API, files or a database. The connector signs in, pages and retries.
- 2. Shape
transformReshape records with DuckDB SQL: filter, join, cast types, compute columns.
- 3. Write
exportWrite files, DuckDB or DuckLake, Singer or dlt, or send rows back to an ad platform or REST API.
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_campaignscalls the API through the source's connection. The connector handles sign-in, paging and retries.cleaned_campaignscasts types, renames fields and keeps active campaigns.campaignsis the dataset the run writes, keyed byid.
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 type | operation keys |
|---|---|
| REST API | path, method, params, headers, json, records, paginator |
| SDK or database | service, 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.
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
submitstarts the report. Its response is{{ submit.* }}, so{{ submit.id }}is the report's id.pollruns every 30 seconds untilstatusisCOMPLETED.FAILED, or no result after an hour, fails the request like an API error.downloadreads the finished file. Each CSV row becomes one record, read by the transform like any request.
| Key | What it does | Required / default |
|---|---|---|
submit.operation | Starts the report. Its response is {{ submit.* }} | Required |
poll.operation | Checks the report. Its response is {{ poll.* }} | Required |
poll.every, poll.timeout | Time between checks, and how long to wait in all (30s, 3h) | Required |
poll.done_when | When the report is ready: { path, equals } or { path, in: [...] } | Required |
poll.fail_when | When the report has failed, in the same form | Optional |
download | Fetch a file: url, format (csv, jsonl or json), compression (none, zip or gzip) | One of download or results |
results | Read 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.servicedefaults 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_statusandchecks.
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'.
mode | Steps run... | Use it for |
|---|---|---|
page | On each page of records, as it arrives. Exactly one request. | Shaping records page by page, with flat memory use |
run | Once, 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
| Key | What it does |
|---|---|
step | The step whose rows are written |
primary_key | Optional. Key columns, used to de-duplicate and merge |
file_name | Optional. 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:
- The run's
--file-name. - The export's own
file_name. - The default in
source.yaml, such asexport: { file_name: "{{ source }}/{{ export }}_{{ today }}.{{ format }}" }. - 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 }}"
| Key | What it does |
|---|---|
cursor_field | The export column that marks how far the stream has read |
start | The first day to read: today, -7d, 2026-01-01, or a reference such as {{ config.start_date }} |
window | Optional. Days per window, at least 1d. Without it, one window runs from the start up to today |
lookback | Optional. 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
| Partition | One request per... |
|---|---|
values | Item of a list, or a list input such as {{ config.account_ids }} |
from_stream + field | Distinct 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 |