Maintain an enriched table from changed records with Permute

Workflow

1Create one Data Table connected to the materialized source, using the data-flow guide. A flow may contain just this operation; separate snapshot, comparison, and accumulation operations are unnecessary.
2Choose the source table, stable unique non-null key columns, and every input column the transformation uses. This first version accepts exactly one declared input and one output row per key.
3Save the handler and incremental settings together through operations.updateSource. The handler queries input, which contains only new or changed rows. Return all its keys with unchanged values.
4Run version: draft and inspect the full output. Draft runs and test_code backfill current inputs without advancing published progress. On an empty first input, return a native table reference so the output schema is known.
5Publish the tested operation or its parent flow using the data-flow guide. Run version: published or schedule the parent. The first published run backfills all current rows; later runs reuse accepted results.
6Inspect the returned workflowId before retrying an uncertain request. Each successful operation advances its own progress, even when a later operation fails. Handler failures preserve the last published table; storage publication failures have the limits below.

Complete example

typescript

const current = await client.operations.getSource(operationId);
// Connect this source through the data-flow guide before saving.
const sourceId = current.dataSourceIds[0] ?? current.dependencyOperationIds[0];
await client.operations.updateSource(operationId, {
  incremental: {
    sourceId,
    table: 'customers',
    keyColumns: ['id'],
    inputColumns: ['name'],
  },
  source: `module.exports = {
    handler: event => event.query(
      'SELECT id, upper(name) AS normalized_name FROM input',
      { result: 'tableReference' }
    )
  };`,
  expectedRevision: current.revision,
  expectedDataFlowRevision: current.dataFlowRevision,
  expectedUpdatedAt: current.updatedAt,
});
const started = await client.operations.run(operationId, { version: 'draft' });
// Inspect listRuns({ workflowId: started.workflowId }) and getRunOutput.
// Publish through the data-flow guide, then run version: 'published'.

One handler, one complete result

Setting	Contract
sourceId	The sole declared data source or prerequisite operation. Existing subscription resolution preserves the source workspace and permissions.
table	An exact table name or block ID identifying one complete materialized table. Prefer a stable table name.
keyColumns	Distinct existing columns forming a unique non-null key; composite keys are supported.
inputColumns	Every column used by the handler. Keys are included automatically. Changes in untracked columns do not cause work.
incremental: null	Disable incremental mode when saving source. Omitting the setting preserves the current configuration.

Incremental runs queue input capture before execution. The worker captures the declared key and input columns into a run-owned BlockDocument before starting the handler; source data is observed at preparation time, not when the run was requested. On the same operation revision it compares that capture with the last accepted published input, using typed equality. The handler sees only creates and updates in input; an update is a change in any tracked column. Row order and column order do not count as changes.

Return exactly one row for each supplied key, including every key column. A native table reference preserves types and supports empty inputs. Row arrays are supported for nonempty results; DuckDB BIGINT values arrive as lossless strings, so preserve their values instead of coercing them to JavaScript numbers.

The runtime replaces changed keys, retains unchanged accepted rows, and removes keys absent from the current complete source capture. Its published output table is named output. A no-change run skips the handler and still publishes the complete table (a deletion-only run also removes the missing keys). Normal queries and historical outputs see complete results, not batches that the caller must reconstruct.

Progress, rebuilds, and retries

Progress belongs to the operation, across parent flows. A completed published run with an accepted output becomes the next baseline without waiting for downstream work. A failed operation does not advance; a new run retries against the last accepted baseline and a fresh source capture. Duplicate dispatch retains its pinned capture and prior output. Concurrent materializations fail visibly; retry after the active run finishes. If progress changes while capturing input, start a new run rather than publishing that stale capture.

The handler and incremental settings are part of the immutable operation revision. A different revision rebuilds all current rows, including when restoring an earlier revision. The previous accepted run remains available during rebuilding. Draft and preview runs never replace published results or advance their baseline; test_code evaluates a full backfill without live progress.

Every execution attempt writes its own BlockDocument. Completion accepts one output and publishes its immutable run alias before releasing downstream work. Keep accepted output files and captured inputs while future runs depend on them. Missing history causes failure; restore the files or deliberately save a new revision to rebuild current input. A normal retry never silently resets the table or assumes no changes.

Failure modes and scope

Condition	Behavior or recovery
Input capture failure or queue delay	Input capture runs asynchronously and may wait behind other operation jobs. A capture failure or exhausted capture retries fails the run through normal completion; inspect its error before starting a new run. A retry of the same run keeps its first pinned capture and does not observe newer source data.
Duplicate or null source keys	The run fails before transformation. Fix source identity; do not choose an unstable row number as the key.
Missing, duplicate, or altered output keys	The run fails without publishing a partial table. Return one row for every input key and preserve key values.
Schema changes on an unchanged revision	The run fails. Save a new operation revision and rebuild; optional enrichment fields should have declared, stable SQL types.
Empty first input	Return a typed native query result. An empty row array alone cannot establish the output schema.
Partial pages, moving windows, or stale sources	Missing rows are treated as deletions; retained stale rows may look unchanged. Ensure the captured table covers the intended complete scope and check upstream sync status. An unchanged result does not prove freshness.
Handler failure or uncertain external effect	The previous published result remains available. Execution can repeat, so investigate effects before retrying; this feature does not provide exactly-once delivery.
Downstream failure	The completed enrichment stays accepted. Retry the failed step using its prerequisite output; restarting the flow will skip unchanged enrichment input.
Warehouse publication fails	The run fails without advancing progress. Stable latest aliases use multi-object copies, not atomic storage transactions; an interrupted copy can leave that alias inconsistent. Inspect the last completed run output and repair storage before retrying. Accepted attempt documents remain immutable.
Remote or schema-only source	Direct incremental capture fails. Materialize a complete table through the source adapter or an explicit capture operation first.

This is snapshot-based incremental row transformation. It reports net observed changes and cannot recover transitions that occur and revert between captures. Arbitrary aggregates, cross-row rankings, joins against changing second inputs, and time-dependent transformations require full recomputation or a different declared maintenance strategy.

Materialized connector, dataset, subscription, and prerequisite outputs use the same BlockDocument path. BigQuery, Supabase, and managed database live-query tables need capture before this mode can use them; native change-feed subscriptions are outside this first version. The handler retains the existing sandbox capabilities, so incremental mode does not add an LLM client, network access, or approval for external actions.