# End-to-End Walkthrough: Reverse ETL Connections with Rudder CLI


> Step-by-step tutorial to connect a Reverse ETL SQL model to a destination with Rudder CLI.



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

- Put a SQL model, a destination, and a Reverse ETL connection in one project
- Validate the graph locally, then apply it so Rudder CLI creates resources in dependency order
- Confirm the first sync in the RudderStack dashboard

For replace behavior and current limits, see [Manage Reverse ETL Connections using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/retl-connections/).

## Prerequisites

- Rudder CLI tool (`rudder-cli`) [installed locally](https://www.rudderstack.com/docs/rudder-cli/installation/), **v0.27.0 or later**. In v0.28.0 and earlier, also set `RUDDERSTACK_CLI_EXPERIMENTAL=true` and `RUDDERSTACK_X_RETL_CONNECTION_SUPPORT=true`. From v0.29.0, neither setting is required for Reverse ETL connections.
- A warehouse account in your workspace for the SQL model to query. Setting up a [Reverse ETL source](https://www.rudderstack.com/docs/sources/reverse-etl/) in the RudderStack dashboard creates one. You can also [define an account](https://www.rudderstack.com/docs/rudder-cli/yaml-accounts/) with Rudder CLI.
- An HTTP endpoint you own that can receive the synced records.
- 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**, **Connect** |
| Destinations | **Create & Delete**, **Edit**, **Connect** |

- **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

```shell
mkdir ~/tutorial-retl-connections
```

**Warning:** `apply` deletes every CLI-managed resource in the workspace that the project doesn't contain. Use this new project only with a workspace that has no CLI-managed resources. If a CLI project already manages your workspace, add the three files from this tutorial to that project instead. See [One project per workspace](https://www.rudderstack.com/docs/rudder-cli/import-resources/#one-project-per-workspace).

## 3. Define the source and destination

List the warehouse accounts in your workspace and note the **account ID** of the warehouse you want to query:

```shell
rudder-cli workspace accounts list
```

Create `~/tutorial-retl-connections/users-model.yaml`. Replace `YOUR_ACCOUNT_ID` with the account ID, set `source_definition` to your warehouse type, and replace the query with one that runs on your warehouse:

```yaml
version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: users-model
spec:
  id: users-model
  display_name: "Users Model"
  description: "Users to sync to the webhook endpoint"
  primary_key: user_id
  source_definition: postgres
  enabled: true
  account_id: "YOUR_ACCOUNT_ID"
  sql: |
    SELECT
      user_id,
      email
    FROM
      users
```

Create `~/tutorial-retl-connections/http-destination.yaml`. Replace `api_url` with your endpoint:

```yaml
version: rudder/v1
kind: destination
metadata:
  name: my-http-destination
spec:
  id: my-http-destination
  display_name: "Users Webhook"
  type: http
  definition_version: 1
  enabled: true
  config:
    api_url: https://api.example.com/v1/users
    auth: noAuth
    method: POST
    format: JSON
    connection_mode:
      warehouse: cloud
```

The `connection_mode` entry for `warehouse` is what lets a Reverse ETL source connect to this destination. Without it, `validate` rejects the connection.

See [SQL Model Resources YAML Reference](https://www.rudderstack.com/docs/rudder-cli/yaml-sql-models/) and [HTTP Webhook Destination Config Reference](https://www.rudderstack.com/docs/rudder-cli/destination-types/http-webhook/) for field details.

## 4. Define the connection

Create `~/tutorial-retl-connections/retl-connections.yaml`:

```yaml
version: rudder/v1
kind: retl-connections
metadata:
  name: retl-connections
spec:
  connections:
    - id: users-to-webhook
      source: "#retl-source-sql-model:users-model"
      destination: "#destination:my-http-destination"
      enabled: true
      config:
        sync_behaviour: upsert
        schedule:
          type: basic
          every_minutes: 30
        identifiers:
          - from: user_id
            to: user_id
        mappings:
          - from: email
            to: traits.email
        event:
          type: identify
```

The `source` and `destination` values must be valid references to resources in this project. The `config` block holds the sync configuration:

- `sync_behaviour: upsert` syncs only the rows that are new or changed since the last sync.
- `schedule` runs a sync every 30 minutes.
- `identifiers` maps the `user_id` column to the event's `user_id`.
- `mappings` sends the `email` column as the `email` trait.
- `event` sends each row as an `identify` event.

The `from` values and the model's `primary_key` must be columns your query returns. Rudder CLI doesn't check this.

HTTP Webhook doesn't support object mapping, so this connection runs the JSON mapper flow. See [Mapping flows](https://www.rudderstack.com/docs/rudder-cli/yaml-retl-connections/#mapping-flows) for the difference.

## 5. Validate and deploy

Validate the project and preview the plan:

```shell
rudder-cli validate -l ~/tutorial-retl-connections
rudder-cli apply -l ~/tutorial-retl-connections --dry-run
```

The plan must list three **New resources** — the SQL model, the destination, and the connection — and no **Removed resources**. If it lists any removed resources, don't apply: Rudder CLI would delete them.

Then, apply:

```shell
rudder-cli apply -l ~/tutorial-retl-connections
```

Apply creates the SQL model and the destination first, then the connection. The connection is enabled, so RudderStack starts syncing on the schedule you set. The first sync is a [full sync](https://www.rudderstack.com/docs/data-pipelines/reverse-etl/developer-guides/sync-modes/#first-sync): it sends every row the model returns, whatever the `sync_behaviour`.

## 6. Verify the sync

Rudder CLI doesn't start syncs or report their status, so check the first run in the RudderStack dashboard:

1. Go to the **Syncs** tab of the connection. To run a sync without waiting for the schedule, click [**Sync Now**](https://www.rudderstack.com/docs/data-pipelines/reverse-etl/developer-guides/start-stop-syncs/).
2. When the run finishes, check its status and any [failed records](https://www.rudderstack.com/docs/data-pipelines/reverse-etl/developer-guides/sync-observability/#failed-records).
3. Confirm that your HTTP endpoint received a record.

To change the connection, edit the YAML and apply again. Adding or changing a mapping triggers a [full sync](https://www.rudderstack.com/docs/data-pipelines/reverse-etl/developer-guides/sync-modes/#after-updating-mappings). Some fields can't change after creation; see [Apply and replace behavior](https://www.rudderstack.com/docs/rudder-cli/retl-connections/#apply-and-replace-behavior).

## See more

- [Manage Reverse ETL Connections using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/retl-connections/)
- [Reverse ETL Connection YAML Reference](https://www.rudderstack.com/docs/rudder-cli/yaml-retl-connections/)
- [Manage SQL Models using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/sql-models/)
- [Manage Destinations using Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/destinations/)
- [GitHub Actions for Rudder CLI](https://www.rudderstack.com/docs/rudder-cli/github-actions/)