> 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/build/v1/productize/assets/data-model.md).

# Data model

A data model is the transform: the SQL or Python that turns raw input into something useful. Each model file produces one physical table or view. Vulcan manages the run order, versioning, and safe deployment for you.

This is where you add the meaning the raw data doesn't have yet: joins, business rules, aggregates.

A Vulcan model is a `.sql` file with two parts:

1. A **`MODEL` block** declaring the name, kind, schedule, grain, description, and assertions.
2. A **`SELECT` query** with the transformation logic.

Vulcan reads dependencies from your `FROM` and `JOIN` clauses, so you never configure a run order by hand. If `silver.fct_daily_sales` selects from `bronze.orders`, Vulcan builds `bronze.orders` first.

## The shape

```sql
MODEL (
  name silver.fct_daily_sales,
  kind FULL,
  cron '*/15 * * * *',
  grains (order_date, region_id, customer_id, product_id),
  description 'Daily sales fact by date, customer, product, and region.',
  assertions (
    not_null(columns := (order_date, total_revenue))
  )
);

SELECT
  o.order_date::DATE AS order_date,
  c.region_id,
  SUM(i.quantity * i.unit_price) AS total_revenue
FROM bronze.orders AS o
INNER JOIN bronze.customers AS c ON o.customer_id = c.customer_id
INNER JOIN bronze.order_items AS i ON o.order_id = i.order_id
GROUP BY 1, 2;
```

Model names follow `schema.table_name`. The `grains` property declares the columns that uniquely identify a row, written as a parenthesized tuple: a single grain is `grains (order_id)`, a composite grain is `grains (customer_id, order_date)`.

Document and tag individual columns directly on the `MODEL` block with `column_descriptions`, `column_tags`, `column_terms`, and `column_classifications`, instead of inline SQL comments.

## Bronze, silver, gold

`orders-analytics` organizes its 12 models into three layers, so dependencies read top to bottom:

* **bronze** copies a source table close to its raw shape
* **silver** joins bronze tables and applies business logic, like the `silver.fct_daily_sales` model above
* **gold** reshapes silver for one specific analytics use case

Each layer only reads from the layer above it. Vulcan resolves that order from the `FROM` and `JOIN` clauses, so you never declare it by hand.

**Bronze** stays close to the source and carries the assertions that guard what every later layer will trust:

```sql
MODEL (
  name bronze.orders,
  kind FULL,
  grains (order_id),
  description 'Order transactions with status, customer, and warehouse.',
  assertions (
    unique_values(columns := (order_id)),
    not_null(columns := (order_id, customer_id, order_date, order_status))
  )
);

SELECT order_id, customer_id, order_date, warehouse_id, order_status
FROM public.orders_ext;
```

**Gold** consumes silver and shapes the result for one consumer-facing question, such as scoring customers by recency, frequency, and monetary value:

```sql
MODEL (
  name gold.rfm_customer_segmentation,
  kind FULL,
  grains (customer_id)
);

WITH scored AS (
  SELECT
    customer_id,
    CASE WHEN days_since_last_order <= 30 THEN 5 ELSE 1 END AS recency_score,
    CASE WHEN total_orders >= 10 THEN 5 ELSE 1 END AS frequency_score,
    CASE WHEN total_revenue >= 1000 THEN 5 ELSE 1 END AS monetary_score
  FROM silver.dim_customer_profile
)
SELECT
  customer_id,
  recency_score::TEXT || frequency_score::TEXT || monetary_score::TEXT AS rfm_score,
  CASE
    WHEN recency_score = 5 AND frequency_score = 5 AND monetary_score = 5 THEN 'Champions'
    ELSE 'Potential Loyalists'
  END AS rfm_segment
FROM scored;
```

The real model buckets each score into 5 tiers and assigns one of 9 segment labels. This is the same CASE-driven pattern, just condensed.

## Choose a kind

The `kind` decides how the model materializes. It's the most important choice you make for each model.

| Kind                                | Rebuilds each run         | Best for                                                           |
| ----------------------------------- | ------------------------- | ------------------------------------------------------------------ |
| `VIEW` (default)                    | No, runs on demand        | Lightweight transforms, always-fresh data                          |
| `FULL`                              | Yes, full rebuild         | Small or aggregate tables                                          |
| `INCREMENTAL_BY_TIME_RANGE`         | Partial, by time interval | Time-series: events, logs, transactions                            |
| `INCREMENTAL_BY_UNIQUE_KEY`         | Partial, upsert by key    | Dimension and current-state tables                                 |
| `INCREMENTAL_BY_PARTITION`          | Partial, by partition key | Datasets reprocessed by group                                      |
| `SEED`                              | Only when the CSV changes | Static reference data                                              |
| `SCD_TYPE_2_BY_TIME` / `_BY_COLUMN` | Partial, history tracking | Slowly changing dimensions                                         |
| `EMBEDDED`                          | Never, no table created   | Reusable SQL snippet inlined as a subquery into referencing models |

`orders-analytics` uses `FULL` for all 12 SQL models, since the data volume is small, plus one `SEED` model. Reach for an incremental kind when a table grows continuously and a full rebuild gets too slow.

Python models can't use `VIEW`, `SEED`, or `EMBEDDED`: those three kinds are SQL-only.

For the full kind reference, see [References → Vulcan → Model → Kinds](https://v2.dataos.info/references/resources/vulcan/models/data-models/model-kinds).

## SQL or Python

Most models are SQL. When a transform needs logic that SQL can't express, write a Python model instead, using the `@model` decorator and an `ExecutionContext`.

You can mix both in one project: SQL for set-based work, Python where it pays off. Reusable logic goes into macros, which you call inline as `@macro_name()`.

For both, see [References → Vulcan → Models → Data Models → Types](https://v2.dataos.info/references/resources/vulcan/models/data-models/types#choose-a-model-type) and [Macros](https://v2.dataos.info/references/resources/vulcan/advanced-features/macros).


---

# 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/build/v1/productize/assets/data-model.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.