Manage Reverse ETL Table Sources using Rudder CLI

A table source is a Reverse ETL source that reads rows from a warehouse table, or from files under an S3 bucket prefix, without a SQL query. Rudder CLI manages table sources as YAML next to your SQL models, accounts, destinations, and connections.

Rudder CLI v0.27.0 or later can manage table source resources. The rudder-cli retl-sources validate and rudder-cli retl-sources preview commands support table sources from v0.28.0.

In v0.28.0 and earlier, table sources are experimental: set RUDDERSTACK_CLI_EXPERIMENTAL=true and RUDDERSTACK_X_RETL_TABLE_SUPPORT=true before you validate, apply, or import them. From v0.29.0, table sources are on by default and these two settings have no effect.

Use kind: retl-source-table. See the Table Source Resources YAML Reference for every field.

Supported source types

Table sources support PostgreSQL, Amazon Redshift, Snowflake, Google BigQuery, MySQL, Databricks, Trino, and S3.

Before you begin

• Install and authenticate Rudder CLI.
• Have a CLI project directory.
• Create an account for the source to read through. Rudder CLI can define BigQuery, PostgreSQL, and Snowflake accounts in the project. For Redshift, MySQL, Databricks, Trino, and S3, create the account in your workspace first and set account_id in the table source spec. Run rudder-cli workspace accounts list to get an existing account’s ID.
• Use a workspace-level Service Access Token that can create, edit, and delete Reverse ETL sources.

Define a table source

Create a YAML file in your project, for example users-table.yaml:

version: rudder/v1
kind: retl-source-table
metadata:
  name: users-table
spec:
  id: users-table
  display_name: Users
  account: "#account:prod-pg"
  source_definition: postgres
  primary_key: id
  schema: public
  table: users

To use an account that already exists in your workspace, replace account with account_id.

For an S3 source, remove account and set account_id to an existing S3 account’s ID. Set source_definition: s3 and replace primary_key, schema, and table with bucket_name and object_prefix. S3 sources have no primary key.

Validate the project

rudder-cli validate -l path/to/project

Validation runs locally and doesn’t read your warehouse. It catches missing or conflicting fields, a duplicate display_name, and an account reference whose type doesn’t match source_definition. See Validation rules for the messages.

From v0.28.0, you can check one table source:

rudder-cli retl-sources validate users-table -l path/to/project

This command takes the source id and runs the same project checks. It doesn’t run a query.

Apply the table source

rudder-cli apply -l path/to/project --dry-run
rudder-cli apply -l path/to/project

Run the dry run first to see the plan. If your project defines the account that the table source references, Rudder CLI creates the account before the source.

Preview the rows

Previewing a table source requires Rudder CLI v0.28.0 or later and an account that already exists in the workspace. If your spec uses account: "#account:prod-pg" as in the example above, apply the project first if prod-pg hasn’t been created in the workspace. To preview before applying, use account_id for an existing account instead.

rudder-cli retl-sources preview users-table -l path/to/project --limit 5

For a warehouse table source, Rudder CLI runs a bounded query on your warehouse and prints the rows. For example, it runs select * from "public"."users" limit 5 for the PostgreSQL source above. It quotes schema and table names with double quotes for PostgreSQL, Redshift, Snowflake, and Trino, and backticks for MySQL and Databricks. For BigQuery, it quotes the dataset.table path with backticks. Quoting preserves the case you set in the spec. The result confirms the account can read the table. The command accepts the same --limit, --json, and --interactive options as it does for SQL models.

S3 sources have no query, so preview isn’t available for them.

Connect the source to a destination

Reference the source from a Reverse ETL connection with #retl-source-table:<id>:

source: "#retl-source-table:users-table"

See the Reverse ETL Connections YAML Reference for the rest of the connection fields.

Import existing table sources

To bring table sources created in the dashboard under Git, run:

rudder-cli import workspace -l path/to/project

Import writes one file per source under imported/retl/tables/, for example imported/retl/tables/users-table.yaml. Where an account is already managed by the CLI, the file references it with account, and otherwise it sets account_id. See How to Import Workspace Resources.

rudder-cli import retl-sources imports SQL models only. Use import workspace for table sources.

To link a table source you already wrote locally to the existing remote source instead of creating a duplicate, use import workspace --merge. Enable experimental mode and the separate importMerge flag first:

export RUDDERSTACK_CLI_EXPERIMENTAL=true
export RUDDERSTACK_X_IMPORT_MERGE=true
rudder-cli import workspace -l path/to/project --merge

In v0.28.0 and earlier, also set RUDDERSTACK_X_RETL_TABLE_SUPPORT=true. Keep importMerge enabled when you validate or apply the generated import manifest. Matching compares display_name and account_id exactly, so a spec that uses account isn’t linked by --merge. Set account_id in the spec for the merge, then switch to account.

Rudder CLI requires object_prefix for S3 sources. If an S3 source in your workspace has an empty prefix, the imported spec fails validate until you set one.

Limits

• source_definition can’t change after the source is created.
• schema and table can’t change while a destination is connected to the source.
• Table sources use version: rudder/v1.
• A Reverse ETL source created from an Audience isn’t supported.

See also

• Table Source Resources YAML Reference
• Manage SQL Models using Rudder CLI
• Manage Reverse ETL Connections using Rudder CLI
• Account Resources YAML Reference