# Data Acceleration

Configure local acceleration for datasets in Spice for faster queries

Datasets can be locally accelerated by the Spice runtime, pulling data from any [Data Connector](https://docs.spiceai.org/components/data-connectors) and storing it locally in a [Data Accelerator](https://docs.spiceai.org/components/data-accelerators) for faster access. The data can be kept up-to-date in real-time or on a refresh schedule, ensuring users always have the latest data locally for querying.

## Supported Data Accelerators

Dataset acceleration is enabled by setting the `acceleration` configuration. The `engine` field selects the accelerator: `cayenne` ([Spice Cayenne](https://spiceai.org/docs/components/data-accelerators/cayenne)), `arrow`, `duckdb`, `sqlite`, `turso`, or `postgres`. For engine specific configuration, see [Data Accelerator Documentation](https://spiceai.org/docs/components/data-accelerators).

From Spice v2.4, an acceleration that sets no `engine` uses Cayenne. On native Windows, where Cayenne is not available, the default stays `arrow`. Spice v2.3 and earlier use `arrow` as the default on every platform. With no `mode`, the acceleration uses `mode: memory`. Set `mode: file` to keep the accelerated data on disk.

> **Warning**
>
> Spice v2.4 deprecates every engine other than `cayenne`: `arrow`, `duckdb`, `sqlite`, `turso`, and `postgres`. Spice v2.5 removes them. A Spicepod that uses one logs a warning at startup. To move an acceleration, set `engine: cayenne`. On Linux and macOS, removing `engine` also selects Cayenne. On native Windows, Cayenne is not available: run Spice in Docker or WSL first. Restart Spice and confirm that Cayenne finished its first load before you delete anything. The old acceleration can hold data that the source no longer has. Then delete the old engine's acceleration data and snapshots. See the [Cayenne migration guide](https://spiceai.org/docs/components/data-accelerators/cayenne/migration).

Some configurations load on Arrow but not on Cayenne, such as `partition_by` in `mode: memory`. On Linux and macOS, a dataset with one of these configurations and no `engine` fails to load in v2.4 until it sets `engine: arrow` or changes the configuration. See [Default Engine](https://spiceai.org/docs/components/data-accelerators#default-engine) for the full list.

### Example - Locally Accelerating taxi\_trips with the Default Accelerator

```yaml
datasets:
  - from: spice.ai/spiceai/quickstart/datasets/taxi_trips
    name: taxi_trips
    acceleration:
      enabled: true
      refresh_mode: full
      refresh_check_interval: 10s
```

## Refresh Modes

Spice supports three modes to refresh/update locally accelerated data from a connected data source. `full` is the default mode. Refer to [Data Refresh](https://spiceai.org/docs/features/data-acceleration/data-refresh) documentation for detailed refresh usage and configuration.

| Mode | Description | Example |
| - | - | - |
| `full` | Replace/overwrite the entire dataset on each refresh | A table of users |
| `append` | Append/add data to the dataset on each refresh | Append-only, immutable datasets, such as time-series or log data |
| `changes` | Apply incremental changes | Customer order lifecycle table |

`refresh_mode: changes` streams committed inserts, updates, and deletes from the source's own changelog. See [Database Replication and CDC](https://spice.ai/docs/features/database-replication-and-cdc) for supported sources and configuration.

### Example - Accelerate with full refresh mode

```yaml
datasets:
  - from: databricks:taxi_trips
    name: taxi_trips
    acceleration:
      refresh_mode: full
      refresh_check_interval: 10m
```

## Incremental Ingestion

For sources that expose a monotonically-increasing version column (e.g. `updated_at`, `lastUpdateTime`), Spice can incrementally ingest only new or modified records using `time_column` together with `refresh_mode: append` and a `refresh_check_interval`. Combined with `retention_period`, old records are automatically evicted so the accelerated replica stays bounded in size.

**Behavior**

- **Initial load**: Spice loads all records from the source where `time_column > now() - refresh_data_window`.
- **Incremental refresh**: On each `refresh_check_interval`, Spice queries the source for records where `time_column` is newer than the most recent value already in the accelerated store, and appends them. If `primary_key` is set, matching rows are upserted instead of duplicated.
- **Overlap window**: Use `refresh_append_overlap` to widen the incremental query to `time_column > max(time_column) - refresh_append_overlap`. This re-reads a small trailing window on every refresh to tolerate clock skew between the source and the runtime, and to pick up late-arriving writes whose `time_column` is slightly behind the refresh boundary. Combined with `primary_key` upserts, any rows re-read in the overlap are deduplicated rather than duplicated. No records are lost near the refresh boundary, and no duplicates are introduced.
- **Retention**: On each `retention_check_interval`, rows where `time_column` is older than `retention_period` are removed from the accelerated store, bounding storage and aging out data that is no longer needed.

**Handling deletes**

For sources that do not emit a change feed (e.g. HTTP APIs), the recommended pattern is **soft deletes**: the source marks removed records with a `deleted` flag (and bumps `time_column`). The incremental refresh picks up the tombstone via the normal append path, the upsert replaces the live row with its soft-deleted version, and `retention_period` eventually evicts it from the accelerated store. Queries should filter `WHERE deleted = false` (or use a view) to hide soft-deleted rows. This avoids the cost of periodic full snapshots.

If soft deletes are not available, schedule a periodic `refresh_mode: full` snapshot to reconcile hard deletes by atomically replacing the accelerated contents. For sources that emit a complete change feed (e.g. Debezium, Kafka), use [`refresh_mode: changes`](https://spice.ai/docs/building-blocks/data-connectors/debezium) instead to propagate inserts, updates, and deletes in real time.

### Example - Incrementally ingest the last 90 days of GitHub pull requests

Checks for new and updated records every 15 minutes, with a 5-minute overlap to cover clock skew and late arrivals. Rows updated in the source are upserted on `primary_key`, and soft-deleted rows (`deleted_at IS NOT NULL`) are evicted by `retention_sql` in addition to the time-based `retention_period`:

```yaml
datasets:
  - from: github:github.com/spiceai/spiceai/pulls
    name: pulls
    params:
      github_token: ${secrets:GITHUB_TOKEN}
      github_query_mode: search
    time_column: updated_at
    acceleration:
      enabled: true
      refresh_mode: append
      refresh_check_interval: 15m
      refresh_append_overlap: 5m
      refresh_data_window: 90d
      primary_key: id
      retention_check_enabled: true
      retention_check_interval: 1h
      retention_period: 90d
      retention_sql: DELETE FROM pulls WHERE deleted_at IS NOT NULL
```

On Spice v2.4, Cayenne keeps one row for each `primary_key` and does not use `on_conflict`. A Cayenne dataset that sets `on_conflict` logs a warning. Every other engine also needs `on_conflict: { id: upsert }` to upsert on the key. That includes `arrow`, which this example uses on native Windows, and every engine on Spice v2.3 and earlier.

## Indexes

Database indexes are essential for optimizing query performance. Configure indexes for accelerators via `indexes` field. For detailed configuration, refer to the [index](https://docs.spiceai.org/features/data-acceleration/indexes) documentation.

### Example - Configure indexes with SQLite Accelerator

```yaml
datasets:
  - from: databricks:taxi_trips
    name: taxi_trips
    acceleration:
      enabled: true
      engine: sqlite
      indexes:
        number: enabled # Index the `number` column
        '(hash, timestamp)': unique # Add a unique index with a multicolumn key comprised of the `hash` and `timestamp` columns
```

## Constraints

Constraints enforce data integrity in a database. Spice supports constraints on locally accelerated tables to ensure data quality and configure behavior for data updates that violate constraints.

Constraints are specified using [column references](https://docs.spiceai.org/#column-references) in the Spicepod via the `primary_key` field in the acceleration configuration. On the `duckdb`, `sqlite`, `turso`, and `postgres` engines, additional unique constraints are specified via the [`indexes`](https://docs.spiceai.org/features/data-acceleration/indexes) field with the value `unique`. On those engines, data that violates these constraints will result in a [conflict](https://docs.spiceai.org/#handling-conflicts). For constraints configuration details, visit [Constraints Documentation](https://docs.spiceai.org/features/data-acceleration/constraints).

On Cayenne, a `unique` entry in `indexes` speeds up lookups but is not a constraint: it does not reject duplicate rows. Cayenne deduplicates on `primary_key` only, and keeps one row for each key instead of reporting a conflict.

### Example - Configure primary key constraints with SQLite Accelerator

```yaml
datasets:
  - from: databricks:taxi_trips
    name: taxi_trips
    acceleration:
      enabled: true
      engine: sqlite
      primary_key: hash # Define a primary key on the `hash` column
      indexes:
        '(number, timestamp)': unique # Add a unique index with a multicolumn key comprised of the `number` and `timestamp` columns
```