# SQL Model Resources YAML Reference > Complete reference for defining your SQL model resources using YAML configuration files. This guide serves as a detailed reference for the CLI project YAML files that contain definitions of your Reverse ETL SQL model resources. ## Overview In the context of the Rudder CLI (`rudder-cli`) tool, SQL model resources are defined using YAML files that specify the configuration for your [Reverse ETL SQL model sources](https://www.rudderstack.com/docs/activate/models/). These YAML files enable you to manage your SQL models through a Git-based workflow with version control and collaboration features. The location and naming of these YAML files is flexible, as you can store the YAML files anywhere within the project's root directory or subdirectories. **Success** You can also group multiple SQL model resources in the same file, allowing structures that can best serve your project's requirements. The Rudder CLI tool processes all valid YAML files within the project structure to recognize the defined resources. The following sections detail the specific YAML formats and parameter definitions for SQL model resources. ## SQL model resources You can define one or more SQL model resources in the YAML file by setting `kind` to `retl-source-sql-model`. The `spec` parameter of the YAML file has the following structure: | Property | Type | Description | | :----| :-----| :------| | `id`
Required | String | Unique identifier for the SQL model resource within the project. This parameter must be unique across all SQL model resources. | | `display_name`
Required | String | Display name for the SQL model resource as shown in the RudderStack dashboard. | | `primary_key`
Required | String | Column name to use as the primary key for the model. This column must exist in your SQL query results. | | `source_definition`
Required | String | Warehouse type identifier.

See [Supported warehouse types](#supported-warehouse-types) for values that you can use. | | `enabled`
Required | Boolean | Whether the SQL model resource is active. Set to `true` to enable the resource. | | `account_id`
Required | String | The account ID for the data warehouse connection. You can obtain this using `rudder-cli workspace accounts list`. | | `description` | String | Optional description for the SQL model resource. | | `sql` | String | Inline SQL query definition.

Use the YAML literal block scalar `|` for multi-line queries. | | `file` | String | Path to an external SQL file containing the query. Path is relative to the YAML file location. | **Warning:** You must use exactly one of `sql` or `file` — they are mutually exclusive. Use `sql` for inline queries or `file` to reference external SQL files. ### Supported warehouse types The `source_definition` field supports the following data warehouse types: | Warehouse | `source_definition` value | | :------| :-------| | PostgreSQL | `postgres` | | MySQL | `mysql` | | Snowflake | `snowflake` | | Amazon Redshift | `redshift` | | Google BigQuery | `bigquery` | | Databricks | `databricks` | | Trino | `trino` | ### Example definitions The following examples highlight the YAML configurations for SQL model resources defined using inline SQL and external SQL files. You can group multiple resources in the same YAML file to manage them together. #### Inline SQL ```yaml version: rudder/v1 kind: "retl-source-sql-model" metadata: name: "product-purchases-model" spec: id: "product-purchases-model" display_name: "Product Purchases SQL Model" primary_key: "user_id" description: "SQL model for tracking product purchases over the last 180 days" source_definition: "postgres" enabled: true account_id: "2rzPI1ARibivIvNH5DSpAKGTATy" sql: | SELECT user_id, event_name, event_type, timestamp, properties, product_id FROM user_events WHERE timestamp >= CURRENT_DATE() - INTERVAL 180 DAY AND event_name = 'Product Purchased' ORDER BY timestamp DESC ``` #### External SQL file ```yaml version: rudder/v1 kind: "retl-source-sql-model" metadata: name: "user-analytics-model" spec: id: "user-analytics-model" display_name: "User Analytics Model" primary_key: "user_id" description: "Comprehensive user analytics model" source_definition: "snowflake" enabled: true account_id: "2rzPI1ARibivIvNH5DSpAKGTATy" file: "./queries/user-analytics.sql" ``` In this example, the SQL query would be stored in a separate file at `./queries/user-analytics.sql` relative to the YAML file location. #### With multiple resources ```yaml version: rudder/v1 kind: "retl-source-sql-model" metadata: name: "ecommerce-models" spec: - id: "product-views-model" display_name: "Product Views Model" primary_key: "user_id" description: "Model for tracking product page views" source_definition: "bigquery" enabled: true account_id: "2rzPI1ARibivIvNH5DSpAKGTATy" sql: | SELECT user_id, product_id, view_timestamp, session_id FROM product_views WHERE view_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) - id: "cart-abandonment-model" display_name: "Cart Abandonment Model" primary_key: "session_id" description: "Model for identifying cart abandonment events" source_definition: "bigquery" enabled: true account_id: "2rzPI1ARibivIvNH5DSpAKGTATy" file: "./queries/cart-abandonment.sql" ``` ### Validation rules
Spec version
Filter by phase

Showing 5 of 5 rules

URNs must be unique across the project

syntactic errorAll spec versions

Rule ID: project/duplicate-urn

Examples

Distinct URNs across files

Each spec declares a different resource, so URNs do not collide.

a.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events-a
spec:
  events:
    - id: order_completed
      name: Order Completed
b.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events-b
spec:
  events:
    - id: cart_viewed
      name: Cart Viewed

Same URN declared in two files

Both files declare the same event ID, which resolves to one URN. The rule flags each occurrence.

a.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events-a
spec:
  events:
    - id: order_completed
      name: Order Completed
b.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events-b
spec:
  events:
    - id: order_completed
      name: Order Completed (duplicate)
Diagnostics
  • a.yaml /spec/events/0/id duplicate URN 'event:order_completed'
  • b.yaml /spec/events/0/id duplicate URN 'event:order_completed'

A (workspace_id, urn) must not be defined in both an import-manifest and inline metadata.import with differing remote_ids

syntactic errorAll spec versions

Rule ID: project/manifest-inline-conflict

Examples

Manifest and inline metadata import distinct resources

One resource is imported via the manifest, a different one via inline metadata. No URN appears in both, so the project is valid.

events.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events
  import:
    workspaces:
      - workspace_id: "ws-1"
        resources:
          - urn: "event:cart_viewed"
            remote_id: "remote_b"
spec:
  events:
    - id: order_completed
      name: Order Completed
    - id: cart_viewed
      name: Cart Viewed
import-manifest.yaml yaml 
version: rudder/v1
kind: import-manifest
metadata:
  name: import-manifest
spec:
  workspaces:
    - workspace_id: "ws-1"
      resources:
        - urn: "event:order_completed"
          remote_id: "remote_a"

Same URN imported by both a manifest and inline metadata

The URN event:order_completed is declared in both the manifest and an inline metadata.import block — an ambiguous double import. The rule flags both locations; remove one.

events.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: events
  import:
    workspaces:
      - workspace_id: "ws-1"
        resources:
          - urn: "event:order_completed"
            remote_id: "remote_b"
spec:
  events:
    - id: order_completed
      name: Order Completed
import-manifest.yaml yaml 
version: rudder/v1
kind: import-manifest
metadata:
  name: import-manifest
spec:
  workspaces:
    - workspace_id: "ws-1"
      resources:
        - urn: "event:order_completed"
          remote_id: "remote_a"
Diagnostics
  • import-manifest.yaml /spec/workspaces/0/resources/0/urn URN 'event:order_completed' is defined in both an import-manifest and inline metadata
  • events.yaml /metadata/import/workspaces/0/resources/0/urn URN 'event:order_completed' is defined in both an import-manifest and inline metadata

Manifest URN overlaps a legacy rudder/0.1 inline local_id import

The events spec uses the legacy rudder/0.1 format, whose inline imports reference resources by local_id + remote_id. The local_id order_completed resolves to the URN event:order_completed — the same URN the manifest imports. Legacy specs are still subject to the conflict rule, so both locations are flagged; remove the inline metadata entry.

events.yaml yaml 
version: rudder/0.1
kind: events
metadata:
  name: events
  import:
    workspaces:
      - workspace_id: "ws-1"
        resources:
          - local_id: order_completed
            remote_id: "remote_b"
spec:
  events:
    - id: order_completed
      name: Order Completed
import-manifest.yaml yaml 
version: rudder/v1
kind: import-manifest
metadata:
  name: import-manifest
spec:
  workspaces:
    - workspace_id: "ws-1"
      resources:
        - urn: "event:order_completed"
          remote_id: "remote_a"
Diagnostics
  • import-manifest.yaml /spec/workspaces/0/resources/0/urn URN 'event:order_completed' is defined in both an import-manifest and inline metadata
  • events.yaml /metadata/import/workspaces/0/resources/0/urn URN 'event:order_completed' is defined in both an import-manifest and inline metadata

Metadata syntax must be valid

syntactic errorAll spec versions

Rule ID: project/metadata-syntax-valid

Examples

Metadata with only the required name

Minimal valid metadata: a name only.

spec.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: my-events
spec:
  events: []

Metadata with a well-formed import block

An import block where each workspace declares workspace_id and each resource references a URN in the spec body.

spec.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: my-events
  import:
    workspaces:
      - workspace_id: ws-123
        resources:
          - urn: "event:order_completed"
            remote_id: ev-remote-456
spec:
  events:
    - id: order_completed
      name: Order Completed

Metadata missing required name

Every spec metadata block must declare a name.

spec.yaml yaml 
version: rudder/v1
kind: events
metadata:
  import:
    workspaces:
      - workspace_id: ws-123
spec:
  events: []
Diagnostics
  • spec.yaml /metadata/name 'name' is required

Import workspace missing required workspace_id

Each import workspace entry must declare a workspace_id.

spec.yaml yaml 
version: rudder/v1
kind: events
metadata:
  name: my-events
  import:
    workspaces:
      - resources:
          - urn: "event:order_completed"
            remote_id: ev-remote-456
spec:
  events:
    - id: order_completed
      name: Order Completed
Diagnostics
  • spec.yaml /metadata/import/workspaces/0/workspace_id 'workspace_id' is required

RETL SQL model semantic constraints must be satisfied

semantic error

Rule ID: retl/sqlmodel/semantic-valid

Examples

Valid SQL model with unique display_name

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model
spec:
  id: churn-model
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals

v1 SQL model with duplicate display_name

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model-duplicate
spec:
  id: churn-model-duplicate
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals
Diagnostics
  • spec.yaml /display_name duplicate display_name 'Churn Risk Model' within kind 'retl-source-sql-model'

V1 SQL models whose display_names differ only in case (names compare case-insensitively)

churn-lowercase.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model-lowercase
spec:
  id: churn-model-lowercase
  display_name: churn risk model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals
churn.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model
spec:
  id: churn-model
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals
Diagnostics
  • churn.yaml /display_name duplicate display_name 'Churn Risk Model' (case-insensitive match with 'churn risk model') within kind 'retl-source-sql-model'
  • churn-lowercase.yaml /display_name duplicate display_name 'churn risk model' (case-insensitive match with 'Churn Risk Model') within kind 'retl-source-sql-model'

V1 SQL model referencing an account that cannot back its source_definition

account.yaml yaml 
version: rudder/v1
kind: account
metadata:
  name: prod-pg
spec:
  id: prod-pg
  name: Production Postgres
  account_definition_name: SOURCE_POSTGRES
  config:
    host: db.internal
    dbname: analytics
    user: rudder
    port: "5432"
    password: "{{ .PG_PASSWORD }}"
spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model
spec:
  id: churn-model
  display_name: Churn Risk Model
  account: "#account:prod-pg"
  primary_key: user_id
  source_definition: snowflake
  sql: SELECT user_id FROM churn_signals
Diagnostics
  • spec.yaml /account account 'prod-pg' is a 'postgres' account (SOURCE_POSTGRES) and cannot back source_definition 'snowflake'

RETL SQL model semantic constraints must be satisfied

semantic error

Rule ID: retl/sqlmodel/semantic-valid

Examples

Valid legacy SQL model with unique display_name

spec.yaml yaml 
version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: revenue-model
spec:
  id: revenue-model
  display_name: Revenue Model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, amount FROM revenue

Legacy SQL model with duplicate display_name

spec.yaml yaml 
version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: revenue-model-copy
spec:
  id: revenue-model-copy
  display_name: Revenue Model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, amount FROM revenue
Diagnostics
  • spec.yaml /display_name duplicate display_name 'Revenue Model' within kind 'retl-source-sql-model'

RETL SQL model spec syntax must be valid

syntactic error

Rule ID: retl/sqlmodel/spec-syntax-valid

Examples

Valid SQL model spec with inline SQL

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: snowflake
  sql: SELECT id, name FROM orders

Valid v1 SQL model spec referencing an account in the project instead of an account_id

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account: "#account:prod-snowflake"
  primary_key: id
  source_definition: snowflake
  sql: SELECT id, name FROM orders

v1 SQL model missing required account_id

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  primary_key: id
  source_definition: bigquery
  sql: SELECT id, name FROM orders
Diagnostics
  • spec.yaml /account_id 'account_id' is required when 'account' is not specified

V1 SQL model specifying both account_id and account (mutually exclusive)

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  account: "#account:prod-snowflake"
  primary_key: id
  source_definition: snowflake
  sql: SELECT id, name FROM orders
Diagnostics
  • spec.yaml /account_id 'account_id' and 'account' cannot be specified together

v1 SQL model specifies both sql and file

spec.yaml yaml 
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id FROM orders
  file: ./query.sql
Diagnostics
  • spec.yaml /sql 'sql' and 'file' cannot be specified together

RETL SQL model spec syntax must be valid

syntactic error

Rule ID: retl/sqlmodel/spec-syntax-valid

Examples

Valid legacy SQL model spec with inline SQL

spec.yaml yaml 
version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, name FROM orders

Legacy SQL model missing required display_name

spec.yaml yaml 
version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, name FROM orders
Diagnostics
  • spec.yaml /display_name 'display_name' is required

Legacy SQL model with unsupported source_definition

spec.yaml yaml 
version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: oracle
  sql: SELECT id FROM orders
Diagnostics
  • spec.yaml /source_definition 'source_definition' must be one of [postgres redshift snowflake bigquery mysql databricks trino]
## Best practices When defining SQL model resources in YAML files, follow these best practices: ### File organization - **Logical grouping**: Group related SQL models in the same YAML file when they share similar purposes or data sources. - **Clear naming**: Use descriptive file names that reflect the models' purpose, such as `user-analytics-models.yaml` or `ecommerce-models.yaml`. - **Directory structure**: Organize SQL files in subdirectories (like `./queries/` or `./sql/`) to keep your project structure clean. ### Resource configuration - **Unique IDs**: Choose clear, descriptive IDs that reflect the model's purpose and avoid conflicts. - **Meaningful names**: Use display names that clearly describe what the model does for dashboard users. - **Documentation**: Always include descriptions to help team members understand the model's purpose. - **External files**: For complex queries, use the `file` option to keep SQL in separate files for better syntax highlighting and version control. When importing resources, use the `--sql-location` parameter if you prefer external SQL files. ### Environment management - **Variable substitution**: Substitute `account_id` values across environments with [How to Use Variable Substitution in Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/variable-substitution/). - **Environment-specific configurations**: Maintain separate configuration files or use templating for environment-specific settings. ### Query optimization - **Primary key selection**: Choose primary keys that are unique, stable, and efficiently indexed in your warehouse. - **Performance considerations**: Write efficient queries that minimize warehouse compute costs and execution time. - **Data freshness**: Include appropriate date filters to control data volume and ensure relevance. ## See also - [Create New SQL Model Resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/create/) - [Import Existing SQL Model Resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/import/) - [Validate and Preview Your Models](https://www.rudderstack.com/docs/rudder-cli/sql-models/validate/) - [Manage SQL Models using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/sql-models/) for feature overview - [End-to-End Walkthrough: SQL Models with Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/sql-models-walkthrough/) for an end-to-end tutorial - [How to Use Variable Substitution in Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/variable-substitution/) for `account_id` substitution - [Reverse ETL Connection YAML Reference](https://www.rudderstack.com/docs/rudder-cli/yaml-retl-connections/) for connecting a SQL model to a destination