Model configuration
This page lists SQLMesh model configuration options and their parameters.
Learn more about specifying SQLMesh model properties in the model concepts overview page.
General model properties
Configuration options for SQLMesh model properties. Supported by all model kinds other than SEED
models.
Option | Description | Type | Required |
---|---|---|---|
name |
The model name. Must include at least a qualifying schema (<schema>.<model> ) and may include a catalog (<catalog>.<schema>.<model> ). If any project model name includes a catalog, all model names must include a catalog or a default catalog must be set. |
str | Y |
kind |
The model kind (Additional Details) (Default: VIEW ) |
str | dict | N |
audits |
SQLMesh audits that should run against the model's output | array[str] | N |
dialect |
The SQL dialect in which the model's query is written. All SQL dialects supported by the SQLGlot library are allowed. | str | N |
owner |
The owner of a model; may be used for notification purposes | str | N |
stamp |
Arbitrary string used to indicate a model's version without changing the model name | str | N |
tags |
Arbitrary strings used to organize or classify a model | array[str] | N |
cron |
The cron expression specifying how often the model should be refreshed. (Default: @daily ) |
str | N |
interval_unit |
The temporal granularity of the model's data intervals. Supported values: year , month , day , hour , half_hour , quarter_hour , five_minute . (Default: inferred from cron ) |
str | N |
start |
The date/time that determines the earliest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as 1 year ago . |
str | int | N |
end |
The date/time that determines the latest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as 1 year ago . |
str | int | N |
grains |
The column(s) whose combination uniquely identifies each row in the model | str | array[str] | N |
references |
The model column(s) used to join to other models' grains | str | array[str] | N |
depends_on |
Models on which this model depends. (Default: dependencies inferred from model code) | array[str] | N |
storage_format |
The storage format that should be used to store physical tables; only applicable to engines such as Spark | str | N |
partitioned_by |
The column(s) used to partition the model's physical table; only applicable to engines that support partitioning | str | N |
clustered_by |
The column(s) used to cluster the model's physical table; only applicable to engines that support clustering | str | N |
columns |
The column names and data types returned by the model. Disables automatic inference of column names and types from the SQL query. | array[str] | N |
table_properties |
Arbitrary table properties specific to the target engine. Specified as key-value pairs (key = value ) |
dict | N |
allow_partials |
Whether this model can process partial (incomplete) data intervals | bool | N |
description |
Description of the model. Automatically registered in the SQL engine's table COMMENT field or equivalent (if supported by the engine). | str | N |
Model defaults
The SQLMesh project-level configuration must contain the model_defaults
key and must specify a value for its dialect
key. Other values are set automatically unless explicitly overridden in the model definition. Learn more about project-level configuration in the configuration guide.
The SQLMesh project-level model_defaults
key supports the following options, described in the general model properties table above:
- kind
- dialect
- cron
- owner
- start
- end
- storage_format
Model kind properties
Configuration options for kind-specific SQLMesh model properties, in addition to the general model properties listed above.
Learn more about model kinds at the model kind concepts page. Learn more about specifying model kind in Python models at the Python models concepts page.
VIEW
models
Configuration options for models of the VIEW
kind (in addition to general model properties).
Option | Description | Type | Required |
---|---|---|---|
materialized |
Whether views should be materialized (for engines supporting materialized views). (Default: False ) |
bool | N |
Python model configuration object: ViewKind()
FULL
models
The FULL
model kind does not support any configuration options other than the general model properties listed above.
Python model configuration object: FullKind()
Incremental models
Configuration options for all incremental models (in addition to general model properties).
Option | Description | Type | Required |
---|---|---|---|
batch_size |
The maximum number of intervals that can be evaluated in a single backfill task. If this is None , all intervals will be processed as part of a single task. If this is set, a model's backfill will be chunked such that each individual task only contains jobs with the maximum of batch_size intervals. (Default: None ) |
int | N |
batch_concurrency |
The maximum number of batches that can run concurrently for this model (Default: the number of concurrent tasks set in the connection settings). | int | N |
lookback |
The number of time unit intervals prior to the current interval that should be processed. (Default: 0 ) |
int | N |
Incremental by time range
Configuration options for INCREMENTAL_BY_TIME_RANGE
models (in addition to general model properties and incremental model properties).
Option | Description | Type | Required |
---|---|---|---|
time_column |
The model column containing each row's timestamp. | str | Y |
format |
Argument to time_column . Format of the time column's data. (Default: %Y-%m-%d ) |
str | N |
forward_only |
Whether the model's changes should always be classified as forward-only. (Default: False ) |
bool | N |
disable_restatement |
Whether restatements should be disabled for the model. (Default: False ) |
bool | N |
Python model configuration object: IncrementalByTimeRangeKind()
Incremental by unique key
Configuration options for INCREMENTAL_BY_UNIQUE_KEY
models (in addition to general model properties and incremental model properties).
Option | Description | Type | Required |
---|---|---|---|
unique_key |
The model column(s) containing each row's unique key. | str | list[str] | Y |
when_matched |
SQL logic used to update columns when a match occurs - only available on engines that support MERGE . (Default: update all columns) |
str | N |
forward_only |
Whether the model's changes should always be classified as forward-only. (Default: False ) |
bool | N |
disable_restatement |
Whether restatements should be disabled for the model. (Default: False ) |
bool | N |
Python model configuration object: IncrementalByUniqueKeyKind()
SCD Type 2 models
Configuration options for SCD_TYPE_2
models (in addition to general model properties and incremental model properties).
Option | Description | Type | Required |
---|---|---|---|
unique_key |
The model column(s) containing each row's unique key. | list[str] | Y |
valid_from_name |
The model column containing each row's valid from date. (Default: valid_from ) |
str | N |
valid_to_name |
The model column containing each row's valid to date. (Default: valid_to ) |
str | N |
invalidate_hard_deletes |
If set to true, when a record is missing from the source table it will be marked as invalid - see here for more information. (Default: True ) |
bool | N |
SCD Type 2 By Time
Configuration options for SCD_TYPE_2_BY_TIME
models (in addition to general model properties, incremental model properties, and SCD Type 2 properties).
Option | Description | Type | Required |
---|---|---|---|
updated_at_name |
The model column containing each row's updated at date. (Default: updated_at ) |
str | N |
updated_at_as_valid_from |
By default, for new rows the valid_from column is set to 1970-01-01 00:00:00. This sets valid_from to the value of updated_at when the row is inserted. (Default: False ) |
bool | N |
SCD Type 2 By Column
Configuration options for SCD_TYPE_2_BY_COLUMN
models (in addition to general model properties, incremental model properties, and SCD Type 2 properties).
Option | Description | Type | Required |
---|---|---|---|
columns |
Columns whose changed data values indicate a data update (instead of an updated_at column). * to represent that all columns should be checked. |
str | array[str] | Y |
execution_time_as_valid_from |
By default, for new rows valid_from is set to 1970-01-01 00:00:00. This changes the behavior to set it to the execution_time of when the pipeline ran. (Default: False ) |
bool | N |
Python model configuration object: SCDType2Kind()
SEED
models
Configuration options for SEED
models. SEED
models do not support all the general properties supported by other models; they only support the properties listed in this table.
Top-level options inside the MODEL DDL:
Option | Description | Type | Required |
---|---|---|---|
name |
The model name. Must include at least a qualifying schema (<schema>.<model> ) and may include a catalog (<catalog>.<schema>.<model> ). If any project model name includes a catalog, all model names must include a catalog or a default catalog must be set. |
str | Y |
kind |
The model kind. Must be SEED . |
str | Y |
columns |
The column names and data types in the CSV file. Disables automatic inference of column names and types by the pandas CSV reader. NOTE: order of columns overrides the order specified in the CSV header row (if present). | array[str] | N |
audits |
SQLMesh audits that should run against the model's output | array[str] | N |
owner |
The owner of a model; may be used for notification purposes | str | N |
stamp |
Arbitrary string used to indicate a model's version without changing the model name | str | N |
tags |
Arbitrary strings used to organize or classify a model | array[str] | N |
description |
Description of the model. Automatically registered in the SQL engine's table COMMENT field or equivalent (if supported by the engine). | str | N |
Options specified within the top-level kind
property:
Option | Description | Type | Required |
---|---|---|---|
path |
Path to seed CSV file. | str | Y |
batch_size |
The maximum number of CSV rows ingested in each batch. All rows ingested in one batch if not specified. | int | N |
csv_settings |
Pandas CSV reader settings (overrides default values). Specified as key-value pairs (key = value ). |
dict | N |
Options specified within the kind
property's csv_settings
property (overrides default Pandas CSV reader settings):
Option | Description | Type | Required |
---|---|---|---|
delimiter |
Character or regex pattern to treat as the delimiter. More information at the Pandas documentation. | str | N |
quotechar |
Character used to denote the start and end of a quoted item. More information at the Pandas documentation. | str | N |
doublequote |
When quotechar is specified, indicate whether or not to interpret two consecutive quotechar elements INSIDE a field as a single quotechar element. More information at the Pandas documentation. | bool | N |
escapechar |
Character used to escape other characters. More information at the Pandas documentation. | str | N |
skipinitialspace |
Skip spaces after delimiter. More information at the Pandas documentation. | bool | N |
lineterminator |
Character used to denote a line break. More information at the Pandas documentation. | str | N |
encoding |
Encoding to use for UTF when reading/writing (ex. 'utf-8'). More information at the Pandas documentation. | str | N |
Python model configuration object: SeedKind()