SQL Model Resources YAML Reference Beta

This guide serves as a detailed reference for the CLI project YAML files that contain definitions of your Reverse ETL SQL model resources.

Overview

In the context of the Rudder CLI (rudder-cli) tool, SQL model resources are defined using YAML files that specify the configuration for your Reverse ETL SQL model sources. These YAML files enable you to manage your SQL models through a Git-based workflow with version control and collaboration features.

The location and naming of these YAML files is flexible, as you can store the YAML files anywhere within the project’s root directory or subdirectories.

You can also group multiple SQL model resources in the same file, allowing structures that can best serve your project’s requirements.

The Rudder CLI tool processes all valid YAML files within the project structure to recognize the defined resources.

The following sections detail the specific YAML formats and parameter definitions for SQL model resources.

SQL model resources

You can define one or more SQL model resources in the YAML file by setting kind to retl-source-sql-model.

The spec parameter of the YAML file has the following structure:

You must use exactly one of sql or file — they are mutually exclusive. Use sql for inline queries or file to reference external SQL files.

Supported warehouse types

The source_definition field supports the following data warehouse types:

Example definitions

The following examples highlight the YAML configurations for SQL model resources defined using inline SQL and external SQL files. You can group multiple resources in the same YAML file to manage them together.

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 tracking product purchases over the last 180 days"
  source_definition: "postgres"
  enabled: true
  account_id: "2rzPI1ARibivIvNH5DSpAKGTATy"
  sql: |
    SELECT
      user_id,
      event_name,
      event_type,
      timestamp,
      properties,
      product_id
    FROM
      user_events
    WHERE
      timestamp >= CURRENT_DATE() - INTERVAL 180 DAY
      AND event_name = 'Product Purchased'
    ORDER BY
      timestamp DESC    

version: "rudder/v1"
kind: "retl-source-sql-model"
metadata:
  name: "user-analytics-model"
spec:
  id: "user-analytics-model"
  display_name: "User Analytics Model"
  primary_key: "user_id"
  description: "Comprehensive user analytics model"
  source_definition: "snowflake"
  enabled: true
  account_id: "2rzPI1ARibivIvNH5DSpAKGTATy"
  file: "./queries/user-analytics.sql"

version: "rudder/v1"
kind: "retl-source-sql-model"
metadata:
  name: "ecommerce-models"
spec:
  - id: "product-views-model"
    display_name: "Product Views Model"
    primary_key: "user_id"
    description: "Model for tracking product page views"
    source_definition: "bigquery"
    enabled: true
    account_id: "2rzPI1ARibivIvNH5DSpAKGTATy"
    sql: |
      SELECT
        user_id,
        product_id,
        view_timestamp,
        session_id
      FROM
        product_views
      WHERE
        view_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)      
  - id: "cart-abandonment-model"
    display_name: "Cart Abandonment Model"
    primary_key: "session_id"
    description: "Model for identifying cart abandonment events"
    source_definition: "bigquery"
    enabled: true
    account_id: "2rzPI1ARibivIvNH5DSpAKGTATy"
    file: "./queries/cart-abandonment.sql"

Validation rules

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model
spec:
  id: churn-model
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model
spec:
  id: churn-model
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: churn-model-duplicate
spec:
  id: churn-model-duplicate
  display_name: Churn Risk Model
  account_id: acc-456
  primary_key: user_id
  source_definition: bigquery
  sql: SELECT user_id FROM churn_signals

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: revenue-model
spec:
  id: revenue-model
  display_name: Revenue Model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, amount FROM revenue

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: revenue-model
spec:
  id: revenue-model
  display_name: Revenue Model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, amount FROM revenue

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: revenue-model-copy
spec:
  id: revenue-model-copy
  display_name: Revenue Model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, amount FROM revenue

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: snowflake
  sql: SELECT id, name FROM orders

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  primary_key: id
  source_definition: bigquery
  sql: SELECT id, name FROM orders

version: rudder/v1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id FROM orders
  file: ./query.sql

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, name FROM orders

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  account_id: acc-123
  primary_key: id
  source_definition: postgres
  sql: SELECT id, name FROM orders

version: rudder/0.1
kind: retl-source-sql-model
metadata:
  name: user-orders-model
spec:
  id: user-orders-model
  display_name: User Orders
  account_id: acc-123
  primary_key: id
  source_definition: oracle
  sql: SELECT id FROM orders

Best practices

When defining SQL model resources in YAML files, follow these best practices:

File organization

• Logical grouping: Group related SQL models in the same YAML file when they share similar purposes or data sources.
• Clear naming: Use descriptive file names that reflect the models’ purpose, such as user-analytics-models.yaml or ecommerce-models.yaml.
• Directory structure: Organize SQL files in subdirectories (like ./queries/ or ./sql/) to keep your project structure clean.

Resource configuration

• Unique IDs: Choose clear, descriptive IDs that reflect the model’s purpose and avoid conflicts.
• Meaningful names: Use display names that clearly describe what the model does for dashboard users.
• Documentation: Always include descriptions to help team members understand the model’s purpose.
• External files: For complex queries, use the file option to keep SQL in separate files for better syntax highlighting and version control. When importing resources, use the --sql-location parameter if you prefer external SQL files.

Environment management

• Variable substitution: Substitute account_id values across environments with How to Use Variable Substitution in Rudder CLI.
• Environment-specific configurations: Maintain separate configuration files or use templating for environment-specific settings.

Query optimization

• Primary key selection: Choose primary keys that are unique, stable, and efficiently indexed in your warehouse.
• Performance considerations: Write efficient queries that minimize warehouse compute costs and execution time.
• Data freshness: Include appropriate date filters to control data volume and ensure relevance.

See also

• Create New SQL Model Resources
• Import Existing SQL Model Resources
• Validate and Preview Your Models
• Manage SQL Models using Rudder CLI for feature overview
• End-to-End Walkthrough: SQL Models with Rudder CLI for an end-to-end tutorial
• How to Use Variable Substitution in Rudder CLI for account_id substitution