derivation-filter-transform

derivation-filter-transform

Stateless Estuary derivation that filters documents and/or transforms field values. Each source document produces zero or one output document; no state, no cross-document coordination.

Prereq: read derivation-basics first for concepts, project layout, workflow, and language choice.

When to use this over alternatives

• Filtering (WHERE clause): drop documents that don't match criteria
• Computed fields: derive new fields from existing ones (CASE, arithmetic, string manipulation, lookups)
• Data cleansing: normalise formats, trim whitespace, replace nulls
• Selecting a subset of fields when combined with the above

For a plain rename or nested-field flattening with no computation, use schema-projections — a projection doesn't require a derivation task.

Canonical Example — SQL

Cleanse a customers collection for downstream analytics: drop inactive accounts and those with obviously invalid emails, normalise the email, and compute a tier bucket from lifetime spend.

schema.yaml

The writeSchema (what the lambda must emit). Enum constraints on tier are enforced at write time; the readSchema (in flow.yaml below) layers in inference for everything else. See derivation-basics § Schema setup.

type: object
properties:
  customer_id:
    type: string
  email_normalized:
    type: string
  signup_date:
    type: string
    format: date
  tier:
    type: string
    enum: [bronze, silver, gold]
required: [customer_id, email_normalized, tier]

flow.yaml

collections:
  acmeCo/analytics/active-customers:
    writeSchema: schema.yaml
    readSchema:
      allOf:
        - $ref: flow://write-schema
        - $ref: flow://inferred-schema
    key: [/customer_id]
    derive:
      using:
        sqlite: {}
      transforms:
        - name: cleanseCustomers
          source: acmeCo/production/customers
          shuffle: any
          lambda: |
            SELECT
              $id AS customer_id,
              LOWER(TRIM($email)) AS email_normalized,
              date($created_at) AS signup_date,
              CASE
                WHEN $lifetime_spend >= 10000 THEN 'gold'
                WHEN $lifetime_spend >= 1000  THEN 'silver'
                ELSE 'bronze'
              END AS tier
            WHERE $status = 'active'
              AND $email LIKE '%@%';