> 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/redshift/materialization-behavior.md).

# Materialization Behavior

Choose materialization strategies for Redshift models and semantic layers.

With a validated gateway, this is the build stage. Choose a model kind and understand its Redshift operation before running it.

## Materialization behavior per model kind

SQL models compile to Redshift SQL (PostgreSQL-derived).

| Model kind                  | Redshift operation (conceptual)             |
| --------------------------- | ------------------------------------------- |
| `FULL`                      | Table rebuild / truncate+insert per adapter |
| `VIEW`                      | `CREATE VIEW`                               |
| `INCREMENTAL_BY_TIME_RANGE` | Windowed delete/merge + insert              |
| `INCREMENTAL_BY_UNIQUE_KEY` | `MERGE` / delete+insert on key              |
| `INCREMENTAL_BY_PARTITION`  | Partition-scoped overwrite                  |

### Engine-native feature support

After core models work, add Redshift-specific capabilities where they add clear value.

| Redshift feature    | Vulcan pattern                        | Notes                          |
| ------------------- | ------------------------------------- | ------------------------------ |
| Cluster + database  | Depot `host`, `database`              | Must exist                     |
| Schema              | Vulcan `CREATE SCHEMA` / `before_all` | e.g. `bronzev2`                |
| Table (dist/sort)   | Guarded `post_statements`             | `COMPOUND SORTKEY`, `DISTKEY`  |
| View                | kind `VIEW`                           | No view comments via Vulcan    |
| MERGE / incremental | Model kinds                           | Validate keys                  |
| COPY from S3        | Boundary / `before_all`               | Spectrum + IAM outside Vulcan  |
| GRANT               | SQL in lifecycle or admin             | Adapter supports grants        |
| Python models       | `.py` in project                      | Validate per image             |
| Redshift Serverless | Depot connection                      | Same SQL path; tune RPU in AWS |

#### Not supported

Vulcan does not manage these Redshift capabilities:

* Cluster creation, resizing, or WLM configuration: no AWS lifecycle exists in Vulcan. Provision through the AWS console, Terraform, or Redshift Serverless admin.
* `REPLACE TABLE` semantics (`SUPPORTS_REPLACE_TABLE = False`): expect the FULL-refresh and merge strategies Vulcan emits, not a naive `CREATE OR REPLACE TABLE`.
* Rich view comments (`COMMENT_CREATION_VIEW = UNSUPPORTED`): document in the semantic `description` or `ai_context` instead.
* The full PostgreSQL extension surface: Redshift supports a subset; validate UDFs against the [Redshift SQL reference](https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html).
* Superuser-only operations: run these as admin outside Vulcan.

Transactions are also limited on Redshift, so design incrementals to be idempotent on retry, and run `ANALYZE` after large loads (`after_all`) so the optimizer and BI stay healthy.

### Lowercase identifiers

{% hint style="warning" %}
Keep **snake\_case** identifiers consistent from SQL through semantic YAML. Match semantic identifiers to the physical column casing on the cluster, which is often lowercase. This matches the Postgres rule and opposes the Snowflake/Spark UPPERCASE rule.
{% endhint %}

### Lifecycle DDL guards

```sql
@IF(@runtime_stage = 'evaluating',
  -- DDL that must not run on plan-only
  SELECT 1
);
```

### Session tuning in `before_all`

Use `SET statement_timeout`, `SET query_group` (WLM) sparingly, and reset them in `after_all`.

```yaml
before_all:
  - SET statement_timeout = '30min'
  - SET query_group TO 'vulcan_etl'
after_all:
  - RESET statement_timeout
  - RESET query_group
```

## Semantic models

Use `kind: semantic`, with explicit dimensions and measures, `joins` for multi-entity queries, and optional `rollups` with `enable_rollup: true` in config. Rollups require the transpiler.

## Business metrics

```yaml
kind: metric
name: daily_sales_performance
measure: daily_sales.total_daily_revenue
ts: daily_sales.order_date
granularity: day
```

## Data quality

Redshift uses the same four layers as other engines: linter, assertions, unit tests (DuckDB locally), audits, and `kind: dq` checks that run on the cluster after a successful run or plan apply.

## Lineage and version rollback

Lineage comes from Vulcan's SQL parse. Cross-schema references need either visible tables or a declaration in `inputs.yaml` so linting and lineage resolve them.

## Before you ship checklist (Data Product developer)

* [ ] Semantic identifiers match **physical column casing**
* [ ] Lifecycle DDL guarded with `@IF(@runtime_stage = 'evaluating', …)`
* [ ] Source tables bootstrapped in `before_all` or declared in `inputs.yaml`
* [ ] Joins declared for multi-entity semantic queries
* [ ] Grains correct on facts and dimensions
* [ ] `enable_rollup: true` only if the transpiler/rollups are validated
* [ ] `endOn` reviewed on the Vulcan resource schedule

## Next step

After models deploy, make data available through Serve, or skip straight to a ready-made shape in [Deployment Recipes](https://v2.dataos.info/references/engine-guide/redshift/recipes/deployment-recipes.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/redshift/materialization-behavior.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.
