> For the complete documentation index, see [llms.txt](https://docs.slingdata.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.slingdata.io/concepts/build/incremental.md). # Incremental Incremental mode lets a model load only new or changed rows on each run, instead of rebuilding the full table. Sling supports **two styles** of incremental models, and both can be in the same project: * **dbt-compatible** — you write your own `WHERE` clause with `is_incremental()` and `{{ this }}`. Models written for dbt work unchanged. * **sling-native** — Sling owns the `WHERE` clause through the `{{ incremental_where_cond() }}` Jinja function. This gives you lookback windows, paged historical backfills, `SLING_STATE` integration, and the `--range` CLI flag. Both styles share the same merge strategies, `--full-refresh` behavior, and first-run fallback. An incremental model does a full load on its first run, when the target table does not yet exist. {% hint style="info" %} Every incremental model needs an `update_key`. Without it, Sling gives the error `model '': mode 'incremental' requires update_key` at project load. {% endhint %} ## Which style should I use? | Situation | Style | | -------------------------------------------------- | ------------------------------------------- | | Migrating existing dbt models | **dbt-compatible** — your models work as-is | | Starting a new project | **sling-native** (recommended) | | Need lookback for late-arriving data | **sling-native** | | Need paged historical loads (multi-year backfills) | **sling-native** | | Need one-shot CLI backfills with `--range` | **sling-native** | {% hint style="warning" %} A single model cannot mix both styles. If Sling finds `is_incremental()` **and** `{{ incremental_where_cond() }}` in the same file, it gives this error at project load: > cannot mix is\_incremental() and incremental\_where\_cond() in the same model. Choose one pattern: either dbt-compatible (is\_incremental() + {{ this }}) or sling-native ({{ incremental\_where\_cond() }}) > {% endhint %} ## Style A — dbt-compatible The `is_incremental()` Jinja function returns `true` on later runs, when the target table exists and `--full-refresh` is not set. You write the `WHERE` clause yourself, and use `{{ this }}` to reference the model's own target table. ```sql {%- config( mode='incremental', unique_key='id', merge_strategy='delete+insert', update_key='created_at' ) -%} SELECT id, name, created_at FROM {{ ref('stg_orders') }} {% if is_incremental() %} WHERE created_at > (SELECT MAX(created_at) FROM {{ this }}) {% endif %} ``` **What dbt-compatible style supports:** * All merge strategies (`delete+insert`, `update+insert`, `insert`) * `--full-refresh` to force a full rebuild * First-run fallback — on the first run Sling skips the `{% if is_incremental() %}` block and loads the full dataset **What dbt-compatible style does NOT support.** Use sling-native for these: * `range:` front-matter (lookback, paged mode) * `--range` CLI flag for backfills * Tier-A state storage with `SLING_STATE` * Automatic watermark resolution `--range` with a dbt-style model gives this error: > model '\' uses is\_incremental() (dbt style); --range requires incremental\_where\_cond() (sling style) ## Style B — sling-native Sling-native models call `{{ incremental_where_cond() }}` in their SQL. Jinja evaluates it to a `WHERE` clause bounded by the model's `update_key` range. On the first run, or with no watermark, the function returns `1=1` and the full dataset loads. ```sql /** mode: incremental unique_key: id update_key: created_at merge_strategy: delete+insert **/ SELECT id, name, created_at::date AS created_at FROM {{ ref('stg_orders') }} WHERE {{ incremental_where_cond() }} ``` Two Jinja functions are available: * **`{{ incremental_where_cond() }}`** — the full WHERE clause body. Jinja evaluates it to something like `"created_at" > '2024-01-01'`, or `1=1` on the first run. Put it after your `WHERE` keyword. * **`{{ incremental_value() }}`** — the lower-bound value as a SQL literal. Jinja evaluates it to `'2024-01-01'`, or `null` on the first run. Use it for expressions that need the watermark value directly, for example inside a function call. ### How Sling resolves the bounds — four drivers Sling resolves `{{ incremental_where_cond() }}` with one of four drivers. It selects the driver from the model's `range:` front-matter and the presence of the `--range` CLI flag: | Driver | Lower bound | Upper bound | Advances state | | ------------------------------- | ------------------------------------------------ | --------------------------------- | -------------- | | Plain incremental (no `range:`) | `state.value` (or `MAX(update_key)` from target) | unbounded (`update_key > lower`) | yes | | `range.lookback` only | `state.value - lookback` | unbounded (`update_key >= lower`) | yes | | `range.advance` (paged) | `state.value - lookback` | `min(state.value + advance, now)` | yes | | `--range` CLI | explicit chunk start | explicit chunk end | **no** | Precedence: `--range` wins over `range.advance`, which wins over plain incremental. ### Driver 1: plain incremental With no `range:` block, Sling resolves the lower bound through a three-tier fallback: 1. **Tier A** — if `SLING_STATE` is configured and a state record exists for `(model.name, model.full_table_name)`, use `state.value`. 2. **Tier B** — if not, and the target table exists, run `SELECT MAX() FROM `. This tier works with no `SLING_STATE`. If the probe fails, Sling warns and continues to tier C. 3. **Tier C** — if not, this is the first run: Sling generates `WHERE 1=1` and does a full load. On success, tier A or tier B gets the watermark for the next run. The compiled WHERE clause uses `>` (strictly greater than): ```sql WHERE "created_at" > '2024-03-15 10:00:00' ``` ```sql /** mode: incremental unique_key: id update_key: created_at merge_strategy: delete+insert **/ SELECT id, name, created_at::date AS created_at FROM {{ ref('stg_orders') }} WHERE {{ incremental_where_cond() }} ``` ### Driver 2: lookback Use `range.lookback` when you must reprocess the most recent part of history on every run. For example, when late-arriving events can appear up to two days after their `created_at`. ```sql /** mode: incremental unique_key: id update_key: created_at merge_strategy: delete+insert range: lookback: 2d **/ SELECT id, name, created_at::date AS created_at FROM {{ ref('stg_orders') }} WHERE {{ incremental_where_cond() }} ``` With lookback, Sling moves the lower bound back by the configured duration, and the comparison becomes **inclusive** (`>=`) to include the overlap region: ```sql WHERE "created_at" >= '2024-03-13 10:00:00' ``` The merge strategy, for example `delete+insert`, removes the duplicates from the re-pulled rows in the overlap window. {% hint style="warning" %} `lookback` and `advance` need a date or datetime `update_key`. Another column type gives the error `range.advance/lookback requires datetime/date update_key, got ""`. {% endhint %} ### Driver 3: paged mode Use `range.advance` to pace a historical backfill across many scheduled runs. On each run, Sling moves the watermark forward by exactly `advance`, clamped to `now`. After enough runs, the watermark reaches the present and the model starts to trail the current edge. **Paged mode requires `SLING_STATE`.** Without it, Sling gives this error: > model '\': range.advance requires SLING\_STATE to be configured #### First run with auto-detected origin When you omit `range.start` and no state exists, Sling probes the source on the first run: ```sql SELECT MIN() FROM () __sling_probe ``` Sling caches the detected origin in `SLING_STATE` immediately, before the first merge runs, so a crash between the probe and the load does not repeat the probe. If the source is empty, Sling logs a warning and skips the run. ```sql /** mode: incremental unique_key: id update_key: created_at merge_strategy: delete+insert range: advance: 7d **/ SELECT id, name, created_at::date AS created_at FROM {{ ref('stg_orders') }} WHERE {{ incremental_where_cond() }} ``` #### First run with explicit start When you know the historical start date, set `range.start` to skip the probe: ```sql /** mode: incremental unique_key: id update_key: created_at merge_strategy: delete+insert range: start: '2024-01-01' advance: 7d **/ SELECT id, name, created_at::date AS created_at FROM {{ ref('stg_orders') }} WHERE {{ incremental_where_cond() }} ``` On the first run the chunk is `[2024-01-01, 2024-01-08)`. On the second run it is `[2024-01-08, 2024-01-15)`, and so on, one week per run, until the watermark reaches the present. #### Subsequent runs On every later run, Sling reads `state.value`, applies `lookback` if you set it, adds `advance`, and clamps the upper bound to `now`: ``` lower = state.value - lookback upper = min(state.value + advance, now) ``` On success, `state.value` moves forward to `upper`. If `lower >= upper`, Sling runs no chunks and leaves the state unchanged. #### Combining advance and lookback When you set both, the steady-state window is `advance + lookback` wide: ```yaml range: advance: 7d lookback: 2d ``` After the first run sets the origin, each later run processes a 9-day window: 7 days of new data plus 2 days of overlap. The merge strategy removes the duplicates in the overlap. ### Driver 4: CLI backfill with `--range` For a one-shot historical backfill of a specific window, use the `--range` CLI flag: ```bash sling build run --range '2024-01-01,2024-12-31,1mo' -s fact_orders ``` The format is `',[,]'`. With no ``, Sling runs a single chunk from `` to ``. With a ``, Sling splits the window into sequential chunks and runs them one at a time. The lower bound of each chunk is inclusive. Invalid values give these errors: > invalid --range "\": expected 'start,end' or 'start,end,step' > > invalid --range "\": start and end must be non-empty A `` also needs ISO date or timestamp values for `` and ``. **`--range` does not advance `SLING_STATE`.** It is a stateless backfill operation. Use it to fill a gap or reprocess a known window, with no effect on the model's scheduled watermark. **`--range` requires sling-native style.** With a dbt-style model it gives a clear error. See Style A above. `--range` also needs the model to have an `update_key`. Sling ignores the front-matter `lookback` during a `--range` run. Each chunk logs a progress line: ``` chunk 1/12 created_at=[2024-01-01, 2024-02-01) OK (14s) chunk 2/12 created_at=[2024-02-01, 2024-03-01) OK (12s) chunk 3/12 created_at=[2024-03-01, 2024-04-01) OK (15s) ``` If a chunk fails in the middle of the sequence, Sling prints a resume hint with the exact command to retry from the failed chunk: ``` ▶ chunk 4/12 failed — resume with: sling build run --range '2024-04-01,2024-12-31,1mo' -s fact_orders ``` ## `range:` front-matter reference Declare the `range:` block in model front-matter, together with `mode`, `unique_key`, and `update_key`. All three fields are optional, and the block itself is optional. ```yaml range: start: '2024-01-01' # optional; literal value for paged mode origin advance: 7d # optional; enables paged mode lookback: 2d # optional; reprocesses last N of update_key ``` {% hint style="warning" %} `range` is a front-matter-only key. You cannot set it in a `config()` block. You also cannot set it in the `defaults` section of `sling_build.yml`. {% endhint %} ### Duration grammar `advance` and `lookback` accept a positive integer followed by a unit: | Unit | Meaning | | ---- | ---------------------- | | `ms` | milliseconds | | `s` | seconds | | `m` | minutes | | `h` | hours | | `d` | 24 hours | | `w` | 7 days | | `mo` | 30 days (approximate) | | `y` | 365 days (approximate) | Examples: `5m`, `2h`, `7d`, `1w`, `1mo`, `1y`. ### Validation rules Sling validates the `range:` block at project load. Invalid combinations give exact, greppable messages: | Invalid config | Error message | | --------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- | | `range.start` without `range.advance` | `model '': range.start requires range.advance` | | `range.*` without `mode: incremental` | `model '': range.* requires mode: incremental` | | `range.advance` without `update_key` | `model '': range.advance requires update_key` | | `range.*` on a dbt-style model (`is_incremental()`) | `model '': range.* requires incremental_where_cond() (sling style); is_incremental() is not compatible with range.*` | | `mode: incremental` without `update_key` | `model '': mode 'incremental' requires update_key` | | `range.advance` with `SLING_STATE` not configured | `model '': range.advance requires SLING_STATE to be configured` | | Bad duration literal | `invalid duration '': expected with unit in ms,s,m,h,d,w,mo,y` | ## `SLING_STATE` integration Sling-native incremental models use the same `SLING_STATE` storage as replications and pipelines. When the `SLING_STATE` environment variable is set, Sling reads and writes watermark records keyed by `(model.name, model.full_table_name)`. `SLING_STATE` accepts the same connection formats as replications: a database connection, for example `CONNNAME/schema`, or a file-system path, for example `AWS_S3/path/to/folder`. Sling stores state records in a `_sling_state` table or in JSON files at that location. When `SLING_STATE` is not set, plain incremental and lookback modes fall back to tier B, `SELECT MAX(update_key)` from the target table. Paged mode (`range.advance`) **requires** `SLING_STATE` and gives an error without it. After a successful run with a bounded upper bound, Sling writes that upper bound to state. With an unbounded upper bound, Sling queries `SELECT MAX(update_key)` from the target after the merge. If the result is empty, it warns and leaves the state unchanged. A failure to advance state warns but does not fail the model. `--full-refresh` always wins over state. `sling build run --full-refresh` drops and rebuilds the model with a full-load pass, and ignores the watermark. ## Feature parity: dbt-compatible vs sling-native | Feature | dbt-compatible | sling-native | | ------------------------------------------- | ------------------------- | ------------------------------ | | `is_incremental()` Jinja function | ✅ | not used | | `{{ this }}` variable | ✅ | ✅ (still works for other uses) | | `{{ incremental_where_cond() }}` | not used | ✅ | | `{{ incremental_value() }}` | not used | ✅ | | All merge strategies | ✅ | ✅ | | `--full-refresh` | ✅ | ✅ | | First-run = full load | ✅ | ✅ | | `SLING_STATE` tier-A watermark | ❌ | ✅ | | Tier-B `SELECT MAX(update_key)` from target | ❌ (you write it manually) | ✅ | | `range: { lookback }` | ❌ | ✅ | | `range: { advance }` paged mode | ❌ | ✅ | | `--range` CLI backfill | ❌ | ✅ | dbt models work as-is. Move to sling-native when you need any of the features above. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.slingdata.io/concepts/build/incremental.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.