Manage SQL Models using Rudder CLI Alpha

Define and manage SQL models as YAML configurations using the Rudder CLI tool.
Available Plans
  • free
  • starter
  • growth
  • enterprise

  •   3 minute read  

info

This feature is in Alpha as part of RudderStack’s Early Access Program, where we work with early users and customers to test new features and get feedback before making them generally available.

Note that these features are functional but can change as we improve them. Make sure to contact the RudderStack team before using them in production.

The SQL Models feature in Rudder CLI lets you manage your Reverse ETL SQL model sources through a Git-based workflow. It lets you store your SQL model configurations as YAML files in Git repositories, and use standard Git workflows to collaborate on any changes.

This approach brings version control, collaboration, and review processes to your SQL model configurations, addressing key limitations of managing these resources solely through the UI.

Key features

Bi-directional management

  • Create new SQL model resources directly from CLI
  • Import existing SQL model sources from your workspace into Git

Validation and preview

  • Validate SQL syntax and connectivity before deployment
  • Preview query results to ensure correctness
  • Check primary key constraints and column mappings

Flexible configuration

  • Define SQL queries inline in YAML files or reference external .sql files
  • Support for multiple warehouse types (listed below)

Automation ready

Supported warehouses

The SQL Models feature supports the following data warehouses:

  • PostgreSQL
  • MySQL
  • Snowflake
  • Amazon Redshift
  • Google BigQuery
  • Databricks
  • Trino

Workflow overview

  1. Configure your Git repository and Rudder CLI project.
  2. Create new SQL models or import existing ones from your workspace.
  3. Make changes to your SQL models using your preferred editor.
  4. Validate your changes using the validate command and apply command in dry-run mode (via the --dry-run flag).
  5. Use the preview command for SQL validation and connection validation.
  6. Apply your changes using the apply command.
  7. Create pull requests for team review and approval.
  8. Automatically sync approved changes to your workspace via GitHub Actions.

Get started

Before you begin, make sure you have:

The SQL model resources need to be in the location specified in the apply command. If you don’t specify a location, the current directory is used as the project location.

info
The apply command is generic and it will sync all the available resources within the project (SQL models, Data Catalog, etc.).

A recommended directory structure for your CLI project is shown:

my-rudder-project/
├── sql-models/
│   ├── user-analytics.yaml
│   ├── product-models.yaml
│   └── sql/
│       ├── user-analytics.sql
│       └── product-views.sql
├── data-catalog/
│   ├── events/
│   │   └── product-events.yaml
│   └── properties/
│       └── user-properties.yaml
└── README.md

Key considerations

  • File discovery: The CLI recursively scans the specified directory for all .yaml and .yml files.
  • Flexible organization: You can organize files in any directory structure that makes sense for your project.
  • External SQL files: When using the file option in your YAML configurations, ensure the SQL files are accessible relative to the YAML file location.
  • Mixed resources: The CLI can manage SQL models alongside Data Catalog resources (events, properties, custom types, Tracking Plans) from the same project directory.

Next steps

See the following guides for working with SQL models using Rudder CLI:

Questions? Contact us by Email or on Slack