> 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/optimisation.md).

# Optimisation

This page collects the levers you pull to make a Vulcan data product faster and cheaper, from choosing the right model kind to writing a custom materialization when no built-in kind fits. Reach for the simple levers first; custom materializations are an advanced, last-resort tool.

## Process only what changed

The single biggest win is not reprocessing data you have already processed. A `FULL` model rebuilds the whole table every run. An `INCREMENTAL_BY_TIME_RANGE` model processes only the missing intervals, which can be dramatically faster and cheaper on time-series data. Vulcan tracks processed intervals in [state](/references/dataos-resources/vulcan/core-concepts/state.md) and skips everything it has already done. See [incremental by time](/references/dataos-resources/vulcan/incremental-by-time.md) for a worked comparison.

Two `WHERE` filters work together on an incremental model: your filter restricts the input read from upstream tables (the performance lever), and Vulcan automatically adds an output filter to prevent data leakage. Always include the `WHERE order_date BETWEEN @start_ds AND @end_ds` filter in your model SQL; without it, Vulcan reads all upstream data every run.

## Batch large backfills

When an incremental model has many missing intervals, group them with `batch_size` so each job processes a bounded chunk instead of everything at once. Use `batch_concurrency` (where supported) to run batches in parallel. Batching helps large datasets finish without timing out and gives clearer progress tracking. Keep `batch_size` small for small datasets and increase it for large ones, watching for timeouts.

```sql
MODEL (
  name sales.weekly_sales,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date,
    batch_size 4
  )
);
```

`INCREMENTAL_BY_UNIQUE_KEY` does not support `batch_concurrency`, because MERGE operations cannot safely run in parallel. Use `merge_filter` to limit the MERGE scan on large tables.

## Use storage layout

Engine storage properties speed up queries by reading less data:

* `partitioned_by` lets the engine skip partitions that do not match a query (partition pruning). For incremental by time models, Vulcan adds the time column to the partition key automatically.
* `clustered_by` stores related rows together, so range filters read fewer blocks.
* `table_format` and `storage_format` (such as `iceberg` and `parquet`) affect compression and query performance.

See the storage properties in [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md#storage-and-engine-properties).

## Tune queries and views

* **Materialize expensive views.** A `VIEW` runs its query on every reference, so a view referenced by many downstream models pays that cost each time. Materialize expensive views as `FULL` or incremental models. For automatic engine-driven refresh on supported engines, use a [managed model](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md#managed-models).
* **Keep the optimizer on.** `optimize_query` defaults to `true`; turn it off only when it breaks a specific query.
* **Profile before tuning serving queries.** Use `vulcan transpile` to inspect the SQL a semantic query generates and check join and filter placement before execution. See [Semantic model and components](/references/dataos-resources/vulcan/core-concepts/semantic-model-and-components.md#querying-the-semantic-layer).
* **Let the cache work.** Semantic query results are cached by fingerprint and reused until upstream data changes. See [Observability](/references/dataos-resources/vulcan/observability.md#the-semantic-query-path).

## Custom materializations

Vulcan's built-in [model kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md#model-kinds) cover the common ways to materialize a transformation. A custom materialization covers the rest: when no built-in kind fits, you write the materialization in Python and control exactly how a model's data lands in the engine.

{% hint style="warning" %}
This is an advanced feature. A custom materialization replaces Vulcan's built-in data definition and manipulation logic for a model. Use one only after ruling out the standard kinds. Most workloads fit `FULL`, `INCREMENTAL_BY_TIME_RANGE`, `INCREMENTAL_BY_UNIQUE_KEY`, or `INCREMENTAL_BY_PARTITION`.
{% endhint %}

A materialization is the "how" behind model execution: the methods that run your transformation and manage the resulting data. Its logic also varies by engine: Postgres has no `CREATE OR REPLACE TABLE`, so a `FULL` model there uses `DROP` then `CREATE`. Vulcan handles these engine-specific details for built-in kinds; with a custom materialization, you control them. A custom materialization is like your own model kind. You define it as a Python class that inherits from `CustomMaterialization` and implements at least `insert`. Vulcan loads any Python files in your project's `materializations/` directory.

```python
from vulcan import CustomMaterialization
from vulcan import Model
import typing as t

class CustomFullMaterialization(CustomMaterialization):
    NAME = "my_custom_full"

    def insert(
        self,
        table_name: str,
        query_or_df: t.Any,
        model: Model,
        is_first_insert: bool,
        render_kwargs: t.Dict[str, t.Any],
        **kwargs: t.Any,
    ) -> None:
        self.adapter.replace_query(table_name, query_or_df)
```

`NAME` is the identifier you reference in a model, `self.adapter` is the engine adapter you use to execute SQL, and `query_or_df` is the SQL query string or DataFrame (Pandas, PySpark, or Snowpark) Vulcan hands you. `is_first_insert` is `True` the first time data is inserted for this model version, and `render_kwargs` is the dictionary of arguments used to render the model query. Branch on `is_first_insert` to create the table before the first insert:

```python
if is_first_insert:
    self.adapter.create_table(table_name, columns=model.columns_to_types)
self.adapter.insert_append(table_name, query_or_df)
```

You can also override `create` and `delete` to control table and view lifecycle.

Use a custom materialization by setting the model's `kind` to `CUSTOM` and passing the class `NAME`:

```sql
MODEL (
  name vulcan_demo.custom_model,
  kind CUSTOM (
    materialization 'my_custom_full',
    materialization_properties (
      'batch_size' = 1000
    )
  )
);

SELECT customer_id, COUNT(*) AS orders
FROM vulcan_demo.orders
GROUP BY customer_id
```

Pass per-model configuration through `materialization_properties` and read it via `model.custom_materialization_properties`. For early validation of those properties at load time (before any database connection), subclass `CustomKind` and link it to your materialization with a generic type parameter. Share a materialization across projects by copying it into each `materializations/` directory, or by packaging it with setuptools entrypoints under `vulcan.materializations` so Vulcan discovers it after install.

### `is_first_insert` branching

Use `is_first_insert` to branch between table creation and subsequent inserts. `query_or_df` is a SQL string for SQL models or a Pandas/PySpark/Snowpark DataFrame for Python models.

```python
from vulcan import CustomMaterialization, model

class SimpleCustomMaterialization(CustomMaterialization):
    NAME = "simple_custom"

    def insert(
        self,
        table_name,
        query_or_df,
        is_first_insert,
        render_kwargs,
        **kwargs,
    ):
        if is_first_insert:
            return [
                f"CREATE TABLE IF NOT EXISTS {table_name} AS ({query_or_df})"
            ]
        return [f"INSERT INTO {table_name} ({query_or_df})"]
```

### Python model usage

Set `kind` to `CUSTOM` with `materialization` pointing to the class `NAME`:

```python
@model(
    name="schema.my_table",
    kind=dict(name=ModelKindName.CUSTOM, materialization="simple_custom"),
    depends_on=["schema.source"],
)
def execute(context, **kwargs):
    df = context.table("schema.source")
    return df
```

### `CustomKind` for early validation

`CustomKind` lets you validate custom properties at parse time (before any database connection):

```python
from vulcan import CustomKind
from pydantic import model_validator

class MyCustomKind(CustomKind["SimpleCustomMaterialization"]):
    primary_key: str

    @model_validator(mode="after")
    def check_primary_key(self):
        if not self.primary_key:
            raise ConfigError("primary_key is required")
        return self
```

{% hint style="warning" %}
`CustomKind` subclasses Vulcan internals — use only when you need parse-time property validation. For runtime checks, use the `insert` method directly.
{% endhint %}

### Sharing materializations

**Option 1 — copy the file:** Copy `materializations/my_materialization.py` into each project that needs it and commit it to version control.

**Option 2 — package as a Python library** (required when a scheduler like Airflow runs on machines without access to the project directory):

```toml
# pyproject.toml
[project.entry-points."vulcan.materializations"]
simple_custom = "my_package.materializations:SimpleCustomMaterialization"
```

Or with setuptools:

```python
# setup.py
entry_points={
    "vulcan.materializations": [
        "simple_custom = my_package.materializations:SimpleCustomMaterialization",
    ],
}
```

After packaging, install with `pip install .` — no project-directory copy needed.

## Related docs

* [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md): kinds, storage properties, and statements.
* [Incremental by time](/references/dataos-resources/vulcan/incremental-by-time.md): the incremental processing model in depth.
* [Observability](/references/dataos-resources/vulcan/observability.md): scheduling, batching, and the query cache in practice.


---

# 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/optimisation.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.