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


This tutorial shows you how to use [Rudder CLI]({{< ref "dev-tools/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]({{< ref "dev-tools/rudder-cli/sql-models/" >}}).

## Prerequisites

- [Rudder CLI]({{< ref "dev-tools/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]({{< ref "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]({{< ref "access-management/service-access-tokens.md#workspace-sat" >}}) with the following [permissions]({{< ref "access-management/policies-overview.md#resource-permissions" >}}):

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

{{< customreadfile "/includes/rudder-cli/token-auth-footer-admin.md" >}}

## 1. Authenticate the CLI tool

{{< customreadfile "/includes/rudder-cli/auth-login-step.md" >}}

## 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`.
{{< /info >}}

## 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]({{< ref "sources/reverse-etl/" >}}) in the dashboard so warehouse credentials exist for that account.
{{< /info >}}

## 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]({{< ref "dev-tools/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.
{{< /warning >}}

For parameter details and troubleshooting, see [Import existing SQL model resources]({{< ref "dev-tools/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]({{< ref "dev-tools/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]({{< ref "dev-tools/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]({{< ref "dev-tools/rudder-cli/sql-models" >}})
- [Create New SQL Model Resources]({{< ref "dev-tools/rudder-cli/sql-models/create" >}}) — deeper how-to for new resources
- [Import Existing SQL Model Resources]({{< ref "dev-tools/rudder-cli/sql-models/import" >}}) — import parameters and edge cases
- [Validate and Preview Your Models]({{< ref "dev-tools/rudder-cli/sql-models/validate" >}}) — preview parameters, CI/CD notes, error tables
- [SQL Model Resources YAML Reference]({{< ref "dev-tools/rudder-cli/yaml-sql-models" >}})
- [GitHub Actions for Rudder CLI]({{< ref "dev-tools/rudder-cli/github-actions" >}}) for automating validate / apply