# Create New SQL Model Resource using Rudder CLI


This guide details the process of creating a new SQL model resource from scratch using Rudder CLI.

## Prerequisites

- Rudder CLI tool [installed and authenticated]({{< ref "dev-tools/rudder-cli/installation.md#install-rudder-cli" >}}) with a [workspace-level Service Access Token]({{< ref "access-management/service-access-tokens.md#workspace-sat" >}}) or [Personal Access Token]({{< ref "access-management/personal-access-tokens.md" >}}) (for **Free** and **self-hosted** plans) — see [Before you begin]({{< ref "dev-tools/rudder-cli/sql-models/" >}}#before-you-begin) for token requirements
- A Rudder CLI project directory for storing the SQL model YAML resources
- **Important**: At least one [Reverse ETL source]({{< ref "sources/reverse-etl/" >}}) in the RudderStack dashboard using the data warehouse you want to query — RudderStack uses the connection credentials used to set up the warehouse account for this source to fetch the [account ID](#list-reverse-etl-accounts). An example is shown below:

{{< image src="images/retl-sources/postgres-connection-credentials.webp" alt="Connection credentials for PostgreSQL source" >}}

## List Reverse ETL accounts

Before creating a SQL model resource, identify the account ID for the warehouse you want to connect to.

Use the following command to list all accounts linked to your workspace:

```bash
rudder-cli workspace accounts list
```

This command displays all available accounts along with their IDs. Make note of the account ID that corresponds to your target warehouse — you will need this for the resource configuration.

## Create the resource file

Create a new YAML file in your project folder containing the SQL model configuration. This file should have the following structure:

```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 product purchases"
  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
    ORDER BY
      timestamp DESC
```

See the [SQL Model YAML reference]({{< ref "dev-tools/rudder-cli/yaml-sql-models" >}}) for more details on the parameters.

## SQL query options

RudderStack provides two options for defining your SQL query:

### Inline SQL

```yaml
sql: |
  SELECT
    user_id,
    event_name
  FROM
    user_events
```

### External file reference

```yaml
file: "./queries/my-query.sql"
```

{{< warning>}}
You must use exactly one of `sql` or `file` — they are mutually exclusive.
{{< /warning>}}

### Supported sources

The `source_definition` field in the `spec` parameter supports the below warehouses:

| Warehouse | `source_definition` value |
| :------| :-------|
| PostgreSQL | `postgres` |
| MySQL | `mysql` |
| Snowflake | `snowflake` |
| Redshift | `redshift` |
| BigQuery | `bigquery` |
| Databricks | `databricks` |
| Trino | `trino` |

## Apply the SQL model

Once you have created the resource file, use the following commands to validate and create the SQL model:

### Validate configuration

```bash
rudder-cli validate -l path/to/project
```

### Dry run

```bash
rudder-cli apply -l path/to/project --dry-run
```
The above command lists the changes that would be applied without actually making them.

### Apply changes

```bash
rudder-cli apply -l path/to/project
```

After successful application, the SQL model will be created in your workspace and you can manage it through the CLI.

## Update the SQL model

You can modify any field in the YAML file of your existing SQL model — including `source_definition`.

{{< warning>}}
Changing the `id` field will delete the existing resource and create a new one.
{{< /warning >}}

To apply the updates, repeat the [validation and apply commands](#apply-the-sql-model) listed above:

```bash
rudder-cli validate -l path/to/project
rudder-cli apply -l path/to/project --dry-run  # Optional: Preview changes
rudder-cli apply -l path/to/project
```

## Best practices

- **Use external files**: For complex queries, use the [`file` option](#external-file-reference) to keep your SQL in separate `.sql` files for better syntax highlighting and version control.
- **Descriptive IDs**: Choose clear, descriptive IDs that reflect the model's purpose.
- **Test first**: Always run the [validation and dry-run commands](#apply-the-sql-model) before applying any changes.

## Next steps

- [End-to-End Walkthrough: SQL Models with Rudder CLI]({{< ref "dev-tools/rudder-cli/sql-models-walkthrough" >}})
- [Import existing SQL model resources]({{< ref "dev-tools/rudder-cli/sql-models/import" >}})
- [Validate and preview your SQL models]({{< ref "dev-tools/rudder-cli/sql-models/validate" >}})
- [YAML reference]({{< ref "dev-tools/rudder-cli/yaml-sql-models" >}})