Streams & tasks

Streams and tasks are the engine behind standing questions: streams detect change with zero stored data, tasks act on it on a schedule or a gate — so an agent's question keeps answering itself as new data lands.

Streams — CDC as version diffs

A stream is a tiny metadata object: an offset into the source table's version timeline. No change data is ever stored — querying the stream computes the delta between the offset version and the current version from the immutable file sets:

• - rows in files added since the offset → candidate inserts
• - rows in files removed since the offset → candidate deletes
• - identical rows on both sides cancel out (multiset EXCEPT ALL) — they were

merely rewritten by copy-on-write DML, not changed

CREATE STREAM s ON TABLE t;             -- offset = t's current version
SELECT * FROM s;                        -- source columns + metadata columns
SELECT SYSTEM$STREAM_HAS_DATA('S');     -- cheap: compares two version numbers

Metadata columns: METADATA$ACTION (INSERT/DELETE), METADATA$ISUPDATE (always FALSE here — see divergences), METADATA$ROW_ID (content hash).

Consumption: the offset advances only when a DML statement reading the stream commits — INSERT INTO tgt SELECT … FROM s or CREATE TABLE x AS SELECT … FROM s. Reading alone never advances it, so a consumer that fails leaves the stream intact.

Append-only streams (APPEND_ONLY = TRUE) see every inserted row — even rows deleted afterwards — and ignore deletes entirely. For insert-only tables (the usual EL landing pattern) they are exact and cheapest.

This tracks the Snowflake stream contract. One divergence: Snowflake pairs update rows via hidden change-tracking row ids; without per-row lineage an UPDATE appears here as a DELETE+INSERT pair.

Tasks — scheduled statements and DAGs

CREATE TASK load_raw
  SCHEDULE = '5 MINUTES'
  WHEN SYSTEM$STREAM_HAS_DATA('S')
  AS INSERT INTO staged SELECT * FROM s;

CREATE TASK refresh_marts AFTER load_raw
  AS EXECUTE PIPE nightly_sync;          -- tasks can run pipes too

ALTER TASK load_raw RESUME;              -- tasks are created SUSPENDED
EXECUTE TASK load_raw;                   -- manual run of the whole graph

• - SCHEDULE — '<n> SECONDS|MINUTES|HOURS' intervals (root tasks only).
• - AFTER parent — the child runs when the parent succeeds; graphs cascade.
• - WHEN — a SYSTEM$STREAM_HAS_DATA gate evaluated as pure metadata; a

closed gate records a SKIPPED run and never spins up a warehouse.

• - Runs are recorded in task_runs (visible in the UI's Tasks pane).

The scheduler

The scheduler runs inside obelisk ui (a tick every 5 seconds). Without the UI running, EXECUTE TASK and session.tasks.tick() still work — cron can call obelisk ingest run / a Python one-liner if you want headless scheduling.

Standing questions

Chain a pipe, a stream, and a WHEN-gated task and a question answers itself: new rows land, the stream reports the delta as metadata, the gate opens, the task refreshes the downstream transform. The gate stays metadata-cheap when nothing changed, so an always-on standing question costs almost nothing at rest. Persist the answers it produces under Insights & memory, and keep the definitions it computes consistent through the Metrics & semantic layer.