> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/dataos-resources/vulcan/incremental-by-time.md).

# Guides

This guide explains how incremental by time models work in Vulcan, using the `orders-analytics` example. You will see why they are efficient, how they process data, and how to create one. For all model kinds, see [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md).

## Why incremental models

A `FULL` model rebuilds the whole table every run. If a table holds a year of sales (365 days), every run reprocesses all 365 days, even when you only need today. An incremental model processes only the new or missing days, which is dramatically faster and cheaper. The trade-off is that an incremental model needs a time column and a `WHERE` clause; a `FULL` model is simpler but reprocesses everything.

## How incremental models work

Incremental models use **time intervals** to track what has been processed. Vulcan keeps a calendar in [state](/references/dataos-resources/vulcan/core-concepts/state.md): it knows which intervals are done and which still need work.

When you run `vulcan run`, Vulcan:

1. Checks the state store: which dates are already processed?
2. Calculates the missing intervals.
3. Sets the time macros (`@start_ds`, `@end_ds`) for each missing interval.
4. Runs your query for that range and inserts the results.
5. Records the interval as processed, so the next run skips it.

```
State database check:
  Jan 1-7:   processed
  Jan 8-14:  processed
  Jan 15-21: missing -> needs processing
```

## Intervals

Vulcan divides time into intervals based on the model's `cron`. For a daily model (`cron '@daily'`), each day is an interval; for a weekly model (`cron '@weekly'`), each week is. An interval is processed only after the period is complete: at 2pm on Jan 3, the Jan 3 daily interval is still in progress, so Vulcan processes Jan 1 and Jan 2.

On the first `vulcan plan`, Vulcan calculates all intervals from the model's `start` date to now, backfills them, and records them. On a later `vulcan run`, it recalculates intervals, compares against state, and processes only the new ones.

```
First plan (Jan 15):   calculate 3 weeks -> process all 3 -> record weeks 1-3
Second run (Jan 22):   calculate 4 weeks -> state has weeks 1-3 -> process only week 4
```

## Creating an incremental model

Create a weekly sales aggregation in `models/sales/weekly_sales.sql`:

```sql
MODEL (
  name sales.weekly_sales,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date,   -- the column that contains the date
    batch_size 1              -- process 1 week at a time
  ),
  start '2025-01-01',         -- start processing from this date
  cron '@weekly',             -- run weekly
  grain (order_date),
  description 'Weekly aggregated sales metrics'
);

SELECT
  DATE_TRUNC('week', order_date) AS order_date,
  COUNT(DISTINCT order_id)::INTEGER AS total_orders,
  SUM(total_amount)::FLOAT AS total_revenue,
  AVG(total_amount)::FLOAT AS avg_order_value
FROM sales.daily_sales
WHERE order_date BETWEEN @start_ds AND @end_ds   -- filter by time range
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY order_date
```

Three components make this work:

* **`time_column`** tells Vulcan which column holds the timestamp for each row. Vulcan uses it to filter and group by time.
* **The `WHERE` clause with `@start_ds` and `@end_ds`** filters the data to the interval being processed. Vulcan fills in the dates; you do not figure out which dates to run.
* **`start`** tells Vulcan when your data begins, so it backfills from there on the first plan.

Apply it with `vulcan plan dev`. Vulcan adds the model, computes the intervals needing backfill, and processes each week:

```
[1/3] sales.weekly_sales  [insert 2025-01-01 - 2025-01-07]  1.2s
[2/3] sales.weekly_sales  [insert 2025-01-08 - 2025-01-14]  1.1s
[3/3] sales.weekly_sales  [insert 2025-01-15 - 2025-01-21]  1.3s
```

To convert a `FULL` model to incremental, change `kind FULL` to `kind INCREMENTAL_BY_TIME_RANGE (time_column order_date)`, add a `start` date, and add the `WHERE order_date BETWEEN @start_ds AND @end_ds` filter.

### Converting a FULL model to incremental

**Before (FULL):**

```sql
MODEL (
  name sales.daily_sales,
  kind FULL,
  cron '@daily'
);
SELECT order_date, COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM raw.raw_orders
GROUP BY order_date
```

**After (INCREMENTAL\_BY\_TIME\_RANGE):**

```sql
MODEL (
  name sales.daily_sales,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date
  ),
  cron '@daily',
  start '2024-01-01'
);
SELECT order_date, COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM raw.raw_orders
WHERE order_date BETWEEN @start_ds AND @end_ds
GROUP BY order_date
```

Key changes: add `time_column`, add `start`, add `WHERE` clause with `@start_ds`/`@end_ds`.

## The two WHERE clauses

Vulcan uses two `WHERE` clauses, and they serve different purposes:

* **Your `WHERE` clause** filters the data read into the model. It is a performance optimization: without it, you read all upstream data even though you need a small range. You control this.
* **Vulcan's automatic `WHERE` clause** filters the data the model outputs. It is a safety net: even if your `WHERE` clause has a bug, Vulcan does not store rows outside the target interval, preventing data leakage. Vulcan adds this automatically.

Always include the `WHERE` clause in your model SQL. It is not optional: Vulcan needs it to know the time range, and it makes queries far more efficient.

## Cron and `vulcan run`

Each model's `cron` sets how often it runs (`@daily`, `@weekly`, `@hourly`). On `vulcan run`, Vulcan checks each model's `cron`, determines whether enough time has passed since the last run, and processes only the models that are due.

```
vulcan run at 2pm on Jan 15:
  daily_sales (@daily):   last run 24h ago -> due, process
  weekly_sales (@weekly): last run 2 days ago -> not due, skip
```

Use `vulcan plan` when you change a model, and `vulcan run` for scheduled execution. See [Observability](/references/dataos-resources/vulcan/observability.md).

## Batch processing

For large datasets, set `batch_size` to process intervals in batches instead of one job. With `batch_size 4`, twelve missing weeks run as three jobs of four weeks each. Use batches for large datasets that might time out or when you want clearer progress; skip them for small datasets and fast queries.

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column order_date,
  batch_size 4
)
```

**Use batches when:** the source table is large and processing all missing intervals at once would time out or exhaust memory.

**Skip batches when:** intervals are small and the overhead of multiple queries outweighs the benefit.

Rule of thumb: start without `batch_size`; add it only if runs time out or fail on large backfills.

## Forward-only models

Some tables are too large or expensive to rebuild. A forward-only model never rebuilds historical data; changes apply only going forward in time. Use forward-only when a full backfill would take too long, when historical data cannot be reprocessed, or when you only care about future data. Do not use it when you need to fix historical data or want full schema consistency across old and new data.

Make a model forward-only with `forward_only true`, or make a single plan forward-only with `vulcan plan dev --forward-only`:

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column order_date,
  forward_only true
)
```

### Schema changes

When you change a forward-only model, Vulcan checks for schema changes. Destructive changes remove or modify existing data (dropping a column, renaming a column, a lossy type change); additive changes add data without removing it (adding a column, a compatible type change). Control how Vulcan reacts with `on_destructive_change` and `on_additive_change`:

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column order_date,
  forward_only true,
  on_destructive_change error,   -- block destructive changes
  on_additive_change allow       -- allow new columns
)
```

Options are `error` (stop, default for destructive), `warn` (log but continue), `allow` (proceed, default for additive), and `ignore` (skip the check, dangerous).

### Common schema-change configs

**Strict schema control** — reject any schema change in production:

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column event_ts,
  on_destructive_change 'error',
  on_additive_change   'error'
)
```

**Development model** — allow all changes, never forward-only:

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column event_ts,
  on_destructive_change 'allow',
  on_additive_change   'allow'
)
```

**Production safety** — allow new columns, block breaking changes:

```sql
kind INCREMENTAL_BY_TIME_RANGE (
  time_column event_ts,
  on_destructive_change 'error',
  on_additive_change   'allow'
)
```

## Important notes

* **Use UTC for the time column.** UTC keeps interval calculations correct and works with Vulcan's scheduler. Local timezones cause daylight-saving and offset bugs.
* **Always include the `WHERE` clause** with `@start_ds` and `@end_ds`.
* **Set a `start` date** so Vulcan knows where to begin backfilling.
* **Choose a `batch_size`** that fits your data: small for small datasets, larger for big ones that might time out.

## Related docs

* [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md): all model kinds and their properties.
* [State](/references/dataos-resources/vulcan/core-concepts/state.md): how Vulcan tracks processed intervals.
* [Plan](/references/dataos-resources/vulcan/core-concepts/plan.md): how backfill, restatement, and forward-only behave during apply.


---

# 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://v2.dataos.info/references/dataos-resources/vulcan/incremental-by-time.md?ask=<question>&goal=<endgoal>
```

`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.