derivation-basics

derivation-basics

Foundation for every Estuary derivation. Each derivation-* skill assumes you've read this and jumps straight to its specific use case.

Docs: https://docs.estuary.dev/concepts/derivations/ — canonical concept page. This skill distills what you actually need to get started.

What a derivation is

A derivation is a collection produced by continuously transforming one or more source collections. It consists of:

• An output collection with its own schema and key
• A catalog task running one or more transforms. Each transform invokes a lambda — the user-written function that maps a source document to zero, one, or many output documents. In SQLite, the lambda is a block of SQL (written inline as lambda: or referenced from a .sql file). In TypeScript and Python, the lambda is a method on a class and the spec references the file via module: instead.
• Optional internal state: SQLite tables declared in migrations (SQL statements run once at derivation startup to create the state tables), or reduction annotations in the output schema, used for aggregations, joins, and windowing

Derivations run continuously and keep up with source updates in real time. They are not batch jobs.

When NOT to use a derivation

The biggest footgun with derivations is misfit, not implementation. They are the wrong tool for:

• dbt-style multi-step SQL DAGs. dbt runs against a destination snapshot and expresses arbitrary multi-CTE pipelines. A derivation processes one document at a time as it streams in, with no global view of the dataset. Use dbt for DAGs that run against the materialised destination; reserve derivations for streaming logic that has to happen before materialisation.
• Massive joins where one side is unbounded. Each transform maintains per-shard state in RocksDB on the reactor. Joining a large fact stream against an unbounded keyspace can balloon state to tens of GB and cause reactor-wide disk pressure (there's no enforced cap). If both sides are unbounded, do the join at the destination.
• One-shot backfills or report generation. A SQL query against the materialised destination is simpler. Derivations earn their complexity by running continuously over a stream.
• External lookups during processing. Derivations can't call external APIs (the one exception is derivation-python on a private data plane, with caveats), can't query other databases, and can only read source collection documents as they arrive.
• Remapping nested paths to flat columns at the destination. Use projections to flatten depth into columns ({a:{b:1}} → a_b column) or rename a field for a destination. No derivation needed.