Incremental SQL Model

Build an incremental append model with cursor tracking

This example shows a SQL model that incrementally appends new events using cursor tracking.

1. Source Data

-- models/raw/events.sql
-- @kind: table

SELECT * FROM (VALUES
    (1, 'u1', 'click',    '/pricing',  '{}', '2026-03-28 10:00:00'),
    (2, 'u2', 'view',     '/features', '{}', '2026-03-28 11:00:00'),
    (3, 'u1', 'purchase', '/checkout', '{}', '2026-03-29 09:00:00'),
    (4, 'u3', 'signup',   '/register', '{}', '2026-03-29 14:00:00'),
    (5, 'u2', 'view',     '/docs',     '{}', '2026-03-30 08:00:00')
) AS t(event_id, user_id, event_type, page_url, metadata, event_time)

2. Incremental Model

-- models/staging/stg_events.sql
-- @kind: append
-- @incremental: event_time
-- @constraint: event_id NOT NULL
-- @constraint: event_type IN ('click', 'view', 'purchase', 'signup')
-- @audit: row_count > 0

SELECT
    event_id,
    user_id,
    event_type,
    page_url,
    metadata,
    event_time
FROM raw.events
ORDER BY event_time

3. Run

ondatrasql run

Running 2 models...
[OK] raw.events (table, backfill, 5 rows, 187ms — first run)
[OK] staging.stg_events (append, backfill, 5 rows, 352ms — first run)

4. Run Again

ondatrasql run

Running 2 models...
[OK] raw.events (table, skip, 0 rows, 0s — no dependencies)
[OK] staging.stg_events (append, incremental, 0 rows, 259ms — unchanged)

Nothing changed, nothing ran.

How It Works

1. First run (backfill): All data is loaded into the target table
2. After first run: OndatraSQL records MAX(event_time) from the target table
3. Subsequent runs: Smart CDC automatically detects changes in raw.events using DuckLake time-travel and only appends new rows
4. No manual filtering: You write a plain SELECT — OndatraSQL handles incremental logic via CDC