> 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/stage-2-productize/define-models-and-logic.md). # Define models Models are the core of your data product. This section covers everything from writing transformation SQL to exposing your data through a semantic layer and defining business metrics for dashboards and APIs. *** ## What is a model? A Vulcan model is a `.sql` file that contains two things: 1. **The `MODEL` block (DDL)**: metadata about the model, including its name, schedule, kind, grain, documentation, and assertions. 2. **The `SELECT` query (DML)**: the transformation logic that produces the output. Vulcan parses model dependencies automatically from the SQL. You do not configure a DAG manually. Write SQL that references other model names, and Vulcan determines the execution order. *** ## What is a kind? The `kind` property inside the `MODEL` block controls how a model materializes in the warehouse. It is the most important decision for each model. | Kind | Rebuilds on each run | Storage | Best for | | --------------------------- | :--------------------------: | ------- | ----------------------------------------------- | | `VIEW` (default) | No: runs on demand | None | Lightweight transforms, always-fresh data | | `FULL` | Yes: full rebuild | Table | Small or aggregate tables, simple transforms | | `INCREMENTAL_BY_TIME_RANGE` | Partial: time intervals only | Table | Time-series data: events, logs, transactions | | `INCREMENTAL_BY_UNIQUE_KEY` | Partial: upsert by key | Table | Dimension tables, customer records | | `INCREMENTAL_BY_PARTITION` | Partial: by partition key | Table | Grouped datasets reprocessed together | | `SEED` | Only when CSV changes | Table | Static reference data loaded from CSV | | `SCD_TYPE_2_BY_TIME` | Partial: history tracking | Table | Slowly changing dimensions with `updated_at` | | `SCD_TYPE_2_BY_COLUMN` | Partial: history tracking | Table | Slowly changing dimensions without `updated_at` | `orders-analytics` uses `kind FULL` for all twelve SQL models and `kind SEED` for the order status lookup table. For projects with large datasets or continuous time-series data, incremental kinds process only the data that has changed, not the entire table. *** ## Project structure The `orders-analytics` project uses a medallion layout with three schema layers, each building on the previous one. ``` orders-analytics/ ├── config.yaml ├── usage.yaml ├── external_models.yaml ├── seeds/ │ └── order_status_lookup.csv ├── macros/ │ ├── __init__.py │ └── orders_helpers.py ├── linter/ │ └── linters.py ├── models/ │ ├── seeds/ │ │ └── order_status_lookup.sql │ ├── bronze/ │ │ ├── orders.sql │ │ ├── order_items.sql │ │ ├── customers.sql │ │ ├── products.sql │ │ ├── regions.sql │ │ ├── suppliers.sql │ │ ├── warehouses.sql │ │ ├── shipments.sql │ │ └── dim_dates.sql │ ├── silver/ │ │ ├── fct_daily_sales.sql │ │ ├── fct_weekly_sales.sql │ │ ├── dim_customer_profile.sql │ │ └── dim_product_profile.sql │ ├── gold/ │ │ ├── rfm_customer_segmentation.sql │ │ └── sales_funnel_analysis.sql │ ├── semantics/ │ │ ├── daily_sales.yml │ │ ├── weekly_sales.yml │ │ ├── customer_profile.yml │ │ ├── product_profile.yml │ │ ├── rfm_customer_segmentation.yml │ │ └── sales_funnel_analysis.yml │ └── metrics/ │ ├── daily_sales_performance.yml │ ├── weekly_revenue_trends.yml │ ├── customer_lifetime_value.yml │ ├── rfm_value_by_segment.yml │ └── fulfillment_conversion.yml ├── dq/ ├── audits/ └── tests/ ``` Model names use the format `schema.table_name`. When Vulcan materializes `silver.fct_daily_sales`, it creates a table named `fct_daily_sales` in the `silver` schema. * `bronze.*` tables are source-aligned copies with assertions. * `silver.*` tables are facts and dimensions with business logic applied. * `gold.*` tables are analytics-ready aggregations for specific use cases. *** ## How dependencies work Vulcan detects model dependencies automatically by reading the `FROM` and `JOIN` clauses in your SQL. If `silver.fct_daily_sales` references `bronze.orders`, Vulcan knows it must materialize `bronze.orders` first. You never configure execution order manually. Write SQL with correct table references and Vulcan handles the rest. *** ## Sections in this topic ### Data models The transformation layer: SQL and Python models that produce physical tables.
PageWhat it covers
PropertiesAll MODEL block properties: scheduling, grain, documentation, and assertions.
SQL modelsBronze, silver, and gold layer examples with DDL syntax and SELECT patterns.
Python modelsThe @model decorator and ExecutionContext for Python-based transformations.
Model kindsChoosing the right materialization strategy for each model.
StatementsPre-statements, post-statements, and on-virtual-update for session setup and access control.
MacrosPython macros that generate SQL and make business logic reusable.
SeedsLoading static CSV reference data as warehouse tables.
### Semantic models The query interface: maps physical columns to business-friendly dimensions, measures, and segments. | Page | What it covers | | --------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------ | | [Semantic models](/build/stage-2-productize/define-models-and-logic/semantic-models.md) | Writing `kind: semantic` files with dimensions, measures, segments, and joins. | ### Metrics Time-series analytical definitions for dashboards and the Semantic Query API. | Page | What it covers | | --------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------- | | [Business metrics](/build/stage-2-productize/define-models-and-logic/semantic-models/business-metrics.md) | Writing `kind: metric` files: time-series definitions, granularity, cross-model metrics. | --- # 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/stage-2-productize/define-models-and-logic.md?ask=&goal= ``` `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.