Directives

Directives are SQL comments that control materialization, change detection, and validation.

-- @kind: merge
-- @unique_key: order_id
-- @incremental: updated_at
-- @constraint: compare(total, >=, 0)
-- @audit: row_count(>, 0)

Core

@unique_key vs @group_key

@unique_key identifies individual rows for merge and scd2 kinds. It determines which rows to update, insert, or delete.

@group_key groups rows for the tracked kind. Tracked computes an MD5 content hash per group — if any row in the group changes, the entire group is replaced. Use @group_key when the identity is a grouping concept (e.g. source_file for a file-based pipeline) rather than a row-level primary key.

-- merge: row-level identity
-- @kind: merge
-- @unique_key: order_id

-- tracked: group-level identity
-- @kind: tracked
-- @group_key: source_file

Outbound Sync

@sink works with table, append, merge, and tracked kinds. Not supported with scd2 (use @kind: table with WHERE is_current = true instead) or events. The runtime exposes raw DuckLake change_type values (insert, update_preimage, update_postimage, delete) to your push function via __ondatra_change_type. Your Starlark code decides how to handle each change type.

Storage and Performance

Validation

Metadata

Example

-- @kind: merge
-- @unique_key: customer_id
-- @sink: hubspot_push
-- @constraint: not_null(customer_id)
-- @audit: row_count(>, 0)

SELECT customer_id, email, plan
FROM mart.customers
WHERE active = true

Compatibility Matrix