# End-to-End Walkthrough: SQL Models with Rudder CLI


> Step-by-step guide to create, import, validate, preview, and apply SQL models with Rudder CLI.



This tutorial shows you how to use [Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/) to:

- Authenticate and prepare a project directory for SQL model YAML
- Resolve the warehouse **account ID** your models use
- **Create** a new SQL model from YAML and sync it to your workspace
- **Import** an existing workspace SQL model into Git (inline SQL or external `.sql` file)
- **Validate** configuration and **preview** query results before you **apply** changes

For feature overview, supported warehouses, and recommended repository layout, see [Manage SQL Models using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/sql-models/).

## Prerequisites

- [Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/) installed locally
- A directory you will use as the CLI project root (this tutorial uses `~/tutorial-sql-models`; replace it with your path)
- At least one [Reverse ETL source](https://www.rudderstack.com/docs/sources/reverse-etl/) in the RudderStack dashboard for the warehouse you query — RudderStack uses that connection to resolve **account IDs** (see [List warehouse accounts](#3-list-warehouse-accounts))
- A [workspace-level Service Access Token](https://www.rudderstack.com/docs/access-management/service-access-tokens/#workspace-sat) with the following [permissions](https://www.rudderstack.com/docs/access-management/policies-overview/#resource-permissions):

| Resource | Permissions |
| :----| :-----|
| SQL Models | **Create & Delete**, **Edit** |

- **If you're on Free or self-hosted plan, or for testing and development only**: Generate a [Personal Access Token](https://www.rudderstack.com/docs/access-management/personal-access-tokens/) with **Read-Write** role
**Warning:** Any action authenticated by a Personal Access Token will break if the user generating the token is removed from the organization or there is a breaking change to their permissions.

**Token permissions for legacy RBAC system**

If you are on the [legacy Permissions Management (RBAC) system](https://www.rudderstack.com/docs/archive/dashboard-guides/user-management/), your workspace-level Service Access Token should have minimum **Admin** permissions.

See [Generate a workspace-level Service Access Token](https://www.rudderstack.com/docs/archive/dashboard-guides/service-access-tokens/#generate-service-access-token) for steps to create the token.

![workspace-level Service Access Token with Admin permission](/docs/images/access-management/permissions/legacy/admin.webp)

## 1. Authenticate the CLI tool

Run the following command and enter your [access token](#prerequisites) when prompted:

```shell
rudder-cli auth login
```

## 2. Create a project directory

Create a folder for YAML and optional SQL files:

```bash
mkdir -p ~/tutorial-sql-models/sql-models/sql
```

**Info:** If you already manage Data Catalog or Tracking Plan YAML in another folder, you can add SQL model files there instead — Rudder CLI discovers `.yaml` / `.yml` files recursively under the path you pass to `-l` / `--location`.

## 3. List warehouse accounts

Each SQL model references an `account_id` for the warehouse connection. List accounts linked to your workspace:

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

Pick the **account ID** that matches your Reverse ETL warehouse setup. You will use it in the YAML `spec` in the next sections.

**Info:** You need a configured [Reverse ETL source](https://www.rudderstack.com/docs/sources/reverse-etl/) in the dashboard so warehouse credentials exist for that account.

## 4. Create a new SQL model

Add a YAML file (for example `~/tutorial-sql-models/sql-models/product-purchases.yaml`) with a `retl-source-sql-model` resource. Replace `account_id` with the value from [List warehouse accounts](#3-list-warehouse-accounts).

```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: "YOUR_ACCOUNT_ID"
  sql: |
    SELECT
      user_id,
      event_name,
      event_type,
      timestamp,
      properties
    FROM
      user_events
    WHERE
      timestamp >= CURRENT_DATE - INTERVAL '180 days'
    ORDER BY
      timestamp DESC
```

**Query shape**

- **Inline SQL**: use `sql: |` as above.
- **External file**: use `file: "./sql/my-query.sql"` instead of `sql` — use **one** of `sql` or `file`, not both.

**`source_definition` values** (examples): `postgres`, `mysql`, `snowflake`, `redshift`, `bigquery`, `databricks`, `trino`. See the [SQL Model YAML reference](https://www.rudderstack.com/docs/rudder-cli/yaml-sql-models/) for the full `spec`.

### Validate and apply (create path)

From your machine (adjust the path):

```bash
rudder-cli validate -l ~/tutorial-sql-models
rudder-cli apply -l ~/tutorial-sql-models --dry-run
rudder-cli apply -l ~/tutorial-sql-models
```

After a successful `apply`, the model exists in the workspace and you can keep editing YAML and re-running validate / apply.

## 5. Import an existing SQL model

Use this path when the model already exists in the workspace and you want it under Git.

### List models in the workspace

```bash
rudder-cli workspace retl-sources list
```

Note the **`source_id`** (remote ID) for the model you want.

### Run import

Pick a **`local_id`** (name for the resource in your project). Replace placeholders with your IDs and paths:

**Inline SQL in YAML (default)**

```bash
rudder-cli import retl-sources --local-id my-imported-model --remote-id YOUR_SOURCE_ID -l ~/tutorial-sql-models
```

**SQL in an external file**

```bash
rudder-cli import retl-sources --local-id my-imported-model --remote-id YOUR_SOURCE_ID -l ~/tutorial-sql-models --sql-location ~/tutorial-sql-models/sql-models/sql/my-imported-model.sql
```

Rudder CLI writes YAML under your project (and a `.sql` file when you use `--sql-location`). The `metadata.import` section maps your `local_id` to the workspace `remote_id` so later `apply` calls stay aligned.

### Finish linking the import

Validate and apply so the CLI project owns the resource:

```bash
rudder-cli validate -l ~/tutorial-sql-models
rudder-cli apply -l ~/tutorial-sql-models --dry-run
rudder-cli apply -l ~/tutorial-sql-models
```

**Warning:** If you use `--sql-location`, keep the YAML and the `.sql` file together in Git; both must be present for validate / apply.

For parameter details and troubleshooting, see [Import existing SQL model resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/import/).

## 6. Validate and preview

### Project-wide validation

Checks YAML structure, references, and related rules for everything under the project path:

```bash
rudder-cli validate -l ~/tutorial-sql-models
```

### Validate a single SQL model

Targets one model by the `spec.id` in your YAML:

```bash
rudder-cli retl-sources validate YOUR_MODEL_ID -l ~/tutorial-sql-models
```

This checks SQL syntax for the warehouse, connectivity to the account, required fields, and that the query can run (limited execution), as described in [Validate SQL model resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/validate/).

### Preview query results

Runs the query and prints sample rows (use your model’s `spec.id`):

```bash
rudder-cli retl-sources preview YOUR_MODEL_ID -l ~/tutorial-sql-models --limit 10
```

Useful flags: `-l` / `--location` for the project root, `--limit` for row cap (default 100), `-j` for JSON output, `--interactive=false` for scripts. See [Validate and preview your models](https://www.rudderstack.com/docs/rudder-cli/sql-models/validate/) for examples and troubleshooting.

### Deploy changes

After you are satisfied:

```bash
rudder-cli apply -l ~/tutorial-sql-models --dry-run
rudder-cli apply -l ~/tutorial-sql-models
```

`apply` syncs SQL models together with any other Rudder CLI resources in the same project directory.

## See more

- [Manage SQL Models using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/sql-models/)
- [Create New SQL Model Resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/create/) — deeper how-to for new resources
- [Import Existing SQL Model Resources](https://www.rudderstack.com/docs/rudder-cli/sql-models/import/) — import parameters and edge cases
- [Validate and Preview Your Models](https://www.rudderstack.com/docs/rudder-cli/sql-models/validate/) — preview parameters, CI/CD notes, error tables
- [SQL Model Resources YAML Reference](https://www.rudderstack.com/docs/rudder-cli/yaml-sql-models/)
- [End-to-End Walkthrough: Reverse ETL Connections with Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/retl-connections-walkthrough/) — sync a SQL model to a destination
- [GitHub Actions for Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/github-actions/) for automating validate / apply