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

Lib-Backed Models (Inbound)

A @fetch model is a passthrough projection of API rows: every output column must be <col>::TYPE AS alias. JOIN, WHERE, GROUP BY, DISTINCT, ORDER BY, LIMIT, OFFSET, UNION are rejected at parse time — push transformation to a downstream model that reads from the @fetch table.

@fetch cannot be combined with @push on the same model (split into a fetch model and a downstream push model).

Outbound Sync

@push works with table, append, merge, and tracked kinds. Not supported with scd2 (use @kind: table with WHERE is_current = true instead). 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.

A @push model produces the shape sent to the API. The strict-push validator only inspects the outermost SELECT projection — every output column must be <expr>::TYPE AS alias (cast + alias on every projection, no SELECT *). Inside the model SQL is fully free: JOIN, WHERE, GROUP BY, aggregates, DISTINCT, ORDER BY, CTEs, derived expressions are all legitimate shape construction. The cross-cutting LIMIT/OFFSET ban still applies. Lib calls in FROM are rejected (that would be a @fetch model — read from the materialized fetch table instead).

Storage and Performance

Validation

Metadata

Example

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

SELECT
    customer_id::BIGINT AS customer_id,
    email::VARCHAR AS email,
    plan::VARCHAR AS plan
FROM mart.customers
WHERE active = true

Compatibility Matrix

@fetch and @push cannot be combined on the same model — split into a @fetch model that materializes raw API data and a downstream @push model that reads from it.