> 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/engine-guide/spark-1/materialization-behaviour.md).

# Materialization behaviour

Understand how Vulcan model kinds create and update Spark and Iceberg tables.

Vulcan evaluates SQL and Python models through Spark. On the DataOS Lakehouse path, materialized models write Apache Iceberg tables through the attached Lakehouse catalog.

This page explains the physical write behavior of each model kind. For general model syntax and fields, see [Model kinds](https://v2.dataos.info/references/resources/vulcan/models/data-models/model-kinds.md).

## Before you begin

Confirm that:

* The Spark gateway can connect to the target catalog.
* The target Lakehouse Depot is attached with read/write access.
* Required connector JARs and Python wheels are available.
* The model uses a kind supported by the Spark deployment path.

See [Connect and configure Spark](https://v2.dataos.info/references/engine-guide/spark-1/connection-options.md) for catalog, resource, and dependency configuration.

## Materialization behavior by model kind

| Model kind                  | First evaluation                                    | Later evaluations                                                | Important behavior                                                                  |
| --------------------------- | --------------------------------------------------- | ---------------------------------------------------------------- | ----------------------------------------------------------------------------------- |
| `FULL`                      | Replaces the target content                         | Replaces the target content                                      | Rebuilds the complete result                                                        |
| `SEED`                      | Creates and loads the target from seed data         | Recreates or reloads changed seed data                           | Loads CSV-backed data in batches                                                    |
| `EMBEDDED`                  | Creates no table                                    | Creates no table                                                 | Expands the model query into downstream queries                                     |
| `INCREMENTAL_BY_TIME_RANGE` | Creates the target and writes the selected interval | Overwrites rows selected by the time predicate                   | Partition replacement depends on Spark and catalog overwrite configuration          |
| `INCREMENTAL_BY_PARTITION`  | Uses replacement semantics for the target           | Issues `INSERT OVERWRITE` for selected partition data            | Overwrite scope depends on Spark and catalog configuration; forward-only model kind |
| `INCREMENTAL_BY_UNIQUE_KEY` | Uses replacement semantics for the target           | Executes `MERGE INTO`                                            | Updates matched keys and inserts unmatched rows                                     |
| `SCD_TYPE_2_BY_TIME`        | Creates the history table                           | Recomputes current and historical rows, then rewrites the target | Detects changes from an updated-at column                                           |
| `SCD_TYPE_2_BY_COLUMN`      | Creates the history table                           | Recomputes current and historical rows, then rewrites the target | Detects changes by comparing selected columns                                       |

The exact SQL emitted for replacement can vary by target format and adapter capability. The Spark adapter uses `CREATE OR REPLACE TABLE AS SELECT` for supported tables and can use overwrite behavior in fallback paths.

## Full models

Use `FULL` when the complete result can be rebuilt on every evaluation.

```sql
MODEL (
  name saleslakehouse.analytics.daily_revenue,
  kind FULL,
  table_format iceberg
);

SELECT
  order_date,
  SUM(net_amount) AS revenue
FROM saleslakehouse.raw.orders
GROUP BY order_date;
```

Each evaluation replaces the target content. Runtime and cost therefore scale with the full source query, not only with changed rows.

## Seed and embedded models

### Seed models

`SEED` loads file-backed reference data. Vulcan reads the seed in batches: the first batch creates or replaces the table, and later batches append to it. If loading fails, Vulcan removes the incomplete target.

Use seeds for bounded reference data that belongs in source control.

### Embedded models

`EMBEDDED` is non-materialized. Vulcan expands its query into downstream models instead of creating a physical table.

Use embedded models for reusable SQL that does not need an independently managed table.

## Time-range incremental models

`INCREMENTAL_BY_TIME_RANGE` evaluates a selected interval and writes it with Spark overwrite semantics.

```sql
MODEL (
  name saleslakehouse.analytics.daily_orders,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date
  ),
  table_format iceberg,
  partitioned_by (order_date)
);

SELECT
  order_id,
  customer_id,
  order_date,
  net_amount
FROM saleslakehouse.raw.orders
WHERE order_date BETWEEN @start_date AND @end_date;
```

Vulcan supplies `@start_date` and `@end_date` values for the evaluation interval; the model query must use them to filter its source data. Vulcan separately constructs an inclusive target predicate for the overwrite. Whether Spark replaces only matching partitions depends on the table partitioning and the catalog's partition-overwrite configuration.

By default, Vulcan includes the time column in partitioning for this model kind. Set `partition_by_time_column false` only when another validated partition strategy is required.

## Partition incremental models

`INCREMENTAL_BY_PARTITION` uses the model's partition columns as the replacement boundary.

* The first evaluation uses replacement semantics to initialize the target.
* Later evaluations issue `INSERT OVERWRITE` for the selected partition data. The actual overwrite scope depends on Spark and catalog partition-overwrite configuration.
* The model kind is forward-only.

Choose partition columns that match how source data is restated. An incorrect partition boundary can replace more data than intended or leave stale rows outside the selected partitions.

## Unique-key incremental models

`INCREMENTAL_BY_UNIQUE_KEY` uses replacement semantics on its first evaluation and native Spark `MERGE INTO` on later evaluations. Depending on target existence and adapter path, initialization can create, replace, or overwrite the target.

```sql
MODEL (
  name saleslakehouse.analytics.customers,
  kind INCREMENTAL_BY_UNIQUE_KEY (
    unique_key customer_id
  ),
  table_format iceberg
);

SELECT
  customer_id,
  customer_name,
  updated_at
FROM postgres.public.customers;
```

By default:

* Matching keys update target columns.
* Unmatched source rows are inserted.
* `merge_filter` adds conditions to the merge match.
* A custom `when_matched` block replaces the default matched clauses.

### Handle conditional updates and deletes

```sql
MODEL (
  name saleslakehouse.analytics.customers,
  kind INCREMENTAL_BY_UNIQUE_KEY (
    unique_key customer_id,
    when_matched (
      WHEN MATCHED AND source.operation = 'D' THEN DELETE
      WHEN MATCHED AND source.operation IN ('I', 'U') THEN UPDATE SET
        customer_name = source.customer_name,
        updated_at = source.updated_at
    )
  ),
  table_format iceberg
);

SELECT
  customer_id,
  customer_name,
  updated_at,
  operation
FROM postgres.public.customer_changes;
```

Vulcan adds an unmatched insert after the custom matched clauses. A delete event for a key that does not exist in the target can therefore be inserted as a new row unless the source query filters it or the model handles it through another validated strategy.

`grain` is model metadata; it does not define the merge predicate. The `unique_key` expression controls key matching.

## SCD Type 2 models

Spark supports both SCD Type 2 variants:

* `SCD_TYPE_2_BY_TIME` detects changes using an updated-at column.
* `SCD_TYPE_2_BY_COLUMN` detects changes by comparing configured columns or all tracked columns.

Both variants build current and historical rows and rewrite the resulting target. They are not implemented as Iceberg row-level `MERGE INTO` operations.

Configure the valid-from, valid-to, and change-detection fields according to the selected kind. SCD evaluation can be expensive because it reads existing history and source data before rewriting the target.

## Python models

A materialized Python model can return or yield:

* A PySpark DataFrame, which is supported directly.
* A Pandas DataFrame, which is not materialized directly and is first converted to a PySpark DataFrame.

```python
from pyspark.sql import DataFrame


def execute(context, **kwargs) -> DataFrame:
    return context.spark.sql(
        """
        SELECT customer_id, customer_name
        FROM postgres.public.customers
        """
    )
```

PySpark models use `context.spark` and execute through Spark. Pandas operations run on the driver and consume driver CPU and memory.

Use PySpark for distributed or large-volume processing. Use Pandas only for data that fits safely in driver memory. Calling `collect()` or `toPandas()` on a large dataset can exhaust the driver.

## Configure Iceberg tables

### Table format

Use `table_format iceberg` to explicitly select Iceberg as the Spark table provider:

```sql
MODEL (
  name saleslakehouse.analytics.orders,
  kind FULL,
  table_format iceberg
);
```

An explicit model-level format is not required when the target catalog or project configuration already establishes the provider. Declare it when the model must be portable or the provider would otherwise be ambiguous.

### Storage format and table properties

Use `storage_format` for the file format and `physical_properties` for Spark table properties:

```sql
MODEL (
  name saleslakehouse.analytics.orders,
  kind FULL,
  table_format iceberg,
  storage_format parquet,
  physical_properties (
    'write.parquet.compression-codec' = 'snappy',
    'write.metadata.delete-after-commit.enabled' = 'true'
  )
);
```

Spark renders `physical_properties` as table properties. These properties configure the table; they do not enable model execution or determine whether Vulcan evaluates the model.

Validate every property against the deployed Iceberg version before using it.

### Partitioning

Use `partitioned_by` to define Spark table partitioning:

```sql
MODEL (
  name saleslakehouse.analytics.daily_orders,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date
  ),
  table_format iceberg,
  partitioned_by (order_date)
);
```

`partitioned_by` accepts column names only. Transform expressions such as `days(event_date)` and `bucket(16, id)` are rejected with `PARTITIONED BY contains non-column value`.

## Identifier casing

Identifier matching depends on `spark.sql.caseSensitive`.

* With case sensitivity disabled, Spark generally resolves identifiers without case sensitivity.
* With case sensitivity enabled, identifiers must match the physical column names.
* Backticks preserve the identifier spelling in SQL, but case distinction during resolution still depends on `spark.sql.caseSensitive`.

Do not impose a universal uppercase or lowercase convention unless the project sets and documents one.

## Concurrency and failure behavior

Spark materialization has these operational boundaries:

* The Spark adapter does not support transactions across multiple statements.
* A failure after an earlier statement succeeds does not automatically roll back that statement.
* Concurrent writers to the same Iceberg table can encounter optimistic commit conflicts.
* Unique-key incremental batches run serially.
* Time-range models can use configured batch concurrency when the model does not depend on past intervals.

Stagger workflows that write to the same Iceberg table. `concurrencyPolicy: Forbid` prevents overlapping runs of one schedule, but it does not coordinate separate workflows or external writers.

Use `pre_statements` and `post_statements` carefully because Spark cannot roll back the complete lifecycle transactionally.

Iceberg write-audit-publish (WAP) is used only when it is explicitly enabled and supported by the target table. It writes to a branch, runs audits, and cherry-picks the approved snapshot; it is not a general rollback mechanism.

## Views

The Spark deployment path does not support `VIEW` models or materialized views. Use a table-backed model such as `FULL` or an incremental model.

## Validate materialization

Before production:

1. Run `vulcan plan` and review the selected model intervals.
2. Apply and run the model in a non-production environment.
3. Confirm the target table format, schema, and partitions.
4. Verify overwrite or merge behavior with controlled source changes.
5. Inspect the Spark UI for shuffle spill, skew, executor failures, and stage duration.
6. Test recovery from a failed write without assuming transactional rollback.

For deployed Spark configuration and runtime validation, see [Connect and configure Spark](https://v2.dataos.info/references/engine-guide/spark-1/connection-options.md).

## Related

* [Spark overview](https://v2.dataos.info/references/engine-guide/spark-1.md)
* [Connect and configure Spark](https://v2.dataos.info/references/engine-guide/spark-1/connection-options.md)
* [Spark Lakehouse limitations](https://v2.dataos.info/references/engine-guide/spark-1/spark-lakehouse-limitations.md)
* [Limits and performance](https://v2.dataos.info/references/engine-guide/spark-1/limits-and-performance.md)
* [Troubleshooting](https://v2.dataos.info/references/engine-guide/spark-1/troubleshooting.md)


---

# 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/engine-guide/spark-1/materialization-behaviour.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
